|
1 # 2008 June 24 |
|
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. |
|
12 # |
|
13 # $Id: select9.test,v 1.4 2008/07/01 14:39:35 danielk1977 Exp $ |
|
14 |
|
15 # The tests in this file are focused on test compound SELECT statements |
|
16 # that have any or all of an ORDER BY, LIMIT or OFFSET clauses. As of |
|
17 # version 3.6.0, SQLite contains code to use SQL indexes where possible |
|
18 # to optimize such statements. |
|
19 # |
|
20 |
|
21 # TODO Points: |
|
22 # |
|
23 # * Are there any "column affinity" issues to consider? |
|
24 |
|
25 set testdir [file dirname $argv0] |
|
26 source $testdir/tester.tcl |
|
27 |
|
28 #set ISQUICK 1 |
|
29 |
|
30 #------------------------------------------------------------------------- |
|
31 # test_compound_select TESTNAME SELECT RESULT |
|
32 # |
|
33 # This command is used to run multiple LIMIT/OFFSET test cases based on |
|
34 # the single SELECT statement passed as the second argument. The SELECT |
|
35 # statement may not contain a LIMIT or OFFSET clause. This proc tests |
|
36 # many statements of the form: |
|
37 # |
|
38 # "$SELECT limit $X offset $Y" |
|
39 # |
|
40 # for various values of $X and $Y. |
|
41 # |
|
42 # The third argument, $RESULT, should contain the expected result of |
|
43 # the command [execsql $SELECT]. |
|
44 # |
|
45 # The first argument, $TESTNAME, is used as the base test case name to |
|
46 # pass to [do_test] for each individual LIMIT OFFSET test case. |
|
47 # |
|
48 proc test_compound_select {testname sql result} { |
|
49 |
|
50 set nCol 1 |
|
51 db eval $sql A { |
|
52 set nCol [llength $A(*)] |
|
53 break |
|
54 } |
|
55 set nRow [expr {[llength $result] / $nCol}] |
|
56 |
|
57 set ::compound_sql $sql |
|
58 do_test $testname { |
|
59 execsql $::compound_sql |
|
60 } $result |
|
61 #return |
|
62 |
|
63 set iLimitIncr 1 |
|
64 set iOffsetIncr 1 |
|
65 if {[info exists ::ISQUICK] && $::ISQUICK && $nRow>=5} { |
|
66 set iOffsetIncr [expr $nRow / 5] |
|
67 set iLimitIncr [expr $nRow / 5] |
|
68 } |
|
69 |
|
70 set iLimitEnd [expr $nRow+$iLimitIncr] |
|
71 set iOffsetEnd [expr $nRow+$iOffsetIncr] |
|
72 |
|
73 for {set iOffset 0} {$iOffset < $iOffsetEnd} {incr iOffset $iOffsetIncr} { |
|
74 for {set iLimit 0} {$iLimit < $iLimitEnd} {incr iLimit} { |
|
75 |
|
76 set ::compound_sql "$sql LIMIT $iLimit" |
|
77 if {$iOffset != 0} { |
|
78 append ::compound_sql " OFFSET $iOffset" |
|
79 } |
|
80 |
|
81 set iStart [expr {$iOffset*$nCol}] |
|
82 set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}] |
|
83 |
|
84 do_test $testname.limit=$iLimit.offset=$iOffset { |
|
85 execsql $::compound_sql |
|
86 } [lrange $result $iStart $iEnd] |
|
87 } |
|
88 } |
|
89 } |
|
90 |
|
91 #------------------------------------------------------------------------- |
|
92 # test_compound_select_flippable TESTNAME SELECT RESULT |
|
93 # |
|
94 # This command is for testing statements of the form: |
|
95 # |
|
96 # <simple select 1> <compound op> <simple select 2> ORDER BY <order by> |
|
97 # |
|
98 # where each <simple select> is a simple (non-compound) select statement |
|
99 # and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION". |
|
100 # |
|
101 # This proc calls [test_compound_select] twice, once with the select |
|
102 # statement as it is passed to this command, and once with the positions |
|
103 # of <select statement 1> and <select statement 2> exchanged. |
|
104 # |
|
105 proc test_compound_select_flippable {testname sql result} { |
|
106 test_compound_select $testname $sql $result |
|
107 |
|
108 set select [string trim $sql] |
|
109 set RE {(.*)(UNION ALL|INTERSECT|UNION)(.*)(ORDER BY.*)} |
|
110 set rc [regexp $RE $select -> s1 op s2 order_by] |
|
111 if {!$rc} {error "Statement is unflippable: $select"} |
|
112 |
|
113 set flipsql "$s2 $op $s1 $order_by" |
|
114 test_compound_select $testname.flipped $flipsql $result |
|
115 } |
|
116 |
|
117 ############################################################################# |
|
118 # Begin tests. |
|
119 # |
|
120 |
|
121 # Create and populate a sample database. |
|
122 # |
|
123 do_test select9-1.0 { |
|
124 execsql { |
|
125 CREATE TABLE t1(a, b, c); |
|
126 CREATE TABLE t2(d, e, f); |
|
127 BEGIN; |
|
128 INSERT INTO t1 VALUES(1, 'one', 'I'); |
|
129 INSERT INTO t1 VALUES(3, NULL, NULL); |
|
130 INSERT INTO t1 VALUES(5, 'five', 'V'); |
|
131 INSERT INTO t1 VALUES(7, 'seven', 'VII'); |
|
132 INSERT INTO t1 VALUES(9, NULL, NULL); |
|
133 INSERT INTO t1 VALUES(2, 'two', 'II'); |
|
134 INSERT INTO t1 VALUES(4, 'four', 'IV'); |
|
135 INSERT INTO t1 VALUES(6, NULL, NULL); |
|
136 INSERT INTO t1 VALUES(8, 'eight', 'VIII'); |
|
137 INSERT INTO t1 VALUES(10, 'ten', 'X'); |
|
138 |
|
139 INSERT INTO t2 VALUES(1, 'two', 'IV'); |
|
140 INSERT INTO t2 VALUES(2, 'four', 'VIII'); |
|
141 INSERT INTO t2 VALUES(3, NULL, NULL); |
|
142 INSERT INTO t2 VALUES(4, 'eight', 'XVI'); |
|
143 INSERT INTO t2 VALUES(5, 'ten', 'XX'); |
|
144 INSERT INTO t2 VALUES(6, NULL, NULL); |
|
145 INSERT INTO t2 VALUES(7, 'fourteen', 'XXVIII'); |
|
146 INSERT INTO t2 VALUES(8, 'sixteen', 'XXXII'); |
|
147 INSERT INTO t2 VALUES(9, NULL, NULL); |
|
148 INSERT INTO t2 VALUES(10, 'twenty', 'XL'); |
|
149 |
|
150 COMMIT; |
|
151 } |
|
152 } {} |
|
153 |
|
154 # Each iteration of this loop runs the same tests with a different set |
|
155 # of indexes present within the database schema. The data returned by |
|
156 # the compound SELECT statements in the test cases should be the same |
|
157 # in each case. |
|
158 # |
|
159 set iOuterLoop 1 |
|
160 foreach indexes [list { |
|
161 /* Do not create any indexes. */ |
|
162 } { |
|
163 CREATE INDEX i1 ON t1(a) |
|
164 } { |
|
165 CREATE INDEX i2 ON t1(b) |
|
166 } { |
|
167 CREATE INDEX i3 ON t2(d) |
|
168 } { |
|
169 CREATE INDEX i4 ON t2(e) |
|
170 }] { |
|
171 |
|
172 do_test select9-1.$iOuterLoop.1 { |
|
173 execsql $indexes |
|
174 } {} |
|
175 |
|
176 # Test some 2-way UNION ALL queries. No WHERE clauses. |
|
177 # |
|
178 test_compound_select select9-1.$iOuterLoop.2 { |
|
179 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 |
|
180 } {1 one 3 {} 5 five 7 seven 9 {} 2 two 4 four 6 {} 8 eight 10 ten 1 two 2 four 3 {} 4 eight 5 ten 6 {} 7 fourteen 8 sixteen 9 {} 10 twenty} |
|
181 test_compound_select select9-1.$iOuterLoop.3 { |
|
182 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1 |
|
183 } {1 one 1 two 2 two 2 four 3 {} 3 {} 4 four 4 eight 5 five 5 ten 6 {} 6 {} 7 seven 7 fourteen 8 eight 8 sixteen 9 {} 9 {} 10 ten 10 twenty} |
|
184 test_compound_select select9-1.$iOuterLoop.4 { |
|
185 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2 |
|
186 } {3 {} 9 {} 6 {} 3 {} 6 {} 9 {} 8 eight 4 eight 5 five 4 four 2 four 7 fourteen 1 one 7 seven 8 sixteen 10 ten 5 ten 10 twenty 2 two 1 two} |
|
187 test_compound_select_flippable select9-1.$iOuterLoop.5 { |
|
188 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1, 2 |
|
189 } {1 one 1 two 2 four 2 two 3 {} 3 {} 4 eight 4 four 5 five 5 ten 6 {} 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 9 {} 10 ten 10 twenty} |
|
190 test_compound_select_flippable select9-1.$iOuterLoop.6 { |
|
191 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2, 1 |
|
192 } {3 {} 3 {} 6 {} 6 {} 9 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two} |
|
193 |
|
194 # Test some 2-way UNION queries. |
|
195 # |
|
196 test_compound_select select9-1.$iOuterLoop.7 { |
|
197 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 |
|
198 } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty} |
|
199 |
|
200 test_compound_select select9-1.$iOuterLoop.8 { |
|
201 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1 |
|
202 } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty} |
|
203 |
|
204 test_compound_select select9-1.$iOuterLoop.9 { |
|
205 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2 |
|
206 } {3 {} 6 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two} |
|
207 |
|
208 test_compound_select_flippable select9-1.$iOuterLoop.10 { |
|
209 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1, 2 |
|
210 } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty} |
|
211 |
|
212 test_compound_select_flippable select9-1.$iOuterLoop.11 { |
|
213 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2, 1 |
|
214 } {3 {} 6 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two} |
|
215 |
|
216 # Test some 2-way INTERSECT queries. |
|
217 # |
|
218 test_compound_select select9-1.$iOuterLoop.11 { |
|
219 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 |
|
220 } {3 {} 6 {} 9 {}} |
|
221 test_compound_select_flippable select9-1.$iOuterLoop.12 { |
|
222 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1 |
|
223 } {3 {} 6 {} 9 {}} |
|
224 test_compound_select select9-1.$iOuterLoop.13 { |
|
225 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2 |
|
226 } {3 {} 6 {} 9 {}} |
|
227 test_compound_select_flippable select9-1.$iOuterLoop.14 { |
|
228 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2, 1 |
|
229 } {3 {} 6 {} 9 {}} |
|
230 test_compound_select_flippable select9-1.$iOuterLoop.15 { |
|
231 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1, 2 |
|
232 } {3 {} 6 {} 9 {}} |
|
233 |
|
234 # Test some 2-way EXCEPT queries. |
|
235 # |
|
236 test_compound_select select9-1.$iOuterLoop.16 { |
|
237 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 |
|
238 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} |
|
239 |
|
240 test_compound_select select9-1.$iOuterLoop.17 { |
|
241 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1 |
|
242 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} |
|
243 |
|
244 test_compound_select select9-1.$iOuterLoop.18 { |
|
245 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2 |
|
246 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two} |
|
247 |
|
248 test_compound_select select9-1.$iOuterLoop.19 { |
|
249 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1, 2 |
|
250 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} |
|
251 |
|
252 test_compound_select select9-1.$iOuterLoop.20 { |
|
253 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2, 1 |
|
254 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two} |
|
255 |
|
256 incr iOuterLoop |
|
257 } |
|
258 |
|
259 do_test select9-2.0 { |
|
260 execsql { |
|
261 DROP INDEX i1; |
|
262 DROP INDEX i2; |
|
263 DROP INDEX i3; |
|
264 DROP INDEX i4; |
|
265 } |
|
266 } {} |
|
267 |
|
268 proc reverse {lhs rhs} { |
|
269 return [string compare $rhs $lhs] |
|
270 } |
|
271 db collate reverse reverse |
|
272 |
|
273 # This loop is similar to the previous one (test cases select9-1.*) |
|
274 # except that the simple select statements have WHERE clauses attached |
|
275 # to them. Sometimes the WHERE clause may be satisfied using the same |
|
276 # index used for ORDER BY, sometimes not. |
|
277 # |
|
278 set iOuterLoop 1 |
|
279 foreach indexes [list { |
|
280 /* Do not create any indexes. */ |
|
281 } { |
|
282 CREATE INDEX i1 ON t1(a) |
|
283 } { |
|
284 DROP INDEX i1; |
|
285 CREATE INDEX i1 ON t1(b, a) |
|
286 } { |
|
287 CREATE INDEX i2 ON t2(d DESC, e COLLATE REVERSE ASC); |
|
288 } { |
|
289 CREATE INDEX i3 ON t1(a DESC); |
|
290 }] { |
|
291 do_test select9-2.$iOuterLoop.1 { |
|
292 execsql $indexes |
|
293 } {} |
|
294 |
|
295 test_compound_select_flippable select9-2.$iOuterLoop.2 { |
|
296 SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 1 |
|
297 } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL} |
|
298 |
|
299 test_compound_select_flippable select9-2.$iOuterLoop.2 { |
|
300 SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1 |
|
301 } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II} |
|
302 |
|
303 test_compound_select_flippable select9-2.$iOuterLoop.3 { |
|
304 SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 |
|
305 ORDER BY 2 COLLATE reverse, 1 |
|
306 } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV} |
|
307 |
|
308 test_compound_select_flippable select9-2.$iOuterLoop.4 { |
|
309 SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 1 |
|
310 } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL} |
|
311 |
|
312 test_compound_select_flippable select9-2.$iOuterLoop.5 { |
|
313 SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1 |
|
314 } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II} |
|
315 |
|
316 test_compound_select_flippable select9-2.$iOuterLoop.6 { |
|
317 SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 |
|
318 ORDER BY 2 COLLATE reverse, 1 |
|
319 } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV} |
|
320 |
|
321 test_compound_select select9-2.$iOuterLoop.4 { |
|
322 SELECT a FROM t1 WHERE a<8 EXCEPT SELECT d FROM t2 WHERE d<=3 ORDER BY 1 |
|
323 } {4 5 6 7} |
|
324 |
|
325 test_compound_select select9-2.$iOuterLoop.4 { |
|
326 SELECT a FROM t1 WHERE a<8 INTERSECT SELECT d FROM t2 WHERE d<=3 ORDER BY 1 |
|
327 } {1 2 3} |
|
328 |
|
329 } |
|
330 |
|
331 do_test select9-2.X { |
|
332 execsql { |
|
333 DROP INDEX i1; |
|
334 DROP INDEX i2; |
|
335 DROP INDEX i3; |
|
336 } |
|
337 } {} |
|
338 |
|
339 # This procedure executes the SQL. Then it checks the generated program |
|
340 # for the SQL and appends a "nosort" to the result if the program contains the |
|
341 # SortCallback opcode. If the program does not contain the SortCallback |
|
342 # opcode it appends "sort" |
|
343 # |
|
344 proc cksort {sql} { |
|
345 set ::sqlite_sort_count 0 |
|
346 set data [execsql $sql] |
|
347 if {$::sqlite_sort_count} {set x sort} {set x nosort} |
|
348 lappend data $x |
|
349 return $data |
|
350 } |
|
351 |
|
352 # If the right indexes exist, the following query: |
|
353 # |
|
354 # SELECT t1.a FROM t1 UNION ALL SELECT t2.d FROM t2 ORDER BY 1 |
|
355 # |
|
356 # can use indexes to run without doing a in-memory sort operation. |
|
357 # This block of tests (select9-3.*) is used to check if the same |
|
358 # is possible with: |
|
359 # |
|
360 # CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 |
|
361 # SELECT a FROM v1 ORDER BY 1 |
|
362 # |
|
363 # It turns out that it is. |
|
364 # |
|
365 do_test select9-3.1 { |
|
366 cksort { SELECT a FROM t1 ORDER BY 1 } |
|
367 } {1 2 3 4 5 6 7 8 9 10 sort} |
|
368 do_test select9-3.2 { |
|
369 execsql { CREATE INDEX i1 ON t1(a) } |
|
370 cksort { SELECT a FROM t1 ORDER BY 1 } |
|
371 } {1 2 3 4 5 6 7 8 9 10 nosort} |
|
372 do_test select9-3.3 { |
|
373 cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 } |
|
374 } {1 1 2 2 3 sort} |
|
375 do_test select9-3.4 { |
|
376 execsql { CREATE INDEX i2 ON t2(d) } |
|
377 cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 } |
|
378 } {1 1 2 2 3 nosort} |
|
379 do_test select9-3.5 { |
|
380 execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 } |
|
381 cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 } |
|
382 } {1 1 2 2 3 nosort} |
|
383 do_test select9-3.X { |
|
384 execsql { |
|
385 DROP INDEX i1; |
|
386 DROP INDEX i2; |
|
387 DROP VIEW v1; |
|
388 } |
|
389 } {} |
|
390 |
|
391 # This block of tests is the same as the preceding one, except that |
|
392 # "UNION" is tested instead of "UNION ALL". |
|
393 # |
|
394 do_test select9-4.1 { |
|
395 cksort { SELECT a FROM t1 ORDER BY 1 } |
|
396 } {1 2 3 4 5 6 7 8 9 10 sort} |
|
397 do_test select9-4.2 { |
|
398 execsql { CREATE INDEX i1 ON t1(a) } |
|
399 cksort { SELECT a FROM t1 ORDER BY 1 } |
|
400 } {1 2 3 4 5 6 7 8 9 10 nosort} |
|
401 do_test select9-4.3 { |
|
402 cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 } |
|
403 } {1 2 3 4 5 sort} |
|
404 do_test select9-4.4 { |
|
405 execsql { CREATE INDEX i2 ON t2(d) } |
|
406 cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 } |
|
407 } {1 2 3 4 5 nosort} |
|
408 do_test select9-4.5 { |
|
409 execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION SELECT d FROM t2 } |
|
410 cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 } |
|
411 } {1 2 3 4 5 sort} |
|
412 do_test select9-4.X { |
|
413 execsql { |
|
414 DROP INDEX i1; |
|
415 DROP INDEX i2; |
|
416 DROP VIEW v1; |
|
417 } |
|
418 } {} |
|
419 |
|
420 |
|
421 finish_test |