1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/where2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,624 @@
1.4 +# 2005 July 28
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the use of indices in WHERE clauses
1.16 +# based on recent changes to the optimizer.
1.17 +#
1.18 +# $Id: where2.test,v 1.13 2007/12/10 05:03:48 danielk1977 Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Build some test data
1.24 +#
1.25 +do_test where2-1.0 {
1.26 + execsql {
1.27 + BEGIN;
1.28 + CREATE TABLE t1(w int, x int, y int, z int);
1.29 + }
1.30 + for {set i 1} {$i<=100} {incr i} {
1.31 + set w $i
1.32 + set x [expr {int(log($i)/log(2))}]
1.33 + set y [expr {$i*$i + 2*$i + 1}]
1.34 + set z [expr {$x+$y}]
1.35 + ifcapable tclvar {
1.36 + execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
1.37 + } else {
1.38 + execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)}
1.39 + }
1.40 + }
1.41 + execsql {
1.42 + CREATE UNIQUE INDEX i1w ON t1(w);
1.43 + CREATE INDEX i1xy ON t1(x,y);
1.44 + CREATE INDEX i1zyx ON t1(z,y,x);
1.45 + COMMIT;
1.46 + }
1.47 +} {}
1.48 +
1.49 +# Do an SQL statement. Append the search count to the end of the result.
1.50 +#
1.51 +proc count sql {
1.52 + set ::sqlite_search_count 0
1.53 + return [concat [execsql $sql] $::sqlite_search_count]
1.54 +}
1.55 +
1.56 +# This procedure executes the SQL. Then it checks to see if the OP_Sort
1.57 +# opcode was executed. If an OP_Sort did occur, then "sort" is appended
1.58 +# to the result. If no OP_Sort happened, then "nosort" is appended.
1.59 +#
1.60 +# This procedure is used to check to make sure sorting is or is not
1.61 +# occurring as expected.
1.62 +#
1.63 +proc cksort {sql} {
1.64 + set ::sqlite_sort_count 0
1.65 + set data [execsql $sql]
1.66 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.67 + lappend data $x
1.68 + return $data
1.69 +}
1.70 +
1.71 +# This procedure executes the SQL. Then it appends to the result the
1.72 +# "sort" or "nosort" keyword (as in the cksort procedure above) then
1.73 +# it appends the ::sqlite_query_plan variable.
1.74 +#
1.75 +proc queryplan {sql} {
1.76 + set ::sqlite_sort_count 0
1.77 + set data [execsql $sql]
1.78 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.79 + lappend data $x
1.80 + return [concat $data $::sqlite_query_plan]
1.81 +}
1.82 +
1.83 +
1.84 +# Prefer a UNIQUE index over another index.
1.85 +#
1.86 +do_test where2-1.1 {
1.87 + queryplan {
1.88 + SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396
1.89 + }
1.90 +} {85 6 7396 7402 nosort t1 i1w}
1.91 +
1.92 +# Always prefer a rowid== constraint over any other index.
1.93 +#
1.94 +do_test where2-1.3 {
1.95 + queryplan {
1.96 + SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396 AND rowid=85
1.97 + }
1.98 +} {85 6 7396 7402 nosort t1 *}
1.99 +
1.100 +# When constrained by a UNIQUE index, the ORDER BY clause is always ignored.
1.101 +#
1.102 +do_test where2-2.1 {
1.103 + queryplan {
1.104 + SELECT * FROM t1 WHERE w=85 ORDER BY random(5);
1.105 + }
1.106 +} {85 6 7396 7402 nosort t1 i1w}
1.107 +do_test where2-2.2 {
1.108 + queryplan {
1.109 + SELECT * FROM t1 WHERE x=6 AND y=7396 ORDER BY random(5);
1.110 + }
1.111 +} {85 6 7396 7402 sort t1 i1xy}
1.112 +do_test where2-2.3 {
1.113 + queryplan {
1.114 + SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random(5);
1.115 + }
1.116 +} {85 6 7396 7402 nosort t1 *}
1.117 +
1.118 +
1.119 +# Efficient handling of forward and reverse table scans.
1.120 +#
1.121 +do_test where2-3.1 {
1.122 + queryplan {
1.123 + SELECT * FROM t1 ORDER BY rowid LIMIT 2
1.124 + }
1.125 +} {1 0 4 4 2 1 9 10 nosort t1 *}
1.126 +do_test where2-3.2 {
1.127 + queryplan {
1.128 + SELECT * FROM t1 ORDER BY rowid DESC LIMIT 2
1.129 + }
1.130 +} {100 6 10201 10207 99 6 10000 10006 nosort t1 *}
1.131 +
1.132 +# The IN operator can be used by indices at multiple layers
1.133 +#
1.134 +ifcapable subquery {
1.135 + do_test where2-4.1 {
1.136 + queryplan {
1.137 + SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201)
1.138 + AND x>0 AND x<10
1.139 + ORDER BY w
1.140 + }
1.141 + } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
1.142 + do_test where2-4.2 {
1.143 + queryplan {
1.144 + SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000
1.145 + AND x>0 AND x<10
1.146 + ORDER BY w
1.147 + }
1.148 + } {99 6 10000 10006 sort t1 i1zyx}
1.149 + do_test where2-4.3 {
1.150 + queryplan {
1.151 + SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
1.152 + AND x>0 AND x<10
1.153 + ORDER BY w
1.154 + }
1.155 + } {99 6 10000 10006 sort t1 i1zyx}
1.156 + ifcapable compound {
1.157 + do_test where2-4.4 {
1.158 + queryplan {
1.159 + SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
1.160 + AND y IN (10000,10201)
1.161 + AND x>0 AND x<10
1.162 + ORDER BY w
1.163 + }
1.164 + } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
1.165 + do_test where2-4.5 {
1.166 + queryplan {
1.167 + SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
1.168 + AND y IN (SELECT 10000 UNION SELECT 10201)
1.169 + AND x>0 AND x<10
1.170 + ORDER BY w
1.171 + }
1.172 + } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
1.173 + }
1.174 + do_test where2-4.6 {
1.175 + queryplan {
1.176 + SELECT * FROM t1
1.177 + WHERE x IN (1,2,3,4,5,6,7,8)
1.178 + AND y IN (10000,10001,10002,10003,10004,10005)
1.179 + ORDER BY 2
1.180 + }
1.181 + } {99 6 10000 10006 sort t1 i1xy}
1.182 +
1.183 + # Duplicate entires on the RHS of an IN operator do not cause duplicate
1.184 + # output rows.
1.185 + #
1.186 + do_test where2-4.6 {
1.187 + queryplan {
1.188 + SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
1.189 + ORDER BY w
1.190 + }
1.191 + } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
1.192 + ifcapable compound {
1.193 + do_test where2-4.7 {
1.194 + queryplan {
1.195 + SELECT * FROM t1 WHERE z IN (
1.196 + SELECT 10207 UNION ALL SELECT 10006
1.197 + UNION ALL SELECT 10006 UNION ALL SELECT 10207)
1.198 + ORDER BY w
1.199 + }
1.200 + } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
1.201 + }
1.202 +
1.203 +} ;# ifcapable subquery
1.204 +
1.205 +# The use of an IN operator disables the index as a sorter.
1.206 +#
1.207 +do_test where2-5.1 {
1.208 + queryplan {
1.209 + SELECT * FROM t1 WHERE w=99 ORDER BY w
1.210 + }
1.211 +} {99 6 10000 10006 nosort t1 i1w}
1.212 +
1.213 +ifcapable subquery {
1.214 + do_test where2-5.2 {
1.215 + queryplan {
1.216 + SELECT * FROM t1 WHERE w IN (99) ORDER BY w
1.217 + }
1.218 + } {99 6 10000 10006 sort t1 i1w}
1.219 +}
1.220 +
1.221 +# Verify that OR clauses get translated into IN operators.
1.222 +#
1.223 +set ::idx {}
1.224 +ifcapable subquery {set ::idx i1w}
1.225 +do_test where2-6.1.1 {
1.226 + queryplan {
1.227 + SELECT * FROM t1 WHERE w=99 OR w=100 ORDER BY +w
1.228 + }
1.229 +} [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
1.230 +do_test where2-6.1.2 {
1.231 + queryplan {
1.232 + SELECT * FROM t1 WHERE 99=w OR 100=w ORDER BY +w
1.233 + }
1.234 +} [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
1.235 +do_test where2-6.2 {
1.236 + queryplan {
1.237 + SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=w ORDER BY +w
1.238 + }
1.239 +} [list 6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
1.240 +
1.241 +do_test where2-6.3 {
1.242 + queryplan {
1.243 + SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w
1.244 + }
1.245 +} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
1.246 +do_test where2-6.4 {
1.247 + queryplan {
1.248 + SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w
1.249 + }
1.250 +} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
1.251 +
1.252 +set ::idx {}
1.253 +ifcapable subquery {set ::idx i1zyx}
1.254 +do_test where2-6.5 {
1.255 + queryplan {
1.256 + SELECT b.* FROM t1 a, t1 b
1.257 + WHERE a.w=1 AND (a.y=b.z OR b.z=10)
1.258 + ORDER BY +b.w
1.259 + }
1.260 +} [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
1.261 +do_test where2-6.6 {
1.262 + queryplan {
1.263 + SELECT b.* FROM t1 a, t1 b
1.264 + WHERE a.w=1 AND (b.z=10 OR a.y=b.z OR b.z=10)
1.265 + ORDER BY +b.w
1.266 + }
1.267 +} [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
1.268 +
1.269 +# Ticket #2249. Make sure the OR optimization is not attempted if
1.270 +# comparisons between columns of different affinities are needed.
1.271 +#
1.272 +do_test where2-6.7 {
1.273 + execsql {
1.274 + CREATE TABLE t2249a(a TEXT UNIQUE);
1.275 + CREATE TABLE t2249b(b INTEGER);
1.276 + INSERT INTO t2249a VALUES('0123');
1.277 + INSERT INTO t2249b VALUES(123);
1.278 + }
1.279 + queryplan {
1.280 + -- Because a is type TEXT and b is type INTEGER, both a and b
1.281 + -- will attempt to convert to NUMERIC before the comparison.
1.282 + -- They will thus compare equal.
1.283 + --
1.284 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b;
1.285 + }
1.286 +} {123 0123 nosort t2249b {} t2249a {}}
1.287 +do_test where2-6.9 {
1.288 + queryplan {
1.289 + -- The + operator removes affinity from the rhs. No conversions
1.290 + -- occur and the comparison is false. The result is an empty set.
1.291 + --
1.292 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b;
1.293 + }
1.294 +} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
1.295 +do_test where2-6.9.2 {
1.296 + # The same thing but with the expression flipped around.
1.297 + queryplan {
1.298 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a
1.299 + }
1.300 +} {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
1.301 +do_test where2-6.10 {
1.302 + queryplan {
1.303 + -- Use + on both sides of the comparison to disable indices
1.304 + -- completely. Make sure we get the same result.
1.305 + --
1.306 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
1.307 + }
1.308 +} {nosort t2249b {} t2249a {}}
1.309 +do_test where2-6.11 {
1.310 + # This will not attempt the OR optimization because of the a=b
1.311 + # comparison.
1.312 + queryplan {
1.313 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
1.314 + }
1.315 +} {123 0123 nosort t2249b {} t2249a {}}
1.316 +do_test where2-6.11.2 {
1.317 + # Permutations of the expression terms.
1.318 + queryplan {
1.319 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
1.320 + }
1.321 +} {123 0123 nosort t2249b {} t2249a {}}
1.322 +do_test where2-6.11.3 {
1.323 + # Permutations of the expression terms.
1.324 + queryplan {
1.325 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
1.326 + }
1.327 +} {123 0123 nosort t2249b {} t2249a {}}
1.328 +do_test where2-6.11.4 {
1.329 + # Permutations of the expression terms.
1.330 + queryplan {
1.331 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
1.332 + }
1.333 +} {123 0123 nosort t2249b {} t2249a {}}
1.334 +ifcapable explain&&subquery {
1.335 + # These tests are not run if subquery support is not included in the
1.336 + # build. This is because these tests test the "a = 1 OR a = 2" to
1.337 + # "a IN (1, 2)" optimisation transformation, which is not enabled if
1.338 + # subqueries and the IN operator is not available.
1.339 + #
1.340 + do_test where2-6.12 {
1.341 + # In this case, the +b disables the affinity conflict and allows
1.342 + # the OR optimization to be used again. The result is now an empty
1.343 + # set, the same as in where2-6.9.
1.344 + queryplan {
1.345 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
1.346 + }
1.347 + } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
1.348 + do_test where2-6.12.2 {
1.349 + # In this case, the +b disables the affinity conflict and allows
1.350 + # the OR optimization to be used again. The result is now an empty
1.351 + # set, the same as in where2-6.9.
1.352 + queryplan {
1.353 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
1.354 + }
1.355 + } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
1.356 + do_test where2-6.12.3 {
1.357 + # In this case, the +b disables the affinity conflict and allows
1.358 + # the OR optimization to be used again. The result is now an empty
1.359 + # set, the same as in where2-6.9.
1.360 + queryplan {
1.361 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
1.362 + }
1.363 + } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
1.364 + do_test where2-6.13 {
1.365 + # The addition of +a on the second term disabled the OR optimization.
1.366 + # But we should still get the same empty-set result as in where2-6.9.
1.367 + queryplan {
1.368 + SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
1.369 + }
1.370 + } {nosort t2249b {} t2249a {}}
1.371 +}
1.372 +
1.373 +# Variations on the order of terms in a WHERE clause in order
1.374 +# to make sure the OR optimizer can recognize them all.
1.375 +do_test where2-6.20 {
1.376 + queryplan {
1.377 + SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
1.378 + }
1.379 +} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
1.380 +ifcapable explain&&subquery {
1.381 + # These tests are not run if subquery support is not included in the
1.382 + # build. This is because these tests test the "a = 1 OR a = 2" to
1.383 + # "a IN (1, 2)" optimisation transformation, which is not enabled if
1.384 + # subqueries and the IN operator is not available.
1.385 + #
1.386 + do_test where2-6.21 {
1.387 + queryplan {
1.388 + SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello'
1.389 + }
1.390 + } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
1.391 + do_test where2-6.22 {
1.392 + queryplan {
1.393 + SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello'
1.394 + }
1.395 + } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
1.396 + do_test where2-6.23 {
1.397 + queryplan {
1.398 + SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a
1.399 + }
1.400 + } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
1.401 +}
1.402 +
1.403 +# Unique queries (queries that are guaranteed to return only a single
1.404 +# row of result) do not call the sorter. But all tables must give
1.405 +# a unique result. If any one table in the join does not give a unique
1.406 +# result then sorting is necessary.
1.407 +#
1.408 +do_test where2-7.1 {
1.409 + cksort {
1.410 + create table t8(a unique, b, c);
1.411 + insert into t8 values(1,2,3);
1.412 + insert into t8 values(2,3,4);
1.413 + create table t9(x,y);
1.414 + insert into t9 values(2,4);
1.415 + insert into t9 values(2,3);
1.416 + select y from t8, t9 where a=1 order by a, y;
1.417 + }
1.418 +} {3 4 sort}
1.419 +do_test where2-7.2 {
1.420 + cksort {
1.421 + select * from t8 where a=1 order by b, c
1.422 + }
1.423 +} {1 2 3 nosort}
1.424 +do_test where2-7.3 {
1.425 + cksort {
1.426 + select * from t8, t9 where a=1 and y=3 order by b, x
1.427 + }
1.428 +} {1 2 3 2 3 sort}
1.429 +do_test where2-7.4 {
1.430 + cksort {
1.431 + create unique index i9y on t9(y);
1.432 + select * from t8, t9 where a=1 and y=3 order by b, x
1.433 + }
1.434 +} {1 2 3 2 3 nosort}
1.435 +
1.436 +# Ticket #1807. Using IN constrains on multiple columns of
1.437 +# a multi-column index.
1.438 +#
1.439 +ifcapable subquery {
1.440 + do_test where2-8.1 {
1.441 + execsql {
1.442 + SELECT * FROM t1 WHERE x IN (20,21) AND y IN (1,2)
1.443 + }
1.444 + } {}
1.445 + do_test where2-8.2 {
1.446 + execsql {
1.447 + SELECT * FROM t1 WHERE x IN (1,2) AND y IN (-5,-6)
1.448 + }
1.449 + } {}
1.450 + execsql {CREATE TABLE tx AS SELECT * FROM t1}
1.451 + do_test where2-8.3 {
1.452 + execsql {
1.453 + SELECT w FROM t1
1.454 + WHERE x IN (SELECT x FROM tx WHERE rowid<0)
1.455 + AND +y IN (SELECT y FROM tx WHERE rowid=1)
1.456 + }
1.457 + } {}
1.458 + do_test where2-8.4 {
1.459 + execsql {
1.460 + SELECT w FROM t1
1.461 + WHERE x IN (SELECT x FROM tx WHERE rowid=1)
1.462 + AND y IN (SELECT y FROM tx WHERE rowid<0)
1.463 + }
1.464 + } {}
1.465 + #set sqlite_where_trace 1
1.466 + do_test where2-8.5 {
1.467 + execsql {
1.468 + CREATE INDEX tx_xyz ON tx(x, y, z, w);
1.469 + SELECT w FROM tx
1.470 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.471 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.472 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 12 AND 14)
1.473 + }
1.474 + } {12 13 14}
1.475 + do_test where2-8.6 {
1.476 + execsql {
1.477 + SELECT w FROM tx
1.478 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.479 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 12 AND 14)
1.480 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.481 + }
1.482 + } {12 13 14}
1.483 + do_test where2-8.7 {
1.484 + execsql {
1.485 + SELECT w FROM tx
1.486 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 12 AND 14)
1.487 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.488 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.489 + }
1.490 + } {10 11 12 13 14 15}
1.491 + do_test where2-8.8 {
1.492 + execsql {
1.493 + SELECT w FROM tx
1.494 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.495 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.496 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.497 + }
1.498 + } {10 11 12 13 14 15 16 17 18 19 20}
1.499 + do_test where2-8.9 {
1.500 + execsql {
1.501 + SELECT w FROM tx
1.502 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.503 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.504 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 2 AND 4)
1.505 + }
1.506 + } {}
1.507 + do_test where2-8.10 {
1.508 + execsql {
1.509 + SELECT w FROM tx
1.510 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.511 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 2 AND 4)
1.512 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.513 + }
1.514 + } {}
1.515 + do_test where2-8.11 {
1.516 + execsql {
1.517 + SELECT w FROM tx
1.518 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 2 AND 4)
1.519 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.520 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.521 + }
1.522 + } {}
1.523 + do_test where2-8.12 {
1.524 + execsql {
1.525 + SELECT w FROM tx
1.526 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.527 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.528 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN -4 AND -2)
1.529 + }
1.530 + } {}
1.531 + do_test where2-8.13 {
1.532 + execsql {
1.533 + SELECT w FROM tx
1.534 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.535 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN -4 AND -2)
1.536 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.537 + }
1.538 + } {}
1.539 + do_test where2-8.14 {
1.540 + execsql {
1.541 + SELECT w FROM tx
1.542 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN -4 AND -2)
1.543 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.544 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.545 + }
1.546 + } {}
1.547 + do_test where2-8.15 {
1.548 + execsql {
1.549 + SELECT w FROM tx
1.550 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.551 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.552 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 200 AND 300)
1.553 + }
1.554 + } {}
1.555 + do_test where2-8.16 {
1.556 + execsql {
1.557 + SELECT w FROM tx
1.558 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
1.559 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 200 AND 300)
1.560 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.561 + }
1.562 + } {}
1.563 + do_test where2-8.17 {
1.564 + execsql {
1.565 + SELECT w FROM tx
1.566 + WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 200 AND 300)
1.567 + AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
1.568 + AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
1.569 + }
1.570 + } {}
1.571 + do_test where2-8.18 {
1.572 + execsql {
1.573 + SELECT w FROM tx
1.574 + WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
1.575 + AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
1.576 + AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 200 AND 300)
1.577 + }
1.578 + } {}
1.579 + do_test where2-8.19 {
1.580 + execsql {
1.581 + SELECT w FROM tx
1.582 + WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
1.583 + AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 200 AND 300)
1.584 + AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
1.585 + }
1.586 + } {}
1.587 + do_test where2-8.20 {
1.588 + execsql {
1.589 + SELECT w FROM tx
1.590 + WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300)
1.591 + AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
1.592 + AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
1.593 + }
1.594 + } {}
1.595 +}
1.596 +
1.597 +# Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized
1.598 +# when we have an index on A and B.
1.599 +#
1.600 +ifcapable or_opt&&tclvar {
1.601 + do_test where2-9.1 {
1.602 + execsql {
1.603 + BEGIN;
1.604 + CREATE TABLE t10(a,b,c);
1.605 + INSERT INTO t10 VALUES(1,1,1);
1.606 + INSERT INTO t10 VALUES(1,2,2);
1.607 + INSERT INTO t10 VALUES(1,3,3);
1.608 + }
1.609 + for {set i 4} {$i<=1000} {incr i} {
1.610 + execsql {INSERT INTO t10 VALUES(1,$i,$i)}
1.611 + }
1.612 + execsql {
1.613 + CREATE INDEX i10 ON t10(a,b);
1.614 + COMMIT;
1.615 + SELECT count(*) FROM t10;
1.616 + }
1.617 + } 1000
1.618 + ifcapable subquery {
1.619 + do_test where2-9.2 {
1.620 + count {
1.621 + SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
1.622 + }
1.623 + } {1 2 2 1 3 3 7}
1.624 + }
1.625 +}
1.626 +
1.627 +finish_test