CREATE TABLE t1(x INTEGER, y INTEGER);
WITH x(a) AS ( SELECT * FROM t1) SELECT 10
SELECT * FROM ( WITH x AS ( SELECT * FROM t1) SELECT 10 );
WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
WITH x(a) AS ( SELECT * FROM t1) DELETE FROM t1;
WITH x(a) AS ( SELECT * FROM t1) UPDATE t1 SET x = y;
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;
WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp;
SELECT * FROM (
WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp
);
WITH tmp1(a) AS ( SELECT * FROM t1 ),
tmp2(x) AS ( SELECT * FROM tmp1)
SELECT * FROM tmp2;
WITH tmp2(x) AS ( SELECT * FROM tmp1),
tmp1(a) AS ( SELECT * FROM t1 )
SELECT * FROM tmp2;
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;
WITH tmp AS ( SELECT * FROM t3 ),
tmp2 AS ( WITH tmp AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
SELECT * FROM tmp2;
WITH tmp AS ( SELECT * FROM t3 ),
tmp2 AS ( WITH xxxx AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
SELECT * FROM tmp2;
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;
WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
INSERT INTO t1 SELECT * FROM iset;
SELECT * FROM t1;
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;
WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i)
SELECT x FROM i LIMIT 10;
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;
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;
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;
WITH i(x) AS ( VALUES(1) UNION ALL SELECT (x+1)%10 FROM i)
SELECT x FROM i LIMIT 20;
WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
SELECT x FROM i LIMIT 20;
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');
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;
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;
WITH x(i) AS (
SELECT 1
UNION ALL
SELECT i+1 FROM x WHERE i<10
)
SELECT count(*) FROM x
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);
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;
WITH t(id) AS (
VALUES(2)
UNION ALL
SELECT i FROM tree, t WHERE p = id
)
SELECT id FROM t;
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;
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;
DROP TABLE IF EXISTS tree;
CREATE TABLE tree(id INTEGER PRIMARY KEY, parentid, payload);
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;
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
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
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;
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;
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;
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;
SELECT a FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
SELECT a||'' FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 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;
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;
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;
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;
WITH x AS (SELECT * FROM t) SELECT 0 EXCEPT SELECT 0 ORDER BY 1 COLLATE binary;
WITH x(a) AS (
WITH y(b) AS (SELECT 10)
SELECT 9 UNION ALL SELECT * FROM y
)
SELECT * FROM x
WITH x AS (
WITH y(b) AS (SELECT 10)
SELECT * FROM y UNION ALL SELECT * FROM y
)
SELECT * FROM x
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;
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;
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;
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;
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;
WITH xyz(x) AS (VALUES(NULL) UNION SELECT round(1<x) FROM xyz ORDER BY 1)
SELECT quote(x) FROM xyz;
WITH xyz(x) AS (
SELECT printf('%d', 5) * NULL
UNION SELECT round(1<1+x)
FROM xyz ORDER BY 1
)
SELECT 1 FROM xyz;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(x);
WITH c(i)AS(VALUES(9)UNION SELECT~i FROM c)SELECT max(5)>i fROM c;
WITH c(i)AS(VALUES(5)UNIoN SELECT 0)SELECT min(1)-i fROM c;
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
/* 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
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));
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;
CREATE TABLE t1(a, b, c);
CREATE VIEW v1 AS SELECT max(a), min(b) FROM t1 GROUP BY c;
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;
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;
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;
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;
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;