Hello,

I have a problem with some tables, which seem to have to do with the collation...

1. case:
========
Database with UTF8 as character set has no default collation, collumns have UNICODE as collation:

CREATE TABLE POSTALCODES
(
ID INTEGER NOT NULL COLLATE UNICODE,
COUNTRY VARCHAR( 3) NOT NULL COLLATE UNICODE,
ZIPCODE VARCHAR( 30) NOT NULL COLLATE UNICODE,
CITY VARCHAR( 50) NOT NULL COLLATE UNICODE,
REGION SMALLINT
);

ALTER TABLE POSTALCODES ADD CONSTRAINT PK_POSTALCODES PRIMARY KEY(ID);
CREATE GENERATOR POSTALCODES_PRIMARYKEY;

CREATE ASC INDEX SYS_POSTALCODES_ZIPCODE ON POSTALCODES (ZIPCODE);
CREATE ASC INDEX SYS_POSTALCODES_CITY ON POSTALCODES (CITY);



2. case:
Database with UTF8 as character set has UNICODE_CI_AI as default collation, collumns have no specific collation:
CREATE TABLE POSTALCODES
(
ID INTEGER NOT NULL,
COUNTRY VARCHAR( 3) NOT NULL,
ZIPCODE VARCHAR( 30) NOT NULL,
CITY VARCHAR( 50) NOT NULL,
REGION SMALLINT
);

ALTER TABLE POSTALCODES ADD CONSTRAINT PK_POSTALCODES PRIMARY KEY(ID);
CREATE GENERATOR POSTALCODES_PRIMARYKEY;

CREATE ASC INDEX SYS_POSTALCODES_ZIPCODE ON POSTALCODES (ZIPCODE);
CREATE ASC INDEX SYS_POSTALCODES_CITY ON POSTALCODES (CITY);


The following query (search string can contain letters too theoretically)

select ID, COUNTRY, ZIPCODE, CITY, REGION
from POSTALCODES
where (ID > 0) and (upper(ZIPCODE) like '12345%')

is being executed immediately in the first case, but takes almost 2 minutes in the second case...

If I remove the UPPER part, it is being executed immediately.
If I specify UNICODE as a differing collation for the ZIPCODE column, it is being executed immediately as well.


What is wrong with the second case and how can I fix it?

Kind regards,
Patrick