SQLite

With 3.20.0, this script:

  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;

returns:

  ABC
  abcd
  DEF
  ------
  ABC
  DEF
  abcd

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.

This problem has been present since 3.9.0 - when the indexes-on-expressions feature was added to SQLite.