summaryrefslogtreecommitdiffstats
path: root/test/withM.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/withM.test')
-rw-r--r--test/withM.test74
1 files changed, 74 insertions, 0 deletions
diff --git a/test/withM.test b/test/withM.test
new file mode 100644
index 0000000..78b757f
--- /dev/null
+++ b/test/withM.test
@@ -0,0 +1,74 @@
+# 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
+source $testdir/malloc_common.tcl
+set ::testprefix withM
+
+ifcapable {!cte} {
+ finish_test
+ return
+}
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(x INTEGER, y INTEGER);
+ INSERT INTO t1 VALUES(123, 456);
+}
+
+do_faultsim_test withM-1.1 -prep {
+ sqlite3 db test.db
+} -body {
+ execsql {
+ WITH tmp AS ( SELECT * FROM t1 )
+ SELECT * FROM tmp;
+ }
+} -test {
+ faultsim_test_result {0 {123 456}}
+ db close
+}
+
+do_faultsim_test withM-1.2 -prep {
+ sqlite3 db test.db
+} -body {
+ execsql {
+ WITH w1 AS ( SELECT * FROM t1 ),
+ w2 AS (
+ WITH w3 AS ( SELECT * FROM w1 )
+ SELECT * FROM w3
+ )
+ SELECT * FROM w2;
+ }
+} -test {
+ faultsim_test_result {0 {123 456}}
+ db close
+}
+
+do_faultsim_test withM-1.3 -prep {
+ sqlite3 db test.db
+} -body {
+ execsql {
+ WITH w1(a,b) AS (
+ SELECT 1, 1
+ UNION ALL
+ SELECT a+1, b + 2*a + 1 FROM w1
+ )
+ SELECT * FROM w1 LIMIT 5;
+ }
+} -test {
+ faultsim_test_result {0 {1 1 2 4 3 9 4 16 5 25}}
+ db close
+}
+
+finish_test