SQLite
View Ticket
Not logged in
Ticket UUID: eb703ba7b50c1a520aeccc64d164c4fc1a0fa408
Title: Incorrect result using index on expression with collating function
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-02-11 14:58:42
Version Found In: 3.17.0
User Comments:
drh added on 2017-02-11 04:07:11: (text/x-fossil-wiki)
In the following SQL, the second query - the one after the CREATE INDEX -
gets an incorrect answer:

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

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.