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.1.1.1 2012/02/21 17:04:16 misho 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: #-------------------------------------------------------------------------
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: #
46: proc 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
62: set iOffsetIncr 1
63: if {[info exists ::G(isquick)] && $::G(isquick) && $nRow>=5} {
64: set iOffsetIncr [expr $nRow / 5]
65: set iLimitIncr [expr $nRow / 5]
66: }
67:
68: 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} {
73:
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: #
103: proc 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: #
121: do_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: #
157: set iOuterLoop 1
158: foreach 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:
257: do_test select9-2.0 {
258: execsql {
259: DROP INDEX i1;
260: DROP INDEX i2;
261: DROP INDEX i3;
262: DROP INDEX i4;
263: }
264: } {}
265:
266: proc reverse {lhs rhs} {
267: return [string compare $rhs $lhs]
268: }
269: db 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: #
276: set iOuterLoop 1
277: foreach 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:
329: do_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: #
342: proc 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: #
361: # It turns out that it is.
362: #
363: do_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}
366: do_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}
370: do_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}
373: do_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}
377: do_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 }
380: } {1 1 2 2 3 nosort}
381: do_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: #
392: do_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}
395: do_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}
399: do_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}
402: do_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}
406: do_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}
410: do_test select9-4.X {
411: execsql {
412: DROP INDEX i1;
413: DROP INDEX i2;
414: DROP VIEW v1;
415: }
416: } {}
417:
418:
419: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select9.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test