CREATE TABLE aa(a);
CREATE TABLE bb(b);
CREATE TABLE cc(c);
INSERT INTO aa VALUES('one');
INSERT INTO bb VALUES('one');
INSERT INTO cc VALUES('one');
CREATE TABLE t1(k1 INTEGER PRIMARY KEY, k2, k3);
CREATE TABLE t2(k2 INTEGER PRIMARY KEY, v2);
-- Prior to this problem being fixed, table t3_2 would be omitted from
-- the join queries below, but if t3_1 were used in its place it would
-- not.
CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID;
CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID;
CREATE TABLE c1(k INTEGER PRIMARY KEY, v1);
CREATE TABLE c2(k INTEGER PRIMARY KEY, v2);
CREATE TABLE c3(k INTEGER PRIMARY KEY, v3);
INSERT INTO c1 VALUES(1, 2);
INSERT INTO c2 VALUES(2, 3);
INSERT INTO c3 VALUES(3, 'v3');
INSERT INTO c1 VALUES(111, 1112);
INSERT INTO c2 VALUES(112, 1113);
INSERT INTO c3 VALUES(113, 'v1113');
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
DROP TABLE c1;
DROP TABLE c2;
DROP TABLE c3;
CREATE TABLE c1(k UNIQUE, v1);
CREATE TABLE c2(k UNIQUE, v2);
CREATE TABLE c3(k UNIQUE, v3);
INSERT INTO c1 VALUES(1, 2);
INSERT INTO c2 VALUES(2, 3);
INSERT INTO c3 VALUES(3, 'v3');
INSERT INTO c1 VALUES(111, 1112);
INSERT INTO c2 VALUES(112, 1113);
INSERT INTO c3 VALUES(113, 'v1113');
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(x PRIMARY KEY) WITHOUT ROWID;
CREATE TABLE t2(x);
SELECT a.x
FROM t1 AS a
LEFT JOIN t1 AS b ON (a.x=b.x)
LEFT JOIN t2 AS c ON (a.x=c.x);
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)
INSERT INTO t1(x) SELECT x FROM c;
INSERT INTO t2(x) SELECT x+9 FROM t1;
SELECT a.x, c.x
FROM t1 AS a
LEFT JOIN t1 AS b ON (a.x=b.x)
LEFT JOIN t2 AS c ON (a.x=c.x);
CREATE TABLE s1 (a INTEGER PRIMARY KEY);
CREATE TABLE s2 (a INTEGER PRIMARY KEY);
CREATE TABLE s3 (a INTEGER);
CREATE UNIQUE INDEX ndx on s3(a);
CREATE TABLE u1(a INTEGER PRIMARY KEY, b, c);
CREATE TABLE u2(a INTEGER PRIMARY KEY, b, c);
CREATE INDEX u1ab ON u1(b, c);
CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2),(3,4),(5,6);
CREATE TABLE t2(c,d); INSERT INTO t2 VALUES(2,4),(3,6);
CREATE TABLE t3(x); INSERT INTO t3 VALUES(9);
CREATE VIEW test AS
SELECT *, 'x'
FROM t1 LEFT JOIN (SELECT * FROM t2, t3) ON (c=b AND x=9)
WHERE c IS NULL;
SELECT * FROM test;
CREATE TABLE t0(c0);
CREATE TABLE t1(c0);
SELECT * FROM t0 LEFT JOIN t1
WHERE (t1.c0 BETWEEN 0 AND 0) > ('' AND t0.c0);
CREATE TABLE t0(c0 INT);
CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0;
INSERT INTO t0(c0) VALUES (0);
SELECT typeof(c0), c0 FROM v0 WHERE c0>='0'
SELECT * FROM t0, v0 WHERE v0.c0 >= '0';
SELECT * FROM t0 LEFT JOIN v0 WHERE v0.c0 >= '0';
SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0';
SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0' WHERE TRUE
UNION SELECT 0,0 WHERE 0;
CREATE TABLE t1 (aaa);
INSERT INTO t1 VALUES(23456);
CREATE TABLE t2(bbb);
CREATE VIEW v2(ccc) AS SELECT bbb IS 1234 FROM t2;
SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
CREATE TABLE t1 (x INTEGER);
INSERT INTO t1 VALUES(1); -- Some true value
CREATE TABLE t2 (z TEXT);
INSERT INTO t2 VALUES('some value');
CREATE TABLE t3(w TEXT);
INSERT INTO t3 VALUES('some other value');
SELECT (
SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)
) FROM t1;
SELECT (
SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v)
) FROM t1;
SELECT (
SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)
) FROM t1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
CREATE TABLE t1(x TEXT, y INTEGER);
INSERT INTO t1(x,y) VALUES(NULL,-2),(NULL,1),('0',2);
CREATE TABLE t2(z INTEGER);
INSERT INTO t2(z) VALUES(2),(-2);
CREATE VIEW t3 AS SELECT z, (SELECT count(*) FROM t1) AS w FROM t2;
SELECT * FROM t1 LEFT JOIN t3 ON y=z;
DROP TABLE t1;
DROP TABLE t2;
DROP VIEW t3;
CREATE TABLE t1(a INTEGER PRIMARY KEY);
WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<100)
INSERT INTO t1(a) SELECT n FROM c;
CREATE VIEW t2(b) AS SELECT a FROM t1;