Debian Codegraph @

test/orderby5.test

raw
1 # 2013-06-14
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 SQLite library. The
12 # focus of this file is testing that the optimizations that disable
13 # ORDER BY clauses work correctly
14 #
15
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19 set ::testprefix orderby5
20
21 # Generate test data for a join. Verify that the join gets the
22 # correct answer.
23 #
24 do_execsql_test 1.1 {
25 CREATE TABLE t1(a,b,c);
26 CREATE INDEX t1bc ON t1(b,c);
27
28 EXPLAIN QUERY PLAN
29 SELECT DISTINCT a, b, c FROM t1 WHERE a=0;
30 } {~/B-TREE/}
31 do_execsql_test 1.2.1 {
32 EXPLAIN QUERY PLAN
33 SELECT DISTINCT a, c, b FROM t1 WHERE a=0;
34 } {~/B-TREE/}
35 do_execsql_test 1.2.2 {
36 EXPLAIN QUERY PLAN
37 SELECT DISTINCT a, c, b FROM t1 WHERE a='xyz' COLLATE nocase;
38 } {/B-TREE/}
39 do_execsql_test 1.2.3 {
40 EXPLAIN QUERY PLAN
41 SELECT DISTINCT a COLLATE nocase, c, b FROM t1 WHERE a='xyz';
42 } {/B-TREE/}
43 do_execsql_test 1.2.4 {
44 EXPLAIN QUERY PLAN
45 SELECT DISTINCT a COLLATE nocase, c, b FROM t1 WHERE a='xyz' COLLATE nocase;
46 } {~/B-TREE/}
47 do_execsql_test 1.3 {
48 EXPLAIN QUERY PLAN
49 SELECT DISTINCT b, a, c FROM t1 WHERE a=0;
50 } {~/B-TREE/}
51 do_execsql_test 1.4 {
52 EXPLAIN QUERY PLAN
53 SELECT DISTINCT b, c, a FROM t1 WHERE a=0;
54 } {~/B-TREE/}
55 do_execsql_test 1.5 {
56 EXPLAIN QUERY PLAN
57 SELECT DISTINCT c, a, b FROM t1 WHERE a=0;
58 } {~/B-TREE/}
59 do_execsql_test 1.6 {
60 EXPLAIN QUERY PLAN
61 SELECT DISTINCT c, b, a FROM t1 WHERE a=0;
62 } {~/B-TREE/}
63 do_execsql_test 1.7 {
64 EXPLAIN QUERY PLAN
65 SELECT DISTINCT c, b, a FROM t1 WHERE +a=0;
66 } {/B-TREE/}
67
68 # In some cases, it is faster to do repeated index lookups than it is to
69 # sort. But in other cases, it is faster to sort than to do repeated index
70 # lookups.
71 #
72 do_execsql_test 2.1a {
73 CREATE TABLE t2(a,b,c);
74 CREATE INDEX t2bc ON t2(b,c);
75 ANALYZE;
76 INSERT INTO sqlite_stat1 VALUES('t1','t1bc','1000000 10 9');
77 INSERT INTO sqlite_stat1 VALUES('t2','t2bc','100 10 5');
78 ANALYZE sqlite_master;
79
80 EXPLAIN QUERY PLAN
81 SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c;
82 } {~/B-TREE/}
83
84 do_execsql_test 2.1b {
85 EXPLAIN QUERY PLAN
86 SELECT * FROM t1 WHERE likelihood(a=0, 0.03) ORDER BY a, b, c;
87 } {/B-TREE/}
88
89 do_execsql_test 2.2 {
90 EXPLAIN QUERY PLAN
91 SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c;
92 } {/B-TREE/}
93 do_execsql_test 2.3 {
94 EXPLAIN QUERY PLAN
95 SELECT * FROM t1 WHERE a=0 ORDER BY b, a, c;
96 } {~/B-TREE/}
97 do_execsql_test 2.4 {
98 EXPLAIN QUERY PLAN
99 SELECT * FROM t1 WHERE a=0 ORDER BY b, c, a;
100 } {~/B-TREE/}
101 do_execsql_test 2.5 {
102 EXPLAIN QUERY PLAN
103 SELECT * FROM t1 WHERE a=0 ORDER BY a, c, b;
104 } {/B-TREE/}
105 do_execsql_test 2.6 {
106 EXPLAIN QUERY PLAN
107 SELECT * FROM t1 WHERE a=0 ORDER BY c, a, b;
108 } {/B-TREE/}
109 do_execsql_test 2.7 {
110 EXPLAIN QUERY PLAN
111 SELECT * FROM t1 WHERE a=0 ORDER BY c, b, a;
112 } {/B-TREE/}
113
114
115 do_execsql_test 3.0 {
116 CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f);
117 CREATE INDEX t3bcde ON t3(b, c, d, e);
118 EXPLAIN QUERY PLAN
119 SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC;
120 } {~/B-TREE/}
121 do_execsql_test 3.1 {
122 DROP TABLE t3;
123 CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f) WITHOUT rowid;
124 CREATE INDEX t3bcde ON t3(b, c, d, e);
125 EXPLAIN QUERY PLAN
126 SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC;
127 } {~/B-TREE/}
128
129 #-------------------------------------------------------------------------
130 do_execsql_test 4.1.0 {
131 CREATE TABLE t4(b COLLATE nocase);
132 INSERT INTO t4 VALUES('abc');
133 INSERT INTO t4 VALUES('ABC');
134 INSERT INTO t4 VALUES('aBC');
135 }
136 do_execsql_test 4.1.1 {
137 SELECT * FROM t4 ORDER BY b COLLATE binary
138 } {ABC aBC abc}
139 do_execsql_test 4.1.2 {
140 SELECT * FROM t4 WHERE b='abc' ORDER BY b COLLATE binary
141 } {ABC aBC abc}
142
143 do_execsql_test 4.2.1 {
144 CREATE TABLE Records(typeID INTEGER, key TEXT COLLATE nocase, value TEXT);
145 CREATE INDEX RecordsIndex ON Records(typeID, key, value);
146 }
147 do_execsql_test 4.2.2 {
148 explain query plan
149 SELECT typeID, key, value FROM Records
150 WHERE typeID = 2 AND key = 'x'
151 ORDER BY key, value;
152 } {~/TEMP B-TREE/}
153 do_execsql_test 4.2.3 {
154 explain query plan
155 SELECT typeID, key, value FROM Records
156 WHERE typeID = 2 AND (key = 'x' COLLATE binary)
157 ORDER BY key, value;
158 } {~/TEMP B-TREE/}
159 do_execsql_test 4.2.4 {
160 explain query plan
161 SELECT typeID, key, value FROM Records
162 WHERE typeID = 2
163 ORDER BY key, value;
164 } {~/TEMP B-TREE/}
165
166 db collate hello [list string match]
167 do_execsql_test 4.3.1 {
168 CREATE TABLE t5(a INTEGER PRIMARY KEY, b COLLATE hello, c, d);
169 }
170 db close
171 sqlite3 db test.db
172 do_catchsql_test 4.3.2 {
173 SELECT a FROM t5 WHERE b='def' ORDER BY b;
174 } {1 {no such collation sequence: hello}}
175
176 # 2020-02-13 ticket 41c1456a6e61c0e7
177 do_execsql_test 4.4.0 {
178 DROP TABLE t1;
179 CREATE TABLE t1(a);
180 DROP TABLE t2;
181 CREATE TABLE t2(b INTEGER PRIMARY KEY, c INT);
182 SELECT DISTINCT *
183 FROM t1 LEFT JOIN t2 ON b=c AND b=(SELECT a FROM t1)
184 WHERE c>10;
185 } {}
186
187 finish_test