Adding upstream version 3.45.1.upstream/3.45.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1240 insertions, 0 deletions

diff --git a/test/with1.test b/test/with1.test
new file mode 100644
index 0000000..7400a7a
--- /dev/null
+++ b/test/with1.test
@@ -0,0 +1,1240 @@
+# 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 with1
+
+ifcapable {!cte} {
+  finish_test
+  return
+}
+
+do_execsql_test 1.0 {
+  CREATE TABLE t1(x INTEGER, y INTEGER);
+  WITH x(a) AS ( SELECT * FROM t1) SELECT 10
+} {10}
+
+do_execsql_test 1.1 {
+  SELECT * FROM ( WITH x AS ( SELECT * FROM t1) SELECT 10 );
+} {10}
+
+do_execsql_test 1.2 {
+  WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
+} {}
+
+do_execsql_test 1.3 {
+  WITH x(a) AS ( SELECT * FROM t1) DELETE FROM t1;
+} {}
+
+do_execsql_test 1.4 {
+  WITH x(a) AS ( SELECT * FROM t1) UPDATE t1 SET x = y;
+} {}
+ 
+#--------------------------------------------------------------------------
+
+do_execsql_test 2.1 {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(x);
+  INSERT INTO t1 VALUES(1);
+  INSERT INTO t1 VALUES(2);
+  WITH tmp AS ( SELECT * FROM t1 ) SELECT x FROM tmp;
+} {1 2}
+
+do_execsql_test 2.2 {
+  WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp;
+} {1 2}
+
+do_execsql_test 2.3 {
+  SELECT * FROM (
+    WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp
+  );
+} {1 2}
+
+do_execsql_test 2.4 {
+  WITH tmp1(a) AS ( SELECT * FROM t1 ),
+       tmp2(x) AS ( SELECT * FROM tmp1)
+  SELECT * FROM tmp2;
+} {1 2}
+
+do_execsql_test 2.5 {
+  WITH tmp2(x) AS ( SELECT * FROM tmp1),
+       tmp1(a) AS ( SELECT * FROM t1 )
+  SELECT * FROM tmp2;
+} {1 2}
+
+#-------------------------------------------------------------------------
+do_catchsql_test 3.1 {
+  WITH tmp2(x) AS ( SELECT * FROM tmp1 ),
+       tmp1(a) AS ( SELECT * FROM tmp2 )
+  SELECT * FROM tmp1;
+} {1 {circular reference: tmp1}}
+
+do_catchsql_test 3.2 {
+  CREATE TABLE t2(x INTEGER);
+  WITH tmp(a) AS (SELECT * FROM t1),
+       tmp(a) AS (SELECT * FROM t1)
+  SELECT * FROM tmp;
+} {1 {duplicate WITH table name: tmp}}
+
+do_execsql_test 3.3 {
+  CREATE TABLE t3(x);
+  CREATE TABLE t4(x);
+
+  INSERT INTO t3 VALUES('T3');
+  INSERT INTO t4 VALUES('T4');
+
+  WITH t3(a) AS (SELECT * FROM t4)
+  SELECT * FROM t3;
+} {T4}
+
+do_execsql_test 3.4 {
+  WITH tmp  AS ( SELECT * FROM t3 ),
+       tmp2 AS ( WITH tmp AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
+  SELECT * FROM tmp2;
+} {T4}
+
+do_execsql_test 3.5 {
+  WITH tmp  AS ( SELECT * FROM t3 ),
+       tmp2 AS ( WITH xxxx AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
+  SELECT * FROM tmp2;
+} {T3}
+
+do_catchsql_test 3.6 {
+  WITH tmp AS ( SELECT * FROM t3 ),
+  SELECT * FROM tmp;
+} {1 {near "SELECT": syntax error}}
+
+#-------------------------------------------------------------------------
+do_execsql_test 4.1 {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(x);
+  INSERT INTO t1 VALUES(1);
+  INSERT INTO t1 VALUES(2);
+  INSERT INTO t1 VALUES(3);
+  INSERT INTO t1 VALUES(4);
+
+  WITH dset AS ( SELECT 2 UNION ALL SELECT 4 )
+  DELETE FROM t1 WHERE x IN dset;
+  SELECT * FROM t1;
+} {1 3}
+
+do_execsql_test 4.2 {
+  WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
+  INSERT INTO t1 SELECT * FROM iset;
+  SELECT * FROM t1;
+} {1 3 2 4}
+
+do_execsql_test 4.3 {
+  WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
+  UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
+  SELECT * FROM t1;
+} {1 3 8 9}
+
+#-------------------------------------------------------------------------
+#
+do_execsql_test 5.1 {
+  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i)
+  SELECT x FROM i LIMIT 10;
+} {1 2 3 4 5 6 7 8 9 10}
+
+do_catchsql_test 5.2 {
+  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i ORDER BY 1)
+  SELECT x FROM i LIMIT 10;
+} {0 {1 2 3 4 5 6 7 8 9 10}}
+
+do_execsql_test 5.2.1 {
+  CREATE TABLE edge(xfrom, xto, seq, PRIMARY KEY(xfrom, xto)) WITHOUT ROWID;
+  INSERT INTO edge VALUES(0, 1, 10);
+  INSERT INTO edge VALUES(1, 2, 20);
+  INSERT INTO edge VALUES(0, 3, 30);
+  INSERT INTO edge VALUES(2, 4, 40);
+  INSERT INTO edge VALUES(3, 4, 40);
+  INSERT INTO edge VALUES(2, 5, 50);
+  INSERT INTO edge VALUES(3, 6, 60);
+  INSERT INTO edge VALUES(5, 7, 70);
+  INSERT INTO edge VALUES(3, 7, 70);
+  INSERT INTO edge VALUES(4, 8, 80);
+  INSERT INTO edge VALUES(7, 8, 80);
+  INSERT INTO edge VALUES(8, 9, 90);
+  
+  WITH RECURSIVE
+    ancest(id, mtime) AS
+      (VALUES(0, 0)
+       UNION
+       SELECT edge.xto, edge.seq FROM edge, ancest
+        WHERE edge.xfrom=ancest.id
+        ORDER BY 2
+      )
+  SELECT * FROM ancest;
+} {0 0 1 10 2 20 3 30 4 40 5 50 6 60 7 70 8 80 9 90}
+do_execsql_test 5.2.2 {
+  WITH RECURSIVE
+    ancest(id, mtime) AS
+      (VALUES(0, 0)
+       UNION ALL
+       SELECT edge.xto, edge.seq FROM edge, ancest
+        WHERE edge.xfrom=ancest.id
+        ORDER BY 2
+      )
+  SELECT * FROM ancest;
+} {0 0 1 10 2 20 3 30 4 40 4 40 5 50 6 60 7 70 7 70 8 80 8 80 8 80 8 80 9 90 9 90 9 90 9 90}
+do_execsql_test 5.2.3 {
+  WITH RECURSIVE
+    ancest(id, mtime) AS
+      (VALUES(0, 0)
+       UNION ALL
+       SELECT edge.xto, edge.seq FROM edge, ancest
+        WHERE edge.xfrom=ancest.id
+        ORDER BY 2 LIMIT 4 OFFSET 2
+      )
+  SELECT * FROM ancest;
+} {2 20 3 30 4 40 4 40}
+
+do_catchsql_test 5.3 {
+  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i LIMIT 5)
+  SELECT x FROM i;
+} {0 {1 2 3 4 5}}
+
+do_execsql_test 5.4 {
+  WITH i(x) AS ( VALUES(1) UNION ALL SELECT (x+1)%10 FROM i)
+  SELECT x FROM i LIMIT 20;
+} {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}
+
+do_execsql_test 5.5 {
+  WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
+  SELECT x FROM i LIMIT 20;
+} {1 2 3 4 5 6 7 8 9 0}
+
+do_catchsql_test 5.6.1 {
+  WITH i(x, y) AS ( VALUES(1) )
+  SELECT * FROM i;
+} {1 {table i has 1 values for 2 columns}}
+
+do_catchsql_test 5.6.2 {
+  WITH i(x) AS ( VALUES(1,2) )
+  SELECT * FROM i;
+} {1 {table i has 2 values for 1 columns}}
+
+do_catchsql_test 5.6.3 {
+  CREATE TABLE t5(a, b);
+  WITH i(x) AS ( SELECT * FROM t5 )
+  SELECT * FROM i;
+} {1 {table i has 2 values for 1 columns}}
+
+do_catchsql_test 5.6.4 {
+  WITH i(x) AS ( SELECT 1, 2 UNION ALL SELECT 1 )
+  SELECT * FROM i;
+} {1 {table i has 2 values for 1 columns}}
+
+do_catchsql_test 5.6.5 {
+  WITH i(x) AS ( SELECT 1 UNION ALL SELECT 1, 2 )
+  SELECT * FROM i;
+} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
+
+do_catchsql_test 5.6.6 {
+  WITH i(x) AS ( SELECT 1 UNION ALL SELECT x+1, x*2 FROM i )
+  SELECT * FROM i;
+} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
+
+do_catchsql_test 5.6.7 {
+  WITH i(x) AS ( SELECT 1, 2 UNION SELECT x+1 FROM i )
+  SELECT * FROM i;
+} {1 {table i has 2 values for 1 columns}}
+
+#-------------------------------------------------------------------------
+#
+do_execsql_test 6.1 {
+  CREATE TABLE f(
+      id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
+  );
+
+  INSERT INTO f VALUES(0, NULL, '');
+  INSERT INTO f VALUES(1, 0, 'bin');
+    INSERT INTO f VALUES(2, 1, 'true');
+    INSERT INTO f VALUES(3, 1, 'false');
+    INSERT INTO f VALUES(4, 1, 'ls');
+    INSERT INTO f VALUES(5, 1, 'grep');
+  INSERT INTO f VALUES(6, 0, 'etc');
+    INSERT INTO f VALUES(7, 6, 'rc.d');
+      INSERT INTO f VALUES(8, 7, 'rc.apache');
+      INSERT INTO f VALUES(9, 7, 'rc.samba');
+  INSERT INTO f VALUES(10, 0, 'home');
+    INSERT INTO f VALUES(11, 10, 'dan');
+      INSERT INTO f VALUES(12, 11, 'public_html');
+        INSERT INTO f VALUES(13, 12, 'index.html');
+          INSERT INTO f VALUES(14, 13, 'logo.gif');
+}
+
+do_execsql_test 6.2 {
+  WITH flat(fid, fpath) AS (
+    SELECT id, '' FROM f WHERE parentid IS NULL
+    UNION ALL
+    SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=fid
+  )
+  SELECT fpath FROM flat WHERE fpath!='' ORDER BY 1;
+} {
+  /bin 
+  /bin/false /bin/grep /bin/ls /bin/true 
+  /etc 
+  /etc/rc.d 
+  /etc/rc.d/rc.apache /etc/rc.d/rc.samba 
+  /home 
+  /home/dan 
+  /home/dan/public_html 
+  /home/dan/public_html/index.html 
+  /home/dan/public_html/index.html/logo.gif
+}
+
+do_execsql_test 6.3 {
+  WITH flat(fid, fpath) AS (
+    SELECT id, '' FROM f WHERE parentid IS NULL
+    UNION ALL
+    SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=fid
+  )
+  SELECT count(*) FROM flat;
+} {15}
+
+do_execsql_test 6.4 {
+  WITH x(i) AS (
+    SELECT 1
+    UNION ALL
+    SELECT i+1 FROM x WHERE i<10
+  )
+  SELECT count(*) FROM x
+} {10}
+
+
+#-------------------------------------------------------------------------
+
+do_execsql_test 7.1 {
+  CREATE TABLE tree(i, p);
+  INSERT INTO tree VALUES(1, NULL);
+  INSERT INTO tree VALUES(2, 1);
+  INSERT INTO tree VALUES(3, 1);
+  INSERT INTO tree VALUES(4, 2);
+  INSERT INTO tree VALUES(5, 4);
+}
+
+do_execsql_test 7.2 {
+  WITH t(id, path) AS (
+    SELECT i, '' FROM tree WHERE p IS NULL
+    UNION ALL
+    SELECT i, path || '/' || i FROM tree, t WHERE p = id
+  ) 
+  SELECT path FROM t;
+} {{} /2 /3 /2/4 /2/4/5}
+
+do_execsql_test 7.3 {
+  WITH t(id) AS (
+    VALUES(2)
+    UNION ALL
+    SELECT i FROM tree, t WHERE p = id
+  ) 
+  SELECT id FROM t;
+} {2 4 5}
+
+do_catchsql_test 7.4 {
+  WITH t(id) AS (
+    VALUES(2)
+    UNION ALL
+    SELECT i FROM tree WHERE p IN (SELECT id FROM t)
+  ) 
+  SELECT id FROM t;
+} {1 {circular reference: t}}
+
+do_catchsql_test 7.5 {
+  WITH t(id) AS (
+    VALUES(2)
+    UNION ALL
+    SELECT i FROM tree, t WHERE p = id AND p IN (SELECT id FROM t)
+  ) 
+  SELECT id FROM t;
+} {1 {multiple recursive references: t}}
+
+do_catchsql_test 7.6 {
+  WITH t(id) AS (
+    SELECT i FROM tree WHERE 2 IN (SELECT id FROM t)
+    UNION ALL
+    SELECT i FROM tree, t WHERE p = id
+  ) 
+  SELECT id FROM t;
+} {1 {circular reference: t}}
+
+# Compute the mandelbrot set using a recursive query
+#
+do_execsql_test 8.1-mandelbrot {
+  WITH RECURSIVE
+    xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
+    yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
+    m(iter, cx, cy, x, y) AS (
+      SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
+      UNION ALL
+      SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
+       WHERE (x*x + y*y) < 4.0 AND iter<28
+    ),
+    m2(iter, cx, cy) AS (
+      SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
+    ),
+    a(t) AS (
+      SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
+      FROM m2 GROUP BY cy
+    )
+  SELECT group_concat(rtrim(t),x'0a') FROM a;
+} {{                                    ....#
+                                   ..#*..
+                                 ..+####+.
+                            .......+####....   +
+                           ..##+*##########+.++++
+                          .+.##################+.
+              .............+###################+.+
+              ..++..#.....*#####################+.
+             ...+#######++#######################.
+          ....+*################################.
+ #############################################...
+          ....+*################################.
+             ...+#######++#######################.
+              ..++..#.....*#####################+.
+              .............+###################+.+
+                          .+.##################+.
+                           ..##+*##########+.++++
+                            .......+####....   +
+                                 ..+####+.
+                                   ..#*..
+                                    ....#
+                                    +.}}
+
+# Solve a sudoku puzzle using a recursive query
+#
+do_execsql_test 8.2-soduko {
+  WITH RECURSIVE
+    input(sud) AS (
+      VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
+    ),
+  
+    /* A table filled with digits 1..9, inclusive. */
+    digits(z, lp) AS (
+      VALUES('1', 1)
+      UNION ALL SELECT
+      CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
+    ),
+  
+    /* The tricky bit. */
+    x(s, ind) AS (
+      SELECT sud, instr(sud, '.') FROM input
+      UNION ALL
+      SELECT
+        substr(s, 1, ind-1) || z || substr(s, ind+1),
+        instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
+       FROM x, digits AS z
+      WHERE ind>0
+        AND NOT EXISTS (
+              SELECT 1
+                FROM digits AS lp
+               WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
+                  OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
+                  OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+                          + ((ind-1)/27) * 27 + lp
+                          + ((lp-1) / 3) * 6, 1)
+           )
+    )
+  SELECT s FROM x WHERE ind=0;
+} {534678912672195348198342567859761423426853791713924856961537284287419635345286179}
+
+#--------------------------------------------------------------------------
+# Some tests that use LIMIT and OFFSET in the definition of recursive CTEs.
+# 
+set I [list 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20]
+proc limit_test {tn iLimit iOffset} {
+  if {$iOffset < 0} { set iOffset 0 }
+  if {$iLimit < 0 } {
+    set result [lrange $::I $iOffset end]
+  } else {
+    set result [lrange $::I $iOffset [expr $iLimit+$iOffset-1]]
+  }
+  uplevel [list do_execsql_test $tn [subst -nocommands {
+    WITH ii(a) AS (
+      VALUES(1)
+      UNION ALL 
+      SELECT a+1 FROM ii WHERE a<20 
+      LIMIT $iLimit OFFSET $iOffset
+    )
+    SELECT * FROM ii
+  }] $result]
+}
+
+limit_test 9.1    20  0
+limit_test 9.2     0  0
+limit_test 9.3    19  1
+limit_test 9.4    20 -1
+limit_test 9.5     5  5
+limit_test 9.6     0 -1
+limit_test 9.7    40 -1
+limit_test 9.8    -1 -1
+limit_test 9.9    -1 -1
+
+#--------------------------------------------------------------------------
+# Test the ORDER BY clause on recursive tables.
+#
+
+do_execsql_test 10.1 {
+  DROP TABLE IF EXISTS tree;
+  CREATE TABLE tree(id INTEGER PRIMARY KEY, parentid, payload);
+}
+
+proc insert_into_tree {L} {
+  db eval { DELETE FROM tree }
+  foreach key $L {
+    unset -nocomplain parentid
+    foreach seg [split $key /] {
+      if {$seg==""} continue
+      set id [db one {
+        SELECT id FROM tree WHERE parentid IS $parentid AND payload=$seg
+      }]
+      if {$id==""} {
+        db eval { INSERT INTO tree VALUES(NULL, $parentid, $seg) }
+        set parentid [db last_insert_rowid]
+      } else {
+        set parentid $id
+      }
+    }
+  }
+}
+
+insert_into_tree {
+  /a/a/a
+  /a/b/c
+  /a/b/c/d
+  /a/b/d
+}
+do_execsql_test 10.2 {
+  WITH flat(fid, p) AS (
+    SELECT id, '/' || payload FROM tree WHERE parentid IS NULL
+    UNION ALL
+    SELECT id, p || '/' || payload FROM flat, tree WHERE parentid=fid
+  )
+  SELECT p FROM flat ORDER BY p;
+} {
+  /a /a/a /a/a/a 
+     /a/b /a/b/c /a/b/c/d
+          /a/b/d
+}
+
+# Scan the tree-structure currently stored in table tree. Return a list
+# of nodes visited.
+#
+proc scan_tree {bDepthFirst bReverse} {
+
+  set order "ORDER BY "
+  if {$bDepthFirst==0} { append order "2 ASC," }
+  if {$bReverse==0} { 
+    append order " 3 ASC" 
+  } else {
+    append order " 3 DESC" 
+  }
+
+  db eval "
+    WITH flat(fid, depth, p) AS (
+        SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
+        UNION ALL
+        SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
+        $order
+    )
+    SELECT p FROM flat;
+  "
+}
+
+insert_into_tree {
+  /a/b
+  /a/b/c
+  /a/d
+  /a/d/e
+  /a/d/f
+  /g/h
+}
+
+# Breadth first, siblings in ascending order.
+#
+do_test 10.3 {
+  scan_tree 0 0
+} [list {*}{
+  /a /g
+  /a/b /a/d /g/h
+  /a/b/c /a/d/e /a/d/f
+}]
+
+# Depth first, siblings in ascending order.
+#
+do_test 10.4 {
+  scan_tree 1 0
+} [list {*}{
+  /a /a/b /a/b/c
+     /a/d /a/d/e 
+          /a/d/f
+  /g /g/h
+}]
+
+# Breadth first, siblings in descending order.
+#
+do_test 10.5 {
+  scan_tree 0 1
+} [list {*}{
+  /g /a 
+  /g/h /a/d /a/b 
+  /a/d/f /a/d/e /a/b/c 
+}]
+
+# Depth first, siblings in ascending order.
+#
+do_test 10.6 {
+  scan_tree 1 1
+} [list {*}{
+  /g /g/h
+  /a /a/d /a/d/f 
+          /a/d/e 
+     /a/b /a/b/c
+}]
+
+
+# Test name resolution in ORDER BY clauses.
+#
+do_catchsql_test 10.7.1 {
+  WITH t(a) AS (
+    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY a
+  ) 
+  SELECT * FROM t
+} {1 {1st ORDER BY term does not match any column in the result set}}
+do_execsql_test 10.7.2 {
+  WITH t(a) AS (
+    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY b
+  ) 
+  SELECT * FROM t
+} {1 2 3 4 5}
+do_execsql_test 10.7.3 {
+  WITH t(a) AS (
+    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY c
+  ) 
+  SELECT * FROM t
+} {1 2 3 4 5}
+
+# Test COLLATE clauses attached to ORDER BY.
+#
+insert_into_tree {
+  /a/b
+  /a/C
+  /a/d
+  /B/e
+  /B/F
+  /B/g
+  /c/h
+  /c/I
+  /c/j
+}
+
+do_execsql_test 10.8.1 {
+  WITH flat(fid, depth, p) AS (
+    SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
+    UNION ALL
+    SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
+    ORDER BY 2, 3 COLLATE nocase
+  )
+  SELECT p FROM flat;
+} {
+  /a /B /c
+  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
+}
+do_execsql_test 10.8.2 {
+  WITH flat(fid, depth, p) AS (
+      SELECT id, 1, ('/' || payload) COLLATE nocase 
+      FROM tree WHERE parentid IS NULL
+    UNION ALL
+      SELECT id, depth+1, (p||'/'||payload)
+      FROM flat, tree WHERE parentid=fid
+    ORDER BY 2, 3
+  )
+  SELECT p FROM flat;
+} {
+  /a /B /c
+  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
+}
+
+do_execsql_test 10.8.3 {
+  WITH flat(fid, depth, p) AS (
+      SELECT id, 1, ('/' || payload)
+      FROM tree WHERE parentid IS NULL
+    UNION ALL
+      SELECT id, depth+1, (p||'/'||payload) COLLATE nocase 
+      FROM flat, tree WHERE parentid=fid
+    ORDER BY 2, 3
+  )
+  SELECT p FROM flat;
+} {
+  /a /B /c
+  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
+}
+
+do_execsql_test 10.8.4.1 {
+  CREATE TABLE tst(a,b);
+  INSERT INTO tst VALUES('a', 'A');
+  INSERT INTO tst VALUES('b', 'B');
+  INSERT INTO tst VALUES('c', 'C');
+  SELECT a COLLATE nocase FROM tst UNION ALL SELECT b FROM tst ORDER BY 1;
+} {a A b B c C}
+do_execsql_test 10.8.4.2 {
+  SELECT a FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
+} {A B C a b c}
+do_execsql_test 10.8.4.3 {
+  SELECT a||'' FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
+} {a A b B c C}
+
+# Test cases to illustrate on the ORDER BY clause on a recursive query can be
+# used to control depth-first versus breath-first search in a tree.
+#
+do_execsql_test 11.1 {
+  CREATE TABLE org(
+    name TEXT PRIMARY KEY,
+    boss TEXT REFERENCES org
+  ) WITHOUT ROWID;
+  INSERT INTO org VALUES('Alice',NULL);
+  INSERT INTO org VALUES('Bob','Alice');
+  INSERT INTO org VALUES('Cindy','Alice');
+  INSERT INTO org VALUES('Dave','Bob');
+  INSERT INTO org VALUES('Emma','Bob');
+  INSERT INTO org VALUES('Fred','Cindy');
+  INSERT INTO org VALUES('Gail','Cindy');
+  INSERT INTO org VALUES('Harry','Dave');
+  INSERT INTO org VALUES('Ingrid','Dave');
+  INSERT INTO org VALUES('Jim','Emma');
+  INSERT INTO org VALUES('Kate','Emma');
+  INSERT INTO org VALUES('Lanny','Fred');
+  INSERT INTO org VALUES('Mary','Fred');
+  INSERT INTO org VALUES('Noland','Gail');
+  INSERT INTO org VALUES('Olivia','Gail');
+  -- The above are all under Alice.  Add a few more records for people
+  -- not in Alice's group, just to prove that they won't be selected.
+  INSERT INTO org VALUES('Xaviar',NULL);
+  INSERT INTO org VALUES('Xia','Xaviar');
+  INSERT INTO org VALUES('Xerxes','Xaviar');
+  INSERT INTO org VALUES('Xena','Xia');
+  -- Find all members of Alice's group, breath-first order  
+  WITH RECURSIVE
+    under_alice(name,level) AS (
+       VALUES('Alice','0')
+       UNION ALL
+       SELECT org.name, under_alice.level+1
+         FROM org, under_alice
+        WHERE org.boss=under_alice.name
+        ORDER BY 2
+    )
+  SELECT group_concat(substr('...............',1,level*3) || name,x'0a')
+    FROM under_alice;
+} {{Alice
+...Bob
+...Cindy
+......Dave
+......Emma
+......Fred
+......Gail
+.........Harry
+.........Ingrid
+.........Jim
+.........Kate
+.........Lanny
+.........Mary
+.........Noland
+.........Olivia}}
+
+# The previous query used "ORDER BY level" to yield a breath-first search.
+# Change that to "ORDER BY level DESC" for a depth-first search.
+#
+do_execsql_test 11.2 {
+  WITH RECURSIVE
+    under_alice(name,level) AS (
+       VALUES('Alice','0')
+       UNION ALL
+       SELECT org.name, under_alice.level+1
+         FROM org, under_alice
+        WHERE org.boss=under_alice.name
+        ORDER BY 2 DESC
+    )
+  SELECT group_concat(substr('...............',1,level*3) || name,x'0a')
+    FROM under_alice;
+} {{Alice
+...Bob
+......Dave
+.........Harry
+.........Ingrid
+......Emma
+.........Jim
+.........Kate
+...Cindy
+......Fred
+.........Lanny
+.........Mary
+......Gail
+.........Noland
+.........Olivia}}
+
+# Without an ORDER BY clause, the recursive query should use a FIFO,
+# resulting in a breath-first search.
+#
+do_execsql_test 11.3 {
+  WITH RECURSIVE
+    under_alice(name,level) AS (
+       VALUES('Alice','0')
+       UNION ALL
+       SELECT org.name, under_alice.level+1
+         FROM org, under_alice
+        WHERE org.boss=under_alice.name
+    )
+  SELECT group_concat(substr('...............',1,level*3) || name,x'0a')
+    FROM under_alice;
+} {{Alice
+...Bob
+...Cindy
+......Dave
+......Emma
+......Fred
+......Gail
+.........Harry
+.........Ingrid
+.........Jim
+.........Kate
+.........Lanny
+.........Mary
+.........Noland
+.........Olivia}}
+
+#--------------------------------------------------------------------------
+# Ticket [31a19d11b97088296ac104aaff113a9790394927] (2014-02-09)
+# Name resolution issue with compound SELECTs and Common Table Expressions 
+#
+do_execsql_test 12.1 {
+WITH RECURSIVE
+  t1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM t1 WHERE x<20),
+  t2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM t2 WHERE y<20)
+SELECT x FROM t1 EXCEPT SELECT y FROM t2 ORDER BY 1;
+} {2 4 8 10 14 16 20}
+
+# 2015-03-21
+# Column wildcards on the LHS of a recursive table expression
+#
+do_catchsql_test 13.1 {
+  WITH RECURSIVE c(i) AS (SELECT * UNION ALL SELECT i+1 FROM c WHERE i<10)
+  SELECT i FROM c;
+} {1 {no tables specified}}
+do_catchsql_test 13.2 {
+  WITH RECURSIVE c(i) AS (SELECT 5,* UNION ALL SELECT i+1 FROM c WHERE i<10)
+  SELECT i FROM c;
+} {1 {no tables specified}}
+do_catchsql_test 13.3 {
+  WITH RECURSIVE c(i,j) AS (SELECT 5,* UNION ALL SELECT i+1,11 FROM c WHERE i<10)
+  SELECT i FROM c;
+} {1 {table c has 1 values for 2 columns}}
+
+# 2015-04-12
+#
+do_execsql_test 14.1 {
+  WITH x AS (SELECT * FROM t) SELECT 0 EXCEPT SELECT 0 ORDER BY 1 COLLATE binary;
+} {}
+
+# 2015-05-27:  Do not allow rowid usage within a CTE
+#
+do_catchsql_test 15.1 {
+  WITH RECURSIVE
+    d(x) AS (VALUES(1) UNION ALL SELECT rowid+1 FROM d WHERE rowid<10)
+  SELECT x FROM d;
+} {1 {no such column: rowid}}
+
+# 2015-07-05:  Do not allow aggregate recursive queries
+#
+do_catchsql_test 16.1 {
+  WITH RECURSIVE
+    i(x) AS (VALUES(1) UNION SELECT count(*) FROM i)
+  SELECT * FROM i;
+} {1 {recursive aggregate queries not supported}}
+
+# Or window-function recursive queries. Ticket e8275b41.
+#
+ifcapable windowfunc {
+  do_catchsql_test 16.2 {
+    WITH RECURSIVE
+      i(x) AS (VALUES(1) UNION SELECT count(*) OVER () FROM i)
+      SELECT * FROM i;
+  } {1 {cannot use window functions in recursive queries}}
+  do_catchsql_test 16.3 {
+    WITH RECURSIVE
+      t(id, parent) AS (VALUES(1,2)),
+      q(id, parent, rn) AS (
+          VALUES(1,2,3)
+          UNION ALL
+          SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
+          FROM q JOIN t ON t.parent = q.id
+          )
+        SELECT * FROM q;
+  } {1 {cannot use window functions in recursive queries}}
+}
+
+#-------------------------------------------------------------------------
+do_execsql_test 17.1 {
+  WITH x(a) AS (
+    WITH y(b) AS (SELECT 10)
+    SELECT 9 UNION ALL SELECT * FROM y
+  )
+  SELECT * FROM x
+} {9 10}
+
+do_execsql_test 17.2 {
+  WITH x AS (
+    WITH y(b) AS (SELECT 10)
+    SELECT * FROM y UNION ALL SELECT * FROM y
+  )
+  SELECT * FROM x
+} {10 10}
+
+do_test 17.2 {
+  db eval {
+    WITH x AS (
+        WITH y(b) AS (SELECT 10)
+        SELECT * FROM y UNION ALL SELECT * FROM y
+    )
+    SELECT * FROM x
+  } A {
+    # no op
+  }
+  set A(*)
+} {b}
+
+do_catchsql_test 17.3 {
+  WITH i AS (
+    WITH j AS (SELECT 5)
+    SELECT 5 FROM i UNION SELECT 8 FROM i
+  )
+  SELECT * FROM i;
+} {1 {circular reference: i}}
+
+do_catchsql_test 17.4 {
+  WITH i AS (
+    WITH j AS (SELECT 5)
+    SELECT 5 FROM t1 UNION SELECT 8 FROM t11
+  )
+  SELECT * FROM i;
+} {1 {no such table: t11}}
+
+do_execsql_test 17.5 {
+  WITH 
+  x1 AS (SELECT 10),
+  x2 AS (SELECT * FROM x1),
+  x3 AS (
+    WITH x1 AS (SELECT 11)
+    SELECT * FROM x2 UNION ALL SELECT * FROM x2
+  )
+  SELECT * FROM x3;
+} {10 10}
+
+do_execsql_test 17.6 {
+  WITH 
+  x1 AS (SELECT 10),
+  x2 AS (SELECT * FROM x1),
+  x3 AS (
+    WITH x1 AS (SELECT 11)
+    SELECT * FROM x2 UNION ALL SELECT * FROM x1
+  )
+  SELECT * FROM x3;
+} {10 11}
+
+do_execsql_test 17.7 {
+  WITH 
+  x1 AS (SELECT 10),
+  x2 AS (SELECT * FROM x1),
+  x3 AS (
+    WITH 
+      x1 AS ( SELECT 11 ),
+      x4 AS ( SELECT * FROM x2 )
+    SELECT * FROM x4 UNION ALL SELECT * FROM x1
+  )
+  SELECT * FROM x3;
+} {10 11}
+
+do_execsql_test 17.8 {
+  WITH 
+  x1 AS (SELECT 10),
+  x2 AS (SELECT * FROM x1),
+  x3 AS (
+    WITH 
+      x1 AS ( SELECT 11 ),
+      x4 AS ( SELECT * FROM x2 )
+    SELECT * FROM x4 UNION ALL SELECT * FROM x1
+  )
+  SELECT * FROM x3;
+} {10 11}
+
+do_execsql_test 17.9 {
+  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}
+
+# Added to test a fix to a faulty assert() discovered by libFuzzer.
+#
+do_execsql_test 18.1 {
+  WITH xyz(x) AS (VALUES(NULL) UNION SELECT round(1<x) FROM xyz ORDER BY 1)
+  SELECT quote(x) FROM xyz;
+} {NULL}
+do_execsql_test 18.2 {
+  WITH xyz(x) AS (
+    SELECT printf('%d', 5) * NULL
+    UNION SELECT round(1<1+x) 
+    FROM xyz ORDER BY 1
+  )
+  SELECT 1 FROM xyz;
+} 1
+
+# EXPLAIN QUERY PLAN on a self-join of a CTE
+#
+do_execsql_test 19.1a {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(x);
+}
+do_eqp_test 19.1b {
+  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;
+} {
+  QUERY PLAN
+  |--MATERIALIZE x1
+  |  `--SCAN CONSTANT ROW
+  |--SCAN x1
+  `--SCAN x1
+}
+
+# 2017-10-28.
+# See check-in https://sqlite.org/src/info/0926df095faf72c2
+# Tried to optimize co-routine processing by changing a Copy opcode
+# into SCopy.  But OSSFuzz found two (similar) cases where that optimization
+# does not work.
+#
+do_execsql_test 20.1 {
+  WITH c(i)AS(VALUES(9)UNION SELECT~i FROM c)SELECT max(5)>i fROM c;
+} {0}
+do_execsql_test 20.2 {
+  WITH c(i)AS(VALUES(5)UNIoN SELECT 0)SELECT min(1)-i fROM c;
+} {1}
+
+# 2018-12-26
+# Two different CTE tables with the same name appear in within a single FROM
+# clause due to the query-flattener optimization.  make sure this does not cause
+# problems.  This problem was discovered by Matt Denton.
+#
+do_execsql_test 21.1 {
+   WITH RECURSIVE t21(a,b) AS (
+    WITH t21(x) AS (VALUES(1))
+    SELECT x, x FROM t21 ORDER BY 1
+  )
+  SELECT * FROM t21 AS tA, t21 AS tB
+} {1 1 1 1}
+do_execsql_test 21.1b {
+   /* This variant from chromium bug 922312 on 2019-01-16 */
+   WITH RECURSIVE t21(a,b) AS (
+    WITH t21(x) AS (VALUES(1))
+    SELECT x, x FROM t21 ORDER BY 1 LIMIT 5
+  )
+  SELECT * FROM t21 AS tA, t21 AS tB
+} {1 1 1 1}
+do_execsql_test 21.2 {
+  SELECT printf('',
+     EXISTS (WITH RECURSIVE Table0 AS (WITH Table0 AS (SELECT DISTINCT 1)
+                                       SELECT *, * FROM Table0 ORDER BY 1 DESC)
+             SELECT * FROM Table0  NATURAL JOIN  Table0));
+} {{}}
+
+# 2019-01-17
+# Make sure crazy nexted CTE joins terminate with an error quickly.
+#
+do_catchsql_test 22.1 {
+  WITH RECURSIVE c AS NOT MATERIALIZED (
+     WITH RECURSIVE c AS NOT MATERIALIZED (
+        WITH RECURSIVE c AS NOT MATERIALIZED (
+           WITH RECURSIVE c AS NOT MATERIALIZED (
+               WITH  c AS (VALUES(0))
+               SELECT 1 FROM c LEFT JOIN c ON ltrim(1)
+           )
+           SELECT 1 FROM c,c,c,c,c,c,c,c,c
+        )
+        SELECT  2 FROM c,c,c,c,c,c,c,c,c
+     )
+     SELECT 3 FROM c,c,c,c,c,c,c,c,c
+  )
+  SELECT 4 FROM c,c,c,c,c,c,c,c,c;
+} {1 {too many FROM clause terms, max: 200}}
+
+# 2019-05-22
+# ticket https://www.sqlite.org/src/tktview/ce823231949d3abf42453c8f20
+#
+sqlite3 db :memory:
+do_execsql_test 23.1 {
+  CREATE TABLE t1(id INTEGER NULL PRIMARY KEY, name Text);
+  INSERT INTO t1 VALUES (1, 'john');
+  INSERT INTO t1 VALUES (2, 'james');
+  INSERT INTO t1 VALUES (3, 'jingle');
+  INSERT INTO t1 VALUES (4, 'himer');
+  INSERT INTO t1 VALUES (5, 'smith');
+  CREATE VIEW v2 AS
+    WITH t4(Name) AS (VALUES ('A'), ('B'))
+    SELECT Name Name FROM t4;
+  CREATE VIEW v3 AS
+    WITH t4(Att, Val, Act) AS (VALUES
+      ('C', 'D', 'E'),
+      ('F', 'G', 'H')
+    )
+    SELECT D.Id Id, P.Name Protocol, T.Att Att, T.Val Val, T.Act Act
+    FROM t1 D
+    CROSS JOIN v2 P
+    CROSS JOIN t4 T;
+  SELECT * FROM v3;
+} {1 A C D E 1 A F G H 1 B C D E 1 B F G H 2 A C D E 2 A F G H 2 B C D E 2 B F G H 3 A C D E 3 A F G H 3 B C D E 3 B F G H 4 A C D E 4 A F G H 4 B C D E 4 B F G H 5 A C D E 5 A F G H 5 B C D E 5 B F G H}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 24.1 {
+  CREATE TABLE t1(a, b, c);
+  CREATE VIEW v1 AS SELECT max(a), min(b) FROM t1 GROUP BY c;
+}
+do_test 24.1 {
+  set program [db eval {EXPLAIN SELECT * FROM v1 AS aa, v1 AS bb, v1 AS cc}]
+  expr [lsearch $program OpenDup]>0
+} {1}
+do_execsql_test 24.2 {
+  ATTACH "" AS aux;
+  CREATE VIEW aux.v3 AS VALUES(1);
+  CREATE VIEW main.v3 AS VALUES(3);
+
+  CREATE VIEW aux.v2 AS SELECT * FROM v3;
+  CREATE VIEW main.v2 AS SELECT * FROM v3;
+
+  SELECT * FROM main.v2 AS a, aux.v2 AS b, aux.v2 AS c, main.v2 AS d;
+} {
+  3 1 1 3
+}
+
+# 2020-01-02 chromium ticket 1033461
+# Do not allow the generated name of a CTE be "true" or "false" as
+# such a label might be later confused for the boolean literals of
+# the same name, causing inconsistencies in the abstract syntax
+# tree.  This problem first arose in version 3.23.0 when SQLite
+# began recognizing "true" and "false" as boolean literals, but also
+# had to continue to recognize "true" and "false" as identifiers for
+# backwards compatibility.
+#
+foreach {id dual} {
+  1  {CREATE TABLE dual AS SELECT 'X' AS dummy}
+  2  {CREATE TEMP TABLE dual AS SELECT 'X' AS dummy}
+  3  {CREATE VIEW dual(dummy) AS VALUES('X')}
+  4  {CREATE TEMP VIEW dual(dummy) AS VALUES('X')}
+} {
+  reset_db
+  db eval $dual
+  do_execsql_test 25.$id {
+    WITH cte1 AS (
+      SELECT TRUE, (
+        WITH cte2 AS (SELECT avg(DISTINCT TRUE) FROM dual)
+        SELECT 2571 FROM cte2
+      ) AS subquery1
+      FROM dual
+      GROUP BY 1
+    )
+    SELECT (SELECT 1324 FROM cte1) FROM cte1;
+  } {1324}
+}
+
+do_catchsql_test 26.0 {
+  WITH i(x) AS ( 
+    VALUES(1) UNION ALL SELECT x+1 FRO, a.b,O. * ,I¬i O, a.b,O. * ORDER BY 1
+  )
+  SELECT x,O. * O FROM i ¬I,I? 10;
+} {1 {near "O": syntax error}}
+
+# 2020-09-17 ticket c51489c3b8f919c5
+# DISTINCT cannot be ignored in a UNION ALL recursive CTE
+#
+reset_db
+do_execsql_test 26.1 {
+  CREATE TABLE t (label VARCHAR(10), step INTEGER);
+  INSERT INTO T VALUES('a', 1);
+  INSERT INTO T VALUES('a', 1);
+  INSERT INTO T VALUES('b', 1);
+  WITH RECURSIVE cte(label, step) AS (
+      SELECT DISTINCT * FROM t 
+    UNION ALL 
+      SELECT label, step + 1 FROM cte WHERE step < 3
+  )
+  SELECT * FROM cte ORDER BY +label, +step;
+} {a 1 a 2 a 3 b 1 b 2 b 3}
+do_execsql_test 26.2 {
+  WITH RECURSIVE cte(label, step) AS (
+      SELECT * FROM t 
+    UNION
+      SELECT label, step + 1 FROM cte WHERE step < 3
+  )
+  SELECT * FROM cte ORDER BY +label, +step;
+} {a 1 a 2 a 3 b 1 b 2 b 3}
+do_execsql_test 26.3 {
+  CREATE TABLE tworow(x);
+  INSERT INTO tworow(x) VALUES(1),(2);
+  DELETE FROM t WHERE rowid=2;
+  WITH RECURSIVE cte(label, step) AS (
+      SELECT * FROM t
+    UNION ALL
+      SELECT DISTINCT label, step + 1 FROM cte, tworow WHERE step < 3
+  )
+  SELECT * FROM cte ORDER BY +label, +step;
+} {a 1 a 2 a 3 b 1 b 2 b 3}
+
+# 2021-05-20
+# forum post https://sqlite.org/forum/forumpost/8590e3f6dc
+#
+reset_db
+do_execsql_test 27.1 {
+  CREATE TABLE t1(k);
+  CREATE TABLE log(k, cte_map, main_map);
+  CREATE TABLE map(k, v);
+  INSERT INTO map VALUES(1, 'main1'), (2, 'main2');
+  
+  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
+    INSERT INTO log
+        WITH map(k,v) AS (VALUES(1,'cte1'),(2,'cte2'))
+        SELECT
+          new.k,
+          (SELECT v FROM map WHERE k=new.k),
+          (SELECT v FROM main.map WHERE k=new.k);
+  END;
+  
+  INSERT INTO t1 VALUES(1);
+  INSERT INTO t1 VALUES(2);
+  SELECT k, cte_map, main_map, '|' FROM log ORDER BY k;
+} {1 cte1 main1 | 2 cte2 main2 |}
+
+finish_test