AryaWu/sqlite
0
1# 2008 June 242#3# The author disclaims copyright to this source code. In place of4# 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.4 2008/07/01 14:39:35 danielk1977 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 of17# 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.tcl27 28#-------------------------------------------------------------------------29# test_compound_select TESTNAME SELECT RESULT30#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 SELECT33# statement may not contain a LIMIT or OFFSET clause. This proc tests34# 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 of41# the command [execsql $SELECT].42# 43# The first argument, $TESTNAME, is used as the base test case name to44# pass to [do_test] for each individual LIMIT OFFSET test case.45# 46proc test_compound_select {testname sql result} {47 48 set nCol 149 db eval $sql A {50 set nCol [llength $A(*)]51 break52 }53 set nRow [expr {[llength $result] / $nCol}]54 55 set ::compound_sql $sql56 do_test $testname { 57 execsql $::compound_sql58 } $result59#return60 61 set iLimitIncr 162 set iOffsetIncr 163 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_sql84 } [lrange $result $iStart $iEnd]85 }86 }87}88 89#-------------------------------------------------------------------------90# test_compound_select_flippable TESTNAME SELECT RESULT91#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 statement97# and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION".98#99# This proc calls [test_compound_select] twice, once with the select100# statement as it is passed to this command, and once with the positions101# of <select statement 1> and <select statement 2> exchanged.102#103proc test_compound_select_flippable {testname sql result} {104 test_compound_select $testname $sql $result105 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 $result113}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 set153# of indexes present within the database schema. The data returned by154# the compound SELECT statements in the test cases should be the same 155# in each case.156#157set iOuterLoop 1158foreach 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 $indexes172 } {}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, 2187 } {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, 1190 } {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, 2208 } {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, 1212 } {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 1221 } {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 2224 } {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, 1227 } {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, 2230 } {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, 2248 } {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, 1252 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}253 254 incr iOuterLoop255}256 257do_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 266proc reverse {lhs rhs} {267 return [string compare $rhs $lhs]268}269db collate reverse reverse270 271# This loop is similar to the previous one (test cases select9-1.*) 272# except that the simple select statements have WHERE clauses attached273# to them. Sometimes the WHERE clause may be satisfied using the same274# index used for ORDER BY, sometimes not.275#276set iOuterLoop 1277foreach 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 $indexes291 } {}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 1295 } {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, 1299 } {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, 1304 } {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 1308 } {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, 1312 } {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, 1317 } {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 1321 } {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 1325 } {1 2 3}326 327}328 329do_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 program338# for the SQL and appends a "nosort" to the result if the program contains the339# SortCallback opcode. If the program does not contain the SortCallback340# opcode it appends "sort"341#342proc cksort {sql} {343 set ::sqlite_sort_count 0344 set data [execsql $sql]345 if {$::sqlite_sort_count} {set x sort} {set x nosort}346 lappend data $x347 return $data348}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 1353#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 t2359# SELECT a FROM v1 ORDER BY 1360#361# It turns out that it is.362#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 }380} {1 1 2 2 3 nosort}381do_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 that390# "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 418# Testing to make sure that queries involving a view of a compound select419# are planned efficiently. This detects a problem reported on the mailing420# list on 2012-04-26. See421#422# http://www.mail-archive.com/sqlite-users%40sqlite.org/msg69746.html423#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 as431 SELECT x, y FROM t51432 UNION ALL433 SELECT x, y FROM t52;434 CREATE INDEX t51x ON t51(x);435 CREATE INDEX t52x ON t52(x);436 EXPLAIN QUERY PLAN437 SELECT * FROM v5 WHERE x='12345' ORDER BY y;438 }439} {~/SCAN/} ;# Uses indices with "*"440do_test select9-5.2 {441 db eval {442 EXPLAIN QUERY PLAN443 SELECT x, y FROM v5 WHERE x='12345' ORDER BY y;444 }445} {~/SCAN/} ;# Uses indices with "x, y"446do_test select9-5.3 {447 db eval {448 EXPLAIN QUERY PLAN449 SELECT x, y FROM v5 WHERE +x='12345' ORDER BY y;450 }451} {/SCAN/} ;# Full table scan if the "+x" prevents index usage.452 453# 2013-07-09: Ticket [490a4b7235624298]: 454# "WHERE 0" on the first element of a UNION causes an assertion fault455#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 471 472finish_test473