Skip to content

Wrong select result in case of special sort character. #7140

@LMasak

Description

@LMasak

Originally discovered with Polish collation but same problem for Czech. Tested with FB3, FB4 and ICU4.2, ICU 5.2, ICU 6.3

To get wrong result you must meet all of the following conditions:

  • test column that has a sort assigned with a special sort for some characters
  • use the less "<" operator
  • use a character with a special order, eg in Czech it is an "c" alphabetical order a, b, c, č, d
  • combine it with statement "or value is null"

create collation test_cz for UTF8 FROM UNICODE CASE INSENSITIVE ACCENT SENSITIVE 'LOCALE=cs_CZ';
CREATE TABLE TBL_TEST
( C1 VARCHAR(50) collate test_cz
);
CREATE INDEX IDX_c1 ON TBL_TEST (C1);
CREATE DESCENDING INDEX IDX_c1_d ON TBL_TEST (C1);

INSERT INTO TBL_TEST (C1) VALUES ('aaa');
INSERT INTO TBL_TEST (C1) VALUES ('bbb');
INSERT INTO TBL_TEST (C1) VALUES ('ccc');
INSERT INTO TBL_TEST (C1) VALUES ('ddd');
INSERT INTO TBL_TEST (C1) VALUES (NULL);

select * from TBL_TEST where c1 < 'b' or c1 is null order by c1 desc
result:
1 aaa
2 [null]

select * from TBL_TEST where c1 < 'c' or c1 is null order by c1 desc
result:
1 [null]

select * from TBL_TEST where c1 < 'd' or c1 is null order by c1 desc
result:
1 ccc
2 bbb
3 aaa
4 [null]

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions