From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Thu, 16 Oct 2014 22:51:35 -0400 Subject: Imported Upstream version 3.2.0 --- test/where2.test | 150 +++++++++++++++++++++++++++++++++++++++++-------------- 1 file changed, 112 insertions(+), 38 deletions(-) (limited to 'test/where2.test') diff --git a/test/where2.test b/test/where2.test index e8c2f36..367eb0d 100644 --- a/test/where2.test +++ b/test/where2.test @@ -66,14 +66,24 @@ proc cksort {sql} { # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword (as in the cksort procedure above) then -# it appends the ::sqlite_query_plan variable. +# it appends the name of the table and index used. # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x - return [concat $data $::sqlite_query_plan] + set eqp [execsql "EXPLAIN QUERY PLAN $sql"] + # puts eqp=$eqp + foreach {a b c x} $eqp { + if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ + $x all as tab idx]} { + lappend data $tab $idx + } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { + lappend data $tab * + } + } + return $data } @@ -111,6 +121,42 @@ do_test where2-2.3 { } } {85 6 7396 7402 nosort t1 *} +# Ticket [65bdeb9739605cc22966f49208452996ff29a640] 2014-02-26 +# Make sure "ORDER BY random" does not gets optimized out. +# +do_test where2-2.4 { + db eval { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b DEFAULT 1); + WITH RECURSIVE + cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<50) + INSERT INTO x1 SELECT x, 1 FROM cnt; + CREATE TABLE x2(x INTEGER PRIMARY KEY); + INSERT INTO x2 VALUES(1); + } + set sql {SELECT * FROM x1, x2 WHERE x=1 ORDER BY random()} + set out1 [db eval $sql] + set out2 [db eval $sql] + set out3 [db eval $sql] + expr {$out1!=$out2 && $out2!=$out3} +} {1} +do_execsql_test where2-2.5 { + -- random() is not optimized out + EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY random(); +} {/ random/} +do_execsql_test where2-2.5b { + -- random() is not optimized out + EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY random(); +} {/ SorterOpen /} +do_execsql_test where2-2.6 { + -- other constant functions are optimized out + EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY abs(5); +} {~/ abs/} +do_execsql_test where2-2.6b { + -- other constant functions are optimized out + EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY abs(5); +} {~/ SorterOpen /} + + # Efficient handling of forward and reverse table scans. # @@ -273,12 +319,12 @@ do_test where2-6.3 { queryplan { SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w } -} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}} +} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 *} do_test where2-6.4 { queryplan { SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w } -} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}} +} {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 *} set ::idx {} ifcapable subquery {set ::idx i1zyx} @@ -297,14 +343,16 @@ do_test where2-6.6 { } } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx] +if {[permutation] != "no_optimization"} { + # Ticket #2249. Make sure the OR optimization is not attempted if # comparisons between columns of different affinities are needed. # do_test where2-6.7 { execsql { - CREATE TABLE t2249a(a TEXT UNIQUE); + CREATE TABLE t2249a(a TEXT UNIQUE, x CHAR(100)); CREATE TABLE t2249b(b INTEGER); - INSERT INTO t2249a VALUES('0123'); + INSERT INTO t2249a(a) VALUES('0123'); INSERT INTO t2249b VALUES(123); } queryplan { @@ -312,56 +360,56 @@ do_test where2-6.7 { -- will attempt to convert to NUMERIC before the comparison. -- They will thus compare equal. -- - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.9 { queryplan { -- The + operator removes affinity from the rhs. No conversions -- occur and the comparison is false. The result is an empty set. -- - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b; } -} {nosort t2249b {} {} sqlite_autoindex_t2249a_1} +} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.9.2 { # The same thing but with the expression flipped around. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a } -} {nosort t2249b {} {} sqlite_autoindex_t2249a_1} +} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.10 { queryplan { -- Use + on both sides of the comparison to disable indices -- completely. Make sure we get the same result. -- - SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b; } -} {nosort t2249b {} t2249a {}} +} {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11 { # This will not attempt the OR optimization because of the a=b # comparison. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello'; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.2 { # Permutations of the expression terms. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.3 { # Permutations of the expression terms. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.4 { # Permutations of the expression terms. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; } -} {123 0123 nosort t2249b {} t2249a {}} +} {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} ifcapable explain&&subquery { # These tests are not run if subquery support is not included in the # build. This is because these tests test the "a = 1 OR a = 2" to @@ -373,41 +421,41 @@ ifcapable explain&&subquery { # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; } - } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} + } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.12.2 { # In this case, the +b disables the affinity conflict and allows # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; } - } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} + } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.12.3 { # In this case, the +b disables the affinity conflict and allows # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; } - } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} + } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.13 { # The addition of +a on the second term disabled the OR optimization. # But we should still get the same empty-set result as in where2-6.9. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello'; } - } {nosort t2249b {} t2249a {}} + } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} } # Variations on the order of terms in a WHERE clause in order # to make sure the OR optimizer can recognize them all. do_test where2-6.20 { queryplan { - SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a + SELECT x.a, y.a FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a } -} {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} +} {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} ifcapable explain&&subquery { # These tests are not run if subquery support is not included in the # build. This is because these tests test the "a = 1 OR a = 2" to @@ -416,19 +464,22 @@ ifcapable explain&&subquery { # do_test where2-6.21 { queryplan { - SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello' + SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y + WHERE x.a=y.a OR y.a='hello' } - } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} + } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} do_test where2-6.22 { queryplan { - SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello' + SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y + WHERE y.a=x.a OR y.a='hello' } - } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} + } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} do_test where2-6.23 { queryplan { - SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a + SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y + WHERE y.a='hello' OR x.a=y.a } - } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} + } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} } # Unique queries (queries that are guaranteed to return only a single @@ -464,6 +515,8 @@ do_test where2-7.4 { } } {1 2 3 2 3 nosort} +} ;# if {[permutation] != "no_optimization"} + # Ticket #1807. Using IN constrains on multiple columns of # a multi-column index. # @@ -685,5 +738,26 @@ do_test where2-11.4 { } } {4 8 10} +# Verify that the OR clause is used in an outer loop even when +# the OR clause scores slightly better on an inner loop. +if {[permutation] != "no_optimization"} { +do_execsql_test where2-12.1 { + CREATE TABLE t12(x INTEGER PRIMARY KEY, y INT, z CHAR(100)); + CREATE INDEX t12y ON t12(y); + EXPLAIN QUERY PLAN + SELECT a.x, b.x + FROM t12 AS a JOIN t12 AS b ON a.y=b.x + WHERE (b.x=$abc OR b.y=$abc); +} {/.*SEARCH TABLE t12 AS b .*SEARCH TABLE t12 AS b .*/} +} + +# Verify that all necessary OP_OpenRead opcodes occur in the OR optimization. +# +do_execsql_test where2-13.1 { + CREATE TABLE t13(a,b); + CREATE INDEX t13a ON t13(a); + INSERT INTO t13 VALUES(4,5); + SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4; +} {4 5} finish_test -- cgit v1.2.3