## Summary **No review yet** ## Minimized query ```sql .recover CREATE TABLE T ( a TEXT, b TEXT, c REAL ); INSERT INTO T VALUES ('a','b',1.0), ('a','c',-1.0), ('b','d',0.0); SELECT COALESCE(a,'e') AS d, COALESCE(b,'f') AS e, SUM(c) AS f FROM T GROUP BY a,b; .echo on .stats stmt PRAGMA optimize; PRAGMA journal_mode = 'MEMORY'; BEGIN EXCLUSIVE; SAVEPOINT sp8818; SAVEPOINT sp9957; .exit -(pi() + acos(('true' + 0).7976931348623157e+308)) CREATE VIRTUAL TABLE t0 USING fts4("x", "y", "z"); CREATE TABLE T ( A VARCHAR(10) PRIMARY KEY, B VARCHAR(15) NOT NULL, C INTEGER, UNIQUE (B, C) ); INSERT INTO T VALUES ('a', 'p', -2147483648); INSERT INTO T VALUES ('b', 'q', 700); INSERT INTO T VALUES ('c', 'r', 0); INSERT INTO T VALUES ('d', 's', 2147483647); INSERT INTO T VALUES ('e', 't', -1); INSERT INTO T VALUES ('f', 'u', 1); SELECT * FROM T WHERE NOT NOT (NOT C BETWEEN -1000 AND 1000) ORDER BY C ASC; DROP TABLE t0; PRAGMA temp_store = '2'; PRAGMA fullsync = FALSE; PRAGMA read_uncommitted = round(9223372036854775807); .cd bugs CREATE TABLE customer (id UNSIGNED BIG INT PRIMARY KEY); CREATE TABLE apr (id INT PRIMARY KEY, apr REAL); CREATE VIEW v1 AS SELECT c.id, i.apr FROM customer c LEFT JOIN apr i ON i.id=c.id; CREATE VIEW v1rj AS SELECT c.id, i.apr FROM apr i RIGHT JOIN customer c ON i.id=c.id; CREATE VIEW v2 AS SELECT c.id, v1.apr FROM customer c LEFT JOIN v1 ON v1.id=c.id; CREATE VIEW v2rj AS SELECT c.id, v1.apr FROM v1 RIGHT JOIN customer c ON v1.id=c.id; CREATE VIEW v2rjrj AS SELECT c.id, v1rj.apr FROM v1rj RIGHT JOIN customer c ON v1rj.id=c.id; INSERT INTO customer (id) VALUES (1); INSERT INTO apr (id, apr) VALUES (1, 12); INSERT INTO customer (id) VALUES (2); INSERT INTO apr (id, apr) VALUES (2, 12.01); PRAGMA automatic_index=ON; SELECT id, (apr / 100), typeof(apr) apr_type FROM v1; SELECT * FROM customer WHERE id IN (SELECT id FROM customer LIMIT 0); SELECT * FROM (SELECT * FROM customer) AS sub; SELECT * FROM apr WHERE EXISTS (SELECT 1 FROM apr WHERE EXISTS (SELECT 1 FROM apr)); CREATE TRIGGER IF NOT EXISTS trg_customer_998 AFTER DELETE ON customer BEGIN INSERT INTO customer(id) VALUES (NULL); END; SELECT * FROM customer WHERE id <> ANY (SELECT id FROM customer); SELECT * FROM apr WHERE EXISTS (SELECT 1 FROM apr t2 WHERE t2.id = apr.id); INSERT OR REPLACE INTO customer VALUES (NULL); SELECT COUNT(*) FILTER (WHERE apr IS NOT NULL), SUM(rowid) FILTER (WHERE apr > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(apr) FILTER (WHERE apr > 0 AND apr < 100), COUNT(*) FILTER (WHERE typeof(apr) = "text") FROM apr; UPDATE customer SET id = 52 WHERE rowid = 1; INSERT INTO apr DEFAULT VALUES; UPDATE apr SET id = CURRENT_TIMESTAMP; ROLLBACK TRANSACTION TO SAVEPOINT sp9957; RELEASE SAVEPOINT sp9957; WITH cte AS (SELECT * FROM customer) SELECT * FROM cte; INSERT INTO customer VALUES ('x') ON CONFLICT(id) DO UPDATE SET id = excluded.id; SELECT * FROM customer; SELECT STRING_AGG(id) FROM customer; ROLLBACK TRANSACTION TO SAVEPOINT sp8818; RELEASE SAVEPOINT sp8818; SELECT COUNT(*) FILTER (WHERE apr IS NOT NULL), SUM(rowid) FILTER (WHERE apr > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(apr) FILTER (WHERE apr > 0 AND apr < 100), COUNT(*) FILTER (WHERE typeof(apr) = "text") FROM apr; ANALYZE customer; DELETE FROM customer WHERE rowid = 55 RETURNING *; COMMIT; INSERT INTO apr VALUES (NULL, NULL); REINDEX; INSERT INTO apr VALUES (NULL, NULL); ANALYZE; CREATE TABLE T ( A VARCHAR(10) PRIMARY KEY, B VARCHAR(15) NOT NULL, C INTEGER, UNIQUE (B, C) ); INSERT INTO T VALUES ('a', 'p', -2147483648); INSERT INTO T VALUES ('b', 'q', 700); INSERT INTO T VALUES ('c', 'r', 0); INSERT INTO T VALUES ('d', 's', 2147483647); INSERT INTO T VALUES ('e', 't', -1); INSERT INTO T VALUES ('f', 'u', 1); SELECT * FROM T WHERE C BETWEEN -1000 AND 1000 ORDER BY C ASC; INSERT INTO apr VALUES (1, 'x') ON CONFLICT(id) DO UPDATE SET id = excluded.id, apr = excluded.apr; INSERT INTO T DEFAULT VALUES; WITH a AS (SELECT id FROM customer), b AS (SELECT COUNT(*) AS cnt FROM a) SELECT * FROM b; CREATE UNIQUE INDEX IF NOT EXISTS idx_T_651 ON T(A COLLATE NOCASE) WHERE A > 0; ANALYZE; CREATE TEMPORARY VIEW IF NOT EXISTS v_customer_2839 AS SELECT id FROM customer; SELECT * FROM T CROSS JOIN customer; SELECT * FROM apr; INSERT OR FAIL INTO customer VALUES (NULL); SELECT * FROM customer AS a FULL OUTER JOIN customer AS b ON a.rowid = b.rowid; SELECT COUNT(*) FILTER (WHERE id IS NOT NULL), SUM(rowid) FILTER (WHERE id > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(id) FILTER (WHERE id > 0 AND id < 100), COUNT(*) FILTER (WHERE typeof(id) = "text") FROM apr; SELECT * FROM customer; ALTER TABLE apr ADD COLUMN extra_3754 ANY NOT NULL DEFAULT 0; SELECT * FROM apr AS a JOIN customer AS b ON a.rowid = b.rowid; ALTER TABLE T RENAME TO T_r1287; CREATE INDEX IF NOT EXISTS idx_T_8324 ON T(B COLLATE NOCASE) WHERE B > 0; ANALYZE customer; ``` ## Actual output ```sql PRAGMA foreign_keys=OFF; BEGIN; PRAGMA writable_schema = on; PRAGMA writable_schema = off; COMMIT; a|b|1.0 a|c|-1.0 b|d|0.0 .stats stmt PRAGMA optimize; Number of output columns: 1 Column 0 name: optimize Column 0 declared type: (null) Memory Used: 173336 (max 747592) bytes Number of Outstanding Allocations: 213 (max 526) Number of Pcache Overflow Bytes: 21032 (max 53608) bytes Largest Allocation: 87200 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 64 (max 123) Successful lookaside attempts: 1695 Lookaside failures due to size: 20 Lookaside failures due to OOM: 900 Pager Heap Usage: 22808 bytes Page cache hits: 27 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1072 bytes Statement Heap/Lookaside Usage: 9888 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 6 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2784 Bytes received by read(): 10665 Bytes sent to write(): 136 Read() system calls: 14 Write() system calls: 3 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 PRAGMA journal_mode = 'MEMORY'; memory Number of output columns: 1 Column 0 name: journal_mode Column 0 declared type: (null) Memory Used: 173336 (max 747592) bytes Number of Outstanding Allocations: 213 (max 526) Number of Pcache Overflow Bytes: 21032 (max 53608) bytes Largest Allocation: 87200 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 63 (max 123) Successful lookaside attempts: 1701 Lookaside failures due to size: 20 Lookaside failures due to OOM: 900 Pager Heap Usage: 22808 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1072 bytes Statement Heap/Lookaside Usage: 9760 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 6 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2656 Bytes received by read(): 10762 Bytes sent to write(): 1585 Read() system calls: 16 Write() system calls: 4 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 BEGIN EXCLUSIVE; Number of output columns: 0 Memory Used: 173336 (max 747592) bytes Number of Outstanding Allocations: 213 (max 526) Number of Pcache Overflow Bytes: 21032 (max 53608) bytes Largest Allocation: 87200 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 62 (max 123) Successful lookaside attempts: 1704 Lookaside failures due to size: 20 Lookaside failures due to OOM: 900 Pager Heap Usage: 22808 bytes Page cache hits: 2 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1072 bytes Statement Heap/Lookaside Usage: 9632 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 4 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2528 Bytes received by read(): 10860 Bytes sent to write(): 3060 Read() system calls: 18 Write() system calls: 5 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 SAVEPOINT sp8818; Number of output columns: 0 Memory Used: 173336 (max 747592) bytes Number of Outstanding Allocations: 213 (max 526) Number of Pcache Overflow Bytes: 21032 (max 53608) bytes Largest Allocation: 87200 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 64 (max 123) Successful lookaside attempts: 1709 Lookaside failures due to size: 20 Lookaside failures due to OOM: 900 Pager Heap Usage: 22808 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1072 bytes Statement Heap/Lookaside Usage: 9760 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 3 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2656 Bytes received by read(): 10958 Bytes sent to write(): 4419 Read() system calls: 20 Write() system calls: 6 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 SAVEPOINT sp9957; Number of output columns: 0 Memory Used: 173336 (max 747592) bytes Number of Outstanding Allocations: 213 (max 526) Number of Pcache Overflow Bytes: 21032 (max 53608) bytes Largest Allocation: 87200 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 65 (max 123) Successful lookaside attempts: 1714 Lookaside failures due to size: 20 Lookaside failures due to OOM: 900 Pager Heap Usage: 22808 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1072 bytes Statement Heap/Lookaside Usage: 9760 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 3 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2656 Bytes received by read(): 11056 Bytes sent to write(): 5779 Read() system calls: 22 Write() system calls: 7 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 .exit -(pi() + acos(('true' + 0).7976931348623157e+308)) ``` ## Expectation ```sql .dbconfig defensive off BEGIN; PRAGMA writable_schema = on; PRAGMA foreign_keys = off; PRAGMA encoding = 'UTF-8'; PRAGMA page_size = '4096'; PRAGMA auto_vacuum = '0'; PRAGMA user_version = '0'; PRAGMA application_id = '0'; PRAGMA writable_schema = off; COMMIT; a|b|1.0 a|c|-1.0 b|d|0.0 .stats stmt PRAGMA optimize; Number of output columns: 1 Column 0 name: optimize Column 0 declared type: (null) Memory Used: 171992 (max 993288) bytes Number of Outstanding Allocations: 175 (max 597) Number of Pcache Overflow Bytes: 21040 (max 61784) bytes Largest Allocation: 87360 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 58 (max 108) Successful lookaside attempts: 409 Lookaside failures due to size: 2 Lookaside failures due to OOM: 0 Pager Heap Usage: 22840 bytes Page cache hits: 17 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 888 bytes Statement Heap/Lookaside Usage: 2784 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 6 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2784 Bytes received by read(): 11229 Bytes sent to write(): 315 Read() system calls: 15 Write() system calls: 4 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 PRAGMA journal_mode = 'MEMORY'; memory Number of output columns: 1 Column 0 name: journal_mode Column 0 declared type: (null) Memory Used: 171992 (max 993288) bytes Number of Outstanding Allocations: 175 (max 597) Number of Pcache Overflow Bytes: 21040 (max 61784) bytes Largest Allocation: 87360 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 57 (max 108) Successful lookaside attempts: 415 Lookaside failures due to size: 2 Lookaside failures due to OOM: 0 Pager Heap Usage: 22840 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: 888 bytes Statement Heap/Lookaside Usage: 2656 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 7 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2656 Bytes received by read(): 11326 Bytes sent to write(): 1813 Read() system calls: 17 Write() system calls: 6 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 BEGIN EXCLUSIVE; Number of output columns: 0 Memory Used: 173528 (max 993288) bytes Number of Outstanding Allocations: 177 (max 597) Number of Pcache Overflow Bytes: 21040 (max 61784) bytes Largest Allocation: 87360 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 56 (max 108) Successful lookaside attempts: 418 Lookaside failures due to size: 2 Lookaside failures due to OOM: 0 Pager Heap Usage: 22840 bytes Page cache hits: 2 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 888 bytes Statement Heap/Lookaside Usage: 2528 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 5 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2528 Bytes received by read(): 11424 Bytes sent to write(): 3307 Read() system calls: 19 Write() system calls: 8 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 SAVEPOINT sp8818; Number of output columns: 0 Memory Used: 173528 (max 993288) bytes Number of Outstanding Allocations: 177 (max 597) Number of Pcache Overflow Bytes: 21040 (max 61784) bytes Largest Allocation: 87360 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 58 (max 108) Successful lookaside attempts: 423 Lookaside failures due to size: 2 Lookaside failures due to OOM: 0 Pager Heap Usage: 22840 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: 888 bytes Statement Heap/Lookaside Usage: 2656 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 4 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2656 Bytes received by read(): 11522 Bytes sent to write(): 4701 Read() system calls: 21 Write() system calls: 10 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 SAVEPOINT sp9957; Number of output columns: 0 Memory Used: 173528 (max 993288) bytes Number of Outstanding Allocations: 177 (max 597) Number of Pcache Overflow Bytes: 21040 (max 61784) bytes Largest Allocation: 87360 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 59 (max 108) Successful lookaside attempts: 428 Lookaside failures due to size: 2 Lookaside failures due to OOM: 0 Pager Heap Usage: 22840 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: 888 bytes Statement Heap/Lookaside Usage: 2656 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 4 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2656 Bytes received by read(): 11621 Bytes sent to write(): 6096 Read() system calls: 23 Write() system calls: 12 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 .exit -(pi() + acos(('true' + 0).7976931348623157e+308)) ``` ## Flag ``` ```