Results returned in a case insensitive database [CR 472049]

In some cases, an optimization in Sybase IQ 12.7 causes the results of a comparison returned in a case insensitive database to be different from the results returned in previous releases of IQ. You cannot expect case sensitive results in a database that is case insensitive (CASE IGNORE). This has always been true for Sybase IQ.

Character strings inserted into tables are always stored in the case they are entered, regardless of whether the database is case sensitive or not. If the string Value is inserted into a character data type column, the string is always stored in the database with an uppercase V and the remainder of the letters lowercase. SELECT statements return the string as Value. If the database is not case sensitive, however, all comparisons make Value the same as value, VALUE, and so on. The IQ server may return results in any combination of lowercase and uppercase, so you cannot expect case sensitive results in a database that is case insensitive.

For example, given the following table and data:

CREATE TABLE tb (id int NOT NULL,
                 string VARCHAR(30) NOT NULL);
INSERT INTO tb VALUES (1, ‘ONE’);
SELECT * FROM tb WHERE string = ‘oNe’;

The result of the SELECT can be ‘oNe’ (as specified in the WHERE clause) and not necessarily ‘ONE’ (as stored in the database).

Similarly, the result of

SELECT * FROM tb WHERE string = ‘One’;

can be ‘One’ and the result of

SELECT * FROM tb WHERE string = ‘ONe’;

can be ‘ONe’.