/ View Ticket
Login
Ticket UUID: 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:

Example:

  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.