blob: 4c42236dc873e30bd9e504bb4fb17d4447f49874 [file] [log] [blame]
danielk19779afe6842008-06-24 11:21:20 +00001# 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#
danielk19774b86ef12008-07-01 14:39:35 +000013# $Id: select9.test,v 1.4 2008/07/01 14:39:35 danielk1977 Exp $
danielk19779afe6842008-06-24 11:21:20 +000014
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
25set testdir [file dirname $argv0]
26source $testdir/tester.tcl
27
28#-------------------------------------------------------------------------
29# test_compound_select TESTNAME SELECT RESULT
30#
31# This command is used to run multiple LIMIT/OFFSET test cases based on
32# the single SELECT statement passed as the second argument. The SELECT
33# statement may not contain a LIMIT or OFFSET clause. This proc tests
34# many statements of the form:
35#
36# "$SELECT limit $X offset $Y"
37#
38# for various values of $X and $Y.
39#
40# The third argument, $RESULT, should contain the expected result of
41# the command [execsql $SELECT].
42#
43# The first argument, $TESTNAME, is used as the base test case name to
44# pass to [do_test] for each individual LIMIT OFFSET test case.
45#
46proc test_compound_select {testname sql result} {
47
48 set nCol 1
49 db eval $sql A {
50 set nCol [llength $A(*)]
51 break
52 }
53 set nRow [expr {[llength $result] / $nCol}]
54
55 set ::compound_sql $sql
56 do_test $testname {
57 execsql $::compound_sql
58 } $result
59#return
60
61 set iLimitIncr 1
danielk19779afe6842008-06-24 11:21:20 +000062 set iOffsetIncr 1
dan430e74c2010-06-07 17:47:26 +000063 if {[info exists ::G(isquick)] && $::G(isquick) && $nRow>=5} {
danielk19776fb64442008-06-24 15:39:43 +000064 set iOffsetIncr [expr $nRow / 5]
65 set iLimitIncr [expr $nRow / 5]
66 }
danielk19779afe6842008-06-24 11:21:20 +000067
danielk19776fb64442008-06-24 15:39:43 +000068 set iLimitEnd [expr $nRow+$iLimitIncr]
69 set iOffsetEnd [expr $nRow+$iOffsetIncr]
70
71 for {set iOffset 0} {$iOffset < $iOffsetEnd} {incr iOffset $iOffsetIncr} {
72 for {set iLimit 0} {$iLimit < $iLimitEnd} {incr iLimit} {
danielk19779afe6842008-06-24 11:21:20 +000073
74 set ::compound_sql "$sql LIMIT $iLimit"
75 if {$iOffset != 0} {
76 append ::compound_sql " OFFSET $iOffset"
77 }
78
79 set iStart [expr {$iOffset*$nCol}]
80 set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}]
81
82 do_test $testname.limit=$iLimit.offset=$iOffset {
83 execsql $::compound_sql
84 } [lrange $result $iStart $iEnd]
85 }
86 }
87}
88
89#-------------------------------------------------------------------------
90# test_compound_select_flippable TESTNAME SELECT RESULT
91#
92# This command is for testing statements of the form:
93#
94# <simple select 1> <compound op> <simple select 2> ORDER BY <order by>
95#
96# where each <simple select> is a simple (non-compound) select statement
97# and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION".
98#
99# This proc calls [test_compound_select] twice, once with the select
100# statement as it is passed to this command, and once with the positions
101# of <select statement 1> and <select statement 2> exchanged.
102#
103proc test_compound_select_flippable {testname sql result} {
104 test_compound_select $testname $sql $result
105
106 set select [string trim $sql]
107 set RE {(.*)(UNION ALL|INTERSECT|UNION)(.*)(ORDER BY.*)}
108 set rc [regexp $RE $select -> s1 op s2 order_by]
109 if {!$rc} {error "Statement is unflippable: $select"}
110
111 set flipsql "$s2 $op $s1 $order_by"
112 test_compound_select $testname.flipped $flipsql $result
113}
114
115#############################################################################
116# Begin tests.
117#
118
119# Create and populate a sample database.
120#
121do_test select9-1.0 {
122 execsql {
123 CREATE TABLE t1(a, b, c);
124 CREATE TABLE t2(d, e, f);
125 BEGIN;
126 INSERT INTO t1 VALUES(1, 'one', 'I');
127 INSERT INTO t1 VALUES(3, NULL, NULL);
128 INSERT INTO t1 VALUES(5, 'five', 'V');
129 INSERT INTO t1 VALUES(7, 'seven', 'VII');
130 INSERT INTO t1 VALUES(9, NULL, NULL);
131 INSERT INTO t1 VALUES(2, 'two', 'II');
132 INSERT INTO t1 VALUES(4, 'four', 'IV');
133 INSERT INTO t1 VALUES(6, NULL, NULL);
134 INSERT INTO t1 VALUES(8, 'eight', 'VIII');
135 INSERT INTO t1 VALUES(10, 'ten', 'X');
136
137 INSERT INTO t2 VALUES(1, 'two', 'IV');
138 INSERT INTO t2 VALUES(2, 'four', 'VIII');
139 INSERT INTO t2 VALUES(3, NULL, NULL);
140 INSERT INTO t2 VALUES(4, 'eight', 'XVI');
141 INSERT INTO t2 VALUES(5, 'ten', 'XX');
142 INSERT INTO t2 VALUES(6, NULL, NULL);
143 INSERT INTO t2 VALUES(7, 'fourteen', 'XXVIII');
144 INSERT INTO t2 VALUES(8, 'sixteen', 'XXXII');
145 INSERT INTO t2 VALUES(9, NULL, NULL);
146 INSERT INTO t2 VALUES(10, 'twenty', 'XL');
147
148 COMMIT;
149 }
150} {}
151
152# Each iteration of this loop runs the same tests with a different set
153# of indexes present within the database schema. The data returned by
154# the compound SELECT statements in the test cases should be the same
155# in each case.
156#
157set iOuterLoop 1
158foreach indexes [list {
159 /* Do not create any indexes. */
160} {
161 CREATE INDEX i1 ON t1(a)
162} {
163 CREATE INDEX i2 ON t1(b)
164} {
165 CREATE INDEX i3 ON t2(d)
166} {
167 CREATE INDEX i4 ON t2(e)
168}] {
169
170 do_test select9-1.$iOuterLoop.1 {
171 execsql $indexes
172 } {}
173
174 # Test some 2-way UNION ALL queries. No WHERE clauses.
175 #
176 test_compound_select select9-1.$iOuterLoop.2 {
177 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2
178 } {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}
179 test_compound_select select9-1.$iOuterLoop.3 {
180 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1
181 } {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}
182 test_compound_select select9-1.$iOuterLoop.4 {
183 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2
184 } {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}
185 test_compound_select_flippable select9-1.$iOuterLoop.5 {
186 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1, 2
187 } {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}
188 test_compound_select_flippable select9-1.$iOuterLoop.6 {
189 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2, 1
190 } {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}
191
192 # Test some 2-way UNION queries.
193 #
194 test_compound_select select9-1.$iOuterLoop.7 {
195 SELECT a, b FROM t1 UNION SELECT d, e FROM t2
196 } {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}
197
198 test_compound_select select9-1.$iOuterLoop.8 {
199 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1
200 } {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}
201
202 test_compound_select select9-1.$iOuterLoop.9 {
203 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2
204 } {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}
205
206 test_compound_select_flippable select9-1.$iOuterLoop.10 {
207 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1, 2
208 } {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}
209
210 test_compound_select_flippable select9-1.$iOuterLoop.11 {
211 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2, 1
212 } {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}
213
214 # Test some 2-way INTERSECT queries.
215 #
216 test_compound_select select9-1.$iOuterLoop.11 {
217 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2
218 } {3 {} 6 {} 9 {}}
219 test_compound_select_flippable select9-1.$iOuterLoop.12 {
220 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1
221 } {3 {} 6 {} 9 {}}
222 test_compound_select select9-1.$iOuterLoop.13 {
223 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2
224 } {3 {} 6 {} 9 {}}
225 test_compound_select_flippable select9-1.$iOuterLoop.14 {
226 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2, 1
227 } {3 {} 6 {} 9 {}}
228 test_compound_select_flippable select9-1.$iOuterLoop.15 {
229 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1, 2
230 } {3 {} 6 {} 9 {}}
231
232 # Test some 2-way EXCEPT queries.
233 #
234 test_compound_select select9-1.$iOuterLoop.16 {
235 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2
236 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}
237
238 test_compound_select select9-1.$iOuterLoop.17 {
239 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1
240 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}
241
242 test_compound_select select9-1.$iOuterLoop.18 {
243 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2
244 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}
245
246 test_compound_select select9-1.$iOuterLoop.19 {
247 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1, 2
248 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}
249
250 test_compound_select select9-1.$iOuterLoop.20 {
251 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2, 1
252 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}
253
254 incr iOuterLoop
255}
256
danielk19776fb64442008-06-24 15:39:43 +0000257do_test select9-2.0 {
258 execsql {
259 DROP INDEX i1;
260 DROP INDEX i2;
261 DROP INDEX i3;
262 DROP INDEX i4;
263 }
264} {}
danielk19779afe6842008-06-24 11:21:20 +0000265
danielk19776fb64442008-06-24 15:39:43 +0000266proc reverse {lhs rhs} {
267 return [string compare $rhs $lhs]
268}
269db collate reverse reverse
270
271# This loop is similar to the previous one (test cases select9-1.*)
272# except that the simple select statements have WHERE clauses attached
273# to them. Sometimes the WHERE clause may be satisfied using the same
274# index used for ORDER BY, sometimes not.
275#
276set iOuterLoop 1
277foreach indexes [list {
278 /* Do not create any indexes. */
279} {
280 CREATE INDEX i1 ON t1(a)
281} {
282 DROP INDEX i1;
283 CREATE INDEX i1 ON t1(b, a)
284} {
285 CREATE INDEX i2 ON t2(d DESC, e COLLATE REVERSE ASC);
286} {
287 CREATE INDEX i3 ON t1(a DESC);
288}] {
289 do_test select9-2.$iOuterLoop.1 {
290 execsql $indexes
291 } {}
292
293 test_compound_select_flippable select9-2.$iOuterLoop.2 {
294 SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 1
295 } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL}
296
297 test_compound_select_flippable select9-2.$iOuterLoop.2 {
298 SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1
299 } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II}
300
301 test_compound_select_flippable select9-2.$iOuterLoop.3 {
302 SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5
303 ORDER BY 2 COLLATE reverse, 1
304 } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV}
305
306 test_compound_select_flippable select9-2.$iOuterLoop.4 {
307 SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 1
308 } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL}
309
310 test_compound_select_flippable select9-2.$iOuterLoop.5 {
311 SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1
312 } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II}
313
314 test_compound_select_flippable select9-2.$iOuterLoop.6 {
315 SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5
316 ORDER BY 2 COLLATE reverse, 1
317 } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV}
318
319 test_compound_select select9-2.$iOuterLoop.4 {
320 SELECT a FROM t1 WHERE a<8 EXCEPT SELECT d FROM t2 WHERE d<=3 ORDER BY 1
321 } {4 5 6 7}
322
323 test_compound_select select9-2.$iOuterLoop.4 {
324 SELECT a FROM t1 WHERE a<8 INTERSECT SELECT d FROM t2 WHERE d<=3 ORDER BY 1
325 } {1 2 3}
326
327}
328
danielk19777a8097b2008-06-30 07:53:09 +0000329do_test select9-2.X {
330 execsql {
331 DROP INDEX i1;
332 DROP INDEX i2;
333 DROP INDEX i3;
334 }
335} {}
336
337# This procedure executes the SQL. Then it checks the generated program
338# for the SQL and appends a "nosort" to the result if the program contains the
339# SortCallback opcode. If the program does not contain the SortCallback
340# opcode it appends "sort"
341#
342proc cksort {sql} {
343 set ::sqlite_sort_count 0
344 set data [execsql $sql]
345 if {$::sqlite_sort_count} {set x sort} {set x nosort}
346 lappend data $x
347 return $data
348}
349
350# If the right indexes exist, the following query:
351#
352# SELECT t1.a FROM t1 UNION ALL SELECT t2.d FROM t2 ORDER BY 1
353#
354# can use indexes to run without doing a in-memory sort operation.
355# This block of tests (select9-3.*) is used to check if the same
356# is possible with:
357#
358# CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2
359# SELECT a FROM v1 ORDER BY 1
360#
danielk19774b86ef12008-07-01 14:39:35 +0000361# It turns out that it is.
danielk19777a8097b2008-06-30 07:53:09 +0000362#
363do_test select9-3.1 {
364 cksort { SELECT a FROM t1 ORDER BY 1 }
365} {1 2 3 4 5 6 7 8 9 10 sort}
366do_test select9-3.2 {
367 execsql { CREATE INDEX i1 ON t1(a) }
368 cksort { SELECT a FROM t1 ORDER BY 1 }
369} {1 2 3 4 5 6 7 8 9 10 nosort}
370do_test select9-3.3 {
371 cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
372} {1 1 2 2 3 sort}
373do_test select9-3.4 {
374 execsql { CREATE INDEX i2 ON t2(d) }
375 cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
376} {1 1 2 2 3 nosort}
377do_test select9-3.5 {
378 execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 }
379 cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }
danielk19774b86ef12008-07-01 14:39:35 +0000380} {1 1 2 2 3 nosort}
danielk19777a8097b2008-06-30 07:53:09 +0000381do_test select9-3.X {
382 execsql {
383 DROP INDEX i1;
384 DROP INDEX i2;
385 DROP VIEW v1;
386 }
387} {}
388
389# This block of tests is the same as the preceding one, except that
390# "UNION" is tested instead of "UNION ALL".
391#
392do_test select9-4.1 {
393 cksort { SELECT a FROM t1 ORDER BY 1 }
394} {1 2 3 4 5 6 7 8 9 10 sort}
395do_test select9-4.2 {
396 execsql { CREATE INDEX i1 ON t1(a) }
397 cksort { SELECT a FROM t1 ORDER BY 1 }
398} {1 2 3 4 5 6 7 8 9 10 nosort}
399do_test select9-4.3 {
400 cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
401} {1 2 3 4 5 sort}
402do_test select9-4.4 {
403 execsql { CREATE INDEX i2 ON t2(d) }
404 cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
405} {1 2 3 4 5 nosort}
406do_test select9-4.5 {
407 execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION SELECT d FROM t2 }
408 cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }
409} {1 2 3 4 5 sort}
410do_test select9-4.X {
411 execsql {
412 DROP INDEX i1;
413 DROP INDEX i2;
414 DROP VIEW v1;
415 }
416} {}
417
drh70331cd2012-04-27 01:09:06 +0000418# Testing to make sure that queries involving a view of a compound select
419# are planned efficiently. This detects a problem reported on the mailing
420# list on 2012-04-26. See
421#
422# http://www.mail-archive.com/sqlite-users%40sqlite.org/msg69746.html
423#
424# For additional information.
425#
426do_test select9-5.1 {
427 db eval {
428 CREATE TABLE t51(x, y);
429 CREATE TABLE t52(x, y);
430 CREATE VIEW v5 as
431 SELECT x, y FROM t51
432 UNION ALL
433 SELECT x, y FROM t52;
434 CREATE INDEX t51x ON t51(x);
435 CREATE INDEX t52x ON t52(x);
436 EXPLAIN QUERY PLAN
437 SELECT * FROM v5 WHERE x='12345' ORDER BY y;
438 }
439} {~/SCAN TABLE/} ;# Uses indices with "*"
440do_test select9-5.2 {
441 db eval {
442 EXPLAIN QUERY PLAN
443 SELECT x, y FROM v5 WHERE x='12345' ORDER BY y;
444 }
445} {~/SCAN TABLE/} ;# Uses indices with "x, y"
446do_test select9-5.3 {
447 db eval {
448 EXPLAIN QUERY PLAN
449 SELECT x, y FROM v5 WHERE +x='12345' ORDER BY y;
450 }
451} {/SCAN TABLE/} ;# Full table scan if the "+x" prevents index usage.
452
drh5e128b22013-07-09 03:04:32 +0000453# 2013-07-09: Ticket [490a4b7235624298]:
454# "WHERE 0" on the first element of a UNION causes an assertion fault
455#
456do_execsql_test select9-6.1 {
457 CREATE TABLE t61(a);
458 CREATE TABLE t62(b);
459 INSERT INTO t61 VALUES(111);
460 INSERT INTO t62 VALUES(222);
461 SELECT a FROM t61 WHERE 0 UNION SELECT b FROM t62;
462} {222}
463do_execsql_test select9-6.2 {
464 SELECT a FROM t61 WHERE 0 UNION ALL SELECT b FROM t62;
465} {222}
466do_execsql_test select9-6.3 {
467 SELECT a FROM t61 UNION SELECT b FROM t62 WHERE 0;
468} {111}
469
470
danielk19776fb64442008-06-24 15:39:43 +0000471
472finish_test