| 1 |
# 2008 December 23 |
| 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 focus |
| 12 |
# is testing of where.c. More specifically, the focus is the optimization |
| 13 |
# of WHERE clauses that feature the OR operator. |
| 14 |
# |
| 15 |
|
| 16 |
set testdir [file dirname $argv0] |
| 17 |
source $testdir/tester.tcl |
| 18 |
|
| 19 |
if {[permutation]=="sorterref"} { |
| 20 |
finish_test |
| 21 |
return |
| 22 |
} |
| 23 |
|
| 24 |
# Test organization: |
| 25 |
# |
| 26 |
# where8-1.*: Tests to demonstrate simple cases work with a single table |
| 27 |
# in the FROM clause. |
| 28 |
# |
| 29 |
# where8-2.*: Tests surrounding virtual tables and the OR optimization. |
| 30 |
# |
| 31 |
# where8-3.*: Tests with more than one table in the FROM clause. |
| 32 |
# |
| 33 |
|
| 34 |
proc execsql_status {sql {db db}} { |
| 35 |
set result [uplevel $db eval [list $sql]] |
| 36 |
concat $result [db status step] [db status sort] |
| 37 |
} |
| 38 |
|
| 39 |
proc execsql_status2 {sql {db db}} { |
| 40 |
set ::sqlite_search_count 0 |
| 41 |
set result [uplevel [list execsql_status $sql $db]] |
| 42 |
concat $result $::sqlite_search_count |
| 43 |
} |
| 44 |
|
| 45 |
do_test where8-1.1 { |
| 46 |
execsql { |
| 47 |
CREATE TABLE t1(a, b TEXT, c); |
| 48 |
CREATE INDEX i1 ON t1(a); |
| 49 |
CREATE INDEX i2 ON t1(b); |
| 50 |
|
| 51 |
INSERT INTO t1 VALUES(1, 'one', 'I'); |
| 52 |
INSERT INTO t1 VALUES(2, 'two', 'II'); |
| 53 |
INSERT INTO t1 VALUES(3, 'three', 'III'); |
| 54 |
INSERT INTO t1 VALUES(4, 'four', 'IV'); |
| 55 |
INSERT INTO t1 VALUES(5, 'five', 'V'); |
| 56 |
INSERT INTO t1 VALUES(6, 'six', 'VI'); |
| 57 |
INSERT INTO t1 VALUES(7, 'seven', 'VII'); |
| 58 |
INSERT INTO t1 VALUES(8, 'eight', 'VIII'); |
| 59 |
INSERT INTO t1 VALUES(9, 'nine', 'IX'); |
| 60 |
INSERT INTO t1 VALUES(10, 'ten', 'X'); |
| 61 |
} |
| 62 |
} {} |
| 63 |
|
| 64 |
do_test where8-1.2 { |
| 65 |
execsql_status2 { SELECT c FROM t1 WHERE a = 1 OR b = 'nine' } |
| 66 |
} {I IX 0 0 6} |
| 67 |
|
| 68 |
do_test where8-1.3 { |
| 69 |
execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' } |
| 70 |
} {IX X II 0 0 6} |
| 71 |
|
| 72 |
ifcapable like_match_blobs { |
| 73 |
do_test where8-1.4a { |
| 74 |
execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' } |
| 75 |
} {IX X III II 0 0 10} |
| 76 |
do_test where8-1.5a { |
| 77 |
execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' } |
| 78 |
} {IX X V IV 0 0 10} |
| 79 |
} else { |
| 80 |
do_test where8-1.4b { |
| 81 |
execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' } |
| 82 |
} {IX X III II 0 0 9} |
| 83 |
do_test where8-1.5 { |
| 84 |
execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' } |
| 85 |
} {IX X V IV 0 0 9} |
| 86 |
} |
| 87 |
|
| 88 |
do_test where8-1.6 { |
| 89 |
execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid } |
| 90 |
} {I III 0 1} |
| 91 |
|
| 92 |
do_test where8-1.7 { |
| 93 |
execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a } |
| 94 |
} {I III 0 1} |
| 95 |
|
| 96 |
do_test where8-1.8 { |
| 97 |
# 18 searches. 9 on the index cursor and 9 on the table cursor. |
| 98 |
execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' } |
| 99 |
} {II III IV IX 0 0 18} |
| 100 |
|
| 101 |
do_test where8-1.9 { |
| 102 |
execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' } |
| 103 |
} {IX X VIII 0 0 7} |
| 104 |
|
| 105 |
do_test where8-1.10 { |
| 106 |
execsql_status2 { |
| 107 |
SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight' |
| 108 |
} |
| 109 |
} {IX VIII 0 0 7} |
| 110 |
|
| 111 |
do_test where8-1.11 { |
| 112 |
execsql_status2 { |
| 113 |
SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine' |
| 114 |
} |
| 115 |
} {IV V VI IX 0 0 10} |
| 116 |
|
| 117 |
do_test where8-1.12.1 { |
| 118 |
execsql_status2 { |
| 119 |
SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5 |
| 120 |
} |
| 121 |
} {I II III V 0 0 14} |
| 122 |
|
| 123 |
do_test where8-1.12.2 { |
| 124 |
execsql_status2 { |
| 125 |
SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5 |
| 126 |
} |
| 127 |
} {I II III V 9 0 9} |
| 128 |
|
| 129 |
do_test where8-1.13 { |
| 130 |
execsql_status2 { |
| 131 |
SELECT c FROM t1 |
| 132 |
WHERE a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 |
| 133 |
ORDER BY rowid |
| 134 |
} |
| 135 |
} {II III IV V VI 0 1 18} |
| 136 |
do_test where8-1.14 { |
| 137 |
execsql_status2 { |
| 138 |
SELECT c FROM t1 |
| 139 |
WHERE |
| 140 |
a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR |
| 141 |
b = 'seven' OR a = 8 OR b = 'nine' OR a = 10 |
| 142 |
ORDER BY rowid |
| 143 |
} |
| 144 |
} {II III IV V VI VII VIII IX X 0 1 33} |
| 145 |
|
| 146 |
do_test where8-1.15 { |
| 147 |
execsql_status2 { |
| 148 |
SELECT c FROM t1 WHERE |
| 149 |
a BETWEEN 2 AND 4 OR b = 'nine' |
| 150 |
ORDER BY rowid |
| 151 |
} |
| 152 |
} {II III IV IX 0 1 12} |
| 153 |
|
| 154 |
|
| 155 |
#-------------------------------------------------------------------------- |
| 156 |
# Tests where8-2.*: Virtual tables |
| 157 |
# |
| 158 |
|
| 159 |
if 0 { |
| 160 |
ifcapable vtab { |
| 161 |
# Register the 'echo' module used for testing virtual tables. |
| 162 |
# |
| 163 |
register_echo_module [sqlite3_connection_pointer db] |
| 164 |
|
| 165 |
do_test where8-2.1 { |
| 166 |
execsql { |
| 167 |
CREATE VIRTUAL TABLE e1 USING echo(t1); |
| 168 |
SELECT b FROM e1; |
| 169 |
} |
| 170 |
} {one two three four five six seven eight nine ten} |
| 171 |
|
| 172 |
do_test where8-2.2.1 { |
| 173 |
set echo_module "" |
| 174 |
execsql { |
| 175 |
SELECT c FROM e1 WHERE a=1 OR b='three'; |
| 176 |
} |
| 177 |
} {I III} |
| 178 |
do_test where8-2.2.2 { |
| 179 |
set echo_module |
| 180 |
} {TODO: What should this be?} |
| 181 |
} |
| 182 |
} |
| 183 |
|
| 184 |
#-------------------------------------------------------------------------- |
| 185 |
# Tests where8-3.*: Cases with multiple tables in the FROM clause. |
| 186 |
# |
| 187 |
do_test where8-3.1 { |
| 188 |
execsql { |
| 189 |
CREATE TABLE t2(d, e, f); |
| 190 |
CREATE INDEX i3 ON t2(d); |
| 191 |
CREATE INDEX i4 ON t2(e); |
| 192 |
|
| 193 |
INSERT INTO t2 VALUES(1, NULL, 'I'); |
| 194 |
INSERT INTO t2 VALUES(2, 'four', 'IV'); |
| 195 |
INSERT INTO t2 VALUES(3, NULL, 'IX'); |
| 196 |
INSERT INTO t2 VALUES(4, 'sixteen', 'XVI'); |
| 197 |
INSERT INTO t2 VALUES(5, NULL, 'XXV'); |
| 198 |
INSERT INTO t2 VALUES(6, 'thirtysix', 'XXXVI'); |
| 199 |
INSERT INTO t2 VALUES(7, 'fortynine', 'XLIX'); |
| 200 |
INSERT INTO t2 VALUES(8, 'sixtyeight', 'LXIV'); |
| 201 |
INSERT INTO t2 VALUES(9, 'eightyone', 'LXXXIX'); |
| 202 |
INSERT INTO t2 VALUES(10, NULL, 'C'); |
| 203 |
} |
| 204 |
} {} |
| 205 |
|
| 206 |
do_test where8-3.2 { |
| 207 |
execsql_status { |
| 208 |
SELECT a, d FROM t1, t2 WHERE b=e |
| 209 |
} |
| 210 |
} {4 2 9 0} |
| 211 |
|
| 212 |
do_test where8-3.3 { |
| 213 |
execsql_status { |
| 214 |
SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = 6 |
| 215 |
} |
| 216 |
} {2 6 3 6 0 0} |
| 217 |
|
| 218 |
do_test where8-3.4 { |
| 219 |
execsql_status { |
| 220 |
SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a |
| 221 |
} |
| 222 |
} {2 2 3 3 0 0} |
| 223 |
|
| 224 |
do_test where8-3.5 { |
| 225 |
execsql_status { |
| 226 |
SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = +a OR e = 'sixteen') |
| 227 |
ORDER BY +a, +d; |
| 228 |
} |
| 229 |
} {2 2 2 4 3 3 3 4 0 1} |
| 230 |
|
| 231 |
do_test where8-3.6 { |
| 232 |
# The first part of the WHERE clause in this query, (a=2 OR a=3) is |
| 233 |
# transformed into "a IN (2, 3)". This is why the sort is required. |
| 234 |
# |
| 235 |
execsql_status { |
| 236 |
SELECT a, d |
| 237 |
FROM t1, t2 |
| 238 |
WHERE (a = 2 OR a = 3) AND (d = +a OR e = 'sixteen') |
| 239 |
ORDER BY t1.rowid |
| 240 |
} |
| 241 |
} {2 2 2 4 3 3 3 4 0 1} |
| 242 |
do_test where8-3.7 { |
| 243 |
execsql_status { |
| 244 |
SELECT a, d |
| 245 |
FROM t1, t2 |
| 246 |
WHERE a = 2 AND (d = a OR e = 'sixteen') |
| 247 |
ORDER BY t1.rowid |
| 248 |
} |
| 249 |
} {/2 2 2 4 0 [01]/} |
| 250 |
do_test where8-3.8 { |
| 251 |
execsql_status { |
| 252 |
SELECT a, d |
| 253 |
FROM t1, t2 |
| 254 |
WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen') |
| 255 |
ORDER BY t1.rowid |
| 256 |
} |
| 257 |
} {2 2 2 4 3 3 3 4 0 1} |
| 258 |
|
| 259 |
do_test where8-3.9 { |
| 260 |
# The "OR c = 'IX'" term forces a linear scan. |
| 261 |
execsql_status { |
| 262 |
SELECT a, d |
| 263 |
FROM t1, t2 |
| 264 |
WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen') |
| 265 |
ORDER BY t1.rowid |
| 266 |
} |
| 267 |
} {2 2 2 4 3 3 3 4 9 9 9 4 9 0} |
| 268 |
|
| 269 |
do_test where8-3.10 { |
| 270 |
execsql_status { |
| 271 |
SELECT d FROM t2 WHERE e IS NULL OR e = 'four' |
| 272 |
} |
| 273 |
} {1 3 5 10 2 0 0} |
| 274 |
|
| 275 |
do_test where8-3.11 { |
| 276 |
execsql_status { |
| 277 |
SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a |
| 278 |
} |
| 279 |
} {1 1 2 2 3 3 4 2 4 4 0 0} |
| 280 |
do_test where8-3.12 { |
| 281 |
execsql_status { |
| 282 |
SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a |
| 283 |
} |
| 284 |
} {1 1 2 2 3 3 4 2 4 4 9 0} |
| 285 |
do_test where8-3.13 { |
| 286 |
execsql_status { |
| 287 |
SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 |
| 288 |
} |
| 289 |
} {1 1 2 2 3 3 4 2 4 4 9 0} |
| 290 |
|
| 291 |
do_test where8-3.14 { |
| 292 |
execsql_status { |
| 293 |
SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5 |
| 294 |
} |
| 295 |
} {IV V 9 0} |
| 296 |
|
| 297 |
do_test where8-3.15 { |
| 298 |
execsql_status { |
| 299 |
SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = ( |
| 300 |
SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d |
| 301 |
) |
| 302 |
ORDER BY c |
| 303 |
} |
| 304 |
} {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1} |
| 305 |
|
| 306 |
|
| 307 |
do_test where8-3.21 { |
| 308 |
execsql_status { |
| 309 |
SELECT a, d FROM t1, (t2) WHERE (a=d OR b=e) AND a<5 ORDER BY a |
| 310 |
} |
| 311 |
} {1 1 2 2 3 3 4 2 4 4 0 0} |
| 312 |
do_test where8-3.21.1 { |
| 313 |
execsql_status { |
| 314 |
SELECT a, d FROM t1, ((t2)) AS t3 WHERE (a=d OR b=e) AND a<5 ORDER BY a |
| 315 |
} |
| 316 |
} {1 1 2 2 3 3 4 2 4 4 0 0} |
| 317 |
if {[permutation] != "no_optimization"} { |
| 318 |
do_test where8-3.21.2 { |
| 319 |
execsql_status { |
| 320 |
SELECT a, d FROM t1, ((SELECT * FROM t2)) AS t3 WHERE (a=d OR b=e) AND a<5 ORDER BY a |
| 321 |
} |
| 322 |
} {1 1 2 2 3 3 4 2 4 4 0 0} |
| 323 |
} |
| 324 |
do_test where8-3.22 { |
| 325 |
execsql_status { |
| 326 |
SELECT a, d FROM ((((((t1))), (((t2)))))) |
| 327 |
WHERE (a=d OR b=e) AND a<5 ORDER BY a |
| 328 |
} |
| 329 |
} {1 1 2 2 3 3 4 2 4 4 0 0} |
| 330 |
if {[permutation] != "no_optimization"} { |
| 331 |
do_test where8-3.23 { |
| 332 |
execsql_status { |
| 333 |
SELECT * FROM ((SELECT * FROM t2)) AS t3; |
| 334 |
} |
| 335 |
} {1 {} I 2 four IV 3 {} IX 4 sixteen XVI 5 {} XXV 6 thirtysix XXXVI 7 fortynine XLIX 8 sixtyeight LXIV 9 eightyone LXXXIX 10 {} C 9 0} |
| 336 |
} |
| 337 |
|
| 338 |
#----------------------------------------------------------------------- |
| 339 |
# The following tests - where8-4.* - verify that adding or removing |
| 340 |
# indexes does not change the results returned by various queries. |
| 341 |
# |
| 342 |
do_test where8-4.1 { |
| 343 |
execsql { |
| 344 |
BEGIN; |
| 345 |
CREATE TABLE t3(a INTEGER, b REAL, c TEXT); |
| 346 |
CREATE TABLE t4(f INTEGER, g REAL, h TEXT); |
| 347 |
INSERT INTO t3 VALUES('hills', NULL, 1415926535); |
| 348 |
INSERT INTO t3 VALUES('and', 'of', NULL); |
| 349 |
INSERT INTO t3 VALUES('have', 'towering', 53594.08128); |
| 350 |
INSERT INTO t3 VALUES(NULL, 45.64856692, 'Not'); |
| 351 |
INSERT INTO t3 VALUES('same', 5028841971, NULL); |
| 352 |
INSERT INTO t3 VALUES('onlookers', 'in', 8214808651); |
| 353 |
INSERT INTO t3 VALUES(346.0348610, 2643383279, NULL); |
| 354 |
INSERT INTO t3 VALUES(1415926535, 'of', 'are'); |
| 355 |
INSERT INTO t3 VALUES(NULL, 0.4811174502, 'snapshots'); |
| 356 |
INSERT INTO t3 VALUES('over', 'the', 8628034825); |
| 357 |
INSERT INTO t3 VALUES(8628034825, 66.59334461, 2847564.823); |
| 358 |
INSERT INTO t3 VALUES('onlookers', 'same', 'and'); |
| 359 |
INSERT INTO t3 VALUES(NULL, 'light', 6939937510); |
| 360 |
INSERT INTO t3 VALUES('from', 'their', 'viewed'); |
| 361 |
INSERT INTO t3 VALUES('from', 'Alpine', 'snapshots'); |
| 362 |
INSERT INTO t3 VALUES('from', 'sometimes', 'unalike'); |
| 363 |
INSERT INTO t3 VALUES(1339.360726, 'light', 'have'); |
| 364 |
INSERT INTO t3 VALUES(6939937510, 3282306647, 'other'); |
| 365 |
INSERT INTO t3 VALUES('paintings', 8628034825, 'all'); |
| 366 |
INSERT INTO t3 VALUES('paintings', NULL, 'same'); |
| 367 |
INSERT INTO t3 VALUES('Alpine', 378678316.5, 'unalike'); |
| 368 |
INSERT INTO t3 VALUES('Alpine', NULL, 'same'); |
| 369 |
INSERT INTO t3 VALUES(1339.360726, 2847564.823, 'over'); |
| 370 |
INSERT INTO t3 VALUES('villages', 'their', 'have'); |
| 371 |
INSERT INTO t3 VALUES('unalike', 'remarkably', 'in'); |
| 372 |
INSERT INTO t3 VALUES('and', 8979323846, 'and'); |
| 373 |
INSERT INTO t3 VALUES(NULL, 1415926535, 'an'); |
| 374 |
INSERT INTO t3 VALUES(271.2019091, 8628034825, 0.4811174502); |
| 375 |
INSERT INTO t3 VALUES('all', 3421170679, 'the'); |
| 376 |
INSERT INTO t3 VALUES('Not', 'and', 1415926535); |
| 377 |
INSERT INTO t3 VALUES('of', 'other', 'light'); |
| 378 |
INSERT INTO t3 VALUES(NULL, 'towering', 'Not'); |
| 379 |
INSERT INTO t3 VALUES(346.0348610, NULL, 'other'); |
| 380 |
INSERT INTO t3 VALUES('Not', 378678316.5, NULL); |
| 381 |
INSERT INTO t3 VALUES('snapshots', 8628034825, 'of'); |
| 382 |
INSERT INTO t3 VALUES(3282306647, 271.2019091, 'and'); |
| 383 |
INSERT INTO t3 VALUES(50.58223172, 378678316.5, 5028841971); |
| 384 |
INSERT INTO t3 VALUES(50.58223172, 2643383279, 'snapshots'); |
| 385 |
INSERT INTO t3 VALUES('writings', 8979323846, 8979323846); |
| 386 |
INSERT INTO t3 VALUES('onlookers', 'his', 'in'); |
| 387 |
INSERT INTO t3 VALUES('unalike', 8628034825, 1339.360726); |
| 388 |
INSERT INTO t3 VALUES('of', 'Alpine', 'and'); |
| 389 |
INSERT INTO t3 VALUES('onlookers', NULL, 'from'); |
| 390 |
INSERT INTO t3 VALUES('writings', 'it', 1339.360726); |
| 391 |
INSERT INTO t3 VALUES('it', 'and', 'villages'); |
| 392 |
INSERT INTO t3 VALUES('an', 'the', 'villages'); |
| 393 |
INSERT INTO t3 VALUES(8214808651, 8214808651, 'same'); |
| 394 |
INSERT INTO t3 VALUES(346.0348610, 'light', 1415926535); |
| 395 |
INSERT INTO t3 VALUES(NULL, 8979323846, 'and'); |
| 396 |
INSERT INTO t3 VALUES(NULL, 'same', 1339.360726); |
| 397 |
INSERT INTO t4 VALUES('his', 'from', 'an'); |
| 398 |
INSERT INTO t4 VALUES('snapshots', 'or', NULL); |
| 399 |
INSERT INTO t4 VALUES('Alpine', 'have', 'it'); |
| 400 |
INSERT INTO t4 VALUES('have', 'peak', 'remarkably'); |
| 401 |
INSERT INTO t4 VALUES('hills', NULL, 'Not'); |
| 402 |
INSERT INTO t4 VALUES('same', 'from', 2643383279); |
| 403 |
INSERT INTO t4 VALUES('have', 'angle', 8628034825); |
| 404 |
INSERT INTO t4 VALUES('sometimes', 'it', 2847564.823); |
| 405 |
INSERT INTO t4 VALUES(0938446095, 'peak', 'of'); |
| 406 |
INSERT INTO t4 VALUES(8628034825, 'and', 'same'); |
| 407 |
INSERT INTO t4 VALUES('and', 271.2019091, 'their'); |
| 408 |
INSERT INTO t4 VALUES('the', 'of', 'remarkably'); |
| 409 |
INSERT INTO t4 VALUES('and', 3421170679, 1415926535); |
| 410 |
INSERT INTO t4 VALUES('and', 'in', 'all'); |
| 411 |
INSERT INTO t4 VALUES(378678316.5, 0.4811174502, 'snapshots'); |
| 412 |
INSERT INTO t4 VALUES('it', 'are', 'have'); |
| 413 |
INSERT INTO t4 VALUES('angle', 'snapshots', 378678316.5); |
| 414 |
INSERT INTO t4 VALUES('from', 1415926535, 8628034825); |
| 415 |
INSERT INTO t4 VALUES('snapshots', 'angle', 'have'); |
| 416 |
INSERT INTO t4 VALUES(3421170679, 0938446095, 'Not'); |
| 417 |
INSERT INTO t4 VALUES('peak', NULL, 0.4811174502); |
| 418 |
INSERT INTO t4 VALUES('same', 'have', 'Alpine'); |
| 419 |
INSERT INTO t4 VALUES(271.2019091, 66.59334461, 0938446095); |
| 420 |
INSERT INTO t4 VALUES(8979323846, 'his', 'an'); |
| 421 |
INSERT INTO t4 VALUES(NULL, 'and', 3282306647); |
| 422 |
INSERT INTO t4 VALUES('remarkably', NULL, 'Not'); |
| 423 |
INSERT INTO t4 VALUES('villages', 4543.266482, 'his'); |
| 424 |
INSERT INTO t4 VALUES(2643383279, 'paintings', 'onlookers'); |
| 425 |
INSERT INTO t4 VALUES(1339.360726, 'of', 'the'); |
| 426 |
INSERT INTO t4 VALUES('peak', 'other', 'peak'); |
| 427 |
INSERT INTO t4 VALUES('it', 'or', 8979323846); |
| 428 |
INSERT INTO t4 VALUES('onlookers', 'Not', 'towering'); |
| 429 |
INSERT INTO t4 VALUES(NULL, 'peak', 'Not'); |
| 430 |
INSERT INTO t4 VALUES('of', 'have', 6939937510); |
| 431 |
INSERT INTO t4 VALUES('light', 'hills', 0.4811174502); |
| 432 |
INSERT INTO t4 VALUES(5028841971, 'Not', 'it'); |
| 433 |
INSERT INTO t4 VALUES('and', 'Not', NULL); |
| 434 |
INSERT INTO t4 VALUES(346.0348610, 'villages', NULL); |
| 435 |
INSERT INTO t4 VALUES(8979323846, NULL, 6939937510); |
| 436 |
INSERT INTO t4 VALUES('an', 'light', 'peak'); |
| 437 |
INSERT INTO t4 VALUES(5028841971, 6939937510, 'light'); |
| 438 |
INSERT INTO t4 VALUES('sometimes', 'peak', 'peak'); |
| 439 |
INSERT INTO t4 VALUES(378678316.5, 5028841971, 'an'); |
| 440 |
INSERT INTO t4 VALUES(378678316.5, 'his', 'Alpine'); |
| 441 |
INSERT INTO t4 VALUES('from', 'of', 'all'); |
| 442 |
INSERT INTO t4 VALUES(0938446095, 'same', NULL); |
| 443 |
INSERT INTO t4 VALUES(0938446095, 'Alpine', NULL); |
| 444 |
INSERT INTO t4 VALUES('his', 'of', 378678316.5); |
| 445 |
INSERT INTO t4 VALUES(271.2019091, 'viewed', 3282306647); |
| 446 |
INSERT INTO t4 VALUES('hills', 'all', 'peak'); |
| 447 |
CREATE TABLE t5(s); |
| 448 |
INSERT INTO t5 VALUES('tab-t5'); |
| 449 |
CREATE TABLE t6(t); |
| 450 |
INSERT INTO t6 VALUES(123456); |
| 451 |
COMMIT; |
| 452 |
} |
| 453 |
} {} |
| 454 |
|
| 455 |
catch {unset results} |
| 456 |
catch {unset A} |
| 457 |
catch {unset B} |
| 458 |
|
| 459 |
set A 2 |
| 460 |
foreach idxsql { |
| 461 |
{ |
| 462 |
/* No indexes */ |
| 463 |
} { |
| 464 |
CREATE INDEX i5 ON t3(a); |
| 465 |
} { |
| 466 |
CREATE INDEX i5 ON t3(a, b); |
| 467 |
CREATE INDEX i6 ON t4(f); |
| 468 |
} { |
| 469 |
CREATE UNIQUE INDEX i5 ON t3(a, b); |
| 470 |
CREATE INDEX i7 ON t3(c); |
| 471 |
CREATE INDEX i6 ON t4(f); |
| 472 |
CREATE INDEX i8 ON t4(h); |
| 473 |
} { |
| 474 |
CREATE INDEX i5 ON t3(a, b, c); |
| 475 |
CREATE INDEX i6 ON t4(f, g, h); |
| 476 |
CREATE INDEX i7 ON t3(c, b, a); |
| 477 |
CREATE INDEX i8 ON t4(h, g, f); |
| 478 |
} |
| 479 |
} { |
| 480 |
|
| 481 |
execsql { |
| 482 |
DROP INDEX IF EXISTS i5; |
| 483 |
DROP INDEX IF EXISTS i6; |
| 484 |
DROP INDEX IF EXISTS i7; |
| 485 |
DROP INDEX IF EXISTS i8; |
| 486 |
} |
| 487 |
execsql $idxsql |
| 488 |
|
| 489 |
foreach {B sql} { |
| 490 |
1 { SELECT * FROM t3 WHERE c LIKE b } |
| 491 |
2 { SELECT * FROM t3 WHERE c||'' LIKE 'the%' } |
| 492 |
3 { SELECT * FROM t3 WHERE rowid LIKE '12%' } |
| 493 |
4 { SELECT * FROM t3 WHERE +c LIKE 'the%' } |
| 494 |
5 { SELECT * FROM t3 WHERE c LIKE 'the%' } |
| 495 |
6 { SELECT * FROM t3 WHERE c GLOB '*llo' } |
| 496 |
|
| 497 |
7 { SELECT * FROM t3 WHERE a = 'angle' } |
| 498 |
8 { SELECT * FROM t3 WHERE a = 'it' OR b = 6939937510 } |
| 499 |
9 { SELECT * FROM t3, t4 WHERE a = 'painting' OR a = 'are' OR a = f } |
| 500 |
10 { SELECT * FROM t3, t4 WHERE a = 'all' OR a = 'and' OR a = h } |
| 501 |
11 { SELECT * FROM t3, t4 WHERE a < 'of' OR b > 346 AND c IS NULL } |
| 502 |
12 { SELECT * FROM t3, t4 WHERE 'the' > a OR b > 'have' AND c = 1415926535 } |
| 503 |
|
| 504 |
13 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a = 3421170679 } |
| 505 |
14 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a IS NULL } |
| 506 |
15 { SELECT * FROM t3 WHERE c > 'one' OR c >= 'one' OR c LIKE 'one%' } |
| 507 |
16 { SELECT * FROM t3 WHERE c > 'one' OR c = c OR c = a } |
| 508 |
17 { SELECT * FROM t3 WHERE c IS NULL OR a >= 'peak' } |
| 509 |
18 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') OR a>1 } |
| 510 |
19 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') AND a>1 } |
| 511 |
20 { SELECT * FROM t3 WHERE c IS NULL AND a>'one' } |
| 512 |
21 { SELECT * FROM t3 WHERE c IS NULL OR a>'one' } |
| 513 |
22 { SELECT * FROM t3 WHERE b = b AND a > 'are' } |
| 514 |
23 { SELECT * FROM t3 WHERE c <= b OR b < 'snapshots' } |
| 515 |
24 { SELECT * FROM t3 WHERE 'onlookers' >= c AND a <= b OR b = 'angle' } |
| 516 |
25 { SELECT * FROM t3 WHERE b = 'from' } |
| 517 |
26 { SELECT * FROM t3 WHERE b = 4543.266482 } |
| 518 |
27 { SELECT * FROM t3 WHERE c < 3282306647 } |
| 519 |
28 { SELECT * FROM t3 WHERE c IS NULL AND b >= c } |
| 520 |
29 { SELECT * FROM t3 WHERE b > 0.4811174502 AND c = 'other' AND 'viewed' > a } |
| 521 |
30 { SELECT * FROM t3 WHERE c = 'peak' } |
| 522 |
31 { SELECT * FROM t3 WHERE c < 53594.08128 OR c <= b } |
| 523 |
32 { SELECT * FROM t3 WHERE 'writings' <= b } |
| 524 |
33 { SELECT * FROM t3 WHERE 2643383279 = b OR c < b AND b <= 3282306647 } |
| 525 |
34 { SELECT * FROM t3 WHERE a IS NULL } |
| 526 |
35 { SELECT * FROM t3 WHERE 'writings' = a OR b = 378678316.5 } |
| 527 |
36 { SELECT * FROM t3 WHERE 'and' >= c } |
| 528 |
37 { SELECT * FROM t3 WHERE c < 'from' } |
| 529 |
38 { SELECT * FROM t3 WHERE 'his' < c OR b < b } |
| 530 |
39 { SELECT * FROM t3 WHERE 53594.08128 = b AND c >= b } |
| 531 |
40 { SELECT * FROM t3 WHERE 'unalike' < c AND 'are' >= c AND a <= b } |
| 532 |
41 { SELECT * FROM t3 WHERE b >= 4543.266482 OR 'Alpine' > a OR 271.2019091 <= a } |
| 533 |
42 { SELECT * FROM t3 WHERE b = c } |
| 534 |
43 { SELECT * FROM t3 WHERE c > a AND b < 'all' } |
| 535 |
44 { SELECT * FROM t3 WHERE c BETWEEN 'hills' AND 'snapshots' AND c <= 'the' OR c = a } |
| 536 |
45 { SELECT * FROM t3 WHERE b > c AND c >= 'hills' } |
| 537 |
46 { SELECT * FROM t3 WHERE b > 'or' OR a <= 'hills' OR c IS NULL } |
| 538 |
47 { SELECT * FROM t3 WHERE c > b OR b BETWEEN 1339.360726 AND 'onlookers' OR 1415926535 >= b } |
| 539 |
48 { SELECT * FROM t3 WHERE a IS NULL } |
| 540 |
49 { SELECT * FROM t3 WHERE a > 'other' } |
| 541 |
50 { SELECT * FROM t3 WHERE 'the' <= c AND a <= c } |
| 542 |
51 { SELECT * FROM t3 WHERE 346.0348610 = a AND c = b } |
| 543 |
52 { SELECT * FROM t3 WHERE c BETWEEN 50.58223172 AND 'same' AND a < b } |
| 544 |
53 { SELECT * FROM t3 WHERE 'Alpine' <= b AND c >= 'angle' OR b <= 271.2019091 } |
| 545 |
54 { SELECT * FROM t3 WHERE a < a AND 1415926535 > b } |
| 546 |
55 { SELECT * FROM t3 WHERE c > a AND 'have' >= c } |
| 547 |
56 { SELECT * FROM t3 WHERE b <= b AND c > b } |
| 548 |
57 { SELECT * FROM t3 WHERE a IS NULL AND c <= c } |
| 549 |
58 { SELECT * FROM t3 WHERE b < c OR b = c } |
| 550 |
59 { SELECT * FROM t3 WHERE c < b AND b >= 'it' } |
| 551 |
60 { SELECT * FROM t3 WHERE a = b AND a <= b OR b >= a } |
| 552 |
61 { SELECT * FROM t3 WHERE b = c } |
| 553 |
62 { SELECT * FROM t3 WHERE c BETWEEN 'the' AND 271.2019091 OR c <= 3282306647 AND c >= b } |
| 554 |
63 { SELECT * FROM t3 WHERE c >= c AND c < 'writings' } |
| 555 |
64 { SELECT * FROM t3 WHERE c <= 3282306647 AND b > a OR 'unalike' <= a } |
| 556 |
65 { SELECT * FROM t3 WHERE a > c } |
| 557 |
66 { SELECT * FROM t3 WHERE c = 'it' OR b >= b } |
| 558 |
67 { SELECT * FROM t3 WHERE c = a OR b < c } |
| 559 |
68 { SELECT * FROM t3 WHERE b > a } |
| 560 |
69 { SELECT * FROM t3 WHERE a < b OR a > 4543.266482 OR 'same' = b } |
| 561 |
70 { SELECT * FROM t3 WHERE c < c OR b <= c OR a <= b } |
| 562 |
71 { SELECT * FROM t3 WHERE c > a } |
| 563 |
72 { SELECT * FROM t3 WHERE c > b } |
| 564 |
73 { SELECT * FROM t3 WHERE b <= a } |
| 565 |
74 { SELECT * FROM t3 WHERE 3282306647 < b AND a >= 'or' OR a >= 378678316.5 } |
| 566 |
75 { SELECT * FROM t3 WHERE 50.58223172 <= c OR c = c AND b < b } |
| 567 |
76 { SELECT * FROM t3 WHERE 'and' < b OR b < c OR c > 1339.360726 } |
| 568 |
77 { SELECT * FROM t3 WHERE b <= c } |
| 569 |
78 { SELECT * FROM t3 WHERE 'in' <= c } |
| 570 |
79 { SELECT * FROM t3 WHERE c <= b AND a > a AND c < b } |
| 571 |
80 { SELECT * FROM t3 WHERE 'over' < b } |
| 572 |
81 { SELECT * FROM t3 WHERE b >= b OR b < c OR a < b } |
| 573 |
82 { SELECT * FROM t3 WHERE 'towering' <= b OR 'towering' = a AND c > b } |
| 574 |
83 { SELECT * FROM t3 WHERE 'peak' = a OR b BETWEEN 2643383279 AND 'the' } |
| 575 |
84 { SELECT * FROM t3 WHERE 'an' < c AND c > 'the' AND c IS NULL } |
| 576 |
85 { SELECT * FROM t3 WHERE a <= 'sometimes' AND a BETWEEN 'unalike' AND 1339.360726 } |
| 577 |
86 { SELECT * FROM t3 WHERE 1339.360726 < c AND c IS NULL } |
| 578 |
87 { SELECT * FROM t3 WHERE b > 'the' } |
| 579 |
88 { SELECT * FROM t3 WHERE 'and' = a } |
| 580 |
89 { SELECT * FROM t3 WHERE b >= b } |
| 581 |
90 { SELECT * FROM t3 WHERE b >= 8979323846 } |
| 582 |
91 { SELECT * FROM t3 WHERE c <= a } |
| 583 |
92 { SELECT * FROM t3 WHERE a BETWEEN 'have' AND 'light' OR a > b OR a >= 378678316.5 } |
| 584 |
93 { SELECT * FROM t3 WHERE c > 3282306647 } |
| 585 |
94 { SELECT * FROM t3 WHERE b > c } |
| 586 |
95 { SELECT * FROM t3 WHERE b >= a AND 'villages' > a AND b >= c } |
| 587 |
96 { SELECT * FROM t3 WHERE 'angle' > a } |
| 588 |
97 { SELECT * FROM t3 WHERE 'paintings' >= a } |
| 589 |
98 { SELECT * FROM t3 WHERE 'or' >= c } |
| 590 |
99 { SELECT * FROM t3 WHERE c < b } |
| 591 |
|
| 592 |
|
| 593 |
101 { SELECT * FROM t3, t4 WHERE f < 'sometimes' OR 'over' <= g AND h < 1415926535 } |
| 594 |
102 { SELECT * FROM t3, t4 WHERE h >= 'from' AND h < 6939937510 OR g > h } |
| 595 |
103 { SELECT * FROM t3, t4 WHERE c <= h AND g = h AND c >= 'all' } |
| 596 |
104 { SELECT * FROM t3, t4 WHERE c = a } |
| 597 |
105 { SELECT * FROM t3, t4 WHERE 'of' >= h } |
| 598 |
106 { SELECT * FROM t3, t4 WHERE f >= b AND a < g AND h < 'and' } |
| 599 |
107 { SELECT * FROM t3, t4 WHERE f <= 8628034825 AND 0938446095 >= b } |
| 600 |
108 { SELECT * FROM t3, t4 WHERE a < 'the' } |
| 601 |
109 { SELECT * FROM t3, t4 WHERE f = 'sometimes' OR b < 'of' } |
| 602 |
110 { SELECT * FROM t3, t4 WHERE c IS NULL } |
| 603 |
111 { SELECT * FROM t3, t4 WHERE 'have' = b OR g <= 346.0348610 } |
| 604 |
112 { SELECT * FROM t3, t4 WHERE f > b AND b <= h } |
| 605 |
113 { SELECT * FROM t3, t4 WHERE f > c OR 'the' = a OR 50.58223172 = a } |
| 606 |
114 { SELECT * FROM t3, t4 WHERE 2643383279 <= a AND c = a } |
| 607 |
115 { SELECT * FROM t3, t4 WHERE h >= b AND 'it' <= b } |
| 608 |
116 { SELECT * FROM t3, t4 WHERE g BETWEEN 'from' AND 'peak' } |
| 609 |
117 { SELECT * FROM t3, t4 WHERE 'their' > a AND g > b AND f <= c } |
| 610 |
118 { SELECT * FROM t3, t4 WHERE h = 5028841971 AND 'unalike' <= f } |
| 611 |
119 { SELECT * FROM t3, t4 WHERE c IS NULL AND a = 3282306647 OR a <= 'Alpine' } |
| 612 |
120 { SELECT * FROM t3, t4 WHERE 'sometimes' <= f OR 8214808651 >= a AND b <= 53594.08128 } |
| 613 |
121 { SELECT * FROM t3, t4 WHERE 6939937510 <= f OR c < f OR 'sometimes' = c } |
| 614 |
122 { SELECT * FROM t3, t4 WHERE b < 'onlookers' AND 'paintings' = g AND c <= h } |
| 615 |
123 { SELECT * FROM t3, t4 WHERE a BETWEEN 'all' AND 'from' OR c > 346.0348610 } |
| 616 |
124 { SELECT * FROM t3, t4 WHERE 'from' <= b OR a BETWEEN 53594.08128 AND 'their' AND c > a } |
| 617 |
125 { SELECT * FROM t3, t4 WHERE h = 2643383279 } |
| 618 |
126 { SELECT * FROM t3, t4 WHERE a <= 'the' } |
| 619 |
127 { SELECT * FROM t3, t4 WHERE h <= c } |
| 620 |
128 { SELECT * FROM t3, t4 WHERE g <= 346.0348610 AND 66.59334461 >= f AND f <= f } |
| 621 |
129 { SELECT * FROM t3, t4 WHERE g >= c OR 'in' < b OR b > g } |
| 622 |
130 { SELECT * FROM t3, t4 WHERE 'over' > g AND b BETWEEN 'unalike' AND 'remarkably' } |
| 623 |
131 { SELECT * FROM t3, t4 WHERE h <= 2847564.823 } |
| 624 |
132 { SELECT * FROM t3, t4 WHERE h <= 'remarkably' AND 4543.266482 > h } |
| 625 |
133 { SELECT * FROM t3, t4 WHERE a >= c AND 'it' > g AND c < c } |
| 626 |
134 { SELECT * FROM t3, t4 WHERE h <= 66.59334461 AND b > 3421170679 } |
| 627 |
135 { SELECT * FROM t3, t4 WHERE h < 'are' OR f BETWEEN 0938446095 AND 'are' OR b = b } |
| 628 |
136 { SELECT * FROM t3, t4 WHERE h = a OR 66.59334461 <= f } |
| 629 |
137 { SELECT * FROM t3, t4 WHERE f > 'of' OR h <= h OR a = f } |
| 630 |
138 { SELECT * FROM t3, t4 WHERE 'other' >= g } |
| 631 |
139 { SELECT * FROM t3, t4 WHERE b <= 3421170679 } |
| 632 |
140 { SELECT * FROM t3, t4 WHERE 'all' = f AND 4543.266482 = b OR f BETWEEN 'and' AND 'angle' } |
| 633 |
141 { SELECT * FROM t3, t4 WHERE 'light' = f OR h BETWEEN 'remarkably' AND 1415926535 } |
| 634 |
142 { SELECT * FROM t3, t4 WHERE 'hills' = f OR 'the' >= f } |
| 635 |
143 { SELECT * FROM t3, t4 WHERE a > 346.0348610 } |
| 636 |
144 { SELECT * FROM t3, t4 WHERE 5028841971 = h } |
| 637 |
145 { SELECT * FROM t3, t4 WHERE b >= c AND 'the' >= g OR 45.64856692 <= g } |
| 638 |
146 { SELECT * FROM t3, t4 WHERE c < 5028841971 } |
| 639 |
147 { SELECT * FROM t3, t4 WHERE a > a } |
| 640 |
148 { SELECT * FROM t3, t4 WHERE c = 'snapshots' } |
| 641 |
149 { SELECT * FROM t3, t4 WHERE h > 1339.360726 AND 'and' > c } |
| 642 |
150 { SELECT * FROM t3, t4 WHERE 'and' > g OR 'sometimes' = c } |
| 643 |
151 { SELECT * FROM t3, t4 WHERE g >= 'the' AND b >= 'onlookers' } |
| 644 |
152 { SELECT * FROM t3, t4 WHERE h BETWEEN 'other' AND 2643383279 } |
| 645 |
153 { SELECT * FROM t3, t4 WHERE 'it' = b } |
| 646 |
154 { SELECT * FROM t3, t4 WHERE f = c OR c BETWEEN 'and' AND 0.4811174502 } |
| 647 |
155 { SELECT * FROM t3, t4 WHERE b <= 'sometimes' OR c <= 0938446095 } |
| 648 |
156 { SELECT * FROM t3, t4 WHERE 'and' <= b } |
| 649 |
157 { SELECT * FROM t3, t4 WHERE g > a AND f = 'the' AND b < a } |
| 650 |
158 { SELECT * FROM t3, t4 WHERE a < 'an' } |
| 651 |
159 { SELECT * FROM t3, t4 WHERE a BETWEEN 'his' AND 'same' OR 8628034825 > f } |
| 652 |
160 { SELECT * FROM t3, t4 WHERE b = 'peak' } |
| 653 |
161 { SELECT * FROM t3, t4 WHERE f IS NULL AND a >= h } |
| 654 |
162 { SELECT * FROM t3, t4 WHERE a IS NULL OR 2643383279 = c } |
| 655 |
163 { SELECT * FROM t3, t4 WHERE b >= 5028841971 AND f < c AND a IS NULL } |
| 656 |
164 { SELECT * FROM t3, t4 WHERE a >= g } |
| 657 |
165 { SELECT * FROM t3, t4 WHERE c IS NULL } |
| 658 |
166 { SELECT * FROM t3, t4 WHERE h >= h } |
| 659 |
167 { SELECT * FROM t3, t4 WHERE 'over' <= h } |
| 660 |
168 { SELECT * FROM t3, t4 WHERE b < 4543.266482 OR b = 2643383279 OR 8628034825 < b } |
| 661 |
169 { SELECT * FROM t3, t4 WHERE g >= 6939937510 } |
| 662 |
170 { SELECT * FROM t3, t4 WHERE 'or' < a OR b < g } |
| 663 |
171 { SELECT * FROM t3, t4 WHERE h < 'hills' OR 'and' > g } |
| 664 |
172 { SELECT * FROM t3, t4 WHERE 'from' > f OR f <= f } |
| 665 |
173 { SELECT * FROM t3, t4 WHERE 'viewed' > b AND f < c } |
| 666 |
174 { SELECT * FROM t3, t4 WHERE 'of' <= a } |
| 667 |
175 { SELECT * FROM t3, t4 WHERE f > 0938446095 } |
| 668 |
176 { SELECT * FROM t3, t4 WHERE a = g } |
| 669 |
177 { SELECT * FROM t3, t4 WHERE g >= b AND f BETWEEN 'peak' AND 'and' } |
| 670 |
178 { SELECT * FROM t3, t4 WHERE g = a AND 'it' > f } |
| 671 |
179 { SELECT * FROM t3, t4 WHERE a <= b OR 'from' > f } |
| 672 |
180 { SELECT * FROM t3, t4 WHERE f < 'and' } |
| 673 |
181 { SELECT * FROM t3, t4 WHERE 6939937510 < b OR 'sometimes' < h } |
| 674 |
182 { SELECT * FROM t3, t4 WHERE f > g AND f < 'peak' } |
| 675 |
183 { SELECT * FROM t3, t4 WHERE a <= 53594.08128 AND c <= f AND f >= c } |
| 676 |
184 { SELECT * FROM t3, t4 WHERE f = c OR 'it' > b OR g BETWEEN 'the' AND 'all' } |
| 677 |
185 { SELECT * FROM t3, t4 WHERE c <= g OR a = h } |
| 678 |
186 { SELECT * FROM t3, t4 WHERE 'same' = b OR c >= 2643383279 } |
| 679 |
187 { SELECT * FROM t3, t4 WHERE h <= g OR c > 66.59334461 OR a <= f } |
| 680 |
188 { SELECT * FROM t3, t4 WHERE b < c AND f = 'writings' } |
| 681 |
189 { SELECT * FROM t3, t4 WHERE b < a } |
| 682 |
190 { SELECT * FROM t3, t4 WHERE c >= f OR c = 'and' } |
| 683 |
191 { SELECT * FROM t3, t4 WHERE f >= 'peak' AND g > f AND h > g } |
| 684 |
192 { SELECT * FROM t3, t4 WHERE a >= 8979323846 AND 'same' > b OR c = 'and' } |
| 685 |
193 { SELECT * FROM t3, t4 WHERE c >= g OR 'writings' >= c AND b = 'all' } |
| 686 |
194 { SELECT * FROM t3, t4 WHERE 'remarkably' < g } |
| 687 |
195 { SELECT * FROM t3, t4 WHERE a BETWEEN 'or' AND 'paintings' AND g <= f } |
| 688 |
196 { SELECT * FROM t3, t4 WHERE 0938446095 > b OR g <= a OR h > b } |
| 689 |
197 { SELECT * FROM t3, t4 WHERE g = 2643383279 AND f = g } |
| 690 |
198 { SELECT * FROM t3, t4 WHERE g < 8979323846 } |
| 691 |
199 { SELECT * FROM t3, t4 WHERE 'are' <= b } |
| 692 |
200 { SELECT * FROM t3, t4 WHERE (a=1415926535 AND f=8628034825) |
| 693 |
OR (a=6939937510 AND f=2643383279) } |
| 694 |
201 { SELECT * FROM t3, t4, t5, t6 |
| 695 |
WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t!=5) |
| 696 |
OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t=123456) } |
| 697 |
202 { SELECT * FROM t3, t4, t5, t6 |
| 698 |
WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t==5) |
| 699 |
OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t!=123456) } |
| 700 |
|
| 701 |
} { |
| 702 |
do_test where8-4.$A.$B.1 { |
| 703 |
unset -nocomplain R |
| 704 |
set R [execsql $sql] |
| 705 |
if {![info exists results($B)]} { |
| 706 |
set results($B) $R |
| 707 |
} |
| 708 |
list |
| 709 |
} {} |
| 710 |
|
| 711 |
do_test where8-4.$A.$B.2 { lsort $R } [lsort $results($B)] |
| 712 |
} |
| 713 |
incr A |
| 714 |
} |
| 715 |
catch {unset results} |
| 716 |
catch {unset A} |
| 717 |
catch {unset B} |
| 718 |
|
| 719 |
# At one point the following tests provoked an invalid write error (writing |
| 720 |
# to memory that had already been freed). It was not possible to demonstrate |
| 721 |
# that this bug could cause a query to return bad data. |
| 722 |
# |
| 723 |
do_test where8-5.1 { |
| 724 |
db close |
| 725 |
sqlite3 db test.db |
| 726 |
sqlite3_db_config_lookaside db 0 0 0 |
| 727 |
execsql { |
| 728 |
CREATE TABLE tA( |
| 729 |
a, b, c, d, e, f, g, h, |
| 730 |
i, j, k, l, m, n, o, p |
| 731 |
); |
| 732 |
} |
| 733 |
execsql { |
| 734 |
SELECT * FROM tA WHERE |
| 735 |
a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND |
| 736 |
i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND |
| 737 |
(p = 1 OR p = 2 OR p = 3) |
| 738 |
} |
| 739 |
} {} |
| 740 |
do_test where8-5.2 { |
| 741 |
execsql { |
| 742 |
SELECT * FROM tA WHERE |
| 743 |
a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND |
| 744 |
i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND |
| 745 |
(p = 1 OR p = 2 OR p = 3) AND n=6 AND o=7 |
| 746 |
} |
| 747 |
} {} |
| 748 |
do_test where8-5.3 { |
| 749 |
execsql { |
| 750 |
INSERT INTO tA VALUES(1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 3, 4, 5, 6, 7, 8); |
| 751 |
CREATE UNIQUE INDEX tAI ON tA(p); |
| 752 |
CREATE TABLE tB(x); |
| 753 |
INSERT INTO tB VALUES('x'); |
| 754 |
} |
| 755 |
execsql { |
| 756 |
SELECT a, x FROM tA LEFT JOIN tB ON ( |
| 757 |
a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND |
| 758 |
i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND |
| 759 |
(p = 1 OR p = 2 OR p = 3) |
| 760 |
) |
| 761 |
} |
| 762 |
} {1 {}} |
| 763 |
|
| 764 |
# The OR optimization and WITHOUT ROWID |
| 765 |
# |
| 766 |
do_execsql_test where8-6.1 { |
| 767 |
CREATE TABLE t600(a PRIMARY KEY, b) WITHOUT rowid; |
| 768 |
CREATE INDEX t600b ON t600(b); |
| 769 |
INSERT INTO t600 VALUES('state','screen'),('exact','dolphin'),('green','mercury'); |
| 770 |
SELECT a, b, '|' FROM t600 WHERE a=='state' OR b='mercury' ORDER BY +a; |
| 771 |
} {green mercury | state screen |} |
| 772 |
|
| 773 |
finish_test |