## Summary **No review yet** ## Minimized query ```sql PRAGMA vdbe_debug = NO; .headers on ATTACH DATABASE ':memory:' AS aux62; -- IS .stats ATTACH DATABASE ':memory:' AS aux68; -- -ascii PRAGMA automatic_index = YES; CREATE TABLE main.T ( a TEXT, b REAL ); INSERT INTO T VALUES ('a',NULL), ('b',-round(trunc(log2(acos(NULL))), json_extract(CAST((x'357972894d4c' + -(-(randomblob('1e10')))) AS FLOAT), '$[#-1]'))), ('c',-(2147483649 - 0)); SELECT a,b, LEAD(b,1) OVER (ORDER BY b DESC) AS c, NTILE(2) OVER (ORDER BY b DESC) AS d FROM T; DELETE FROM T WHERE NOT NOT NOT (b IS NULL) ; SELECT * FROM T WHERE b IN (SELECT b FROM T LIMIT 0); PRAGMA writable_schema = 1; SELECT COUNT(*) FROM T; DETACH DATABASE aux68; VACUUM; SELECT b FROM T EXCEPT SELECT b FROM (SELECT * FROM T) AS sub; DELETE FROM T WHERE a IS NULL RETURNING *; CREATE TABLE main.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 INSERT INTO t1 DEFAULT VALUES; CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_1206 ON t1(lower(d)); CREATE TRIGGER IF NOT EXISTS trg_t1_1786 AFTER UPDATE OF b ON t1 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END; SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM t1; CREATE TABLE T ( a INTEGER, b TEXT, c REAL, d REAL ); INSERT INTO T VALUES (1,'a',40.5,-70.0), (2,'b',-10.25,20.5), (3,'c',9e999,-9e999); SELECT b FROM T WHERE ABS(c) > 5 AND ABS(d) > 5; DELETE FROM T WHERE rowid = 56; SELECT * FROM T; ANALYZE; REINDEX t1; SELECT COUNT(*) FILTER (WHERE d IS NOT NULL), SUM(rowid) FILTER (WHERE d > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(d) FILTER (WHERE d > 0 AND d < 100), COUNT(*) FILTER (WHERE typeof(d) = "text") FROM t1; DETACH DATABASE aux62; SELECT COUNT(*) FILTER (WHERE d IS NOT NULL), SUM(rowid) FILTER (WHERE d > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(d) FILTER (WHERE d > 0 AND d < 100), COUNT(*) FILTER (WHERE typeof(d) = "text") FROM t1; CREATE TEMP VIEW IF NOT EXISTS v_t1_4360 AS SELECT c FROM t1; ALTER TABLE t1 RENAME TO t1_r613; SELECT AVG(d) FROM t1; REINDEX t1; WITH cte AS (SELECT * FROM t1) UPDATE t1 SET a = (SELECT a FROM cte LIMIT 1) WHERE 1=0; SELECT * FROM T FULL OUTER JOIN t1; SELECT * FROM T; ALTER TABLE T DROP COLUMN b; CREATE TRIGGER IF NOT EXISTS trg_t1_7504 AFTER INSERT ON t1 BEGIN SELECT RAISE(ABORT, 'abort'); END; INSERT INTO t1 VALUES ('x', 1, 'x', 1) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b, c = excluded.c; CREATE TABLE T1 ( A VARCHAR(20) PRIMARY KEY, X VARCHAR(10) UNIQUE ); CREATE TABLE T2 ( A VARCHAR(20) PRIMARY KEY, Y VARCHAR(10) UNIQUE ); INSERT INTO T1 VALUES ('a', 'm'); INSERT INTO T1 VALUES ('b', 'n'); INSERT INTO T1 VALUES ('c', 'o'); INSERT INTO T2 VALUES ('b', 'k'); INSERT INTO T2 VALUES ('c', 'l'); SELECT A FROM T1 INTERSECT SELECT A FROM T2; SELECT * FROM T2 WHERE A = (SELECT SUM(A) FROM T2); ALTER TABLE T2 DROP COLUMN A; UPDATE T1 SET X = NULL WHERE 1; INSERT INTO T2 DEFAULT VALUES; ANALYZE; INSERT OR REPLACE INTO t1 VALUES ('x', 0, 0, 0); PRAGMA vdbe_debug = NO; .headers on ATTACH DATABASE ':memory:' AS aux62; -- IS .stats ATTACH DATABASE ':memory:' AS aux68; -- -ascii PRAGMA automatic_index = YES; CREATE TABLE main.T ( a TEXT, b REAL ); INSERT INTO T VALUES ('a',NULL), ('b',-round(trunc(log2(acos(NULL))), json_extract(CAST((x'357972894d4c' + -(-(randomblob(16)))) AS FLOAT), '$[#-1]'))), ('c',9e999); SELECT a,b, LEAD(b,1) OVER (ORDER BY b DESC) AS c, NTILE(2) OVER (ORDER BY b DESC) AS d FROM T; DELETE FROM T WHERE NOT NOT (b IS NULL) ; SELECT * FROM T WHERE b IN (SELECT b FROM T LIMIT 0); PRAGMA writable_schema = 1; SELECT COUNT(*) FROM T; DETACH DATABASE aux68; VACUUM; SELECT b FROM T EXCEPT SELECT b FROM (SELECT * FROM T) AS sub; DELETE FROM T WHERE a IS NULL RETURNING *; CREATE TABLE main.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 INSERT INTO t1 DEFAULT VALUES; CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_1206 ON t1(lower(d)); CREATE TRIGGER IF NOT EXISTS trg_t1_1786 AFTER UPDATE OF b ON t1 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END; SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM t1; CREATE TABLE T ( a INTEGER, b TEXT, c REAL, d REAL ); INSERT INTO T VALUES (1,'a',40.5,-70.0), (2,'b',-10.25,20.5), (3,'c',9e999,-9e999); SELECT b FROM T WHERE ABS(c) > 5 AND ABS(d) > 5; DELETE FROM T WHERE rowid = 56; SELECT * FROM T; ANALYZE; REINDEX t1; SELECT COUNT(*) FILTER (WHERE d IS NOT NULL), SUM(rowid) FILTER (WHERE d > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(d) FILTER (WHERE d > 0 AND d < 100), COUNT(*) FILTER (WHERE typeof(d) = "text") FROM t1; DETACH DATABASE aux62; SELECT COUNT(*) FILTER (WHERE d IS NOT NULL), SUM(rowid) FILTER (WHERE d > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(d) FILTER (WHERE d > 0 AND d < 100), COUNT(*) FILTER (WHERE typeof(d) = "text") FROM t1; CREATE TEMP VIEW IF NOT EXISTS v_t1_4360 AS SELECT c FROM t1; ALTER TABLE t1 RENAME TO t1_r613; SELECT AVG(d) FROM t1; REINDEX t1; WITH cte AS (SELECT * FROM t1) UPDATE t1 SET a = (SELECT a FROM cte LIMIT 1) WHERE 1=0; SELECT * FROM T FULL OUTER JOIN t1; SELECT * FROM T; ALTER TABLE T DROP COLUMN b; CREATE TRIGGER IF NOT EXISTS trg_t1_7504 AFTER INSERT ON t1 BEGIN SELECT RAISE(ABORT, 'abort'); END; INSERT INTO t1 VALUES ('x', 1, 'x', 1) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b, c = excluded.c; CREATE TABLE T1 ( A VARCHAR(20) PRIMARY KEY, X VARCHAR(10) UNIQUE ); CREATE TABLE T2 ( A VARCHAR(20) PRIMARY KEY, Y VARCHAR(10) UNIQUE ); INSERT INTO T1 VALUES ('a', 'm'); INSERT INTO T1 VALUES ('b', 'n'); INSERT INTO T1 VALUES ('c', 'o'); INSERT INTO T2 VALUES ('b', 'k'); INSERT INTO T2 VALUES ('c', 'l'); SELECT A FROM T1 INTERSECT SELECT A FROM T2; SELECT * FROM T2 WHERE A = (SELECT SUM(A) FROM T2); ALTER TABLE T2 DROP COLUMN A; UPDATE T1 SET X = NULL WHERE 1; INSERT INTO T2 DEFAULT VALUES; ANALYZE; INSERT OR REPLACE INTO t1 VALUES ('x', 0, 0, 0); PRAGMA vdbe_debug = NO; .headers on ATTACH DATABASE ':memory:' AS aux62; -- IS .stats ATTACH DATABASE ':memory:' AS aux68; -- -ascii PRAGMA automatic_index = YES; CREATE TABLE main.T ( a TEXT, b REAL ); INSERT INTO T VALUES ('a',NULL), ('b',-round(trunc(log2(acos(NULL))), json_extract(CAST((x'357972894d4c' + -(-(randomblob(16)))) AS FLOAT), '$[#-1]'))), ('c',9e999); SELECT a,b, LEAD(b,1) OVER (ORDER BY b DESC) AS c, NTILE(2) OVER (ORDER BY b DESC) AS d FROM T; DELETE FROM T WHERE NOT NOT (b IS NULL) ; SELECT * FROM T WHERE b IN (SELECT b FROM T LIMIT 0); PRAGMA writable_schema = 1; SELECT COUNT(*) FROM T; DETACH DATABASE aux68; VACUUM; SELECT b FROM T EXCEPT SELECT b FROM (SELECT * FROM T) AS sub; DELETE FROM T WHERE a IS NULL RETURNING *; CREATE TABLE main.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 INSERT INTO t1 DEFAULT VALUES; CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_1206 ON t1(lower(d)); CREATE TRIGGER IF NOT EXISTS trg_t1_1786 AFTER UPDATE OF b ON t1 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END; SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM t1; CREATE TABLE T ( a INTEGER, b TEXT, c REAL, d REAL ); INSERT INTO T VALUES (1,'a',40.5,-70.0), (2,'b',-10.25,20.5), (3,'c',9e999,-9e999); SELECT b FROM T WHERE ABS(c) > 5 AND ABS(d) > 5; DELETE FROM T WHERE rowid = 56; SELECT * FROM T; ANALYZE; REINDEX t1; SELECT COUNT(*) FILTER (WHERE d IS NOT NULL), SUM(rowid) FILTER (WHERE d > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(d) FILTER (WHERE d > 0 AND d < 100), COUNT(*) FILTER (WHERE typeof(d) = "text") FROM t1; DETACH DATABASE aux62; SELECT COUNT(*) FILTER (WHERE d IS NOT NULL), SUM(rowid) FILTER (WHERE d > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(d) FILTER (WHERE d > 0 AND d < 100), COUNT(*) FILTER (WHERE typeof(d) = "text") FROM t1; CREATE TEMP VIEW IF NOT EXISTS v_t1_4360 AS SELECT c FROM t1; ALTER TABLE t1 RENAME TO t1_r613; SELECT AVG(d) FROM t1; REINDEX t1; WITH cte AS (SELECT * FROM t1) UPDATE t1 SET a = (SELECT a FROM cte LIMIT 1) WHERE 1=0; SELECT * FROM T FULL OUTER JOIN t1; SELECT * FROM T; ALTER TABLE T DROP COLUMN b; CREATE TRIGGER IF NOT EXISTS trg_t1_7504 AFTER INSERT ON t1 BEGIN SELECT RAISE(ABORT, 'abort'); END; INSERT INTO t1 VALUES ('x', 1, 'x', 1) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b, c = excluded.c; CREATE TABLE T1 ( A VARCHAR(20) PRIMARY KEY, X VARCHAR(10) UNIQUE ); CREATE TABLE T2 ( A VARCHAR(20) PRIMARY KEY, Y VARCHAR(10) UNIQUE ); INSERT INTO T1 VALUES ('a', 'm'); INSERT INTO T1 VALUES ('b', 'n'); INSERT INTO T1 VALUES ('c', 'o'); INSERT INTO T2 VALUES ('b', 'k'); INSERT INTO T2 VALUES ('c', 'l'); SELECT A FROM T1 INTERSECT SELECT A FROM T2; SELECT * FROM T2 WHERE A = (SELECT SUM(A) FROM T2); ALTER TABLE T2 DROP COLUMN A; UPDATE T1 SET X = NULL WHERE 1; INSERT INTO T2 DEFAULT VALUES; ANALYZE; INSERT OR REPLACE INTO t1 VALUES ('x', 0, 0, 0); WITH cte AS (SELECT X, RANK() OVER (ORDER BY X) AS rnk FROM T1) SELECT * FROM cte; SELECT * FROM T AS a FULL OUTER JOIN T1 AS b ON a.rowid = b.rowid; SELECT COUNT(*) FROM T2; SELECT * FROM t1; ALTER TABLE T2 RENAME TO T2_r8673; ``` ## Actual output ```sql Memory Used: 79920 (max 79920) bytes Number of Outstanding Allocations: 158 (max 158) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 34 (max 80) Successful lookaside attempts: 143 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 18496 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1104 bytes Statement Heap/Lookaside Usage: 0 bytes Bytes received by read(): 10409 Bytes sent to write(): 0 Read() system calls: 13 Write() system calls: 0 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 a|b|c|d c|-2147483649.0||1 a|||1 b|||2 COUNT(*) 2 CREATE TABLE "t2"("b","c","_ROWID_",PRIMARY KEY("b","c","_ROWID_"))WITHOUT ROWID; WARNING: writing to an imposter table will corrupt the "t1bc" index! b|c|_ROWID_ Braun|30|3 Wernher|10|1 von|20|2 b|c Braun|30 Wernher|10 von|20 ``` ## Expectation ```sql Memory Used: 80568 (max 80568) bytes Number of Outstanding Allocations: 168 (max 168) Number of Pcache Overflow Bytes: 16944 (max 16944) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 50 (max 91) Successful lookaside attempts: 149 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 18512 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 944 bytes Statement Heap/Lookaside Usage: 0 bytes Bytes received by read(): 11185 Bytes sent to write(): 0 Read() system calls: 14 Write() system calls: 0 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 a|b|c|d c|-2147483649.0||1 a|||1 b|||2 COUNT(*) 2 CREATE TABLE "t2"("b","c","_ROWID_",PRIMARY KEY("b","c","_ROWID_"))WITHOUT ROWID; b|c|_ROWID_ Braun|30|3 Wernher|10|1 von|20|2 b|c Braun|30 Wernher|10 von|20 ``` ## Flag ``` ```