## Summary **No review yet** ## Minimized query ```sql .echo on .stats stmt PRAGMA optimize; PRAGMA journal_mode = 'MEMORY'; BEGIN EXCLUSIVE; SAVEPOINT sp8818; SAVEPOINT sp9957; .exit -(pi() + acos(1.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 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; ``` ## Actual output ```sql .stats stmt PRAGMA optimize; Number of output columns: 1 Column 0 name: optimize Column 0 declared type: (null) Memory Used: 67720 (max 67720) bytes Number of Outstanding Allocations: 141 (max 141) Number of Pcache Overflow Bytes: 8464 (max 8464) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 37 (max 75) Successful lookaside attempts: 86 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9248 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 736 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(): 10409 Bytes sent to write(): 12 Read() system calls: 13 Write() system calls: 1 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: 67720 (max 67760) bytes Number of Outstanding Allocations: 141 (max 142) Number of Pcache Overflow Bytes: 8464 (max 8464) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 36 (max 75) Successful lookaside attempts: 92 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9248 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 736 bytes Statement Heap/Lookaside Usage: 2656 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: 2656 Bytes received by read(): 10505 Bytes sent to write(): 1447 Read() system calls: 15 Write() system calls: 2 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 BEGIN EXCLUSIVE; Number of output columns: 0 Memory Used: 69272 (max 69272) bytes Number of Outstanding Allocations: 143 (max 143) Number of Pcache Overflow Bytes: 8464 (max 8464) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 35 (max 75) Successful lookaside attempts: 95 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9248 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 736 bytes Statement Heap/Lookaside Usage: 2528 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(): 10859 Bytes sent to write(): 2910 Read() system calls: 18 Write() system calls: 3 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 SAVEPOINT sp8818; Number of output columns: 0 Memory Used: 69272 (max 69272) bytes Number of Outstanding Allocations: 143 (max 143) Number of Pcache Overflow Bytes: 8464 (max 8464) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 37 (max 75) Successful lookaside attempts: 100 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9248 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 736 bytes Statement Heap/Lookaside Usage: 2656 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(): 10957 Bytes sent to write(): 4257 Read() system calls: 20 Write() system calls: 4 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 SAVEPOINT sp9957; Number of output columns: 0 Memory Used: 69272 (max 69272) bytes Number of Outstanding Allocations: 143 (max 143) Number of Pcache Overflow Bytes: 8464 (max 8464) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 38 (max 75) Successful lookaside attempts: 105 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9248 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 736 bytes Statement Heap/Lookaside Usage: 2656 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(): 11055 Bytes sent to write(): 5606 Read() system calls: 22 Write() system calls: 5 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 .exit -(pi() + acos(1.7976931348623157e+308)) ``` ## Expectation ```sql .stats stmt PRAGMA optimize; Number of output columns: 1 Column 0 name: optimize Column 0 declared type: (null) Memory Used: 68416 (max 68416) bytes Number of Outstanding Allocations: 151 (max 151) Number of Pcache Overflow Bytes: 8472 (max 8472) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 53 (max 86) Successful lookaside attempts: 97 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9256 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 632 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(): 11185 Bytes sent to write(): 29 Read() system calls: 14 Write() system calls: 2 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: 68416 (max 68448) bytes Number of Outstanding Allocations: 151 (max 152) Number of Pcache Overflow Bytes: 8472 (max 8472) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 52 (max 86) Successful lookaside attempts: 103 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9256 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: 632 bytes Statement Heap/Lookaside Usage: 2656 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(): 11281 Bytes sent to write(): 1518 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: 69952 (max 69952) bytes Number of Outstanding Allocations: 153 (max 153) Number of Pcache Overflow Bytes: 8472 (max 8472) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 51 (max 86) Successful lookaside attempts: 106 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9256 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 632 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(): 11379 Bytes sent to write(): 3006 Read() system calls: 18 Write() system calls: 6 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 SAVEPOINT sp8818; Number of output columns: 0 Memory Used: 69952 (max 69952) bytes Number of Outstanding Allocations: 153 (max 153) Number of Pcache Overflow Bytes: 8472 (max 8472) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 53 (max 86) Successful lookaside attempts: 111 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9256 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: 632 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(): 11477 Bytes sent to write(): 4394 Read() system calls: 20 Write() system calls: 8 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 SAVEPOINT sp9957; Number of output columns: 0 Memory Used: 69952 (max 69952) bytes Number of Outstanding Allocations: 153 (max 153) Number of Pcache Overflow Bytes: 8472 (max 8472) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 54 (max 86) Successful lookaside attempts: 116 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 9256 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: 632 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(): 11575 Bytes sent to write(): 5782 Read() system calls: 22 Write() system calls: 10 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 .exit -(pi() + acos(1.7976931348623157e+308)) ``` ## Flag ``` ```