1# 2016 June 17 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. The 12# focus of this file is testing the SELECT statement. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set ::testprefix rowvalue2 18 19do_execsql_test 1.0 { 20 CREATE TABLE t1(a, b, c); 21 INSERT INTO t1 VALUES(0, 0, 0); 22 INSERT INTO t1 VALUES(0, 1, 1); 23 INSERT INTO t1 VALUES(1, 0, 2); 24 INSERT INTO t1 VALUES(1, 1, 3); 25 26 CREATE INDEX i1 ON t1(a, b); 27} 28 29do_execsql_test 1.1.1 { SELECT c FROM t1 WHERE (a, b) >= (1, 0) } {2 3} 30do_execsql_test 1.1.2 { SELECT c FROM t1 WHERE (a, b) > (1, 0) } {3} 31 32#------------------------------------------------------------------------- 33 34do_execsql_test 2.0.1 { 35 CREATE TABLE t2(a INTEGER, b INTEGER, c INTEGER, d INTEGER); 36 CREATE INDEX i2 ON t2(a, b, c); 37} 38do_test 2.0.2 { 39 foreach a {0 1 2 3} { 40 foreach b {0 1 2 3} { 41 foreach c {0 1 2 3} { 42 execsql { INSERT INTO t2 VALUES($a, $b, $c, $c + $b*4 + $a*16); } 43 }}} 44} {} 45 46do_execsql_test 2.1 { 47 SELECT d FROM t2 WHERE (a, b) > (2, 2); 48} [db eval { SELECT d FROM t2 WHERE a>2 OR (a=2 AND b>2) }] 49 50do_execsql_test 2.2 { 51 SELECT d FROM t2 WHERE (a, b) >= (2, 2); 52} [db eval { SELECT d FROM t2 WHERE a>2 OR (a=2 AND b>=2) }] 53 54do_execsql_test 2.3 { 55 SELECT d FROM t2 WHERE a=1 AND (b, c) >= (1, 2); 56} [db eval { SELECT d FROM t2 WHERE +a=1 AND (b>1 OR (b==1 AND c>=2)) }] 57 58do_execsql_test 2.4 { 59 SELECT d FROM t2 WHERE a=1 AND (b, c) > (1, 2); 60} [db eval { SELECT d FROM t2 WHERE +a=1 AND (b>1 OR (b==1 AND c>2)) }] 61 62#------------------------------------------------------------------------- 63 64set words { 65airfare airfield airfields airflow airfoil 66airfoils airframe airframes airily airing 67airings airless airlift airlifts airline 68airliner airlines airlock airlocks airmail 69airmails airman airmen airplane airplanes 70 71arraignment arraignments arraigns arrange arranged 72arrangement arrangements arranger arrangers arranges 73arranging arrant array arrayed arrays 74arrears arrest arrested arrester arresters 75arresting arrestingly arrestor arrestors arrests 76 77edifices edit edited editing edition 78editions editor editorial editorially editorials 79editors edits educable educate educated 80educates educating education educational educationally 81educations educator educators eel eelgrass 82} 83 84do_test 3.0 { 85 execsql { CREATE TABLE t3(a, b, c, w); } 86 foreach w $words { 87 set a [string range $w 0 2] 88 set b [string range $w 3 5] 89 set c [string range $w 6 end] 90 execsql { INSERT INTO t3 VALUES($a, $b, $c, $w) } 91 } 92} {} 93 94 95foreach {tn idx} { 96 IDX1 {} 97 IDX2 { CREATE INDEX i3 ON t3(a, b, c); } 98 IDX3 { CREATE INDEX i3 ON t3(a, b); } 99 IDX4 { CREATE INDEX i3 ON t3(a); } 100} { 101 execsql { DROP INDEX IF EXISTS i3 } 102 execsql $idx 103 104 foreach w $words { 105 set a [string range $w 0 2] 106 set b [string range $w 3 5] 107 set c [string range $w 6 end] 108 109 foreach op [list > >= < <= == IS] { 110 do_execsql_test 3.1.$tn.$w.$op [subst -novar { 111 SELECT rowid FROM t3 WHERE (a, b, c) [set op] ($a, $b, $c) 112 ORDER BY +rowid 113 }] [db eval [subst -novar { 114 SELECT rowid FROM t3 WHERE w [set op] $w ORDER BY +rowid 115 }]] 116 117 do_execsql_test 3.1.$tn.$w.$op.subselect [subst -novar { 118 SELECT rowid FROM t3 WHERE (a, b, c) [set op] ( 119 SELECT a, b, c FROM t3 WHERE w = $w 120 ) 121 ORDER BY +rowid 122 }] [db eval [subst -novar { 123 SELECT rowid FROM t3 WHERE w [set op] $w ORDER BY +rowid 124 }]] 125 } 126 127 } 128} 129 130#------------------------------------------------------------------------- 131# 132 133do_execsql_test 4.0 { 134 CREATE TABLE t4(a, b, c); 135 INSERT INTO t4 VALUES(NULL, NULL, NULL); 136 INSERT INTO t4 VALUES(NULL, NULL, 0); 137 INSERT INTO t4 VALUES(NULL, NULL, 1); 138 INSERT INTO t4 VALUES(NULL, 0, NULL); 139 INSERT INTO t4 VALUES(NULL, 0, 0); 140 INSERT INTO t4 VALUES(NULL, 0, 1); 141 INSERT INTO t4 VALUES(NULL, 1, NULL); 142 INSERT INTO t4 VALUES(NULL, 1, 0); 143 INSERT INTO t4 VALUES(NULL, 1, 1); 144 145 INSERT INTO t4 VALUES( 0, NULL, NULL); 146 INSERT INTO t4 VALUES( 0, NULL, 0); 147 INSERT INTO t4 VALUES( 0, NULL, 1); 148 INSERT INTO t4 VALUES( 0, 0, NULL); 149 INSERT INTO t4 VALUES( 0, 0, 0); 150 INSERT INTO t4 VALUES( 0, 0, 1); 151 INSERT INTO t4 VALUES( 0, 1, NULL); 152 INSERT INTO t4 VALUES( 0, 1, 0); 153 INSERT INTO t4 VALUES( 0, 1, 1); 154 155 INSERT INTO t4 VALUES( 1, NULL, NULL); 156 INSERT INTO t4 VALUES( 1, NULL, 0); 157 INSERT INTO t4 VALUES( 1, NULL, 1); 158 INSERT INTO t4 VALUES( 1, 0, NULL); 159 INSERT INTO t4 VALUES( 1, 0, 0); 160 INSERT INTO t4 VALUES( 1, 0, 1); 161 INSERT INTO t4 VALUES( 1, 1, NULL); 162 INSERT INTO t4 VALUES( 1, 1, 0); 163 INSERT INTO t4 VALUES( 1, 1, 1); 164} 165 166proc make_expr1 {cList vList op} { 167 return "([join $cList ,]) $op ([join $vList ,])" 168} 169 170proc make_expr3 {cList vList op} { 171 set n [llength $cList] 172 173 set aList [list] 174 foreach c [lrange $cList 0 end-1] v [lrange $vList 0 end-1] { 175 lappend aList "$c == $v" 176 } 177 lappend aList "[lindex $cList end] $op [lindex $vList end]" 178 179 return "([join $aList { AND }])" 180} 181 182proc make_expr2 {cList vList op} { 183 set ret "" 184 185 switch -- $op { 186 == - IS { 187 set aList [list] 188 foreach c $cList v $vList { lappend aList "($c $op $v)" } 189 set ret [join $aList " AND "] 190 } 191 192 < - > { 193 set oList [list] 194 for {set i 0} {$i < [llength $cList]} {incr i} { 195 lappend oList [make_expr3 [lrange $cList 0 $i] [lrange $vList 0 $i] $op] 196 } 197 set ret [join $oList " OR "] 198 } 199 200 <= - >= { 201 set o2 [string range $op 0 0] 202 set oList [list] 203 for {set i 0} {$i < [llength $cList]-1} {incr i} { 204 lappend oList [make_expr3 [lrange $cList 0 $i] [lrange $vList 0 $i] $o2] 205 } 206 lappend oList [make_expr3 $cList $vList $op] 207 set ret [join $oList " OR "] 208 } 209 210 211 default { 212 error "Unknown op: $op" 213 } 214 } 215 216 set ret 217} 218 219foreach {tn idx} { 220 IDX1 {} 221 IDX2 { CREATE INDEX i4 ON t4(a, b, c); } 222 IDX3 { CREATE INDEX i4 ON t4(a, b); } 223 IDX4 { CREATE INDEX i4 ON t4(a); } 224} { 225 execsql { DROP INDEX IF EXISTS i4 } 226 execsql $idx 227 228 foreach {tn2 vector} { 229 1 {0 0 0} 230 2 {1 1 1} 231 3 {0 0 NULL} 232 4 {0 NULL 0} 233 5 {NULL 0 0} 234 6 {1 1 NULL} 235 7 {1 NULL 1} 236 8 {NULL 1 1} 237 } { 238 foreach op { IS == < <= > >= } { 239 set e1 [make_expr1 {a b c} $vector $op] 240 set e2 [make_expr2 {a b c} $vector $op] 241 242 do_execsql_test 4.$tn.$tn2.$op \ 243 "SELECT rowid FROM t4 WHERE $e2 ORDER BY +rowid" [ 244 db eval "SELECT rowid FROM t4 WHERE $e1 ORDER BY +rowid" 245 ] 246 } 247 } 248} 249 250do_execsql_test 5.0 { 251 CREATE TABLE r1(a TEXT, iB TEXT); 252 CREATE TABLE r2(x TEXT, zY INTEGER); 253 CREATE INDEX r1ab ON r1(a, iB); 254 255 INSERT INTO r1 VALUES(35, 35); 256 INSERT INTO r2 VALUES(35, 36); 257 INSERT INTO r2 VALUES(35, 4); 258 INSERT INTO r2 VALUES(35, 35); 259} {} 260 261foreach {tn lhs rhs} { 262 1 {x +zY} {a iB} 263 2 {x zY} {a iB} 264 3 {x zY} {a +iB} 265 4 {+x zY} {a iB} 266 5 {x zY} {+a iB} 267} { 268 foreach op { IS == < <= > >= } { 269 set e1 [make_expr1 $lhs $rhs $op] 270 set e2 [make_expr2 $lhs $rhs $op] 271 do_execsql_test 5.$tn.$op \ 272 "SELECT * FROM r1, r2 WHERE $e2 ORDER BY iB" [db eval \ 273 "SELECT * FROM r1, r2 WHERE $e1 ORDER BY iB" 274 ] 275 } 276} 277 278 279finish_test 280