# 2008 June 24 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # $Id: select9.test,v 1.4 2008/07/01 14:39:35 danielk1977 Exp $ # The tests in this file are focused on test compound SELECT statements # that have any or all of an ORDER BY, LIMIT or OFFSET clauses. As of # version 3.6.0, SQLite contains code to use SQL indexes where possible # to optimize such statements. # # TODO Points: # # * Are there any "column affinity" issues to consider? set testdir [file dirname $argv0] source $testdir/tester.tcl #set ISQUICK 1 #------------------------------------------------------------------------- # test_compound_select TESTNAME SELECT RESULT # # This command is used to run multiple LIMIT/OFFSET test cases based on # the single SELECT statement passed as the second argument. The SELECT # statement may not contain a LIMIT or OFFSET clause. This proc tests # many statements of the form: # # "$SELECT limit $X offset $Y" # # for various values of $X and $Y. # # The third argument, $RESULT, should contain the expected result of # the command [execsql $SELECT]. # # The first argument, $TESTNAME, is used as the base test case name to # pass to [do_test] for each individual LIMIT OFFSET test case. # proc test_compound_select {testname sql result} { set nCol 1 db eval $sql A { set nCol [llength $A(*)] break } set nRow [expr {[llength $result] / $nCol}] set ::compound_sql $sql do_test $testname { execsql $::compound_sql } $result #return set iLimitIncr 1 set iOffsetIncr 1 if {[info exists ::ISQUICK] && $::ISQUICK && $nRow>=5} { set iOffsetIncr [expr $nRow / 5] set iLimitIncr [expr $nRow / 5] } set iLimitEnd [expr $nRow+$iLimitIncr] set iOffsetEnd [expr $nRow+$iOffsetIncr] for {set iOffset 0} {$iOffset < $iOffsetEnd} {incr iOffset $iOffsetIncr} { for {set iLimit 0} {$iLimit < $iLimitEnd} {incr iLimit} { set ::compound_sql "$sql LIMIT $iLimit" if {$iOffset != 0} { append ::compound_sql " OFFSET $iOffset" } set iStart [expr {$iOffset*$nCol}] set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}] do_test $testname.limit=$iLimit.offset=$iOffset { execsql $::compound_sql } [lrange $result $iStart $iEnd] } } } #------------------------------------------------------------------------- # test_compound_select_flippable TESTNAME SELECT RESULT # # This command is for testing statements of the form: # # ORDER BY # # where each is a simple (non-compound) select statement # and is one of "INTERSECT", "UNION ALL" or "UNION". # # This proc calls [test_compound_select] twice, once with the select # statement as it is passed to this command, and once with the positions # of exchanged. # proc test_compound_select_flippable {testname sql result} { test_compound_select $testname $sql $result set select [string trim $sql] set RE {(.*)(UNION ALL|INTERSECT|UNION)(.*)(ORDER BY.*)} set rc [regexp $RE $select -> s1 op s2 order_by] if {!$rc} {error "Statement is unflippable: $select"} set flipsql "$s2 $op $s1 $order_by" test_compound_select $testname.flipped $flipsql $result } ############################################################################# # Begin tests. # # Create and populate a sample database. # do_test select9-1.0 { execsql { CREATE TABLE t1(a, b, c); CREATE TABLE t2(d, e, f); BEGIN; INSERT INTO t1 VALUES(1, 'one', 'I'); INSERT INTO t1 VALUES(3, NULL, NULL); INSERT INTO t1 VALUES(5, 'five', 'V'); INSERT INTO t1 VALUES(7, 'seven', 'VII'); INSERT INTO t1 VALUES(9, NULL, NULL); INSERT INTO t1 VALUES(2, 'two', 'II'); INSERT INTO t1 VALUES(4, 'four', 'IV'); INSERT INTO t1 VALUES(6, NULL, NULL); INSERT INTO t1 VALUES(8, 'eight', 'VIII'); INSERT INTO t1 VALUES(10, 'ten', 'X'); INSERT INTO t2 VALUES(1, 'two', 'IV'); INSERT INTO t2 VALUES(2, 'four', 'VIII'); INSERT INTO t2 VALUES(3, NULL, NULL); INSERT INTO t2 VALUES(4, 'eight', 'XVI'); INSERT INTO t2 VALUES(5, 'ten', 'XX'); INSERT INTO t2 VALUES(6, NULL, NULL); INSERT INTO t2 VALUES(7, 'fourteen', 'XXVIII'); INSERT INTO t2 VALUES(8, 'sixteen', 'XXXII'); INSERT INTO t2 VALUES(9, NULL, NULL); INSERT INTO t2 VALUES(10, 'twenty', 'XL'); COMMIT; } } {} # Each iteration of this loop runs the same tests with a different set # of indexes present within the database schema. The data returned by # the compound SELECT statements in the test cases should be the same # in each case. # set iOuterLoop 1 foreach indexes [list { /* Do not create any indexes. */ } { CREATE INDEX i1 ON t1(a) } { CREATE INDEX i2 ON t1(b) } { CREATE INDEX i3 ON t2(d) } { CREATE INDEX i4 ON t2(e) }] { do_test select9-1.$iOuterLoop.1 { execsql $indexes } {} # Test some 2-way UNION ALL queries. No WHERE clauses. # test_compound_select select9-1.$iOuterLoop.2 { SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 } {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} test_compound_select select9-1.$iOuterLoop.3 { SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1 } {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} test_compound_select select9-1.$iOuterLoop.4 { SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2 } {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} test_compound_select_flippable select9-1.$iOuterLoop.5 { SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1, 2 } {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} test_compound_select_flippable select9-1.$iOuterLoop.6 { SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2, 1 } {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} # Test some 2-way UNION queries. # test_compound_select select9-1.$iOuterLoop.7 { SELECT a, b FROM t1 UNION SELECT d, e FROM t2 } {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} test_compound_select select9-1.$iOuterLoop.8 { SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1 } {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} test_compound_select select9-1.$iOuterLoop.9 { SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2 } {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} test_compound_select_flippable select9-1.$iOuterLoop.10 { SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1, 2 } {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} test_compound_select_flippable select9-1.$iOuterLoop.11 { SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2, 1 } {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} # Test some 2-way INTERSECT queries. # test_compound_select select9-1.$iOuterLoop.11 { SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 } {3 {} 6 {} 9 {}} test_compound_select_flippable select9-1.$iOuterLoop.12 { SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1 } {3 {} 6 {} 9 {}} test_compound_select select9-1.$iOuterLoop.13 { SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2 } {3 {} 6 {} 9 {}} test_compound_select_flippable select9-1.$iOuterLoop.14 { SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2, 1 } {3 {} 6 {} 9 {}} test_compound_select_flippable select9-1.$iOuterLoop.15 { SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1, 2 } {3 {} 6 {} 9 {}} # Test some 2-way EXCEPT queries. # test_compound_select select9-1.$iOuterLoop.16 { SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} test_compound_select select9-1.$iOuterLoop.17 { SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} test_compound_select select9-1.$iOuterLoop.18 { SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two} test_compound_select select9-1.$iOuterLoop.19 { SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1, 2 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} test_compound_select select9-1.$iOuterLoop.20 { SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2, 1 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two} incr iOuterLoop } do_test select9-2.0 { execsql { DROP INDEX i1; DROP INDEX i2; DROP INDEX i3; DROP INDEX i4; } } {} proc reverse {lhs rhs} { return [string compare $rhs $lhs] } db collate reverse reverse # This loop is similar to the previous one (test cases select9-1.*) # except that the simple select statements have WHERE clauses attached # to them. Sometimes the WHERE clause may be satisfied using the same # index used for ORDER BY, sometimes not. # set iOuterLoop 1 foreach indexes [list { /* Do not create any indexes. */ } { CREATE INDEX i1 ON t1(a) } { DROP INDEX i1; CREATE INDEX i1 ON t1(b, a) } { CREATE INDEX i2 ON t2(d DESC, e COLLATE REVERSE ASC); } { CREATE INDEX i3 ON t1(a DESC); }] { do_test select9-2.$iOuterLoop.1 { execsql $indexes } {} test_compound_select_flippable select9-2.$iOuterLoop.2 { SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 1 } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL} test_compound_select_flippable select9-2.$iOuterLoop.2 { SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1 } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II} test_compound_select_flippable select9-2.$iOuterLoop.3 { SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 2 COLLATE reverse, 1 } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV} test_compound_select_flippable select9-2.$iOuterLoop.4 { SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 1 } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL} test_compound_select_flippable select9-2.$iOuterLoop.5 { SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1 } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II} test_compound_select_flippable select9-2.$iOuterLoop.6 { SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 2 COLLATE reverse, 1 } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV} test_compound_select select9-2.$iOuterLoop.4 { SELECT a FROM t1 WHERE a<8 EXCEPT SELECT d FROM t2 WHERE d<=3 ORDER BY 1 } {4 5 6 7} test_compound_select select9-2.$iOuterLoop.4 { SELECT a FROM t1 WHERE a<8 INTERSECT SELECT d FROM t2 WHERE d<=3 ORDER BY 1 } {1 2 3} } do_test select9-2.X { execsql { DROP INDEX i1; DROP INDEX i2; DROP INDEX i3; } } {} # This procedure executes the SQL. Then it checks the generated program # for the SQL and appends a "nosort" to the result if the program contains the # SortCallback opcode. If the program does not contain the SortCallback # opcode it appends "sort" # proc cksort {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x return $data } # If the right indexes exist, the following query: # # SELECT t1.a FROM t1 UNION ALL SELECT t2.d FROM t2 ORDER BY 1 # # can use indexes to run without doing a in-memory sort operation. # This block of tests (select9-3.*) is used to check if the same # is possible with: # # CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 # SELECT a FROM v1 ORDER BY 1 # # It turns out that it is. # do_test select9-3.1 { cksort { SELECT a FROM t1 ORDER BY 1 } } {1 2 3 4 5 6 7 8 9 10 sort} do_test select9-3.2 { execsql { CREATE INDEX i1 ON t1(a) } cksort { SELECT a FROM t1 ORDER BY 1 } } {1 2 3 4 5 6 7 8 9 10 nosort} do_test select9-3.3 { cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 } } {1 1 2 2 3 sort} do_test select9-3.4 { execsql { CREATE INDEX i2 ON t2(d) } cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 } } {1 1 2 2 3 nosort} do_test select9-3.5 { execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 } cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 } } {1 1 2 2 3 nosort} do_test select9-3.X { execsql { DROP INDEX i1; DROP INDEX i2; DROP VIEW v1; } } {} # This block of tests is the same as the preceding one, except that # "UNION" is tested instead of "UNION ALL". # do_test select9-4.1 { cksort { SELECT a FROM t1 ORDER BY 1 } } {1 2 3 4 5 6 7 8 9 10 sort} do_test select9-4.2 { execsql { CREATE INDEX i1 ON t1(a) } cksort { SELECT a FROM t1 ORDER BY 1 } } {1 2 3 4 5 6 7 8 9 10 nosort} do_test select9-4.3 { cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 } } {1 2 3 4 5 sort} do_test select9-4.4 { execsql { CREATE INDEX i2 ON t2(d) } cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 } } {1 2 3 4 5 nosort} do_test select9-4.5 { execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION SELECT d FROM t2 } cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 } } {1 2 3 4 5 sort} do_test select9-4.X { execsql { DROP INDEX i1; DROP INDEX i2; DROP VIEW v1; } } {} finish_test