|Title:||Incorrect sorting when a column uses BINARY collation in the ORDER BY, but is constrained by an == with a different collation in the WHERE clause|
|Last Modified:||2020-02-12 13:02:45|
|Version Found In:||3.31.0|
dan added on 2020-02-12 06:21:39:
CREATE TABLE t1(b COLLATE nocase); INSERT INTO t1 VALUES('abc'); INSERT INTO t1 VALUES('ABC'); INSERT INTO t1 VALUES('aBC'); -- correctly returns: "ABC aBC abc" SELECT * FROM t1 ORDER BY b COLLATE binary; -- incorrectly omits external sort and returns: "abc ABC aBC" SELECT * FROM t1 WHERE b='abc' ORDER BY b COLLATE binary;
drh added on 2020-02-12 11:18:29:
This problem appears to have been introduced by the Next Generation Query Planner change of version 3.8.0, and specifically check-in [0fe31f60cadc5fe5] (2013-06-21). This problem was found in the wild and was brought to the attention of the developers via a private email message.