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 2008/06/24 11:21:21 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#------------------------------------------------------------------------- 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# 46proc 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 iLimitEnd [expr $nRow+1] 63 64 set iOffsetIncr 1 65 set iOffsetEnd $nRow 66 67 for {set iOffset 0} {$iOffset <= $iOffsetEnd} {incr iOffset $iOffsetIncr} { 68 for {set iLimit 0} {$iLimit <= ($nRow+1)} {incr iLimit} { 69 70 set ::compound_sql "$sql LIMIT $iLimit" 71 if {$iOffset != 0} { 72 append ::compound_sql " OFFSET $iOffset" 73 } 74 75 set iStart [expr {$iOffset*$nCol}] 76 set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}] 77 78 do_test $testname.limit=$iLimit.offset=$iOffset { 79 execsql $::compound_sql 80 } [lrange $result $iStart $iEnd] 81 } 82 } 83} 84 85#------------------------------------------------------------------------- 86# test_compound_select_flippable TESTNAME SELECT RESULT 87# 88# This command is for testing statements of the form: 89# 90# <simple select 1> <compound op> <simple select 2> ORDER BY <order by> 91# 92# where each <simple select> is a simple (non-compound) select statement 93# and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION". 94# 95# This proc calls [test_compound_select] twice, once with the select 96# statement as it is passed to this command, and once with the positions 97# of <select statement 1> and <select statement 2> exchanged. 98# 99proc test_compound_select_flippable {testname sql result} { 100 test_compound_select $testname $sql $result 101 102 set select [string trim $sql] 103 set RE {(.*)(UNION ALL|INTERSECT|UNION)(.*)(ORDER BY.*)} 104 set rc [regexp $RE $select -> s1 op s2 order_by] 105 if {!$rc} {error "Statement is unflippable: $select"} 106 107 set flipsql "$s2 $op $s1 $order_by" 108 test_compound_select $testname.flipped $flipsql $result 109} 110 111############################################################################# 112# Begin tests. 113# 114 115# Create and populate a sample database. 116# 117do_test select9-1.0 { 118 execsql { 119 CREATE TABLE t1(a, b, c); 120 CREATE TABLE t2(d, e, f); 121 BEGIN; 122 INSERT INTO t1 VALUES(1, 'one', 'I'); 123 INSERT INTO t1 VALUES(3, NULL, NULL); 124 INSERT INTO t1 VALUES(5, 'five', 'V'); 125 INSERT INTO t1 VALUES(7, 'seven', 'VII'); 126 INSERT INTO t1 VALUES(9, NULL, NULL); 127 INSERT INTO t1 VALUES(2, 'two', 'II'); 128 INSERT INTO t1 VALUES(4, 'four', 'IV'); 129 INSERT INTO t1 VALUES(6, NULL, NULL); 130 INSERT INTO t1 VALUES(8, 'eight', 'VIII'); 131 INSERT INTO t1 VALUES(10, 'ten', 'X'); 132 133 INSERT INTO t2 VALUES(1, 'two', 'IV'); 134 INSERT INTO t2 VALUES(2, 'four', 'VIII'); 135 INSERT INTO t2 VALUES(3, NULL, NULL); 136 INSERT INTO t2 VALUES(4, 'eight', 'XVI'); 137 INSERT INTO t2 VALUES(5, 'ten', 'XX'); 138 INSERT INTO t2 VALUES(6, NULL, NULL); 139 INSERT INTO t2 VALUES(7, 'fourteen', 'XXVIII'); 140 INSERT INTO t2 VALUES(8, 'sixteen', 'XXXII'); 141 INSERT INTO t2 VALUES(9, NULL, NULL); 142 INSERT INTO t2 VALUES(10, 'twenty', 'XL'); 143 144 COMMIT; 145 } 146} {} 147 148# Each iteration of this loop runs the same tests with a different set 149# of indexes present within the database schema. The data returned by 150# the compound SELECT statements in the test cases should be the same 151# in each case. 152# 153set iOuterLoop 1 154foreach indexes [list { 155 /* Do not create any indexes. */ 156} { 157 CREATE INDEX i1 ON t1(a) 158} { 159 CREATE INDEX i2 ON t1(b) 160} { 161 CREATE INDEX i3 ON t2(d) 162} { 163 CREATE INDEX i4 ON t2(e) 164}] { 165 166 do_test select9-1.$iOuterLoop.1 { 167 execsql $indexes 168 } {} 169 170 # Test some 2-way UNION ALL queries. No WHERE clauses. 171 # 172 test_compound_select select9-1.$iOuterLoop.2 { 173 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 174 } {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} 175 test_compound_select select9-1.$iOuterLoop.3 { 176 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1 177 } {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} 178 test_compound_select select9-1.$iOuterLoop.4 { 179 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2 180 } {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} 181 test_compound_select_flippable select9-1.$iOuterLoop.5 { 182 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1, 2 183 } {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} 184 test_compound_select_flippable select9-1.$iOuterLoop.6 { 185 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2, 1 186 } {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} 187 188 # Test some 2-way UNION queries. 189 # 190 test_compound_select select9-1.$iOuterLoop.7 { 191 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 192 } {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} 193 194 test_compound_select select9-1.$iOuterLoop.8 { 195 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1 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.9 { 199 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2 200 } {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} 201 202 test_compound_select_flippable select9-1.$iOuterLoop.10 { 203 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1, 2 204 } {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} 205 206 test_compound_select_flippable select9-1.$iOuterLoop.11 { 207 SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2, 1 208 } {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} 209 210 # Test some 2-way INTERSECT queries. 211 # 212 test_compound_select select9-1.$iOuterLoop.11 { 213 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 214 } {3 {} 6 {} 9 {}} 215 test_compound_select_flippable select9-1.$iOuterLoop.12 { 216 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1 217 } {3 {} 6 {} 9 {}} 218 test_compound_select select9-1.$iOuterLoop.13 { 219 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2 220 } {3 {} 6 {} 9 {}} 221 test_compound_select_flippable select9-1.$iOuterLoop.14 { 222 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2, 1 223 } {3 {} 6 {} 9 {}} 224 test_compound_select_flippable select9-1.$iOuterLoop.15 { 225 SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1, 2 226 } {3 {} 6 {} 9 {}} 227 228 # Test some 2-way EXCEPT queries. 229 # 230 test_compound_select select9-1.$iOuterLoop.16 { 231 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 232 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} 233 234 test_compound_select select9-1.$iOuterLoop.17 { 235 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1 236 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} 237 238 test_compound_select select9-1.$iOuterLoop.18 { 239 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2 240 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two} 241 242 test_compound_select select9-1.$iOuterLoop.19 { 243 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1, 2 244 } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten} 245 246 test_compound_select select9-1.$iOuterLoop.20 { 247 SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2, 1 248 } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two} 249 250 incr iOuterLoop 251} 252 253finish_test 254 255