2518 lines
98 KiB
Markdown
2518 lines
98 KiB
Markdown
## Summary
|
|
<!--Explain briefly what goes wrong and explain why you believe this is a bug and not the intended behavior of SQLite (if it is not a crash).-->**No review yet**
|
|
|
|
## Minimized query
|
|
|
|
```sql
|
|
.timeout 999999
|
|
SAVEPOINT sp5236;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
SAVEPOINT sp9146;
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(degrees(CAST(x'18361805800027' AS FLOAT))))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO /**/ t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4
|
|
SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
.exit
|
|
.version
|
|
SAVEPOINT sp8126;
|
|
.limit COLUMN char(-sign(7009305126866414024))
|
|
PRAGMA cache_spill = OFF;
|
|
PRAGMA synchronous = '0';
|
|
ATTACH DATABASE ':memory:' AS aux55;
|
|
BEGIN EXCLUSIVE;
|
|
ATTACH DATABASE ':memory:' AS aux43;
|
|
CREATE TABLE t1 (a); INSERT INTO t1 (a) VALUES (8444384041937530243);
|
|
CREATE TABLE t2 (b); INSERT INTO t2 (b) VALUES (104);
|
|
CREATE TABLE t3 (c); INSERT INTO t3 (c) VALUES (104);
|
|
CREATE TABLE t4 (d); INSERT INTO t4 (d) VALUES (104);
|
|
SELECT * FROM t1 CROSS JOIN t2 ON (t1.a = t2.b) WHERE t2.b IN ( SELECT t3.c FROM t3 WHERE t3.c IN ( SELECT d FROM (SELECT DISTINCT d FROM t4) AS x WHERE x.d=104));
|
|
CREATE TABLE t5(a, b, c, d);
|
|
CREATE INDEX t5a ON t5(a);
|
|
CREATE INDEX t5b ON t5(b);
|
|
CREATE TABLE main.t6(e);
|
|
INSERT INTO t6 VALUES(1);
|
|
INSERT INTO t5 VALUES(1,1,1,1), (2,2,2,2);
|
|
SELECT * FROM t5 WHERE (a=1 OR b=2) AND c IN ( SELECT e FROM (SELECT DISTINCT e FROM t6) WHERE e=1);
|
|
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,'a',1.25), (2,'b',-3.5), (3,'c',9e999);
|
|
WITH d AS (SELECT * FROM T) SELECT * FROM d WHERE c > 0;
|
|
DETACH DATABASE aux43;
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
DETACH DATABASE aux55;
|
|
WITH RECURSIVE empty(x) AS (SELECT 1 WHERE 1=0 UNION ALL SELECT x+1 FROM empty WHERE x<10) SELECT * FROM empty;
|
|
VACUUM main;
|
|
SELECT LAG(e, 1, NULL) OVER (PARTITION BY e ORDER BY e GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t6;
|
|
SELECT * FROM T AS a FULL OUTER JOIN T AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t5 AS a RIGHT JOIN t4 AS b ON a.rowid = b.rowid;
|
|
RELEASE sp8126;
|
|
INSERT INTO t4 SELECT * FROM t4;
|
|
ALTER TABLE t4 DROP COLUMN d;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t2_5478 ON t2((b + 1));
|
|
SELECT * FROM T WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.c = T.c);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2063 BEFORE UPDATE OF b ON t2 BEGIN INSERT INTO t2(b) VALUES (NULL); END;
|
|
ALTER TABLE t6 ADD COLUMN extra_8228 TEXT DEFAULT '';
|
|
DROP INDEX IF EXISTS T;
|
|
REINDEX T;
|
|
SELECT AVG(d) FROM t4;
|
|
ALTER TABLE t2 RENAME TO t2_r7592;
|
|
ANALYZE;
|
|
SELECT * FROM t4 WHERE d IN (SELECT ALL d FROM t4);
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_8691 BEFORE UPDATE ON t3 FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t6_842 AS SELECT e FROM t6;
|
|
ALTER TABLE t4 RENAME TO t4_r8549;
|
|
DELETE FROM t1 WHERE 0;
|
|
SELECT COUNT(*) FILTER (WHERE e IS NOT NULL), SUM(rowid) FILTER (WHERE e > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(e) FILTER (WHERE e > 0 AND e < 100), COUNT(*) FILTER (WHERE typeof(e) = "text") FROM t6;
|
|
INSERT OR ABORT INTO t2 VALUES (0);
|
|
ROLLBACK TO sp9146;
|
|
RELEASE sp9146;
|
|
UPDATE t6 SET e = '' WHERE e IS NOT NULL RETURNING *;
|
|
DELETE FROM t6 WHERE e > (SELECT AVG(e) FROM t6);
|
|
SELECT * FROM t2;
|
|
|
|
INSERT INTO T VALUES (1, NULL, NULL) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b, c = excluded.c;
|
|
RELEASE sp5236;
|
|
ALTER TABLE t3 RENAME TO t3_r330;
|
|
SELECT LAG(b, b) OVER (PARTITION BY b ORDER BY b RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM T;
|
|
ALTER TABLE t4 RENAME COLUMN d TO d_r8667;
|
|
INSERT INTO t6 VALUES (NULL);
|
|
UPDATE t4 SET d = NULL WHERE d BETWEEN 0 AND 100;
|
|
PRAGMA hard_heap_limit = 50000000;
|
|
SELECT * FROM T WHERE EXISTS (SELECT 1 FROM T WHERE b = NULL);
|
|
WITH m AS MATERIALIZED (SELECT * FROM t1) SELECT m.a FROM m JOIN m AS m2 ON m.a = m2.a;
|
|
SELECT * FROM t1 WHERE a < (SELECT AVG(a) FROM t1);
|
|
SELECT STRING_AGG(c) FROM t3;
|
|
SELECT * FROM T AS a RIGHT OUTER JOIN t4 AS b ON a.rowid = b.rowid;
|
|
WITH cte AS (SELECT 1 AS x) INSERT INTO t1(a) SELECT x FROM cte WHERE 1=0;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
SQLite version 3.39.0 2022-05-10 23:28:12
|
|
Enter ".help" for usage hints.
|
|
sqlite> sqlite> sqlite> sqlite>
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
SQLite version 3.51.1 2025-11-28 17:28:25
|
|
Enter ".help" for usage hints.
|
|
sqlite> sqlite> sqlite> sqlite>
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
-interactive
|
|
```
|
|
|