| # 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 1 FROM v1,v1,v1}] |
| 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 |
