SQLite
View Ticket
Not logged in
Ticket UUID: e20dd54ab0e4383805167564bd2c6df5aa2ad01c
Title: COLLATE sequence for ORDER/GROUP BY ignored when using an index-on-expression
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-08-18 13:25:46
Version Found In:
User Comments:
dan added on 2017-08-18 07:14:45: (text/x-fossil-wiki)
With 3.20.0, this script:

<verbatim>
  CREATE TABLE t2(x);
  INSERT INTO t2 VALUES('.ABC');
  INSERT INTO t2 VALUES('.abcd');
  INSERT INTO t2 VALUES('.DEF');

  SELECT substr(x, 2) FROM t2 ORDER BY substr(x, 2) COLLATE nocase;
  .print ------
  CREATE INDEX i2 ON t2( substr(x, 2) );
  SELECT substr(x, 2) FROM t2 ORDER BY substr(x, 2) COLLATE nocase;
</verbatim>

returns:

<verbatim>
  ABC
  abcd
  DEF
  ------
  ABC
  DEF
  abcd
</verbatim>

The problem is that in the second query SQLite uses the index to optimize the ORDER BY clause, even though the collation sequence of the index does not match that of the ORDER BY expression.

dan added on 2017-08-18 10:27:01: (text/x-fossil-wiki)
This problem has been present since 3.9.0 - when the indexes-on-expressions feature was added to SQLite.