Index: test/where7.test ================================================================== --- test/where7.test +++ test/where7.test @@ -8,12 +8,10 @@ # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the multi-index OR clause optimizer. -# -# $Id: where7.test,v 1.9 2009/06/07 23:45:11 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !or_opt { @@ -23339,12 +23337,12 @@ AND t302.c3 > 1287603136 AND (t301.c4 = 1407449685622784 OR t301.c8 = 1407424651264000) ORDER BY t302.c5 LIMIT 200; } { - 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} + 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~5 rows)} 0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } finish_test Index: test/where9.test ================================================================== --- test/where9.test +++ test/where9.test @@ -9,11 +9,10 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the multi-index OR clause optimizer. # -# $Id: where9.test,v 1.9 2009/06/05 17:09:12 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !or_opt { @@ -363,21 +362,21 @@ SELECT t2.a FROM t1, t2 WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) } { 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)} } do_execsql_test where9-3.2 { EXPLAIN QUERY PLAN SELECT coalesce(t2.a,9999) FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f WHERE t1.a=80 } { 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)} } } # Make sure that INDEXED BY and multi-index OR clauses play well with # one another. @@ -452,12 +451,12 @@ # the former is an equality test which is expected to return fewer rows. # do_execsql_test where9-5.1 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} - 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~2 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~2 rows)} } # In contrast, b=1000 is preferred over any OR-clause. # do_execsql_test where9-5.2 { @@ -780,7 +779,82 @@ WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {1 {cannot use index: t1b}} + +############################################################################ +# Test cases where terms inside an OR series are combined with AND terms +# external to the OR clause. In other words, cases where +# +# x AND (y OR z) +# +# is able to use indices on x,y and x,z, or indices y,x and z,x. +# +do_test where9-7.0 { + execsql { + CREATE TABLE t5(a, b, c, d, e, f, g, x, y); + INSERT INTO t5 + SELECT a, b, c, e, d, f, g, + CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END, + CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END + FROM t1; + CREATE INDEX t5xb ON t5(x, b); + CREATE INDEX t5xc ON t5(x, c); + CREATE INDEX t5xd ON t5(x, d); + CREATE INDEX t5xe ON t5(x, e); + CREATE INDEX t5xf ON t5(x, f); + CREATE INDEX t5xg ON t5(x, g); + CREATE INDEX t5yb ON t5(y, b); + CREATE INDEX t5yc ON t5(y, c); + CREATE INDEX t5yd ON t5(y, d); + CREATE INDEX t5ye ON t5(y, e); + CREATE INDEX t5yf ON t5(y, f); + CREATE INDEX t5yg ON t5(y, g); + CREATE TABLE t6(a, b, c, e, d, f, g, x, y); + INSERT INTO t6 SELECT * FROM t5; + ANALYZE t5; + } +} {} +do_test where9-7.1.1 { + count_steps { + SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a; + } +} {79 81 83 scan 0 sort 1} +do_test where9-7.1.2 { + execsql { + SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a; + } +} {79 81 83} +do_test where9-7.1.3 { + count_steps { + SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a; + } +} {80 scan 0 sort 1} +do_test where9-7.1.4 { + execsql { + SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a; + } +} {80} +do_test where9-7.2.1 { + count_steps { + SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a; + } +} {83 scan 0 sort 1} +do_test where9-7.2.2 { + execsql { + SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a; + } +} {83} +do_test where9-7.3.1 { + count_steps { + SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a; + } +} {79 81 scan 0 sort 1} +do_test where9-7.3.2 { + execsql { + SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a; + } +} {79 81} + finish_test