116 lines
3.4 KiB
Markdown
116 lines
3.4 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
|
|
PRAGMA trusted_schema;
|
|
ATTACH DATABASE ':memory:' AS aux17;
|
|
PRAGMA count_changes = OFF;
|
|
ATTACH DATABASE ('' || '') || (':memory:') AS aux67;
|
|
.check
|
|
CREATE TABLE `T` (
|
|
A VARCHAR(CAST(like(json_array(-trunc(json_quote('true'))), '') AS NUMERIC)) PRIMARY KEY,
|
|
B VARCHAR(-(hex('') + 9223372036854775807)),
|
|
C BIGINT NOT NULL,
|
|
UNIQUE (B, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 3000);
|
|
INSERT INTO T VALUES ('b', 'q', 9223372036854775807);
|
|
INSERT INTO T VALUES ('c', 'r', 1700);
|
|
INSERT INTO T VALUES ('e', 'q', -9223372036854775808);
|
|
INSERT INTO T VALUES ('f', 'r', 0);
|
|
WITH _m AS MATERIALIZED (SELECT * FROM T) SELEC_m /* .filectrl */ B, COUNT(*) AS D, SUM(C) AS E, AVG(C) AS F FROM T GROUP BY B HAVING SUM(C) > 0;
|
|
DELETE FROM T WHERE 0 RETURNING *;
|
|
DELETE FROM T WHERE +A > (SELECT AVG(A) FROM T);
|
|
ANALYZE;
|
|
DROP TABLE IF EXISTS T;
|
|
SELECT * FROM (SELECT * FROM T) AS sub;
|
|
UPDATE T SET A = NULL WHERE A BETWEEN 0 AND 100 RETURNING *;
|
|
ANALYZE T;
|
|
DETACH DATABASE aux67;
|
|
INSERT INTO T VALUES (NULL, NULL, NULL);
|
|
INSERT INTO T VALUES (NULL, NULL, NULL);
|
|
INSERT INTO T DEFAULT VALUES;
|
|
PRAGMA temp_store = '1';
|
|
PRAGMA temp_store = 'FILE';
|
|
INSERT INTO T VALUES (NULL, NULL, NULL);
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES (1,2.0,10.5), (1,-3.5,20.25), (2,9e999,-1.0);
|
|
WITH d AS (SELECT a,SUM(b*c) AS e FROM T GROUP BY a) SELECT * FROM d;
|
|
DROP TRIGGER IF EXISTS T;
|
|
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 T;
|
|
SELECT COUNT(*) FROM T;
|
|
INSERT INTO T DEFAULT VALUES;
|
|
SELECT COUNT(*) FROM T;
|
|
SELECT * FROM T WHERE A = (SELECT MAX(A) FROM T WHERE A = (SELECT MAX(A) FROM T));
|
|
PRAGMA synchronous;
|
|
DETACH DATABASE aux17;
|
|
DELETE FROM T WHERE 0 RETURNING *;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_1028 BEFORE UPDATE OF C ON T FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
PRAGMA parser_trace = TRUE;
|
|
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM T WHERE C IN (SELECT x FROM cte);
|
|
UPDATE T SET c = json_object('k', c) WHERE c IS NOT NULL;
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS v_T_8093 AS SELECT a FROM T;
|
|
CREATE TABLE T (
|
|
a TEXT,
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INSERT INTO T VALUES ('a','b',1.5), ('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;
|
|
VACUUM;
|
|
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;
|
|
ALTER TABLE T RENAME TO T_r2091;
|
|
INSERT INTO T VALUES (NULL, NULL, NULL);
|
|
PRAGMA defer_foreign_keys = YES;
|
|
DROP TRIGGER IF EXISTS T;
|
|
UPDATE T SET a = CURRENT_TIMESTAMP WHERE a BETWEEN 0 AND 100;
|
|
INSERT INTO T SELECT * FROM T;
|
|
SELECT * FROM T WHERE EXISTS (SELECT 1 FROM T t2 WHERE t2.b = T.b);
|
|
CREATE VIEW IF NOT EXISTS v_T_7910 AS SELECT a FROM T;
|
|
WITH a AS (SELECT b FROM T), b AS (SELECT b FROM a), c AS (SELECT b FROM b) SELECT * FROM c;
|
|
REINDEX;
|
|
REINDEX T;
|
|
VACUUM main;
|
|
ALTER TABLE T DROP COLUMN c;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
1
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
0
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
|
|
```
|
|
|