lang/sql/sqlite/test/where2.test

   1 # 2005 July 28
   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 use of indices in WHERE clauses
  13 # based on recent changes to the optimizer.
  14 #
  15 # $Id: where2.test,v 1.15 2009/02/02 01:50:40 drh Exp $
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19
  20 # Build some test data
  21 #
  22 do_test where2-1.0 {
  23   execsql {
  24     BEGIN;
  25     CREATE TABLE t1(w int, x int, y int, z int);
  26   }
  27   for {set i 1} {$i<=100} {incr i} {
  28     set w $i
  29     set x [expr {int(log($i)/log(2))}]
  30     set y [expr {$i*$i + 2*$i + 1}]
  31     set z [expr {$x+$y}]
  32     ifcapable tclvar {
  33       execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
  34     } else {
  35       execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)}
  36     }
  37   }
  38   execsql {
  39     CREATE UNIQUE INDEX i1w ON t1(w);
  40     CREATE INDEX i1xy ON t1(x,y);
  41     CREATE INDEX i1zyx ON t1(z,y,x);
  42     COMMIT;
  43   }
  44 } {}
  45
  46 # Do an SQL statement.  Append the search count to the end of the result.
  47 #
  48 proc count sql {
  49   set ::sqlite_search_count 0
  50   return [concat [execsql $sql] $::sqlite_search_count]
  51 }
  52
  53 # This procedure executes the SQL.  Then it checks to see if the OP_Sort
  54 # opcode was executed.  If an OP_Sort did occur, then "sort" is appended
  55 # to the result.  If no OP_Sort happened, then "nosort" is appended.
  56 #
  57 # This procedure is used to check to make sure sorting is or is not
  58 # occurring as expected.
  59 #
  60 proc cksort {sql} {
  61   set data [execsql $sql]
  62   if {[db status sort]} {set x sort} {set x nosort}
  63   lappend data $x
  64   return $data
  65 }
  66
  67 # This procedure executes the SQL.  Then it appends to the result the
  68 # "sort" or "nosort" keyword (as in the cksort procedure above) then
  69 # it appends the ::sqlite_query_plan variable.
  70 #
  71 proc queryplan {sql} {
  72   set ::sqlite_sort_count 0
  73   set data [execsql $sql]
  74   if {$::sqlite_sort_count} {set x sort} {set x nosort}
  75   lappend data $x
  76   return [concat $data $::sqlite_query_plan]
  77 }
  78
  79
  80 # Prefer a UNIQUE index over another index.
  81 #
  82 do_test where2-1.1 {
  83   queryplan {
  84     SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396
  85   }
  86 } {85 6 7396 7402 nosort t1 i1w}
  87
  88 # Always prefer a rowid== constraint over any other index.
  89 #
  90 do_test where2-1.3 {
  91   queryplan {
  92     SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396 AND rowid=85
  93   }
  94 } {85 6 7396 7402 nosort t1 *}
  95
  96 # When constrained by a UNIQUE index, the ORDER BY clause is always ignored.
  97 #
  98 do_test where2-2.1 {
  99   queryplan {
 100     SELECT * FROM t1 WHERE w=85 ORDER BY random();
 101   }
 102 } {85 6 7396 7402 nosort t1 i1w}
 103 do_test where2-2.2 {
 104   queryplan {
 105     SELECT * FROM t1 WHERE x=6 AND y=7396 ORDER BY random();
 106   }
 107 } {85 6 7396 7402 sort t1 i1xy}
 108 do_test where2-2.3 {
 109   queryplan {
 110     SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random();
 111   }
 112 } {85 6 7396 7402 nosort t1 *}
 113
 114
 115 # Efficient handling of forward and reverse table scans.
 116 #
 117 do_test where2-3.1 {
 118   queryplan {
 119     SELECT * FROM t1 ORDER BY rowid LIMIT 2
 120   }
 121 } {1 0 4 4 2 1 9 10 nosort t1 *}
 122 do_test where2-3.2 {
 123   queryplan {
 124     SELECT * FROM t1 ORDER BY rowid DESC LIMIT 2
 125   }
 126 } {100 6 10201 10207 99 6 10000 10006 nosort t1 *}
 127
 128 # The IN operator can be used by indices at multiple layers
 129 #
 130 ifcapable subquery {
 131   do_test where2-4.1 {
 132     queryplan {
 133       SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201)
 134                        AND x>0 AND x<10
 135       ORDER BY w
 136     }
 137   } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
 138   do_test where2-4.2 {
 139     queryplan {
 140       SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000
 141                        AND x>0 AND x<10
 142       ORDER BY w
 143     }
 144   } {99 6 10000 10006 sort t1 i1zyx}
 145   do_test where2-4.3 {
 146     queryplan {
 147       SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
 148                        AND x>0 AND x<10
 149       ORDER BY w
 150     }
 151   } {99 6 10000 10006 sort t1 i1zyx}
 152   ifcapable compound {
 153     do_test where2-4.4 {
 154       queryplan {
 155         SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
 156                          AND y IN (10000,10201)
 157                          AND x>0 AND x<10
 158         ORDER BY w
 159       }
 160     } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
 161     do_test where2-4.5 {
 162       queryplan {
 163         SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
 164                          AND y IN (SELECT 10000 UNION SELECT 10201)
 165                          AND x>0 AND x<10
 166         ORDER BY w
 167       }
 168     } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
 169   }
 170   do_test where2-4.6 {
 171     queryplan {
 172       SELECT * FROM t1
 173        WHERE x IN (1,2,3,4,5,6,7,8)
 174          AND y IN (10000,10001,10002,10003,10004,10005)
 175        ORDER BY 2
 176     }
 177   } {99 6 10000 10006 sort t1 i1xy}
 178
 179   # Duplicate entires on the RHS of an IN operator do not cause duplicate
 180   # output rows.
 181   #
 182   do_test where2-4.6 {
 183     queryplan {
 184       SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
 185       ORDER BY w
 186     }
 187   } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
 188   ifcapable compound {
 189     do_test where2-4.7 {
 190       queryplan {
 191         SELECT * FROM t1 WHERE z IN (
 192            SELECT 10207 UNION ALL SELECT 10006
 193            UNION ALL SELECT 10006 UNION ALL SELECT 10207)
 194         ORDER BY w
 195       }
 196     } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
 197   }
 198
 199 } ;# ifcapable subquery
 200
 201 # The use of an IN operator disables the index as a sorter.
 202 #
 203 do_test where2-5.1 {
 204   queryplan {
 205     SELECT * FROM t1 WHERE w=99 ORDER BY w
 206   }
 207 } {99 6 10000 10006 nosort t1 i1w}
 208
 209 ifcapable subquery {
 210   do_test where2-5.2 {
 211     queryplan {
 212       SELECT * FROM t1 WHERE w IN (99) ORDER BY w
 213     }
 214   } {99 6 10000 10006 sort t1 i1w}
 215 }
 216
 217 # Verify that OR clauses get translated into IN operators.
 218 #
 219 set ::idx {}
 220 ifcapable subquery {set ::idx i1w}
 221 do_test where2-6.1.1 {
 222   queryplan {
 223     SELECT * FROM t1 WHERE w=99 OR w=100 ORDER BY +w
 224   }
 225 } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
 226 do_test where2-6.1.2 {
 227   queryplan {
 228     SELECT * FROM t1 WHERE 99=w OR 100=w ORDER BY +w
 229   }
 230 } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
 231 do_test where2-6.2 {
 232   queryplan {
 233     SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=w ORDER BY +w
 234   }
 235 } [list 6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
 236
 237 do_test where2-6.3 {
 238   queryplan {
 239     SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w
 240   }
 241 } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
 242 do_test where2-6.4 {
 243   queryplan {
 244     SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w
 245   }
 246 } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
 247
 248 set ::idx {}
 249 ifcapable subquery {set ::idx i1zyx}
 250 do_test where2-6.5 {
 251   queryplan {
 252     SELECT b.* FROM t1 a, t1 b
 253      WHERE a.w=1 AND (a.y=b.z OR b.z=10)
 254      ORDER BY +b.w
 255   }
 256 } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
 257 do_test where2-6.6 {
 258   queryplan {
 259     SELECT b.* FROM t1 a, t1 b
 260      WHERE a.w=1 AND (b.z=10 OR a.y=b.z OR b.z=10)
 261      ORDER BY +b.w
 262   }
 263 } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
 264
 265 # Ticket #2249.  Make sure the OR optimization is not attempted if
 266 # comparisons between columns of different affinities are needed.
 267 #
 268 do_test where2-6.7 {
 269   execsql {
 270     CREATE TABLE t2249a(a TEXT UNIQUE);
 271     CREATE TABLE t2249b(b INTEGER);
 272     INSERT INTO t2249a VALUES('0123');
 273     INSERT INTO t2249b VALUES(123);
 274   }
 275   queryplan {
 276     -- Because a is type TEXT and b is type INTEGER, both a and b
 277     -- will attempt to convert to NUMERIC before the comparison.
 278     -- They will thus compare equal.
 279     --
 280     SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b;
 281   }
 282 } {123 0123 nosort t2249b {} t2249a {}}
 283 do_test where2-6.9 {
 284   queryplan {
 285     -- The + operator removes affinity from the rhs.  No conversions
 286     -- occur and the comparison is false.  The result is an empty set.
 287     --
 288     SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b;
 289   }
 290 } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
 291 do_test where2-6.9.2 {
 292   # The same thing but with the expression flipped around.
 293   queryplan {
 294     SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a
 295   }
 296 } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
 297 do_test where2-6.10 {
 298   queryplan {
 299     -- Use + on both sides of the comparison to disable indices
 300     -- completely.  Make sure we get the same result.
 301     --
 302     SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
 303   }
 304 } {nosort t2249b {} t2249a {}}
 305 do_test where2-6.11 {
 306   # This will not attempt the OR optimization because of the a=b
 307   # comparison.
 308   queryplan {
 309     SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
 310   }
 311 } {123 0123 nosort t2249b {} t2249a {}}
 312 do_test where2-6.11.2 {
 313   # Permutations of the expression terms.
 314   queryplan {
 315     SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
 316   }
 317 } {123 0123 nosort t2249b {} t2249a {}}
 318 do_test where2-6.11.3 {
 319   # Permutations of the expression terms.
 320   queryplan {
 321     SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
 322   }
 323 } {123 0123 nosort t2249b {} t2249a {}}
 324 do_test where2-6.11.4 {
 325   # Permutations of the expression terms.
 326   queryplan {
 327     SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
 328   }
 329 } {123 0123 nosort t2249b {} t2249a {}}
 330 ifcapable explain&&subquery {
 331   # These tests are not run if subquery support is not included in the
 332   # build. This is because these tests test the "a = 1 OR a = 2" to
 333   # "a IN (1, 2)" optimisation transformation, which is not enabled if
 334   # subqueries and the IN operator is not available.
 335   #
 336   do_test where2-6.12 {
 337     # In this case, the +b disables the affinity conflict and allows
 338     # the OR optimization to be used again.  The result is now an empty
 339     # set, the same as in where2-6.9.
 340     queryplan {
 341       SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
 342     }
 343   } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
 344   do_test where2-6.12.2 {
 345     # In this case, the +b disables the affinity conflict and allows
 346     # the OR optimization to be used again.  The result is now an empty
 347     # set, the same as in where2-6.9.
 348     queryplan {
 349       SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
 350     }
 351   } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
 352   do_test where2-6.12.3 {
 353     # In this case, the +b disables the affinity conflict and allows
 354     # the OR optimization to be used again.  The result is now an empty
 355     # set, the same as in where2-6.9.
 356     queryplan {
 357       SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
 358     }
 359   } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
 360   do_test where2-6.13 {
 361     # The addition of +a on the second term disabled the OR optimization.
 362     # But we should still get the same empty-set result as in where2-6.9.
 363     queryplan {
 364       SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
 365     }
 366   } {nosort t2249b {} t2249a {}}
 367 }
 368
 369 # Variations on the order of terms in a WHERE clause in order
 370 # to make sure the OR optimizer can recognize them all.
 371 do_test where2-6.20 {
 372   queryplan {
 373     SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
 374   }
 375 } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
 376 ifcapable explain&&subquery {
 377   # These tests are not run if subquery support is not included in the
 378   # build. This is because these tests test the "a = 1 OR a = 2" to
 379   # "a IN (1, 2)" optimisation transformation, which is not enabled if
 380   # subqueries and the IN operator is not available.
 381   #
 382   do_test where2-6.21 {
 383     queryplan {
 384       SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello'
 385     }
 386   } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
 387   do_test where2-6.22 {
 388     queryplan {
 389       SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello'
 390     }
 391   } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
 392   do_test where2-6.23 {
 393     queryplan {
 394       SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a
 395     }
 396   } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
 397 }
 398
 399 # Unique queries (queries that are guaranteed to return only a single
 400 # row of result) do not call the sorter.  But all tables must give
 401 # a unique result.  If any one table in the join does not give a unique
 402 # result then sorting is necessary.
 403 #
 404 do_test where2-7.1 {
 405   cksort {
 406     create table t8(a unique, b, c);
 407     insert into t8 values(1,2,3);
 408     insert into t8 values(2,3,4);
 409     create table t9(x,y);
 410     insert into t9 values(2,4);
 411     insert into t9 values(2,3);
 412     select y from t8, t9 where a=1 order by a, y;
 413   }
 414 } {3 4 sort}
 415 do_test where2-7.2 {
 416   cksort {
 417     select * from t8 where a=1 order by b, c
 418   }
 419 } {1 2 3 nosort}
 420 do_test where2-7.3 {
 421   cksort {
 422     select * from t8, t9 where a=1 and y=3 order by b, x
 423   }
 424 } {1 2 3 2 3 sort}
 425 do_test where2-7.4 {
 426   cksort {
 427     create unique index i9y on t9(y);
 428     select * from t8, t9 where a=1 and y=3 order by b, x
 429   }
 430 } {1 2 3 2 3 nosort}
 431
 432 # Ticket #1807.  Using IN constrains on multiple columns of
 433 # a multi-column index.
 434 #
 435 ifcapable subquery {
 436   do_test where2-8.1 {
 437     execsql {
 438       SELECT * FROM t1 WHERE x IN (20,21) AND y IN (1,2)
 439     }
 440   } {}
 441   do_test where2-8.2 {
 442     execsql {
 443       SELECT * FROM t1 WHERE x IN (1,2) AND y IN (-5,-6)
 444     }
 445   } {}
 446   execsql {CREATE TABLE tx AS SELECT * FROM t1}
 447   do_test where2-8.3 {
 448     execsql {
 449       SELECT w FROM t1
 450        WHERE x IN (SELECT x FROM tx WHERE rowid<0)
 451          AND +y IN (SELECT y FROM tx WHERE rowid=1)
 452     }
 453   } {}
 454   do_test where2-8.4 {
 455     execsql {
 456       SELECT w FROM t1
 457        WHERE x IN (SELECT x FROM tx WHERE rowid=1)
 458          AND y IN (SELECT y FROM tx WHERE rowid<0)
 459     }
 460   } {}
 461   #set sqlite_where_trace 1
 462   do_test where2-8.5 {
 463     execsql {
 464       CREATE INDEX tx_xyz ON tx(x, y, z, w);
 465       SELECT w FROM tx
 466        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 467          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 468          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 12 AND 14)
 469     }
 470   } {12 13 14}
 471   do_test where2-8.6 {
 472     execsql {
 473       SELECT w FROM tx
 474        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 475          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 12 AND 14)
 476          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 477     }
 478   } {12 13 14}
 479   do_test where2-8.7 {
 480     execsql {
 481       SELECT w FROM tx
 482        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 12 AND 14)
 483          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 484          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 485     }
 486   } {10 11 12 13 14 15}
 487   do_test where2-8.8 {
 488     execsql {
 489       SELECT w FROM tx
 490        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 491          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 492          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 493     }
 494   } {10 11 12 13 14 15 16 17 18 19 20}
 495   do_test where2-8.9 {
 496     execsql {
 497       SELECT w FROM tx
 498        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 499          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 500          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 2 AND 4)
 501     }
 502   } {}
 503   do_test where2-8.10 {
 504     execsql {
 505       SELECT w FROM tx
 506        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 507          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 2 AND 4)
 508          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 509     }
 510   } {}
 511   do_test where2-8.11 {
 512     execsql {
 513       SELECT w FROM tx
 514        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 2 AND 4)
 515          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 516          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 517     }
 518   } {}
 519   do_test where2-8.12 {
 520     execsql {
 521       SELECT w FROM tx
 522        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 523          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 524          AND z IN (SELECT z FROM t1 WHERE w BETWEEN -4 AND -2)
 525     }
 526   } {}
 527   do_test where2-8.13 {
 528     execsql {
 529       SELECT w FROM tx
 530        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 531          AND y IN (SELECT y FROM t1 WHERE w BETWEEN -4 AND -2)
 532          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 533     }
 534   } {}
 535   do_test where2-8.14 {
 536     execsql {
 537       SELECT w FROM tx
 538        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN -4 AND -2)
 539          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 540          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 541     }
 542   } {}
 543   do_test where2-8.15 {
 544     execsql {
 545       SELECT w FROM tx
 546        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 547          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 548          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 200 AND 300)
 549     }
 550   } {}
 551   do_test where2-8.16 {
 552     execsql {
 553       SELECT w FROM tx
 554        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
 555          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 200 AND 300)
 556          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 557     }
 558   } {}
 559   do_test where2-8.17 {
 560     execsql {
 561       SELECT w FROM tx
 562        WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 200 AND 300)
 563          AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
 564          AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
 565     }
 566   } {}
 567   do_test where2-8.18 {
 568     execsql {
 569       SELECT w FROM tx
 570        WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
 571          AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
 572          AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 200 AND 300)
 573     }
 574   } {}
 575   do_test where2-8.19 {
 576     execsql {
 577       SELECT w FROM tx
 578        WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
 579          AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 200 AND 300)
 580          AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
 581     }
 582   } {}
 583   do_test where2-8.20 {
 584     execsql {
 585       SELECT w FROM tx
 586        WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300)
 587          AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
 588          AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
 589     }
 590   } {}
 591 }
 592
 593 # Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized
 594 # when we have an index on A and B.
 595 #
 596 ifcapable or_opt&&tclvar {
 597   do_test where2-9.1 {
 598     execsql {
 599       BEGIN;
 600       CREATE TABLE t10(a,b,c);
 601       INSERT INTO t10 VALUES(1,1,1);
 602       INSERT INTO t10 VALUES(1,2,2);
 603       INSERT INTO t10 VALUES(1,3,3);
 604     }
 605     for {set i 4} {$i<=1000} {incr i} {
 606       execsql {INSERT INTO t10 VALUES(1,$i,$i)}
 607     }
 608     execsql {
 609       CREATE INDEX i10 ON t10(a,b);
 610       COMMIT;
 611       SELECT count(*) FROM t10;
 612     }
 613   } 1000
 614   ifcapable subquery {
 615     do_test where2-9.2 {
 616       count {
 617         SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
 618       }
 619     } {1 2 2 1 3 3 7}
 620   }
 621 }
 622
 623 # Indices with redundant columns
 624 #
 625 do_test where2-11.1 {
 626   execsql {
 627     CREATE TABLE t11(a,b,c,d);
 628     CREATE INDEX i11aba ON t11(a,b,a,c); -- column A occurs twice.
 629     INSERT INTO t11 VALUES(1,2,3,4);
 630     INSERT INTO t11 VALUES(5,6,7,8);
 631     INSERT INTO t11 VALUES(1,2,9,10);
 632     INSERT INTO t11 VALUES(5,11,12,13);
 633     SELECT c FROM t11 WHERE a=1 AND b=2 ORDER BY c;
 634   }
 635 } {3 9}
 636 do_test where2-11.2 {
 637   execsql {
 638     CREATE INDEX i11cccccccc ON t11(c,c,c,c,c,c,c,c); -- repeated column
 639     SELECT d FROM t11 WHERE c=9;
 640   }
 641 } {10}
 642 do_test where2-11.3 {
 643   execsql {
 644     SELECT d FROM t11 WHERE c IN (1,2,3,4,5);
 645   }
 646 } {4}
 647 do_test where2-11.4 {
 648   execsql {
 649     SELECT d FROM t11 WHERE c=7 OR (a=1 AND b=2) ORDER BY d;
 650   }
 651 } {4 8 10}
 652
 653
 654 finish_test