SQLite

View Ticket
Login
Ticket Hash: fb8c538a8f57ae2a69540a293141dcc9dfb832b5
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
Status: Fixed Type: Code_Defect
Severity: Minor Priority: Low
Subsystem: Unknown Resolution: Fixed
Last Modified: 2020-02-12 13:02:45
Version Found In: 3.31.0
User Comments:
dan added on 2020-02-12 06:21:39: (text/x-fossil-wiki)
Example:

<verbatim>
  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;
</verbatim>

drh added on 2020-02-12 11:18:29: (text/x-fossil-wiki)
This problem appears to have been introduced by the
[https://www.sqlite.org/queryplanner-ng.html|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.