1 files changed, 194 insertions, 0 deletions
diff --git a/test/with5.test b/test/with5.test
new file mode 100644
index 0000000..430c5f2
--- /dev/null
+++ b/test/with5.test
@@ -0,0 +1,194 @@
+# 2020-10-19
+#
+# 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 recursive common table expressions with
+# multiple recursive terms in the compound select.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set ::testprefix with5
+
+ifcapable {!cte} {
+  finish_test
+  return
+}
+
+do_execsql_test 100 {
+  CREATE TABLE link(aa INT, bb INT);
+  CREATE INDEX link_f ON link(aa,bb);
+  CREATE INDEX link_t ON link(bb,aa);
+  INSERT INTO link(aa,bb) VALUES
+    (1,3),
+    (5,3),
+    (7,1),
+    (7,9),
+    (9,9),
+    (5,11),
+    (11,7),
+    (2,4),
+    (4,6),
+    (8,6);
+} {}
+do_execsql_test 110 {
+  WITH RECURSIVE closure(x) AS (
+     VALUES(1)
+     UNION
+     SELECT aa FROM closure, link WHERE link.bb=closure.x
+     UNION
+     SELECT bb FROM closure, link WHERE link.aa=closure.x
+  )
+  SELECT x FROM closure ORDER BY x;
+} {1 3 5 7 9 11}
+do_execsql_test 111 {
+  WITH RECURSIVE closure(x) AS (
+     VALUES(1)
+     UNION
+     SELECT aa FROM link, closure WHERE link.bb=closure.x
+     UNION
+     SELECT bb FROM closure, link WHERE link.aa=closure.x
+  )
+  SELECT x FROM closure ORDER BY x;
+} {1 3 5 7 9 11}
+do_execsql_test 112 {
+  WITH RECURSIVE closure(x) AS (
+     VALUES(1)
+     UNION
+     SELECT bb FROM closure, link WHERE link.aa=closure.x
+     UNION
+     SELECT aa FROM link, closure WHERE link.bb=closure.x
+  )
+  SELECT x FROM closure ORDER BY x;
+} {1 3 5 7 9 11}
+do_execsql_test 113 {
+  WITH RECURSIVE closure(x) AS (
+     VALUES(1),(200),(300),(400)
+     INTERSECT
+     VALUES(1)
+     UNION
+     SELECT bb FROM closure, link WHERE link.aa=closure.x
+     UNION
+     SELECT aa FROM link, closure WHERE link.bb=closure.x
+  )
+  SELECT x FROM closure ORDER BY x;
+} {1 3 5 7 9 11}
+do_execsql_test 114 {
+  WITH RECURSIVE closure(x) AS (
+     VALUES(1),(200),(300),(400)
+     UNION ALL
+     VALUES(2)
+     UNION
+     SELECT bb FROM closure, link WHERE link.aa=closure.x
+     UNION
+     SELECT aa FROM link, closure WHERE link.bb=closure.x
+  )
+  SELECT x FROM closure ORDER BY x;
+} {1 2 3 4 5 6 7 8 9 11 200 300 400}
+
+do_catchsql_test 120 {
+  WITH RECURSIVE closure(x) AS (
+     VALUES(1),(200),(300),(400)
+     UNION ALL
+     VALUES(2)
+     UNION ALL
+     SELECT bb FROM closure, link WHERE link.aa=closure.x
+     UNION
+     SELECT aa FROM link, closure WHERE link.bb=closure.x
+  )
+  SELECT x FROM closure ORDER BY x;
+} {1 {circular reference: closure}}
+do_catchsql_test 121 {
+  WITH RECURSIVE closure(x) AS (
+     VALUES(1),(200),(300),(400)
+     UNION ALL
+     VALUES(2)
+     UNION
+     SELECT bb FROM closure, link WHERE link.aa=closure.x
+     UNION ALL
+     SELECT aa FROM link, closure WHERE link.bb=closure.x
+  )
+  SELECT x FROM closure ORDER BY x;
+} {1 {circular reference: closure}}
+
+do_execsql_test 130 {
+  WITH RECURSIVE closure(x) AS (
+    SELECT 1 AS x
+    UNION
+    SELECT aa FROM link JOIN closure ON bb=x
+    UNION
+    SELECT bb FROM link JOIN closure on aa=x
+    ORDER BY x LIMIT 4
+  )
+  SELECT * FROM closure;
+} {1 3 5 7}
+do_execsql_test 131 {
+  WITH RECURSIVE closure(x) AS (
+    SELECT 1 AS x
+    UNION ALL
+    SELECT 2
+    UNION
+    SELECT aa FROM link JOIN closure ON bb=x
+    UNION
+    SELECT bb FROM link JOIN closure on aa=x
+    ORDER BY x LIMIT 4
+  )
+  SELECT * FROM closure;
+} {1 2 3 4}
+
+do_execsql_test 200 {
+  CREATE TABLE linkA(aa1,aa2);
+  INSERT INTO linkA(aa1,aa2) VALUES(1,3),(5,7),(9,11);
+  CREATE TABLE linkB(bb1,bb2);
+  INSERT INTO linkB(bb1,bb2) VALUES(7,9),(11,13),(3,5);
+  CREATE TABLE linkC(cc1,cc2);
+  INSERT INTO linkC(cc1,cc2) VALUES(1,2),(2,4),(6,8);
+  CREATE TABLE linkD(dd1,dd2);
+  INSERT INTO linkD(dd1,dd2) VALUES(4,6),(100,110);
+} {}
+do_execsql_test 210 {
+  WITH RECURSIVE closure(x) AS (
+    VALUES(1)
+    UNION ALL
+    SELECT aa2 FROM linkA JOIN closure ON x=aa1
+    UNION ALL
+    SELECT bb2 FROM linkB JOIN closure ON x=bb1
+    UNION ALL
+    SELECT cc2 FROM linkC JOIN closure ON x=cc1
+    UNION ALL
+    SELECT dd2 FROM linkD JOIN closure ON x=dd1
+  )
+  SELECT x FROM closure ORDER BY +x;
+} {1 2 3 4 5 6 7 8 9 11 13}
+do_execsql_test 220 {
+  CREATE TABLE linkA_ipk(aa1 INTEGER PRIMARY KEY,aa2);
+  INSERT INTO linkA_ipk(aa1,aa2) SELECT aa1, aa2 FROM linkA;
+  CREATE TABLE linkB_ipk(bb1 INTEGER PRIMARY KEY,bb2);
+  INSERT INTO linkB_ipk(bb1,bb2) SELECT bb1, bb2 FROM linkB;
+  CREATE TABLE linkC_ipk(cc1 INTEGER PRIMARY KEY,cc2);
+  INSERT INTO linkC_ipk(cc1,cc2) SELECT cc1, cc2 FROM linkC;
+  CREATE TABLE linkD_ipk(dd1 INTEGER PRIMARY KEY,dd2);
+  INSERT INTO linkD_ipk(dd1,dd2) SELECT dd1, dd2 FROM linkD;
+  WITH RECURSIVE closure(x) AS (
+    VALUES(1)
+    UNION ALL
+    SELECT aa2 FROM linkA_ipk JOIN closure ON x=aa1
+    UNION ALL
+    SELECT bb2 FROM linkB_ipk JOIN closure ON x=bb1
+    UNION ALL
+    SELECT cc2 FROM linkC_ipk JOIN closure ON x=cc1
+    UNION ALL
+    SELECT dd2 FROM linkD_ipk JOIN closure ON x=dd1
+  )
+  SELECT x FROM closure ORDER BY +x;
+} {1 2 3 4 5 6 7 8 9 11 13}
+
+
+finish_test