1090 lines
42 KiB
Markdown
1090 lines
42 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
|
|
BEGIN DEFERRED;
|
|
-- SUM
|
|
|
|
.import corrupt001.db main
|
|
PRAGMA empty_result_callbacks = TRUE;
|
|
.width
|
|
CREATE TABLE t1(a BLOB PRIMARY KEY, b TEXT, c INT, d INT);
|
|
-- substr({v}, 100)
|
|
|
|
INSERT INTO t1 VALUES (unixepoch((abs('123') + CAST(0.0 AS ANY))) ->> '$.key', 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE NOT +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t3 VALUES ('x', 'x') ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
UPDATE t3 SET c4 = -47;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_7080 BEFORE INSERT ON t2 BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2305 ON t2((c2 + 1)) WHERE c2 IS NOT NULL;
|
|
ALTER TABLE t1 DROP COLUMN c1;
|
|
INSERT INTO t3 VALUES (NULL, 1) ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
ALTER TABLE t2 RENAME TO t2_r5897;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_1559 BEFORE UPDATE OF c3 ON t3 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT * FROM t1;
|
|
WITH cte AS (SELECT c2, ROW_NUMBER() OVER (ORDER BY c2) AS rn FROM t2) SELECT * FROM cte WHERE rn <= 5;
|
|
.width
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (unixepoch(1) ->> '$.key', 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE NOT +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t3 VALUES ('x', 'x') ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
UPDATE t3 SET c4 = -47;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_7080 BEFORE INSERT ON t2 BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2305 ON t2((c2 + 1)) WHERE c2 IS NOT NULL;
|
|
ALTER TABLE t1 DROP COLUMN c1;
|
|
INSERT INTO t3 VALUES (NULL, 1) ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
ALTER TABLE t2 RENAME TO t2_r5897;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_1559 BEFORE UPDATE OF c3 ON t3 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT * FROM t1;
|
|
WITH cte AS (SELECT c2, ROW_NUMBER() OVER (ORDER BY c2) AS rn FROM t2) SELECT * FROM cte WHERE rn <= 5;
|
|
.width
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (unixepoch(1) ->> '$.key', 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE NOT +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t3 VALUES ('x', 'x') ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
UPDATE t3 SET c4 = -47;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_7080 BEFORE INSERT ON t2 BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2305 ON t2((c2 + 1)) WHERE c2 IS NOT NULL;
|
|
ALTER TABLE t1 DROP COLUMN c1;
|
|
INSERT INTO t3 VALUES (NULL, 1) ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
ALTER TABLE t2 RENAME TO t2_r5897;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_1559 BEFORE UPDATE OF c3 ON t3 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT * FROM t1;
|
|
WITH cte AS (SELECT c2, ROW_NUMBER() OVER (ORDER BY c2) AS rn FROM t2) SELECT * FROM cte WHERE rn <= 5;
|
|
.width
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (unixepoch(1) ->> '$.key', 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE NOT +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t3 VALUES ('x', 'x') ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
UPDATE t3 SET c4 = -47;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_7080 BEFORE INSERT ON t2 BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2305 ON t2((c2 + 1)) WHERE c2 IS NOT NULL;
|
|
ALTER TABLE t1 DROP COLUMN c1;
|
|
INSERT INTO t3 VALUES (NULL, 1) ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
ALTER TABLE t2 RENAME TO t2_r5897;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_1559 BEFORE UPDATE OF c3 ON t3 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT * FROM t1;
|
|
WITH cte AS (SELECT c2, ROW_NUMBER() OVER (ORDER BY c2) AS rn FROM t2) SELECT * FROM cte WHERE rn <= 5;
|
|
.width
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (unixepoch(1) ->> '$.key', 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE NOT +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t3 VALUES ('x', 'x') ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
UPDATE t3 SET c4 = -47;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_7080 BEFORE INSERT ON t2 BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2305 ON t2((c2 + 1)) WHERE c2 IS NOT NULL;
|
|
ALTER TABLE t1 DROP COLUMN c1;
|
|
INSERT INTO t3 VALUES (NULL, 1) ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
ALTER TABLE t2 RENAME TO t2_r5897;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_1559 BEFORE UPDATE OF c3 ON t3 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT * FROM t1;
|
|
WITH cte AS (SELECT c2, ROW_NUMBER() OVER (ORDER BY c2) AS rn FROM t2) SELECT * FROM cte WHERE rn <= 5;
|
|
.width
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (unixepoch(1) ->> '$.key', 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE NOT +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t3 VALUES ('x', 'x') ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
UPDATE t3 SET c4 = -47;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_7080 BEFORE INSERT ON t2 BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2305 ON t2((c2 + 1)) WHERE c2 IS NOT NULL;
|
|
ALTER TABLE t1 DROP COLUMN c1;
|
|
INSERT INTO t3 VALUES (NULL, 1) ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
ALTER TABLE t2 RENAME TO t2_r5897;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_1559 BEFORE UPDATE OF c3 ON t3 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT * FROM t1;
|
|
WITH cte AS (SELECT c2, ROW_NUMBER() OVER (ORDER BY c2) AS rn FROM t2) SELECT * FROM cte WHERE rn <= 5;
|
|
.width
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (unixepoch(1) ->> '$.key', 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE NOT +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
((PRAGMA)) case_sensitive_like = FALSE;
|
|
PRAGMA synchronous = 'NORMAL';
|
|
PRAGMA parser_trace = YES;
|
|
.progress log(-(-(-(-(NULL)))), 3)
|
|
CREATE TABLE t1(c1);
|
|
INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
|
|
CREATE TABLE t2(c2);
|
|
INSERT INTO t2 VALUES(44),(55),(123);
|
|
CREATE TABLE t3(c3,c4);
|
|
INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
|
|
CREATE VIEW t4 AS SELECT c3 FROM t3;
|
|
CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
|
|
SELECT * FROM t1, t2 WHERE +c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
|
|
SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
|
|
|
|
|
|
SELECT COUNT(*) FROM t3;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_4914 ON t1(lower(c1)) WHERE c1 IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_t3_9390 AS SELECT c4 FROM t3;
|
|
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t3 VALUES ('x', 'x') ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
UPDATE t3 SET c4 = -47;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_7080 BEFORE INSERT ON t2 BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2305 ON t2((c2 + 1)) WHERE c2 IS NOT NULL;
|
|
ALTER TABLE t1 DROP COLUMN c1;
|
|
INSERT INTO t3 VALUES (NULL, 1) ON CONFLICT(c3) DO UPDATE SET c3 = excluded.c3, c4 = excluded.c4;
|
|
ALTER TABLE t2 RENAME TO t2_r5897;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t3_1559 BEFORE UPDATE OF c3 ON t3 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT * FROM t1;
|
|
WITH cte AS (SELECT c2, ROW_NUMBER() OVER (ORDER BY c2) AS rn FROM t2) SELECT * FROM cte WHERE rn <= 5;
|
|
|
|
SELECT MAX(c3) FROM t3;
|
|
REINDEX;
|
|
ALTER TABLE t3 RENAME COLUMN c4 TO c4_r411;
|
|
SELECT * FROM t2 AS a FULL OUTER JOIN t1 AS b ON a.rowid = b.rowid;
|
|
|
|
SELECT COUNT(*) FROM t1;
|
|
SELECT * FROM t3 WHERE c4 IN (SELECT c4 FROM t3 WHERE c4 IN (SELECT c4 FROM t3));
|
|
CREATE TABLE T (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15),
|
|
C BIGINT,
|
|
UNIQUE (B, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', -9223372036854775808);
|
|
INSERT INTO T VALUES ('b', 'q', 4000);
|
|
INSERT INTO T VALUES ('c', 'r', 1700);
|
|
INSERT INTO T VALUES ('d', 'r', 9223372036854775807);
|
|
INSERT INTO T VALUES ('e', 's', 0);
|
|
SELECT A, C FROM T WHERE C > 1200 AND C < 9999999999 ORDER BY C;
|
|
PRAGMA synchronous = '2';
|
|
UPDATE t1 SET c1 = c1 + 1 WHERE c1 IS NOT NULL RETURNING *;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<49) SELECT MAX(x) FROM cnt;
|
|
SELECT FIRST_VALUE(A) OVER (ORDER BY A) FROM T;
|
|
SELECT DENSE_RANK() OVER (PARTITION BY C ORDER BY C ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP) FROM T;
|
|
COMMIT TRANSACTION;
|
|
INSERT INTO t2 DEFAULT VALUES;
|
|
ALTER TABLE t1 RENAME TO t1_r5777;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
CREATE TABLE "t2"("b","c","_ROWID_",PRIMARY KEY("b","c","_ROWID_"))WITHOUT ROWID;
|
|
WARNING: writing to an imposter table will corrupt the "t1bc" index!
|
|
Braun|30|3
|
|
Wernher|10|1
|
|
von|20|2
|
|
Braun|30
|
|
Wernher|10
|
|
von|20
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
CREATE TABLE "t2"("b","c","_ROWID_",PRIMARY KEY("b","c","_ROWID_"))WITHOUT ROWID;
|
|
Braun|30|3
|
|
Wernher|10|1
|
|
von|20|2
|
|
Braun|30
|
|
Wernher|10
|
|
von|20
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
|
|
```
|
|
|