summaryrefslogtreecommitdiff
path: root/test/where2.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/where2.test')
-rw-r--r--test/where2.test150
1 files changed, 112 insertions, 38 deletions
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
generated by cgit v1.2.3 (git 2.25.1) at 2024-05-09 10:17:50 +0000