xref: /sqlite-3.40.0/test/selectE.test (revision 45f31be8) 
1# 2013-05-07
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#***********************************************************************
11# This file implements regression tests for compound SELECT statements
12# that have ORDER BY clauses with collating sequences that differ
13# from the collating sequence used for comparison in the compound.
14#
15# Ticket 6709574d2a8d8b9be3a9cb1afbf4ff2de48ea4e7:
16# drh added on 2013-05-06 15:21:16:
17#
18# In the code shown below (which is intended to be run from the
19# sqlite3.exe command-line tool) the three SELECT statements should all
20# generate the same answer. But the third one does not. It is as if the
21# COLLATE clause on the ORDER BY somehow got pulled into the EXCEPT
22# operator. Note that the ".print" commands are instructions to the
23# sqlite3.exe shell program to output delimiter lines so that you can more
24# easily tell where the output of one query ends and the next query
25# begins.
26#
27#     CREATE TABLE t1(a);
28#     INSERT INTO t1 VALUES('abc'),('def');
29#     CREATE TABLE t2(a);
30#     INSERT INTO t2 VALUES('DEF');
31#
32#     SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
33#     .print -----
34#     SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
35#      ORDER BY a COLLATE nocase;
36#     .print -----
37#     SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
38#
39# Bisecting shows that this problem was introduced in SQLite version 3.6.0
40# by check-in [8bbfa97837a74ef] on 2008-06-15.
41#
42
43set testdir [file dirname $argv0]
44source $testdir/tester.tcl
45
46do_test selectE-1.0 {
47  db eval {
48    CREATE TABLE t1(a);
49    INSERT INTO t1 VALUES('abc'),('def'),('ghi');
50    CREATE TABLE t2(a);
51    INSERT INTO t2 VALUES('DEF'),('abc');
52    CREATE TABLE t3(a);
53    INSERT INTO t3 VALUES('def'),('jkl');
54
55    SELECT a FROM t1 EXCEPT SELECT a FROM t2
56     ORDER BY a COLLATE nocase;
57  }
58} {def ghi}
59do_test selectE-1.1 {
60  db eval {
61    SELECT a FROM t2 EXCEPT SELECT a FROM t3
62     ORDER BY a COLLATE nocase;
63  }
64} {abc DEF}
65do_test selectE-1.2 {
66  db eval {
67    SELECT a FROM t2 EXCEPT SELECT a FROM t3
68     ORDER BY a COLLATE binary;
69  }
70} {DEF abc}
71do_test selectE-1.3 {
72  db eval {
73    SELECT a FROM t2 EXCEPT SELECT a FROM t3
74     ORDER BY a;
75  }
76} {DEF abc}
77
78do_test selectE-2.1 {
79  db eval {
80    DELETE FROM t2;
81    DELETE FROM t3;
82    INSERT INTO t2 VALUES('ABC'),('def'),('GHI'),('jkl');
83    INSERT INTO t3 SELECT lower(a) FROM t2;
84    SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3
85     ORDER BY 1
86  }
87} {}
88do_test selectE-2.2 {
89  db eval {
90    SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3
91     ORDER BY 1 COLLATE binary
92  }
93} {}
94
95do_catchsql_test selectE-3.1 {
96  SELECT 1 EXCEPT SELECT 2 ORDER BY 1 COLLATE nocase EXCEPT SELECT 3;
97} {1 {ORDER BY clause should come after EXCEPT not before}}
98
99
100finish_test
101