mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
422 lines
11 KiB
Plaintext
422 lines
11 KiB
Plaintext
|
|
SELECT t1.rowid, t2.rowid, '|' FROM t1, t2 ON t1.a=t2.b;
|
|
|
|
|
|
CREATE TABLE t14(x);
|
|
INSERT INTO t14 VALUES('abcdefghij');
|
|
|
|
|
|
CREATE TABLE aa(a);
|
|
CREATE TABLE bb(b);
|
|
CREATE TABLE cc(c);
|
|
|
|
INSERT INTO aa VALUES(45);
|
|
INSERT INTO cc VALUES(45);
|
|
INSERT INTO cc VALUES(45);
|
|
|
|
|
|
SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
|
|
|
|
|
|
CREATE INDEX ccc ON cc(c);
|
|
SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
|
|
|
|
|
|
SELECT *
|
|
FROM (SELECT 1 a) AS x
|
|
LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT 1)));
|
|
|
|
|
|
SELECT *
|
|
FROM (SELECT 1 a) AS x
|
|
LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT * FROM (SELECT 1)))) AS y
|
|
JOIN (SELECT * FROM (SELECT 9)) AS z;
|
|
|
|
|
|
SELECT *
|
|
FROM (SELECT 111)
|
|
LEFT JOIN (SELECT cc+222, * FROM (SELECT * FROM (SELECT 333 cc)));
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(c PRIMARY KEY, a TEXT(10000), b TEXT(10000));
|
|
SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
|
|
|
|
|
|
SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1);
|
|
|
|
|
|
SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 222)
|
|
LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
|
|
|
|
|
|
SELECT count(*)
|
|
FROM (SELECT 111 AS x UNION ALL SELECT 222)
|
|
LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y;
|
|
|
|
|
|
SELECT count(*)
|
|
FROM (SELECT c+333 AS y FROM t1)
|
|
RIGHT JOIN (SELECT 111 AS x UNION ALL SELECT 222) ON x=y;
|
|
|
|
|
|
SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111)
|
|
LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
|
|
|
|
|
|
SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111 UNION ALL SELECT 222)
|
|
LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
|
|
|
|
|
|
INSERT INTO t1(c) VALUES(-111);
|
|
SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111 UNION ALL SELECT 222)
|
|
LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(c PRIMARY KEY) WITHOUT ROWID;
|
|
SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
|
|
|
|
|
|
CREATE TABLE t1(a);
|
|
INSERT INTO t1 VALUES(1),(2),(3);
|
|
CREATE VIEW v2 AS SELECT a, 1 AS b FROM t1;
|
|
CREATE TABLE t3(x);
|
|
INSERT INTO t3 VALUES(2),(4);
|
|
SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b=1;
|
|
|
|
|
|
SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b+1=x;
|
|
|
|
|
|
SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x ORDER BY b;
|
|
|
|
|
|
CREATE TABLE t1(id INTEGER PRIMARY KEY);
|
|
CREATE TABLE t2(id INTEGER PRIMARY KEY, c2 INTEGER);
|
|
CREATE TABLE t3(id INTEGER PRIMARY KEY, c3 INTEGER);
|
|
INSERT INTO t1(id) VALUES(456);
|
|
INSERT INTO t3(id) VALUES(1),(2);
|
|
SELECT t1.id, x2.id, x3.id
|
|
FROM t1
|
|
LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
|
|
LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
|
|
|
|
|
|
CREATE TABLE t1(a INT, b INT);
|
|
INSERT INTO t1 VALUES(1,2),(3,4);
|
|
CREATE TABLE t2(x INT, y INT);
|
|
SELECT *, 'x'
|
|
FROM t1 LEFT JOIN t2
|
|
WHERE CASE WHEN FALSE THEN a=x ELSE 1 END;
|
|
|
|
|
|
SELECT *, 'x'
|
|
FROM t1 LEFT JOIN t2
|
|
WHERE a IN (1,3,x,y);
|
|
|
|
|
|
SELECT *, 'x'
|
|
FROM t1 LEFT JOIN t2
|
|
WHERE NOT ( 'x'='y' AND t2.y=1 );
|
|
|
|
|
|
SELECT *, 'x'
|
|
FROM t1 LEFT JOIN t2
|
|
WHERE ~ ( 'x'='y' AND t2.y=1 );
|
|
|
|
|
|
SELECT *, 'x'
|
|
FROM t1 LEFT JOIN t2
|
|
WHERE t2.y IS NOT 'abc'
|
|
|
|
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
|
|
INSERT INTO t1(a,b) VALUES(1,0),(11,1),(12,1),(13,1),(121,12);
|
|
CREATE INDEX t1b ON t1(b);
|
|
CREATE TABLE t2(x INTEGER PRIMARY KEY);
|
|
INSERT INTO t2(x) VALUES(0),(1);
|
|
SELECT a1, a2, a3, a4, a5
|
|
FROM (SELECT a AS a1 FROM t1 WHERE b=0)
|
|
JOIN (SELECT x AS x1 FROM t2)
|
|
LEFT JOIN (SELECT a AS a2, b AS b2 FROM t1)
|
|
ON x1 IS TRUE AND b2=a1
|
|
JOIN (SELECT x AS x2 FROM t2)
|
|
ON x2<=CASE WHEN x1 THEN CASE WHEN a2 THEN 1 ELSE -1 END ELSE 0 END
|
|
LEFT JOIN (SELECT a AS a3, b AS b3 FROM t1)
|
|
ON x2 IS TRUE AND b3=a2
|
|
JOIN (SELECT x AS x3 FROM t2)
|
|
ON x3<=CASE WHEN x2 THEN CASE WHEN a3 THEN 1 ELSE -1 END ELSE 0 END
|
|
LEFT JOIN (SELECT a AS a4, b AS b4 FROM t1)
|
|
ON x3 IS TRUE AND b4=a3
|
|
JOIN (SELECT x AS x4 FROM t2)
|
|
ON x4<=CASE WHEN x3 THEN CASE WHEN a4 THEN 1 ELSE -1 END ELSE 0 END
|
|
LEFT JOIN (SELECT a AS a5, b AS b5 FROM t1)
|
|
ON x4 IS TRUE AND b5=a4
|
|
ORDER BY a1, a2, a3, a4, a5;
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t1(a INT);
|
|
INSERT INTO t1(a) VALUES(1);
|
|
CREATE TABLE t2(b INT);
|
|
SELECT a, b
|
|
FROM t1 LEFT JOIN t2 ON 0
|
|
WHERE (b IS NOT NULL)=0;
|
|
|
|
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(x);
|
|
INSERT INTO t1(x) VALUES(0),(1);
|
|
SELECT * FROM t1 LEFT JOIN (SELECT abs(1) AS y FROM t1) ON x WHERE NOT(y='a');
|
|
|
|
|
|
SELECT * FROM t1 LEFT JOIN (SELECT abs(1)+2 AS y FROM t1) ON x
|
|
WHERE NOT(y='a');
|
|
|
|
|
|
CREATE TABLE t0(a);
|
|
CREATE TABLE t1(b);
|
|
CREATE VIEW v0 AS SELECT a FROM t1 LEFT JOIN t0;
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
|
|
SELECT * FROM v0 WHERE NOT(v0.a IS FALSE);
|
|
|
|
|
|
SELECT * FROM t1 LEFT JOIN t0 WHERE NOT(a IS FALSE);
|
|
|
|
|
|
SELECT NOT(v0.a IS FALSE) FROM v0
|
|
|
|
|
|
CREATE TABLE t1(a);
|
|
CREATE TABLE t2(b);
|
|
INSERT INTO t1(a) VALUES(0);
|
|
CREATE VIEW v0(c) AS SELECT t2.b FROM t1 LEFT JOIN t2;
|
|
|
|
|
|
SELECT * FROM v0 WHERE v0.c NOTNULL NOTNULL;
|
|
|
|
|
|
SELECT * FROM t1 LEFT JOIN t2
|
|
|
|
|
|
SELECT * FROM t1 LEFT JOIN t2 WHERE (b IS NOT NULL) IS NOT NULL;
|
|
|
|
|
|
SELECT (b IS NOT NULL) IS NOT NULL FROM t1 LEFT JOIN t2
|
|
|
|
|
|
SELECT * FROM t1 LEFT JOIN t2 WHERE
|
|
(b IS NOT NULL AND b IS NOT NULL) IS NOT NULL;
|
|
|
|
|
|
CREATE TABLE t1(c1);
|
|
CREATE TABLE t0(c0);
|
|
INSERT INTO t0(c0) VALUES (0);
|
|
SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
|
|
|
|
|
|
CREATE INDEX t1x ON t1(0) WHERE NULL IN (c1);
|
|
SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
|
|
|
|
|
|
DROP TABLE t0;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t0(aa);
|
|
CREATE TABLE t1(bb);
|
|
INSERT INTO t0(aa) VALUES (1);
|
|
INSERT INTO t1(bb) VALUES (1);
|
|
SELECT 11, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
|
|
SELECT 12, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
|
|
SELECT 13, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
|
|
SELECT 14, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
|
|
CREATE INDEX i0 ON t0(aa) WHERE aa ISNULL;
|
|
SELECT 21, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
|
|
SELECT 22, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
|
|
SELECT 23, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
|
|
SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
|
|
|
|
|
|
CREATE TABLE t0(a, b);
|
|
CREATE INDEX t0a ON t0(a);
|
|
INSERT INTO t0 VALUES(10,10),(10,11),(10,12);
|
|
SELECT DISTINCT c FROM t0 LEFT JOIN (SELECT a+1 AS c FROM t0) ORDER BY c ;
|
|
|
|
|
|
CREATE TABLE t0(c0);
|
|
INSERT INTO t0(c0) VALUES(123);
|
|
CREATE VIEW v0(c0) AS SELECT 0 GROUP BY 1;
|
|
SELECT t0.c0, v0.c0, vt0.name
|
|
FROM v0, t0 LEFT JOIN pragma_table_info('t0') AS vt0
|
|
ON vt0.name LIKE 'c0'
|
|
WHERE v0.c0 == 0;
|
|
|
|
|
|
CREATE TABLE a(value TEXT);
|
|
INSERT INTO a(value) SELECT value FROM json_each('["a", "b", null]');
|
|
CREATE TABLE b(value TEXT);
|
|
INSERT INTO b(value) SELECT value FROM json_each('["a", "c", null]');
|
|
SELECT a.value, b.value FROM a RIGHT JOIN b ON a.value = b.value;
|
|
|
|
|
|
SELECT a.value, b.value FROM b LEFT JOIN a ON a.value = b.value;
|
|
|
|
|
|
SELECT a.value, b.value
|
|
FROM json_each('["a", "c", null]') AS b
|
|
LEFT JOIN
|
|
json_each('["a", "b", null]') AS a ON a.value = b.value;
|
|
|
|
|
|
SELECT a.value, b.value
|
|
FROM json_each('["a", "b", null]') AS a
|
|
RIGHT JOIN
|
|
json_each('["a", "c", null]') AS b ON a.value = b.value;
|
|
|
|
|
|
SELECT a.value, b.value
|
|
FROM json_each('["a", "b", null]') AS a
|
|
RIGHT JOIN
|
|
b ON a.value = b.value;
|
|
|
|
|
|
SELECT a.value, b.value
|
|
FROM a
|
|
RIGHT JOIN
|
|
json_each('["a", "c", null]') AS b ON a.value = b.value;
|
|
|
|
|
|
CREATE TABLE t1(a PRIMARY KEY, x);
|
|
CREATE TABLE t2(b INT);
|
|
CREATE INDEX t1aa ON t1(a, a);
|
|
|
|
INSERT INTO t1 VALUES('abc', 'def');
|
|
INSERT INTO t2 VALUES(1);
|
|
|
|
|
|
SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='def';
|
|
|
|
|
|
SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='abc';
|
|
|
|
|
|
SELECT * FROM t2 LEFT JOIN t1 ON a=0 WHERE (x='x' OR x IS NULL);
|
|
|
|
|
|
CREATE TABLE t0(c0 INT);
|
|
CREATE VIEW v0 AS SELECT (NULL AND 5) as c0 FROM t0;
|
|
INSERT INTO t0(c0) VALUES (NULL);
|
|
SELECT count(*) FROM v0 LEFT JOIN t0 ON v0.c0;
|
|
|
|
|
|
CREATE TABLE t1(a INT,b INT,c INT); INSERT INTO t1 VALUES(NULL,NULL,NULL);
|
|
CREATE TABLE t2(d INT,e INT); INSERT INTO t2 VALUES(NULL,NULL);
|
|
CREATE INDEX x2 ON t1(c,b);
|
|
CREATE TABLE t3(x INT); INSERT INTO t3 VALUES(NULL);
|
|
|
|
|
|
WITH t99(b) AS MATERIALIZED (
|
|
SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
|
|
)
|
|
SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
|
|
|
|
|
|
WITH t99(b) AS NOT MATERIALIZED (
|
|
SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
|
|
)
|
|
SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
|
|
|
|
|
|
WITH t99(b) AS (SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3))
|
|
SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
|
|
|
|
|
|
SELECT 5
|
|
FROM t2 JOIN (
|
|
SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
|
|
) AS t99 ON b IN (1,2,3);
|
|
|
|
|
|
INSERT INTO t1 VALUES(3,4,NULL);
|
|
INSERT INTO t2 VALUES(1,2);
|
|
WITH t99(b) AS (
|
|
SELECT coalesce(b,3) FROM t2 AS x LEFT JOIN t1 ON c IN (SELECT x FROM t3)
|
|
)
|
|
SELECT d, e, b FROM t2 JOIN t99 ON b IN (1,2,3) ORDER BY +d;
|
|
|
|
|
|
SELECT d, e, b2
|
|
FROM t2
|
|
JOIN (SELECT coalesce(b,3) AS b2 FROM t2 AS x LEFT JOIN t1
|
|
ON c IN (SELECT x FROM t3)) AS t99
|
|
ON b2 IN (1,2,3) ORDER BY +d;
|
|
|
|
|
|
DELETE FROM t1;
|
|
DELETE FROM t2 WHERE d IS NOT NULL;
|
|
DELETE FROM t3;
|
|
SELECT * FROM t2 JOIN (SELECT b FROM t2 LEFT JOIN t1
|
|
ON c IN (SELECT x FROM t3)) AS t99 ON b IN (1,2,3);
|
|
|
|
|
|
DELETE FROM t1;
|
|
DELETE FROM t2;
|
|
DELETE FROM t3;
|
|
INSERT INTO t1 VALUES(4,3,5);
|
|
INSERT INTO t2 VALUES(1,2);
|
|
INSERT INTO t3 VALUES(5);
|
|
SELECT * FROM t2 JOIN (SELECT b FROM t2 LEFT JOIN t1
|
|
ON c IN (SELECT x FROM t3)) AS t99 ON b IS NULL;
|
|
|
|
|
|
WITH t99(b) AS (
|
|
SELECT b FROM t2 AS x LEFT JOIN t1 ON c IN (SELECT x FROM t3)
|
|
)
|
|
SELECT d, e, b FROM t2 JOIN t99 ON b IS NULL;
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT);
|
|
CREATE TABLE t2(d INTEGER PRIMARY KEY, e INT);
|
|
CREATE VIEW t3(a,b,c,d,e) AS SELECT * FROM t1 LEFT JOIN t2 ON d=c;
|
|
CREATE TABLE t4(x INT, y INT);
|
|
INSERT INTO t1 VALUES(1,2,3);
|
|
INSERT INTO t2 VALUES(1,5);
|
|
INSERT INTO t4 VALUES(1,4);
|
|
SELECT a, b, y FROM t4 JOIN t3 ON a=x;
|
|
|
|
|
|
CREATE TABLE t0(a INT); INSERT INTO t0(a) VALUES (1);
|
|
CREATE TABLE t1(b INT); INSERT INTO t1(b) VALUES (2);
|
|
CREATE VIEW v2(c) AS SELECT 3 FROM t1;
|
|
SELECT * FROM t1 JOIN v2 ON 0 FULL OUTER JOIN t0 ON true;
|
|
|
|
|
|
SELECT * FROM t1 JOIN v2 ON 1=0 FULL OUTER JOIN t0 ON true;
|
|
|
|
|
|
SELECT * FROM t1 JOIN v2 ON false FULL OUTER JOIN t0 ON true;
|
|
|
|
|
|
CREATE TABLE t0(z INT); INSERT INTO t0 VALUES(1),(2);
|
|
CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(1);
|
|
CREATE TABLE t2(b INT); INSERT INTO t2 VALUES(2);
|
|
CREATE TABLE t3(c INT, d INT); INSERT INTO t3 VALUES(3,4);
|
|
CREATE TABLE t4(e INT); INSERT INTO t4 VALUES(5);
|
|
CREATE VIEW v5(x,y) AS SELECT c, d FROM t3 LEFT JOIN t4 ON false;
|
|
|
|
|
|
SELECT DISTINCT a, b
|
|
FROM t1 RIGHT JOIN t2 ON a=b LEFT JOIN v5 ON false
|
|
WHERE x <= y;
|
|
|
|
|
|
SELECT DISTINCT a, b
|
|
FROM t0 JOIN t1 ON z=a RIGHT JOIN t2 ON a=b LEFT JOIN v5 ON false
|
|
WHERE x <= y;
|
|
|