summaryrefslogtreecommitdiff
path: root/test/where9.test
diff options
context:
space:
mode:
authorHans-Christoph Steiner <hans@eds.org>2014-10-16 22:51:35 -0400
committerHans-Christoph Steiner <hans@eds.org>2014-10-16 22:51:35 -0400
commit569c6676a6ddb0ff73821d7693b5e18ddef809b9 (patch)
tree833538da7bba39105daff45e265aef386a200acd /test/where9.test
parent08119c361d1181b3e8f1abb429236e488a664753 (diff)
Imported Upstream version 3.2.0upstream
Diffstat (limited to 'test/where9.test')
-rw-r--r--test/where9.test105
1 files changed, 88 insertions, 17 deletions
diff --git a/test/where9.test b/test/where9.test
index 1e94fdf..d073074 100644
--- a/test/where9.test
+++ b/test/where9.test
@@ -362,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
@@ -372,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=?)}
}
}
@@ -420,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
@@ -436,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
@@ -444,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
@@ -453,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=?) (~3 rows)}
- 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~3 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.
@@ -462,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
@@ -471,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>?)}
}
}
@@ -768,20 +768,62 @@ 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}}
+set solution_possible 0
+ifcapable stat4||stat3 {
+ if {[permutation] != "no_optimization"} { set solution_possible 1 }
+}
+if $solution_possible {
+ # 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
@@ -814,6 +856,11 @@ do_test where9-7.0 {
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 {
@@ -914,4 +961,28 @@ do_test where9-9.1 {
}
} {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