SQLite

View Ticket
Login
Plaintext
2013-05-08
16:58 • Fixed ticket [6709574d]: COLLATE clause in ORDER BY causes incorrect EXCEPT plus 5 other changes (artifact: 63278902 user: drh)
16:57
Make sure the ORDER BY collating sequences are compatible with the comparison collations before using the merge algorithm for compound SELECT statements. Fix for ticket [6709574d2a8d8]. (check-in: ce853a75 user: drh tags: trunk)
2013-05-07
17:49
Make sure the ORDER BY collating sequences are compatible with the comparison collations before using the merge algorithm for compound SELECT statements. Candidate fix for ticket [6709574d2a8d8]. (Closed-Leaf check-in: fc3630cd user: drh tags: tkt-6709574)
2013-05-06
15:21 • New ticket [6709574d] COLLATE clause in ORDER BY causes incorrect EXCEPT. (artifact: 5bbf9ac1 user: drh)
Ticket Hash: 6709574d2a8d8b9be3a9cb1afbf4ff2de48ea4e7
Title: COLLATE clause in ORDER BY causes incorrect EXCEPT
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2013-05-08 16:58:10
Version Found In: 3.7.16.2
User Comments:
drh added on 2013-05-06 15:21:16:

In the code shown below (which is intended to be run from the sqlite3.exe command-line tool) the three SELECT statements should all generate the same answer. But the third one does not. It is as if the COLLATE clause on the ORDER BY somehow got pulled into the EXCEPT operator. Note that the ".print" commands are instructions to the sqlite3.exe shell program to output delimiter lines so that you can more easily tell where the output of one query ends and the next query begins. 

CREATE TABLE t1(a);
INSERT INTO t1 VALUES('abc'),('def');
CREATE TABLE t2(a);
INSERT INTO t2 VALUES('DEF');

SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
.print -----
SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
 ORDER BY a COLLATE nocase;
.print -----
SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;

Bisecting shows that this problem was introduced in SQLite version 3.6.0 by check-in [8bbfa97837a74ef] on 2008-06-15.