1664 lines
66 KiB
Markdown
1664 lines
66 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
|
|
SAVEPOINT sp3035;
|
|
ATTACH DATABASE ':memory:' AS aux93;
|
|
ATTACH DATABASE ':memory:' AS aux17;
|
|
.limit
|
|
.changes off
|
|
.show
|
|
SAVEPOINT sp6204;
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', quote(octet_length('你好'))))NVARCHAR(CAST(-replace('aaa', 'a', 'bb') AS NATIVE CHARACTER(jsonb_tree(round(atan(x'5e277118718d1dd04170ff'), CAST(CAST(-like(datetime(CAST(9223372036854775807 AS INT8), '+1 day'), '') AS MEDIUMINT) AS NUMERIC))))NVARCHAR(100))),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE NOT NOT (0) ;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
REINDEX T;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM t1;
|
|
PRAGMA count_changes = TRUE;
|
|
SELECT COUNT(*) FROM T;
|
|
DROP TABLE IF EXISTS t1;
|
|
UPDATE one SET o = o + 1 WHERE rowid = 1 RETURNING *;
|
|
PRAGMA temp_store = '2';
|
|
RELEASE sp6204;
|
|
SELECT COUNT(*) FILTER (WHERE o IS NOT NULL), SUM(rowid) FILTER (WHERE o > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(o) FILTER (WHERE o > 0 AND o < 100), COUNT(*) FILTER (WHERE typeof(o) = "text") FROM one;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_4183 AFTER DELETE ON T BEGIN SELECT RAISE(IGNORE); END;
|
|
INSERT INTO t1 VALUES ('x', 1) ON CONFLICT(x) DO UPDATE SET x = excluded.x, z = excluded.z;
|
|
.show
|
|
SAVEPOINT sp6204;
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', quote(octet_length('你好'))))NVARCHAR(CAST(100 AS NATIVE CHARACTER(70)NVARCHAR(100))),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
REINDEX T;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM t1;
|
|
PRAGMA count_changes = TRUE;
|
|
SELECT COUNT(*) FROM T;
|
|
DROP TABLE IF EXISTS t1;
|
|
UPDATE one SET o = o + 1 WHERE rowid = 1 RETURNING *;
|
|
PRAGMA temp_store = '2';
|
|
RELEASE sp6204;
|
|
SELECT COUNT(*) FILTER (WHERE o IS NOT NULL), SUM(rowid) FILTER (WHERE o > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(o) FILTER (WHERE o > 0 AND o < 100), COUNT(*) FILTER (WHERE typeof(o) = "text") FROM one;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_4183 AFTER DELETE ON T BEGIN SELECT RAISE(IGNORE); END;
|
|
INSERT INTO t1 VALUES ('x', 1) ON CONFLICT(x) DO UPDATE SET x = excluded.x, z = excluded.z;
|
|
.show
|
|
SAVEPOINT sp6204;
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', quote(octet_length('你好'))))NVARCHAR(CAST(100 AS NATIVE CHARACTER(70)NVARCHAR(100))),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
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 INNER JOIN T1 ON T2.A = T1.A WHERE T1.C > -1000000000000;
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
REINDEX T;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM t1;
|
|
PRAGMA count_changes = TRUE;
|
|
SELECT COUNT(*) FROM T;
|
|
DROP TABLE IF EXISTS t1;
|
|
UPDATE one SET o = o + 1 WHERE rowid = 1 RETURNING *;
|
|
PRAGMA temp_store = '2';
|
|
RELEASE sp6204;
|
|
SELECT COUNT(*) FILTER (WHERE o IS NOT NULL), SUM(rowid) FILTER (WHERE o > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(o) FILTER (WHERE o > 0 AND o < 100), COUNT(*) FILTER (WHERE typeof(o) = "text") FROM one;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_4183 AFTER DELETE ON T BEGIN SELECT RAISE(IGNORE); END;
|
|
INSERT INTO t1 VALUES ('x', 1) ON CONFLICT(x) DO UPDATE SET x = excluded.x, z = excluded.z;
|
|
.show
|
|
SAVEPOINT sp6204;
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', quote(octet_length('你好'))))NVARCHAR(CAST(100 AS NATIVE CHARACTER(70)NVARCHAR(100))),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
REINDEX T;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM t1;
|
|
PRAGMA count_changes = TRUE;
|
|
SELECT COUNT(*) FROM T;
|
|
DROP TABLE IF EXISTS t1;
|
|
UPDATE one SET o = o + 1 WHERE rowid = 1 RETURNING *;
|
|
PRAGMA temp_store = '2';
|
|
RELEASE sp6204;
|
|
SELECT COUNT(*) FILTER (WHERE o IS NOT NULL), SUM(rowid) FILTER (WHERE o > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(o) FILTER (WHERE o > 0 AND o < 100), COUNT(*) FILTER (WHERE typeof(o) = "text") FROM one;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_4183 AFTER DELETE ON T BEGIN SELECT RAISE(IGNORE); END;
|
|
INSERT INTO t1 VALUES ('x', 1) ON CONFLICT(x) DO UPDATE SET x = excluded.x, z = excluded.z;
|
|
.show
|
|
SAVEPOINT sp6204;
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', quote(octet_length('你好'))))NVARCHAR(CAST(100 AS NATIVE CHARACTER(70)NVARCHAR(100))),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
.parameter
|
|
ATTACH DATABASE ':memory:' AS aux70;
|
|
.check
|
|
CREATE TABLE T (
|
|
a NATIVE CHARACTER(strftime('%Y-%m-%d', 70))NVARCHAR(100),
|
|
b TEXT,
|
|
c REAL
|
|
);
|
|
INsErT INTO T VALUES ('a','b',round(julianday(x'ce'))), ('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;
|
|
PRAGMA vdbe_addoptrace = OFF;
|
|
CREATE TABLE one(o);
|
|
INSERT INTO one DEFAULT VALUES;
|
|
CREATE TABLE t1(x, z);
|
|
INSERT INTO t1 VALUES('' || ('aaa'), json_array_length(1320017232380564505, '$.arr'));
|
|
INSERT INTO t1 VALUES('aaa', 4.0);
|
|
CREATE VIEW vvv AS WITH _m AS MATERIALIZED (SELECT * FROM t1) SELECT * FROM _m UNION ALL SELECT 0, 0 WHERE 0;
|
|
SELECT ( SELECT sum(z) FROM vvv WHERE x='aaa' ) FROM one;
|
|
INSERT INTO one VALUES (NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_8851 INT8;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
CREATE TRIGGER IF NOT EXISTS trg_one_5101 BEFORE UPDATE OF o ON one BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT COUNT(*) FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT COUNT(*) FILTER (WHERE x IS NOT NULL), SUM(rowid) FILTER (WHERE x > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(x) FILTER (WHERE x > 0 AND x < 100), COUNT(*) FILTER (WHERE typeof(x) = "text") FROM t1;
|
|
UPDATE one SET o = json_object('k', o) WHERE o IS NOT NULL RETURNING *;
|
|
WITH a AS MATERIALIZED (SELECT * FROM one), b AS NOT MATERIALIZED (SELECT * FROM a) SELECT * FROM b;
|
|
SELECT MAX(o) FROM one;
|
|
UPDATE t1 SET z = 'x' WHERE 1 RETURNING *;
|
|
ALTER TABLE t1 DROP COLUMN x;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(30),
|
|
C VARCHAR(30),
|
|
PRIMARY KEY (A, B),
|
|
UNIQUE (A, C)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', 'x');
|
|
INSERT INTO T VALUES ('a', 'q', 'y');
|
|
INSERT INTO T VALUES ('b', 'p', 'z');
|
|
INSERT INTO T VALUES ('c', 'r', 'w');
|
|
INSERT INTO T VALUES ('d', 's', 'v');
|
|
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
|
|
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t1 t2 WHERE t2.x = t1.x);
|
|
INSERT INTO one VALUES (NULL);
|
|
CREATE INDEX IF NOT EXISTS idx_t1_5499 ON t1(lower(x));
|
|
SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 LIMIT 1);
|
|
ALTER TABLE one ADD COLUMN extra_45 TEXT DEFAULT '';
|
|
SELECT STRING_AGG(z) FROM t1;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
SELECT COUNT(*) FROM t1;
|
|
DETACH DATABASE aux70;
|
|
SELECT * FROM t1 WHERE z IN (SELECT ALL z FROM t1);
|
|
REINDEX T;
|
|
SELECT COUNT(*) FILTER (WHERE z IS NOT NULL), SUM(rowid) FILTER (WHERE z > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(z) FILTER (WHERE z > 0 AND z < 100), COUNT(*) FILTER (WHERE typeof(z) = "text") FROM t1;
|
|
PRAGMA count_changes = TRUE;
|
|
SELECT COUNT(*) FROM T;
|
|
DROP TABLE IF EXISTS t1;
|
|
UPDATE one SET o = o + 1 WHERE rowid = 1 RETURNING *;
|
|
PRAGMA temp_store = '2';
|
|
RELEASE sp6204;
|
|
SELECT COUNT(*) FILTER (WHERE o IS NOT NULL), SUM(rowid) FILTER (WHERE o > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(o) FILTER (WHERE o > 0 AND o < 100), COUNT(*) FILTER (WHERE typeof(o) = "text") FROM one;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_4183 AFTER DELETE ON T /**/ BEGIN SELECT RAISE(IGNORE); END;
|
|
INSERT INTO t1 VALUES ('x', 1) ON CONFLICT(x) DO UPDATE SET x = excluded.x, z = excluded.z;
|
|
INSERT INTO t1 VALUES (NULL, NULL);
|
|
ALTER TABLE t1 ADD COLUMN extra_9021 REAL DEFAULT (abs(random()) % 1000);
|
|
ALTER TABLE one DROP COLUMN o;
|
|
SELECT * FROM one;
|
|
UPDATE t1 SET x = '' WHERE 1 RETURNING *;
|
|
UPDATE one SET o = '' RETURNING *;
|
|
INSERT OR ABORT INTO one VALUES (0);
|
|
WITH cte AS (SELECT * FROM T) SELECT * FROM cte c1 JOIN cte c2 ON c1.B = c2.B;
|
|
ALTER TABLE T RENAME COLUMN B TO B_r7570;
|
|
INSERT INTO T VALUES (1, NULL, NULL) ON CONFLICT(A) DO UPDATE SET A = excluded.A, B = excluded.B, C = excluded.C;
|
|
CREATE INDEX IF NOT EXISTS idx_T_9245 ON T((C + 1));
|
|
CREATE TEMP VIEW IF NOT EXISTS v_t1_944 AS SELECT z FROM t1;
|
|
REINDEX;
|
|
DROP TABLE IF EXISTS one;
|
|
SELECT * FROM T WHERE C IN (SELECT C FROM T);
|
|
ALTER TABLE one ADD COLUMN extra_6532 INT8 COLLATE RTRIM;
|
|
PRAGMA quick_check(20);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_2021 ON t1(z COLLATE NOCASE) WHERE z > 0;
|
|
INSERT INTO T SELECT * FROM T;
|
|
SELECT * FROM t1 WHERE z < ALL (SELECT z FROM t1);
|
|
DETACH DATABASE aux17;
|
|
REINDEX one;
|
|
DETACH DATABASE aux93;
|
|
SELECT COUNT(*) FROM T;
|
|
ROLLBACK TO sp3035;
|
|
RELEASE sp3035;
|
|
INSERT INTO T VALUES ('x', NULL, 1) ON CONFLICT(A) DO UPDATE SET A = excluded.A, B = excluded.B, C = excluded.C;
|
|
CREATE TEMP VIEW IF NOT EXISTS v_T_8944 AS SELECT C FROM T;
|
|
WITH cte AS (SELECT 1 AS x) SELECT x + 0.5 FROM cte;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
length 1000000000
|
|
sql_length 1000000000
|
|
column 2000
|
|
expr_depth 1000
|
|
compound_select 500
|
|
vdbe_op 250000000
|
|
function_arg 127
|
|
attached 10
|
|
like_pattern_length 50000
|
|
variable_number 32766
|
|
trigger_depth 1000
|
|
worker_threads 0
|
|
echo: off
|
|
eqp: off
|
|
explain: auto
|
|
headers: off
|
|
mode: list
|
|
nullvalue: ""
|
|
output: stdout
|
|
colseparator: "|"
|
|
rowseparator: "\n"
|
|
stats: off
|
|
width:
|
|
filename: :memory:
|
|
.parameter CMD ... Manage SQL parameter bindings
|
|
clear Erase all bindings
|
|
init Initialize the TEMP table that holds bindings
|
|
list List the current parameter bindings
|
|
set PARAMETER VALUE Given SQL parameter PARAMETER a value of VALUE
|
|
PARAMETER should start with one of: $ : @ ?
|
|
unset PARAMETER Remove PARAMETER from the binding table
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
length 1000000000
|
|
sql_length 1000000000
|
|
column 2000
|
|
expr_depth 1000
|
|
compound_select 500
|
|
vdbe_op 250000000
|
|
function_arg 1000
|
|
attached 10
|
|
like_pattern_length 50000
|
|
variable_number 32766
|
|
trigger_depth 1000
|
|
worker_threads 0
|
|
echo: off
|
|
eqp: off
|
|
explain: auto
|
|
headers: off
|
|
mode: list
|
|
nullvalue: ""
|
|
output: stdout
|
|
colseparator: "|"
|
|
rowseparator: "\n"
|
|
stats: off
|
|
width:
|
|
filename: :memory:
|
|
.parameter CMD ... Manage SQL parameter bindings
|
|
clear Erase all bindings
|
|
init Initialize the TEMP table that holds bindings
|
|
list List the current parameter bindings
|
|
set PARAMETER VALUE Given SQL parameter PARAMETER a value of VALUE
|
|
PARAMETER should start with one of: $ : @ ?
|
|
unset PARAMETER Remove PARAMETER from the binding table
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
|
|
```
|
|
|