summaryrefslogtreecommitdiff
path: root/test/with2.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/with2.test')
-rw-r--r--test/with2.test418
1 files changed, 418 insertions, 0 deletions
diff --git a/test/with2.test b/test/with2.test
new file mode 100644
index 0000000..eb06147
--- /dev/null
+++ b/test/with2.test
@@ -0,0 +1,418 @@
+# 2014 January 11
+#
+# 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 with2
+
+ifcapable {!cte} {
+ finish_test
+ return
+}
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a);
+ INSERT INTO t1 VALUES(1);
+ INSERT INTO t1 VALUES(2);
+}
+
+do_execsql_test 1.1 {
+ WITH x1 AS (SELECT * FROM t1)
+ SELECT sum(a) FROM x1;
+} {3}
+
+do_execsql_test 1.2 {
+ WITH x1 AS (SELECT * FROM t1)
+ SELECT (SELECT sum(a) FROM x1);
+} {3}
+
+do_execsql_test 1.3 {
+ WITH x1 AS (SELECT * FROM t1)
+ SELECT (SELECT sum(a) FROM x1);
+} {3}
+
+do_execsql_test 1.4 {
+ CREATE TABLE t2(i);
+ INSERT INTO t2 VALUES(2);
+ INSERT INTO t2 VALUES(3);
+ INSERT INTO t2 VALUES(5);
+
+ WITH x1 AS (SELECT i FROM t2),
+ i(a) AS (
+ SELECT min(i)-1 FROM x1 UNION SELECT a+1 FROM i WHERE a<10
+ )
+ SELECT a FROM i WHERE a NOT IN x1
+} {1 4 6 7 8 9 10}
+
+do_execsql_test 1.5 {
+ WITH x1 AS (SELECT a FROM t1),
+ x2 AS (SELECT i FROM t2),
+ x3 AS (SELECT * FROM x1, x2 WHERE x1.a IN x2 AND x2.i IN x1)
+ SELECT * FROM x3
+} {2 2}
+
+do_execsql_test 1.6 {
+ CREATE TABLE t3 AS SELECT 3 AS x;
+ CREATE TABLE t4 AS SELECT 4 AS x;
+
+ WITH x1 AS (SELECT * FROM t3),
+ x2 AS (
+ WITH t3 AS (SELECT * FROM t4)
+ SELECT * FROM x1
+ )
+ SELECT * FROM x2;
+} {3}
+
+do_execsql_test 1.7 {
+ WITH x2 AS (
+ WITH t3 AS (SELECT * FROM t4)
+ SELECT * FROM t3
+ )
+ SELECT * FROM x2;
+} {4}
+
+do_execsql_test 1.8 {
+ WITH x2 AS (
+ WITH t3 AS (SELECT * FROM t4)
+ SELECT * FROM main.t3
+ )
+ SELECT * FROM x2;
+} {3}
+
+do_execsql_test 1.9 {
+ WITH x1 AS (SELECT * FROM t1)
+ SELECT (SELECT sum(a) FROM x1), (SELECT max(a) FROM x1);
+} {3 2}
+
+do_execsql_test 1.10 {
+ WITH x1 AS (SELECT * FROM t1)
+ SELECT (SELECT sum(a) FROM x1), (SELECT max(a) FROM x1), a FROM x1;
+} {3 2 1 3 2 2}
+
+do_execsql_test 1.11 {
+ WITH
+ i(x) AS (
+ WITH
+ j(x) AS ( SELECT * FROM i ),
+ i(x) AS ( SELECT * FROM t1 )
+ SELECT * FROM j
+ )
+ SELECT * FROM i;
+} {1 2}
+
+do_execsql_test 1.12 {
+ WITH r(i) AS (
+ VALUES('.')
+ UNION ALL
+ SELECT i || '.' FROM r, (
+ SELECT x FROM x INTERSECT SELECT y FROM y
+ ) WHERE length(i) < 10
+ ),
+ x(x) AS ( VALUES(1) UNION ALL VALUES(2) UNION ALL VALUES(3) ),
+ y(y) AS ( VALUES(2) UNION ALL VALUES(4) UNION ALL VALUES(6) )
+
+ SELECT * FROM r;
+} {. .. ... .... ..... ...... ....... ........ ......... ..........}
+
+do_execsql_test 1.13 {
+ WITH r(i) AS (
+ VALUES('.')
+ UNION ALL
+ SELECT i || '.' FROM r, ( SELECT x FROM x WHERE x=2 ) WHERE length(i) < 10
+ ),
+ x(x) AS ( VALUES(1) UNION ALL VALUES(2) UNION ALL VALUES(3) )
+
+ SELECT * FROM r ORDER BY length(i) DESC;
+} {.......... ......... ........ ....... ...... ..... .... ... .. .}
+
+do_execsql_test 1.14 {
+ WITH
+ t4(x) AS (
+ VALUES(4)
+ UNION ALL
+ SELECT x+1 FROM t4 WHERE x<10
+ )
+ SELECT * FROM t4;
+} {4 5 6 7 8 9 10}
+
+do_execsql_test 1.15 {
+ WITH
+ t4(x) AS (
+ VALUES(4)
+ UNION ALL
+ SELECT x+1 FROM main.t4 WHERE x<10
+ )
+ SELECT * FROM t4;
+} {4 5}
+
+do_catchsql_test 1.16 {
+ WITH
+ t4(x) AS (
+ VALUES(4)
+ UNION ALL
+ SELECT x+1 FROM t4, main.t4, t4 WHERE x<10
+ )
+ SELECT * FROM t4;
+} {1 {multiple references to recursive table: t4}}
+
+
+#---------------------------------------------------------------------------
+# Check that variables can be used in CTEs.
+#
+set ::min [expr 3]
+set ::max [expr 9]
+do_execsql_test 2.1 {
+ WITH i(x) AS (
+ VALUES($min) UNION ALL SELECT x+1 FROM i WHERE x < $max
+ )
+ SELECT * FROM i;
+} {3 4 5 6 7 8 9}
+
+do_execsql_test 2.2 {
+ WITH i(x) AS (
+ VALUES($min) UNION ALL SELECT x+1 FROM i WHERE x < $max
+ )
+ SELECT x FROM i JOIN i AS j USING (x);
+} {3 4 5 6 7 8 9}
+
+#---------------------------------------------------------------------------
+# Check that circular references are rejected.
+#
+do_catchsql_test 3.1 {
+ WITH i(x, y) AS ( VALUES(1, (SELECT x FROM i)) )
+ SELECT * FROM i;
+} {1 {circular reference: i}}
+
+do_catchsql_test 3.2 {
+ WITH
+ i(x) AS ( SELECT * FROM j ),
+ j(x) AS ( SELECT * FROM k ),
+ k(x) AS ( SELECT * FROM i )
+ SELECT * FROM i;
+} {1 {circular reference: i}}
+
+do_catchsql_test 3.3 {
+ WITH
+ i(x) AS ( SELECT * FROM (SELECT * FROM j) ),
+ j(x) AS ( SELECT * FROM (SELECT * FROM i) )
+ SELECT * FROM i;
+} {1 {circular reference: i}}
+
+do_catchsql_test 3.4 {
+ WITH
+ i(x) AS ( SELECT * FROM (SELECT * FROM j) ),
+ j(x) AS ( SELECT * FROM (SELECT * FROM i) )
+ SELECT * FROM j;
+} {1 {circular reference: j}}
+
+do_catchsql_test 3.5 {
+ WITH
+ i(x) AS (
+ WITH j(x) AS ( SELECT * FROM i )
+ SELECT * FROM j
+ )
+ SELECT * FROM i;
+} {1 {circular reference: i}}
+
+#---------------------------------------------------------------------------
+# Try empty and very long column lists.
+#
+do_catchsql_test 4.1 {
+ WITH x() AS ( SELECT 1,2,3 )
+ SELECT * FROM x;
+} {1 {near ")": syntax error}}
+
+proc genstmt {n} {
+ for {set i 1} {$i<=$n} {incr i} {
+ lappend cols "c$i"
+ lappend vals $i
+ }
+ return "
+ WITH x([join $cols ,]) AS (SELECT [join $vals ,])
+ SELECT (c$n == $n) FROM x
+ "
+}
+
+do_execsql_test 4.2 [genstmt 10] 1
+do_execsql_test 4.3 [genstmt 100] 1
+do_execsql_test 4.4 [genstmt 255] 1
+set nLimit [sqlite3_limit db SQLITE_LIMIT_COLUMN -1]
+do_execsql_test 4.5 [genstmt [expr $nLimit-1]] 1
+do_execsql_test 4.6 [genstmt $nLimit] 1
+do_catchsql_test 4.7 [genstmt [expr $nLimit+1]] {1 {too many columns in index}}
+
+#---------------------------------------------------------------------------
+# Check that adding a WITH clause to an INSERT disables the xfer
+# optimization.
+#
+proc do_xfer_test {tn bXfer sql {res {}}} {
+ set ::sqlite3_xferopt_count 0
+ uplevel [list do_test $tn [subst -nocommands {
+ set dres [db eval {$sql}]
+ list [set ::sqlite3_xferopt_count] [set dres]
+ }] [list $bXfer $res]]
+}
+
+do_execsql_test 5.1 {
+ DROP TABLE IF EXISTS t1;
+ DROP TABLE IF EXISTS t2;
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(a, b);
+}
+
+do_xfer_test 5.2 1 { INSERT INTO t1 SELECT * FROM t2 }
+do_xfer_test 5.3 0 { INSERT INTO t1 SELECT a, b FROM t2 }
+do_xfer_test 5.4 0 { INSERT INTO t1 SELECT b, a FROM t2 }
+do_xfer_test 5.5 0 {
+ WITH x AS (SELECT a, b FROM t2) INSERT INTO t1 SELECT * FROM x
+}
+do_xfer_test 5.6 0 {
+ WITH x AS (SELECT a, b FROM t2) INSERT INTO t1 SELECT * FROM t2
+}
+do_xfer_test 5.7 0 {
+ INSERT INTO t1 WITH x AS ( SELECT * FROM t2 ) SELECT * FROM x
+}
+do_xfer_test 5.8 0 {
+ INSERT INTO t1 WITH x(a,b) AS ( SELECT * FROM t2 ) SELECT * FROM x
+}
+
+#---------------------------------------------------------------------------
+# Check that syntax (and other) errors in statements with WITH clauses
+# attached to them do not cause problems (e.g. memory leaks).
+#
+do_execsql_test 6.1 {
+ DROP TABLE IF EXISTS t1;
+ DROP TABLE IF EXISTS t2;
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(a, b);
+}
+
+do_catchsql_test 6.2 {
+ WITH x AS (SELECT * FROM t1)
+ INSERT INTO t2 VALUES(1, 2,);
+} {1 {near ")": syntax error}}
+
+do_catchsql_test 6.3 {
+ WITH x AS (SELECT * FROM t1)
+ INSERT INTO t2 SELECT a, b, FROM t1;
+} {1 {near "FROM": syntax error}}
+
+do_catchsql_test 6.3 {
+ WITH x AS (SELECT * FROM t1)
+ INSERT INTO t2 SELECT a, b FROM abc;
+} {1 {no such table: abc}}
+
+do_catchsql_test 6.4 {
+ WITH x AS (SELECT * FROM t1)
+ INSERT INTO t2 SELECT a, b, FROM t1 a a a;
+} {1 {near "FROM": syntax error}}
+
+do_catchsql_test 6.5 {
+ WITH x AS (SELECT * FROM t1)
+ DELETE FROM t2 WHERE;
+} {1 {near ";": syntax error}}
+
+do_catchsql_test 6.6 {
+ WITH x AS (SELECT * FROM t1) DELETE FROM t2 WHERE
+} {/1 {near .* syntax error}/}
+
+do_catchsql_test 6.7 {
+ WITH x AS (SELECT * FROM t1) DELETE FROM t2 WHRE 1;
+} {/1 {near .* syntax error}/}
+
+do_catchsql_test 6.8 {
+ WITH x AS (SELECT * FROM t1) UPDATE t2 SET a = 10, b = ;
+} {/1 {near .* syntax error}/}
+
+do_catchsql_test 6.9 {
+ WITH x AS (SELECT * FROM t1) UPDATE t2 SET a = 10, b = 1 WHERE a===b;
+} {/1 {near .* syntax error}/}
+
+do_catchsql_test 6.10 {
+ WITH x(a,b) AS (
+ SELECT 1, 1
+ UNION ALL
+ SELECT a*b,a+b FROM x WHERE c=2
+ )
+ SELECT * FROM x
+} {1 {no such column: c}}
+
+#-------------------------------------------------------------------------
+# Recursive queries in IN(...) expressions.
+#
+do_execsql_test 7.1 {
+ CREATE TABLE t5(x INTEGER);
+ CREATE TABLE t6(y INTEGER);
+
+ WITH s(x) AS ( VALUES(7) UNION ALL SELECT x+7 FROM s WHERE x<49 )
+ INSERT INTO t5
+ SELECT * FROM s;
+
+ INSERT INTO t6
+ WITH s(x) AS ( VALUES(2) UNION ALL SELECT x+2 FROM s WHERE x<49 )
+ SELECT * FROM s;
+}
+
+do_execsql_test 7.2 {
+ SELECT * FROM t6 WHERE y IN (SELECT x FROM t5)
+} {14 28 42}
+
+do_execsql_test 7.3 {
+ WITH ss AS (SELECT x FROM t5)
+ SELECT * FROM t6 WHERE y IN (SELECT x FROM ss)
+} {14 28 42}
+
+do_execsql_test 7.4 {
+ WITH ss(x) AS ( VALUES(7) UNION ALL SELECT x+7 FROM ss WHERE x<49 )
+ SELECT * FROM t6 WHERE y IN (SELECT x FROM ss)
+} {14 28 42}
+
+do_execsql_test 7.5 {
+ SELECT * FROM t6 WHERE y IN (
+ WITH ss(x) AS ( VALUES(7) UNION ALL SELECT x+7 FROM ss WHERE x<49 )
+ SELECT x FROM ss
+ )
+} {14 28 42}
+
+#-------------------------------------------------------------------------
+# At one point the following was causing an assertion failure and a
+# memory leak.
+#
+do_execsql_test 8.1 {
+ CREATE TABLE t7(y);
+ INSERT INTO t7 VALUES(NULL);
+ CREATE VIEW v AS SELECT * FROM t7 ORDER BY y;
+}
+
+do_execsql_test 8.2 {
+ WITH q(a) AS (
+ SELECT 1
+ UNION
+ SELECT a+1 FROM q, v WHERE a<5
+ )
+ SELECT * FROM q;
+} {1 2 3 4 5}
+
+do_execsql_test 8.3 {
+ WITH q(a) AS (
+ SELECT 1
+ UNION ALL
+ SELECT a+1 FROM q, v WHERE a<5
+ )
+ SELECT * FROM q;
+} {1 2 3 4 5}
+
+
+finish_test
+
generated by cgit v1.2.3 (git 2.25.1) at 2024-05-09 15:02:59 +0000