Index: third_party/sqlite/sqlite-src-3170000/test/with3.test |
diff --git a/third_party/sqlite/sqlite-src-3170000/test/with3.test b/third_party/sqlite/sqlite-src-3170000/test/with3.test |
new file mode 100644 |
index 0000000000000000000000000000000000000000..5540a7fca6de6a561d94e9c36ba44f2cc7ab6e3e |
--- /dev/null |
+++ b/third_party/sqlite/sqlite-src-3170000/test/with3.test |
@@ -0,0 +1,120 @@ |
+# 2015-11-07 |
+# |
+# The author disclaims copyright to this source code. In place of |
+# a legal notice, here is a blessing: |
+# |
+# May you do good and not evil. |
+# May you find forgiveness for yourself and forgive others. |
+# 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 WITH clause. |
+# |
+ |
+set testdir [file dirname $argv0] |
+source $testdir/tester.tcl |
+set ::testprefix with3 |
+ |
+ifcapable {!cte} { |
+ finish_test |
+ return |
+} |
+ |
+# Test problems found by Kostya Serebryany using |
+# LibFuzzer. (http://llvm.org/docs/LibFuzzer.html) |
+# |
+do_catchsql_test 1.0 { |
+ WITH i(x) AS ( |
+ WITH j AS (SELECT 10) |
+ SELECT 5 FROM t0 UNION SELECT 8 FROM m |
+ ) |
+ SELECT * FROM i; |
+} {1 {no such table: m}} |
+ |
+# Additional test cases that came out of the work to |
+# fix for Kostya's problem. |
+# |
+do_execsql_test 2.0 { |
+ WITH |
+ x1 AS (SELECT 10), |
+ x2 AS (SELECT 11), |
+ x3 AS ( |
+ SELECT * FROM x1 UNION ALL SELECT * FROM x2 |
+ ), |
+ x4 AS ( |
+ WITH |
+ x1 AS (SELECT 12), |
+ x2 AS (SELECT 13) |
+ SELECT * FROM x3 |
+ ) |
+ SELECT * FROM x4; |
+ |
+} {10 11} |
+ |
+do_execsql_test 2.1 { |
+ CREATE TABLE t1(x); |
+ WITH |
+ x1(a) AS (values(100)) |
+ INSERT INTO t1(x) |
+ SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2); |
+ SELECT * FROM t1; |
+} {200} |
+ |
+#------------------------------------------------------------------------- |
+# Test that the planner notices LIMIT clauses on recursive WITH queries. |
+# |
+ |
+ifcapable analyze { |
+ do_execsql_test 3.1.1 { |
+ CREATE TABLE y1(a, b); |
+ CREATE INDEX y1a ON y1(a); |
+ |
+ WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000) |
+ INSERT INTO y1 SELECT i%10, i FROM cnt; |
+ ANALYZE; |
+ |
+ } |
+ |
+ do_eqp_test 3.1.2 { |
+ WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1) |
+ SELECT * FROM cnt, y1 WHERE i=a |
+ } { |
+ 3 0 0 {SCAN TABLE cnt} |
+ 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} |
+ 0 0 0 {SCAN SUBQUERY 1} |
+ 0 1 1 {SEARCH TABLE y1 USING INDEX y1a (a=?)} |
+ } |
+ |
+ do_eqp_test 3.1.3 { |
+ WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000) |
+ SELECT * FROM cnt, y1 WHERE i=a |
+ } { |
+ 3 0 0 {SCAN TABLE cnt} |
+ 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} |
+ 0 0 1 {SCAN TABLE y1} |
+ 0 1 0 {SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (i=?)} |
+ } |
+} |
+ |
+do_execsql_test 3.2.1 { |
+ CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER); |
+ CREATE TABLE w2(pk INTEGER PRIMARY KEY); |
+} |
+ |
+do_eqp_test 3.2.2 { |
+ WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1) |
+ UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1) |
+ SELECT * FROM c, w2, w1 |
+ WHERE c.id=w2.pk AND c.id=w1.pk; |
+} { |
+ 2 0 0 {EXECUTE SCALAR SUBQUERY 3} |
+ 3 0 0 {SCAN TABLE w2} |
+ 4 0 0 {SCAN TABLE w1} |
+ 4 1 1 {SCAN TABLE c} |
+ 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} 0 0 0 {SCAN SUBQUERY 1} |
+ 0 1 1 {SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)} |
+ 0 2 2 {SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)} |
+} |
+ |
+finish_test |