## Summary **No review yet** ## Minimized query ```sql CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT); INSERT INTO t1 VALUES (1, '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; ``` ## 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 ``` ```