summaryrefslogtreecommitdiff
path: root/lang/sql/sqlite/test/where9.test
diff options
context:
space:
mode:
Diffstat (limited to 'lang/sql/sqlite/test/where9.test')
-rw-r--r--lang/sql/sqlite/test/where9.test239
1 files changed, 218 insertions, 21 deletions
diff --git a/lang/sql/sqlite/test/where9.test b/lang/sql/sqlite/test/where9.test
index 9a180116..1c32ff82 100644
--- a/lang/sql/sqlite/test/where9.test
+++ b/lang/sql/sqlite/test/where9.test
@@ -11,12 +11,11 @@
# 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 {
+ifcapable !or_opt||!compound {
finish_test
return
}
@@ -233,7 +232,7 @@ do_test where9-1.3.3 {
} {90 91 92 97 scan 98 sort 0}
do_test where9-1.3.4 {
count_steps {
- SELECT a FROM t4
+ SELECT a FROM (t4)
WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
OR (b NOT NULL AND c NOT NULL AND d IS NULL)
OR (b NOT NULL AND c IS NULL AND d NOT NULL)
@@ -363,9 +362,9 @@ ifcapable explain {
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 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)}
+ 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)}
+ 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
}
do_execsql_test where9-3.2 {
EXPLAIN QUERY PLAN
@@ -373,9 +372,9 @@ ifcapable explain {
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 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)}
+ 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)}
+ 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
}
}
@@ -421,7 +420,7 @@ do_test where9-4.5 {
AND (c=31031 OR d IS NULL)
ORDER BY +a
}
-} {1 {cannot use index: t1b}}
+} {1 {no query solution}}
do_test where9-4.6 {
count_steps {
SELECT a FROM t1 NOT INDEXED
@@ -437,7 +436,7 @@ do_test where9-4.7 {
AND (c=31031 OR d IS NULL)
ORDER BY +a
}
-} {1 {cannot use index: t1c}}
+} {1 {no query solution}}
do_test where9-4.8 {
catchsql {
SELECT a FROM t1 INDEXED BY t1d
@@ -445,7 +444,7 @@ do_test where9-4.8 {
AND (c=31031 OR d IS NULL)
ORDER BY +a
}
-} {1 {cannot use index: t1d}}
+} {1 {no query solution}}
ifcapable explain {
# The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
@@ -454,8 +453,8 @@ ifcapable explain {
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=?)}
+ 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)}
}
# In contrast, b=1000 is preferred over any OR-clause.
@@ -463,7 +462,7 @@ ifcapable explain {
do_execsql_test where9-5.2 {
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 t1b (b=?) (~5 rows)}
+ 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}
}
# Likewise, inequalities in an AND are preferred over inequalities in
@@ -472,7 +471,7 @@ ifcapable explain {
do_execsql_test where9-5.3 {
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 t1b (b>?) (~125000 rows)}
+ 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)}
}
}
@@ -693,7 +692,7 @@ do_test where9-6.5.3 {
do_test where9-6.5.4 {
db eval {
SELECT count(*) FROM t1 UNION ALL
- SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87);
+ SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY rowid;
ROLLBACK;
}
} {99 105 131 157 182 183 184 185 186 187}
@@ -769,18 +768,216 @@ do_test where9-6.7.4 {
do_test where9-6.8.1 {
catchsql {
DELETE FROM t1 INDEXED BY t1b
- WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
+ 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}}
+} {1 {no query solution}}
do_test where9-6.8.2 {
catchsql {
UPDATE t1 INDEXED BY t1b SET a=a+100
- WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
+ 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}}
+} {1 {no query solution}}
+ifcapable stat4||stat3 {
+ # When STAT3 is enabled, the "b NOT NULL" terms get translated
+ # into b>NULL, which can be satified by the index t1b. It is a very
+ # expensive way to do the query, but it works, and so a solution is possible.
+ do_test where9-6.8.3-stat4 {
+ catchsql {
+ UPDATE t1 INDEXED BY t1b SET a=a+100
+ 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)
+ }
+ } {0 {}}
+ do_test where9-6.8.4-stat4 {
+ catchsql {
+ DELETE FROM t1 INDEXED BY t1b
+ 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)
+ }
+ } {0 {}}
+} else {
+ do_test where9-6.8.3 {
+ catchsql {
+ UPDATE t1 INDEXED BY t1b SET a=a+100
+ 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 {no query solution}}
+ do_test where9-6.8.4 {
+ catchsql {
+ DELETE FROM t1 INDEXED BY t1b
+ 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 {no query solution}}
+}
+############################################################################
+# 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;
+ }
+ ifcapable stat3 {
+ sqlite3 db2 test.db
+ db2 eval { DROP TABLE IF EXISTS sqlite_stat3 }
+ db2 close
+ }
+} {}
+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}
+
+# Fix for ticket [b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4]
+# "Incorrect result from LEFT JOIN with OR in the WHERE clause"
+#
+do_test where9-8.1 {
+ db eval {
+ CREATE TABLE t81(a INTEGER PRIMARY KEY, b, c, d);
+ CREATE TABLE t82(x INTEGER PRIMARY KEY, y);
+ CREATE TABLE t83(p INTEGER PRIMARY KEY, q);
+
+ INSERT INTO t81 VALUES(2,3,4,5);
+ INSERT INTO t81 VALUES(3,4,5,6);
+ INSERT INTO t82 VALUES(2,4);
+ INSERT INTO t83 VALUES(5,55);
+
+ SELECT *
+ FROM t81 LEFT JOIN t82 ON y=b JOIN t83
+ WHERE c==p OR d==p
+ ORDER BY +a;
+ }
+} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
+do_test where9-8.2 {
+ db eval {
+ SELECT *
+ FROM t81 LEFT JOIN (t82) ON y=b JOIN t83
+ WHERE c==p OR d==p
+ ORDER BY +a;
+ }
+} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
+do_test where9-8.3 {
+ db eval {
+ SELECT *
+ FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83
+ WHERE c==p OR d==p
+ ORDER BY +a;
+ }
+} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
+
+# Fix for ticket [f2369304e47167e3e644e2f1fe9736063391d7b7]
+# Incorrect results when OR is used in the ON clause of a LEFT JOIN
+#
+do_test where9-9.1 {
+ db eval {
+ CREATE TABLE t91(x); INSERT INTO t91 VALUES(1);
+ CREATE TABLE t92(y INTEGER PRIMARY KEY,a,b);
+ INSERT INTO t92 VALUES(1,2,3);
+ SELECT 1 FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
+ SELECT 2 FROM t91 LEFT JOIN t92 ON a=2 AND b=3;
+ SELECT 3 FROM t91 LEFT JOIN t92 ON (a=2 OR b=3) AND y IS NULL;
+ SELECT 4 FROM t91 LEFT JOIN t92 ON (a=2 AND b=3) AND y IS NULL;
+ CREATE TEMP TABLE x9 AS SELECT * FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
+ SELECT 5 FROM x9 WHERE y IS NULL;
+ SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
+ SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
+ SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
+ SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
+ }
+} {1 2 3 4 8 9}
+
+# Fix for ticket [bc878246eafe0f52c519e29049b2fe4a99491b27]
+# Incorrect result when OR is used in a join to the right of a LEFT JOIN
+#
+do_test where9-10.1 {
+ db eval {
+ CREATE TABLE t101 (id INTEGER PRIMARY KEY);
+ INSERT INTO t101 VALUES (1);
+ SELECT * FROM t101 AS t0
+ LEFT JOIN t101 AS t1 ON t1.id BETWEEN 10 AND 20
+ JOIN t101 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
+ }
+} {1 {} 1}
+do_test where9-10.2 {
+ db eval {
+ CREATE TABLE t102 (id TEXT UNIQUE NOT NULL);
+ INSERT INTO t102 VALUES ('1');
+ SELECT * FROM t102 AS t0
+ LEFT JOIN t102 AS t1 ON t1.id GLOB 'abc%'
+ JOIN t102 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
+ }
+} {1 {} 1}
+
+
finish_test
View Lorry Controller Status