/trunk/test/select9.test |
@@ -0,0 +1,419 @@ |
# 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 |
|
#------------------------------------------------------------------------- |
# 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 ::G(isquick)] && $::G(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: |
# |
# <simple select 1> <compound op> <simple select 2> ORDER BY <order by> |
# |
# where each <simple select> is a simple (non-compound) select statement |
# and <compound op> 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 <select statement 1> and <select statement 2> 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 |