456 lines
18 KiB
Markdown
456 lines
18 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
|
|
.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
|
|
|
|
```
|
|
|
|
```
|
|
|