195 lines
6.3 KiB
Markdown
195 lines
6.3 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
|
|
ATTACH DATABASE ':memory:' AS aux16;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES ('a','b',CAST(1.5 AS BIGINT)), ('a','c',-2.25), ('b','b',4.75);
|
|
SELECT a,
|
|
SUM(CASE WHEN b='b' THEN c ELSE 0 END) AS d,
|
|
SUM(CASE WHEN b='c' THEN c ELSE 0 END) AS e
|
|
FROM T
|
|
GROUP BY a;
|
|
ATTACH DATABASE '' || (':memory:') AS aux79;
|
|
.stats on
|
|
BEGIN EXCLUSIVE;
|
|
.exit
|
|
ATTACH DATABASE ':memory:' AS aux56;
|
|
SAVEPOINT sp2544;
|
|
ATTACH DATABASE ':memory:' AS aux29;
|
|
.clone new_db
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES ('a','b',CAST(length(CAST(date(unicode(''), 'start of month') AS NATIVE CHARACTER(CAST(70 AS INT8))NVARCHAR(100))) AS CHAR(10))), ('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;
|
|
CREATE TABLE [T] (
|
|
a INTEGER,
|
|
b REAL,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,1.5,10.0), (2,-2.5,20.0), (3,-9e999,30.0);
|
|
SELECT * FROM T WHERE b < 2.0 ORDER BY b;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_7215 AFTER UPDATE ON T BEGIN INSERT INTO T(a) VALUES (NULL); END;
|
|
SELECT COUNT(*) FROM T;
|
|
DELETE FROM T WHERE 1;
|
|
SELECT /* .quit */ LEAD(b, 0) OVER (PARTITION BY b ORDER BY b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) FROM T;
|
|
ANALYZE;
|
|
ALTER TABLE T ADD COLUMN extra_4634 NATIVE CHARACTER(70)NVARCHAR(100) DEFAULT 0;
|
|
VACUUM;
|
|
DETACH DATABASE aux29;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_T_2211 ON T(a);
|
|
INSERT INTO T VALUES ('x', NULL, NULL) ON CONFLICT(a) DO UPDATE SET a = excluded.a, b = excluded.b, c = excluded.c;
|
|
ROLLBACK TO sp2544;
|
|
RELEASE sp2544;
|
|
ANALYZE;
|
|
DETACH DATABASE aux56;
|
|
SELECT GROUP_CONCAT(b, '.') OVER (PARTITION BY b ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM T;
|
|
SELECT MIN(c) FROM T;
|
|
END;
|
|
SELECT a, (SELECT a FROM T LIMIT 1) AS first_val FROM T;
|
|
WITH cte AS (SELECT c FROM T) SELECT * FROM cte;
|
|
CREATE TABLE T1 (
|
|
A VARCHAR(10) PRIMARY KEY,
|
|
B VARCHAR(15) UNIQUE,
|
|
C BIGINT
|
|
);
|
|
CREATE TABLE T2 (
|
|
X VARCHAR(20) PRIMARY KEY,
|
|
A VARCHAR(10),
|
|
FOREIGN KEY (A) REFERENCES T1(A)
|
|
);
|
|
INSERT INTO T1 VALUES ('a', 'p', 9999999999999);
|
|
INSERT INTO T1 VALUES ('b', 'q', -9999999999999);
|
|
INSERT INTO T2 VALUES ('m', 'a');
|
|
INSERT INTO T2 VALUES ('n', 'b');
|
|
SELECT T2.X, T1.C FROM T2 LEFT JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
VACUUM main;
|
|
ANALYZE T;
|
|
DELETE FROM T WHERE 1 RETURNING *;
|
|
INSERT INTO T SELECT * FROM T;
|
|
ALTER TABLE T ADD COLUMN extra_3907 BOOLEAN DEFAULT (abs(random()) % 1000);
|
|
INSERT INTO T SELECT * FROM T;
|
|
ANALYZE T;
|
|
SELECT GROUP_CONCAT(a, '|') FROM T;
|
|
SELECT * FROM T WHERE b IN (SELECT b FROM T);
|
|
SELECT * FROM T WHERE c IN (SELECT DISTINCT c FROM T);
|
|
DETACH DATABASE aux79;
|
|
ALTER TABLE T RENAME COLUMN c TO c_r6707;
|
|
VACUUM main;
|
|
ALTER TABLE T2 DROP COLUMN A;
|
|
SELECT * FROM T1 WHERE B IN (SELECT DISTINCT B FROM T1);
|
|
CREATE INDEX IF NOT EXISTS idx_T2_8391 ON T2((A + 1));
|
|
SELECT AVG(c) FROM T;
|
|
SELECT COUNT(A) FROM T2;
|
|
SELECT MAX(A) FROM T2;
|
|
ALTER TABLE T RENAME TO T_r7922;
|
|
PRAGMA short_column_names = 1;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_T1_8734 AS SELECT C FROM T1;
|
|
DELETE FROM T WHERE a IS NULL RETURNING *;
|
|
INSERT OR ABORT INTO T1 VALUES ('', 'x', '');
|
|
CREATE INDEX IF NOT EXISTS idx_T1_1166 ON T1(C COLLATE NOCASE) WHERE C IS NOT NULL;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
ALTER TABLE T2 RENAME TO T2_r5606;
|
|
WITH cte AS (SELECT c, COUNT(*) AS cnt FROM T GROUP BY c) SELECT * FROM cte;
|
|
INSERT OR FAIL INTO T2 VALUES (NULL, 'x');
|
|
DETACH DATABASE aux16;
|
|
SELECT * FROM T2;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
a|1.0|-2.25
|
|
b|4.75|0
|
|
Memory Used: 104064 (max 104064) bytes
|
|
Number of Outstanding Allocations: 190 (max 194)
|
|
Number of Pcache Overflow Bytes: 33856 (max 33856) bytes
|
|
Largest Allocation: 48000 bytes
|
|
Largest Pcache Allocation: 4360 bytes
|
|
Lookaside Slots Used: 38 (max 102)
|
|
Successful lookaside attempts: 416
|
|
Lookaside failures due to size: 2
|
|
Lookaside failures due to OOM: 0
|
|
Pager Heap Usage: 32088 bytes
|
|
Page cache hits: 11
|
|
Page cache misses: 0
|
|
Page cache writes: 0
|
|
Page cache spills: 0
|
|
Schema Heap Usage: 1824 bytes
|
|
Statement Heap/Lookaside Usage: 2528 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: 2528
|
|
Bytes received by read(): 10020
|
|
Bytes sent to write(): 21
|
|
Read() system calls: 14
|
|
Write() system calls: 1
|
|
Bytes read from storage: 0
|
|
Bytes written to storage: 0
|
|
Cancelled write bytes: 0
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
a|1.0|-2.25
|
|
b|4.75|0
|
|
Memory Used: 104512 (max 104512) bytes
|
|
Number of Outstanding Allocations: 200 (max 204)
|
|
Number of Pcache Overflow Bytes: 33880 (max 33880) bytes
|
|
Largest Allocation: 48000 bytes
|
|
Largest Pcache Allocation: 4368 bytes
|
|
Lookaside Slots Used: 54 (max 114)
|
|
Successful lookaside attempts: 433
|
|
Lookaside failures due to size: 2
|
|
Lookaside failures due to OOM: 0
|
|
Pager Heap Usage: 32120 bytes
|
|
Page cache hits: 11
|
|
Page cache misses: 0
|
|
Page cache writes: 0
|
|
Page cache spills: 0
|
|
Temporary data spilled to disk: 0
|
|
Schema Heap Usage: 1512 bytes
|
|
Statement Heap/Lookaside Usage: 2528 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: 2528
|
|
Bytes received by read(): 10540
|
|
Bytes sent to write(): 21
|
|
Read() system calls: 14
|
|
Write() system calls: 1
|
|
Bytes read from storage: 0
|
|
Bytes written to storage: 0
|
|
Cancelled write bytes: 0
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
|
|
```
|
|
|