3
# The author disclaims copyright to this source code. In place of
4
# a legal notice, here is a blessing:
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.
10
#***********************************************************************
11
# This file implements regression tests for SQLite library.
13
# $Id: select9.test,v 1.4 2008/07/01 14:39:35 danielk1977 Exp $
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.
23
# * Are there any "column affinity" issues to consider?
25
set testdir [file dirname $argv0]
26
source $testdir/tester.tcl
30
#-------------------------------------------------------------------------
31
# test_compound_select TESTNAME SELECT RESULT
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:
38
# "$SELECT limit $X offset $Y"
40
# for various values of $X and $Y.
42
# The third argument, $RESULT, should contain the expected result of
43
# the command [execsql $SELECT].
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.
48
proc test_compound_select {testname sql result} {
52
set nCol [llength $A(*)]
55
set nRow [expr {[llength $result] / $nCol}]
57
set ::compound_sql $sql
59
execsql $::compound_sql
65
if {[info exists ::ISQUICK] && $::ISQUICK && $nRow>=5} {
66
set iOffsetIncr [expr $nRow / 5]
67
set iLimitIncr [expr $nRow / 5]
70
set iLimitEnd [expr $nRow+$iLimitIncr]
71
set iOffsetEnd [expr $nRow+$iOffsetIncr]
73
for {set iOffset 0} {$iOffset < $iOffsetEnd} {incr iOffset $iOffsetIncr} {
74
for {set iLimit 0} {$iLimit < $iLimitEnd} {incr iLimit} {
76
set ::compound_sql "$sql LIMIT $iLimit"
78
append ::compound_sql " OFFSET $iOffset"
81
set iStart [expr {$iOffset*$nCol}]
82
set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}]
84
do_test $testname.limit=$iLimit.offset=$iOffset {
85
execsql $::compound_sql
86
} [lrange $result $iStart $iEnd]
91
#-------------------------------------------------------------------------
92
# test_compound_select_flippable TESTNAME SELECT RESULT
94
# This command is for testing statements of the form:
96
# <simple select 1> <compound op> <simple select 2> ORDER BY <order by>
98
# where each <simple select> is a simple (non-compound) select statement
99
# and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION".
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.
105
proc test_compound_select_flippable {testname sql result} {
106
test_compound_select $testname $sql $result
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"}
113
set flipsql "$s2 $op $s1 $order_by"
114
test_compound_select $testname.flipped $flipsql $result
117
#############################################################################
121
# Create and populate a sample database.
123
do_test select9-1.0 {
125
CREATE TABLE t1(a, b, c);
126
CREATE TABLE t2(d, e, f);
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');
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');
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
160
foreach indexes [list {
161
/* Do not create any indexes. */
163
CREATE INDEX i1 ON t1(a)
165
CREATE INDEX i2 ON t1(b)
167
CREATE INDEX i3 ON t2(d)
169
CREATE INDEX i4 ON t2(e)
172
do_test select9-1.$iOuterLoop.1 {
176
# Test some 2-way UNION ALL queries. No WHERE clauses.
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}
194
# Test some 2-way UNION queries.
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}
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}
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}
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}
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}
216
# Test some 2-way INTERSECT queries.
218
test_compound_select select9-1.$iOuterLoop.11 {
219
SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2
221
test_compound_select_flippable select9-1.$iOuterLoop.12 {
222
SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1
224
test_compound_select select9-1.$iOuterLoop.13 {
225
SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2
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
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
234
# Test some 2-way EXCEPT queries.
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}
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}
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}
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}
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}
259
do_test select9-2.0 {
268
proc reverse {lhs rhs} {
269
return [string compare $rhs $lhs]
271
db collate reverse reverse
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.
279
foreach indexes [list {
280
/* Do not create any indexes. */
282
CREATE INDEX i1 ON t1(a)
285
CREATE INDEX i1 ON t1(b, a)
287
CREATE INDEX i2 ON t2(d DESC, e COLLATE REVERSE ASC);
289
CREATE INDEX i3 ON t1(a DESC);
291
do_test select9-2.$iOuterLoop.1 {
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}
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}
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}
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}
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}
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}
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
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
331
do_test select9-2.X {
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"
345
set ::sqlite_sort_count 0
346
set data [execsql $sql]
347
if {$::sqlite_sort_count} {set x sort} {set x nosort}
352
# If the right indexes exist, the following query:
354
# SELECT t1.a FROM t1 UNION ALL SELECT t2.d FROM t2 ORDER BY 1
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
360
# CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2
361
# SELECT a FROM v1 ORDER BY 1
363
# It turns out that it is.
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 }
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 }
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 }
383
do_test select9-3.X {
391
# This block of tests is the same as the preceding one, except that
392
# "UNION" is tested instead of "UNION ALL".
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 }
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 }
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 }
412
do_test select9-4.X {