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.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 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 25set testdir [file dirname $argv0] 26source $testdir/tester.tcl 27 28#set ISQUICK 1 29 30#------------------------------------------------------------------------- 31# test_compound_select TESTNAME SELECT RESULT 32# 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: 37# 38# "$SELECT limit $X offset $Y" 39# 40# for various values of $X and $Y. 41# 42# The third argument, $RESULT, should contain the expected result of 43# the command [execsql $SELECT]. 44# 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. 47# 48proc test_compound_select {testname sql result} { 49 50 set nCol 1 51 db eval $sql A { 52 set nCol [llength $A(*)] 53 break 54 } 55 set nRow [expr {[llength $result] / $nCol}] 56 57 set ::compound_sql $sql 58 do_test $testname { 59 execsql $::compound_sql 60 } $result 61#return 62 63 set iLimitIncr 1 64 set iOffsetIncr 1 65 if {[info exists ::ISQUICK] && $::ISQUICK && $nRow>=5} { 66 set iOffsetIncr [expr $nRow / 5] 67 set iLimitIncr [expr $nRow / 5] 68 } 69 70 set iLimitEnd [expr $nRow+$iLimitIncr] 71 set iOffsetEnd [expr $nRow+$iOffsetIncr] 72 73 for {set iOffset 0} {$iOffset < $iOffsetEnd} {incr iOffset $iOffsetIncr} { 74 for {set iLimit 0} {$iLimit < $iLimitEnd} {incr iLimit} { 75 76 set ::compound_sql "$sql LIMIT $iLimit" 77 if {$iOffset != 0} { 78 append ::compound_sql " OFFSET $iOffset" 79 } 80 81 set iStart [expr {$iOffset*$nCol}] 82 set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}] 83 84 do_test $testname.limit=$iLimit.offset=$iOffset { 85 execsql $::compound_sql 86 } [lrange $result $iStart $iEnd] 87 } 88 } 89} 90 91#------------------------------------------------------------------------- 92# test_compound_select_flippable TESTNAME SELECT RESULT 93# 94# This command is for testing statements of the form: 95# 96# <simple select 1> <compound op> <simple select 2> ORDER BY <order by> 97# 98# where each <simple select> is a simple (non-compound) select statement 99# and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION". 100# 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. 104# 105proc test_compound_select_flippable {testname sql result} { 106 test_compound_select $testname $sql $result 107 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"} 112 113 set flipsql "$s2 $op $s1 $order_by" 114 test_compound_select $testname.flipped $flipsql $result 115} 116 117############################################################################# 118# Begin tests. 119# 120 121# Create and populate a sample database. 122# 123do_test select9-1.0 { 124 execsql { 125 CREATE TABLE t1(a, b, c); 126 CREATE TABLE t2(d, e, f); 127 BEGIN; 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'); 138 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'); 149 150 COMMIT; 151 } 152} {} 153 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 157# in each case. 158# 159set iOuterLoop 1 160foreach indexes [list { 161 /* Do not create any indexes. */ 162} { 163 CREATE INDEX i1 ON t1(a) 164} { 165 CREATE INDEX i2 ON t1(b) 166} { 167 CREATE INDEX i3 ON t2(d) 168} { 169 CREATE INDEX i4 ON t2(e) 170}] { 171 172 do_test select9-1.$iOuterLoop.1 { 173 execsql $indexes 174 } {} 175 176 # Test some 2-way UNION ALL queries. No WHERE clauses. 177 # 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} 193 194 # Test some 2-way UNION queries. 195 # 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} 199 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} 203 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} 207 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} 211 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} 215 216 # Test some 2-way INTERSECT queries. 217 # 218 test_compound_select select9-1.$iOuterLoop.11 { 219 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 220 } {3 {} 6 {} 9 {}} 221 test_compound_select_flippable select9-1.$iOuterLoop.12 { 222 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1 223 } {3 {} 6 {} 9 {}} 224 test_compound_select select9-1.$iOuterLoop.13 { 225 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2 226 } {3 {} 6 {} 9 {}} 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 229 } {3 {} 6 {} 9 {}} 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 232 } {3 {} 6 {} 9 {}} 233 234 # Test some 2-way EXCEPT queries. 235 # 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} 239 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} 243 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} 247 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} 251 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} 255 256 incr iOuterLoop 257} 258 259do_test select9-2.0 { 260 execsql { 261 DROP INDEX i1; 262 DROP INDEX i2; 263 DROP INDEX i3; 264 DROP INDEX i4; 265 } 266} {} 267 268proc reverse {lhs rhs} { 269 return [string compare $rhs $lhs] 270} 271db collate reverse reverse 272 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. 277# 278set iOuterLoop 1 279foreach indexes [list { 280 /* Do not create any indexes. */ 281} { 282 CREATE INDEX i1 ON t1(a) 283} { 284 DROP INDEX i1; 285 CREATE INDEX i1 ON t1(b, a) 286} { 287 CREATE INDEX i2 ON t2(d DESC, e COLLATE REVERSE ASC); 288} { 289 CREATE INDEX i3 ON t1(a DESC); 290}] { 291 do_test select9-2.$iOuterLoop.1 { 292 execsql $indexes 293 } {} 294 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} 298 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} 302 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} 307 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} 311 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} 315 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} 320 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 323 } {4 5 6 7} 324 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 327 } {1 2 3} 328 329} 330 331do_test select9-2.X { 332 execsql { 333 DROP INDEX i1; 334 DROP INDEX i2; 335 DROP INDEX i3; 336 } 337} {} 338 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" 343# 344proc cksort {sql} { 345 set ::sqlite_sort_count 0 346 set data [execsql $sql] 347 if {$::sqlite_sort_count} {set x sort} {set x nosort} 348 lappend data $x 349 return $data 350} 351 352# If the right indexes exist, the following query: 353# 354# SELECT t1.a FROM t1 UNION ALL SELECT t2.d FROM t2 ORDER BY 1 355# 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 358# is possible with: 359# 360# CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 361# SELECT a FROM v1 ORDER BY 1 362# 363# It turns out that it is. 364# 365do_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} 368do_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} 372do_test select9-3.3 { 373 cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 } 374} {1 1 2 2 3 sort} 375do_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 } 378} {1 1 2 2 3 nosort} 379do_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 } 382} {1 1 2 2 3 nosort} 383do_test select9-3.X { 384 execsql { 385 DROP INDEX i1; 386 DROP INDEX i2; 387 DROP VIEW v1; 388 } 389} {} 390 391# This block of tests is the same as the preceding one, except that 392# "UNION" is tested instead of "UNION ALL". 393# 394do_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} 397do_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} 401do_test select9-4.3 { 402 cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 } 403} {1 2 3 4 5 sort} 404do_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 } 407} {1 2 3 4 5 nosort} 408do_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 } 411} {1 2 3 4 5 sort} 412do_test select9-4.X { 413 execsql { 414 DROP INDEX i1; 415 DROP INDEX i2; 416 DROP VIEW v1; 417 } 418} {} 419 420 421finish_test 422