|15:02||Ensure that indexed expressions with collating sequences are handled correctly. Fix for ticket [eb703ba7b50c1a5] backported from trunk. (check-in: b2e49ae3 user: drh tags: branch-3.17)|
|14:58||• Fixed ticket [eb703ba7]: Incorrect result using index on expression with collating function plus 5 other changes (artifact: d0eb9fbf user: drh)|
|13:51||Ensure that indexed expressions with collating sequences are handled correctly. Proposed fix for ticket [eb703ba7b50c1a5]. (check-in: 9689d04b user: drh tags: trunk)|
|04:07||• New ticket [eb703ba7] Incorrect result using index on expression with collating function. (artifact: d008a3ff user: drh)|
|Title:||Incorrect result using index on expression with collating function|
|Last Modified:||2017-02-11 14:58:42|
|Version Found In:||3.17.0|
drh added on 2017-02-11 04:07:11:
In the following SQL, the second query - the one after the CREATE INDEX - gets an incorrect answer:
CREATE TABLE t1(a INTEGER PRIMARY KEY, b); INSERT INTO t1 VALUES(1,'coffee'),(2,'COFFEE'),(3,'stress'),(4,'STRESS'); SELECT '1:', a FROM t1 WHERE substr(b,4)='ess' COLLATE nocase; CREATE INDEX t1b ON t1(substr(b,4)); SELECT '2:', a FROM t1 WHERE substr(b,4)='ess' COLLATE nocase;
The problem appears to be that the index is being used even though it uses a different collation.
This problem was discovered during internal analysis and has never been observed in the wild - probably because nobody has ever though to create an index on an expression with non-BINARY collation.