2892 lines
95 KiB
Markdown
2892 lines
95 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 sp3737;
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
.log OFF
|
|
SAVEPOINT sp9712;
|
|
PRAGMA trusted_schema = OFF;
|
|
PRAGMA vdbe_listing = YES;
|
|
-- .log OFF
|
|
|
|
SAVEPOINT sp2394;
|
|
BEGIN;
|
|
BEGIN EXCLUSIVE TRANSACTION;
|
|
BEGIN IMMEDIATE;
|
|
PRAGMA table_xinfo(users);
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE `T` (
|
|
a DATETIME,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', --(-(1.0))),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT NOT (NOT a >= NULL) ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(sign(-if('', timediff(NULL, likelihood(NULL, 0.5)), 1)), 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE +a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
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(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
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;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<9) SELECT SUM(x) FROM cnt;
|
|
UPDATE t2 SET a = json_object('k', a) WHERE a IS NOT NULL;
|
|
ROLLBACK;
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
SELECT GROUP_CONCAT(a) FROM t1;
|
|
COMMIT;
|
|
SELECT COUNT(*) FROM T;
|
|
ANALYZE T;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_7577 BEFORE DELETE ON T BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
COMMIT;
|
|
INSERT INTO T VALUES (NULL, NULL);
|
|
ALTER TABLE T ADD COLUMN extra_5604 REAL DEFAULT 0;
|
|
WITH cte AS (SELECT a FROM t2) SELECT * FROM cte;
|
|
ALTER TABLE t1 ADD COLUMN extra_6447 DATE DEFAULT 0;
|
|
SELECT * FROM T1;
|
|
ALTER TABLE T1 ADD COLUMN extra_2609 NATIVE CHARACTER(70)NVARCHAR(100) DEFAULT CURRENT_TIMESTAMP;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp2394;
|
|
RELEASE SAVEPOINT sp2394;
|
|
WITH RECURSIVE multi(a, b) AS (VALUES(1, 1.0) UNION ALL SELECT a+1, b*0.5 FROM multi WHERE a<4) SELECT * FROM multi;
|
|
SELECT NTH_VALUE(a, -1) OVER (PARTITION BY a ORDER BY a) FROM t2;
|
|
ALTER TABLE t1 RENAME COLUMN a TO a_r2686;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_6625 BEFORE UPDATE OF b ON T FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT LEAD(a, 2, a) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2;
|
|
REINDEX;
|
|
SELECT CUME_DIST() OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2356 AFTER INSERT ON t2 FOR EACH ROW BEGIN INSERT INTO t2(a) VALUES (NULL); END;
|
|
ALTER TABLE t2 RENAME TO t2_r8773;
|
|
UPDATE t1 SET a = NULL WHERE 1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
DELETE FROM T WHERE a > (SELECT AVG(a) FROM T);
|
|
SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
DELETE FROM T WHERE 0 RETURNING *;
|
|
|
|
SELECT COUNT(*) FROM t1;
|
|
DELETE FROM t1 WHERE rowid = 62 RETURNING *;
|
|
ANALYZE;
|
|
SELECT SUM(a) OVER (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS) FROM t1;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2262 ON t2(lower(a)) WHERE a IS NOT NULL;
|
|
ALTER TABLE t1 ADD COLUMN extra_3935 DECIMAL(10,5) DEFAULT 0;
|
|
SELECT * FROM T AS a FULL JOIN t1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t1 AS a RIGHT JOIN t1 AS b ON a.rowid = b.rowid;
|
|
|
|
SELECT b FROM T UNION SELECT b FROM (SELECT * FROM T) AS sub;
|
|
ANALYZE;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT SUM(a) FROM t1;
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
ALTER TABLE t1 DROP COLUMN a;
|
|
ANALYZE T;
|
|
INSERT INTO T SELECT * FROM T;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
UPDATE t2 SET a = json_object('k', a) WHERE 1;
|
|
WITH cte AS (SELECT a FROM t2 WHERE a IS NULL) SELECT * FROM cte;
|
|
ALTER TABLE T ADD COLUMN extra_9103 REAL;
|
|
SELECT * FROM T AS a FULL JOIN t2 AS b ON a.rowid = b.rowid;
|
|
SELECT SUM(b) FILTER (WHERE b > 0) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM T;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp9712;
|
|
RELEASE SAVEPOINT sp9712;
|
|
DELETE FROM T WHERE rowid = 29 RETURNING *;
|
|
DETACH DATABASE aux80;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_722 BEFORE UPDATE OF a ON T BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
INSERT OR ABORT INTO T VALUES (6, 'x');
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
.log OFF
|
|
SAVEPOINT sp9712;
|
|
PRAGMA trusted_schema = OFF;
|
|
PRAGMA vdbe_listing = YES;
|
|
-- .log OFF
|
|
|
|
SAVEPOINT sp2394;
|
|
BEGIN;
|
|
BEGIN EXCLUSIVE TRANSACTION;
|
|
BEGIN IMMEDIATE;
|
|
PRAGMA table_xinfo(users);
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE `T` (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', --(-(1.0))),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= NULL ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(sign(-if('', timediff(NULL, likelihood(NULL, 0.5)), 1)), 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE +a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
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(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
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;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<9) SELECT SUM(x) FROM cnt;
|
|
UPDATE t2 SET a = json_object('k', a) WHERE a IS NOT NULL;
|
|
ROLLBACK;
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
SELECT GROUP_CONCAT(a) FROM t1;
|
|
COMMIT;
|
|
SELECT COUNT(*) FROM T;
|
|
ANALYZE T;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_7577 BEFORE DELETE ON T BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
COMMIT;
|
|
INSERT INTO T VALUES (NULL, NULL);
|
|
ALTER TABLE T ADD COLUMN extra_5604 REAL DEFAULT 0;
|
|
WITH cte AS (SELECT a FROM t2) SELECT * FROM cte;
|
|
ALTER TABLE t1 ADD COLUMN extra_6447 DATE DEFAULT 0;
|
|
SELECT * FROM T1;
|
|
ALTER TABLE T1 ADD COLUMN extra_2609 NATIVE CHARACTER(70)NVARCHAR(100) DEFAULT CURRENT_TIMESTAMP;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp2394;
|
|
RELEASE SAVEPOINT sp2394;
|
|
WITH RECURSIVE multi(a, b) AS (VALUES(1, 1.0) UNION ALL SELECT a+1, b*0.5 FROM multi WHERE a<4) SELECT * FROM multi;
|
|
SELECT NTH_VALUE(a, -1) OVER (PARTITION BY a ORDER BY a) FROM t2;
|
|
ALTER TABLE t1 RENAME COLUMN a TO a_r2686;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_6625 BEFORE UPDATE OF b ON T FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT LEAD(a, 2, a) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2;
|
|
REINDEX;
|
|
SELECT CUME_DIST() OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2356 AFTER INSERT ON t2 FOR EACH ROW BEGIN INSERT INTO t2(a) VALUES (NULL); END;
|
|
ALTER TABLE t2 RENAME TO t2_r8773;
|
|
UPDATE t1 SET a = NULL WHERE 1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
DELETE FROM T WHERE a > (SELECT AVG(a) FROM T);
|
|
SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
DELETE FROM T WHERE 0 RETURNING *;
|
|
|
|
SELECT COUNT(*) FROM t1;
|
|
DELETE FROM t1 WHERE rowid = 62 RETURNING *;
|
|
ANALYZE;
|
|
SELECT SUM(a) OVER (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS) FROM t1;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2262 ON t2(lower(a)) WHERE a IS NOT NULL;
|
|
ALTER TABLE t1 ADD COLUMN extra_3935 DECIMAL(10,5) DEFAULT 0;
|
|
SELECT * FROM T AS a FULL JOIN t1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t1 AS a RIGHT JOIN t1 AS b ON a.rowid = b.rowid;
|
|
|
|
SELECT b FROM T UNION SELECT b FROM (SELECT * FROM T) AS sub;
|
|
ANALYZE;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT SUM(a) FROM t1;
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
ALTER TABLE t1 DROP COLUMN a;
|
|
ANALYZE T;
|
|
INSERT INTO T SELECT * FROM T;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
UPDATE t2 SET a = json_object('k', a) WHERE 1;
|
|
WITH cte AS (SELECT a FROM t2 WHERE a IS NULL) SELECT * FROM cte;
|
|
ALTER TABLE T ADD COLUMN extra_9103 REAL;
|
|
SELECT * FROM T AS a FULL JOIN t2 AS b ON a.rowid = b.rowid;
|
|
SELECT SUM(b) FILTER (WHERE b > 0) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM T;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp9712;
|
|
RELEASE SAVEPOINT sp9712;
|
|
DELETE FROM T WHERE rowid = 29 RETURNING *;
|
|
DETACH DATABASE aux80;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_722 BEFORE UPDATE OF a ON T BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
INSERT OR ABORT INTO T VALUES (6, 'x');
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
.log OFF
|
|
SAVEPOINT sp9712;
|
|
PRAGMA trusted_schema = OFF;
|
|
PRAGMA vdbe_listing = YES;
|
|
-- .log OFF
|
|
|
|
SAVEPOINT sp2394;
|
|
BEGIN;
|
|
BEGIN EXCLUSIVE TRANSACTION;
|
|
BEGIN IMMEDIATE;
|
|
PRAGMA table_xinfo(users);
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE `T` (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', --(-(1.0))),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= NULL ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(sign(-if('', timediff(NULL, likelihood(NULL, 0.5)), 1)), 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE +a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
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(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
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;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<9) SELECT SUM(x) FROM cnt;
|
|
UPDATE t2 SET a = json_object('k', a) WHERE a IS NOT NULL;
|
|
ROLLBACK;
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
SELECT GROUP_CONCAT(a) FROM t1;
|
|
COMMIT;
|
|
SELECT COUNT(*) FROM T;
|
|
ANALYZE T;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_7577 BEFORE DELETE ON T BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
COMMIT;
|
|
INSERT INTO T VALUES (NULL, NULL);
|
|
ALTER TABLE T ADD COLUMN extra_5604 REAL DEFAULT 0;
|
|
WITH cte AS (SELECT a FROM t2) SELECT * FROM cte;
|
|
ALTER TABLE t1 ADD COLUMN extra_6447 DATE DEFAULT 0;
|
|
SELECT * FROM T1;
|
|
ALTER TABLE T1 ADD COLUMN extra_2609 NATIVE CHARACTER(70)NVARCHAR(100) DEFAULT CURRENT_TIMESTAMP;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp2394;
|
|
RELEASE SAVEPOINT sp2394;
|
|
WITH RECURSIVE multi(a, b) AS (VALUES(1, 1.0) UNION ALL SELECT a+1, b*0.5 FROM multi WHERE a<4) SELECT * FROM multi;
|
|
SELECT NTH_VALUE(a, -1) OVER (PARTITION BY a ORDER BY a) FROM t2;
|
|
ALTER TABLE t1 RENAME COLUMN a TO a_r2686;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_6625 BEFORE UPDATE OF b ON T FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT LEAD(a, 2, a) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2;
|
|
REINDEX;
|
|
SELECT CUME_DIST() OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2356 AFTER INSERT ON t2 FOR EACH ROW BEGIN INSERT INTO t2(a) VALUES (NULL); END;
|
|
ALTER TABLE t2 RENAME TO t2_r8773;
|
|
UPDATE t1 SET a = NULL WHERE 1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
DELETE FROM T WHERE a > (SELECT AVG(a) FROM T);
|
|
SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
DELETE FROM T WHERE 0 RETURNING *;
|
|
|
|
SELECT COUNT(*) FROM t1;
|
|
DELETE FROM t1 WHERE rowid = 62 RETURNING *;
|
|
ANALYZE;
|
|
SELECT SUM(a) OVER (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS) FROM t1;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2262 ON t2(lower(a)) WHERE a IS NOT NULL;
|
|
ALTER TABLE t1 ADD COLUMN extra_3935 DECIMAL(10,5) DEFAULT 0;
|
|
SELECT * FROM T AS a FULL JOIN t1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t1 AS a RIGHT JOIN t1 AS b ON a.rowid = b.rowid;
|
|
|
|
SELECT b FROM T UNION SELECT b FROM (SELECT * FROM T) AS sub;
|
|
ANALYZE;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT SUM(a) FROM t1;
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
ALTER TABLE t1 DROP COLUMN a;
|
|
ANALYZE T;
|
|
INSERT INTO T SELECT * FROM T;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
UPDATE t2 SET a = json_object('k', a) WHERE 1;
|
|
WITH cte AS (SELECT a FROM t2 WHERE a IS NULL) SELECT * FROM cte;
|
|
ALTER TABLE T ADD COLUMN extra_9103 REAL;
|
|
SELECT * FROM T AS a FULL JOIN t2 AS b ON a.rowid = b.rowid;
|
|
SELECT SUM(b) FILTER (WHERE b > 0) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM T;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp9712;
|
|
RELEASE SAVEPOINT sp9712;
|
|
DELETE FROM T WHERE rowid = 29 RETURNING *;
|
|
DETACH DATABASE aux80;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_722 BEFORE UPDATE OF a ON T BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
INSERT OR ABORT INTO T VALUES (6, 'x');
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
.log OFF
|
|
SAVEPOINT sp9712;
|
|
PRAGMA trusted_schema = OFF;
|
|
PRAGMA vdbe_listing = YES;
|
|
-- .log OFF
|
|
|
|
SAVEPOINT sp2394;
|
|
BEGIN;
|
|
BEGIN EXCLUSIVE TRANSACTION;
|
|
BEGIN IMMEDIATE;
|
|
PRAGMA table_xinfo(users);
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE `T` (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', --(-(1.0))),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= NULL ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(sign(-if('', timediff(NULL, likelihood(NULL, 0.5)), 1)), 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE +a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
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(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
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;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE NOT a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
.limit
|
|
ATTACH DATABASE ':memory:' AS aux80;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b REAL
|
|
);
|
|
INSERT INTO T VALUES (substr(NULL, '', -1.0),1.5), (2,-7.25), (3,0.0);
|
|
SELECT a,b FROM T WHERE a >= 2 ORDER BY b;
|
|
CREATE TABLE t1(a TEXT COLLATE BINARY);
|
|
-- substr({v}, -2)
|
|
|
|
ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
|
|
INSERT INTO t1 VALUES(x'c714fadb5d678c9f65d584f7a42a737d','-2');
|
|
INSERT INTO t1 VALUES(instr('hello', 'lo'),'5.4e-08');
|
|
SELECT typeof(a), a, typeof(b), b FROM t1;
|
|
CREATE TABLE t2(a INTEGER);
|
|
INSERT INTO t2 VALUES((glob('1', 'hello') + replace(0.0, 'a', '')));
|
|
INSERT INTO t2 VALUES(1);
|
|
INSERT INTO t2 VALUES(2);
|
|
ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
|
|
SELECT a, sum(b) FROM t2 GROUP BY a;
|
|
PRAGMA parser_trace = FALSE;
|
|
SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
DROP VIEW IF EXISTS t1;
|
|
DETACH DATABASE aux80;
|
|
WITH cte AS (SELECT * FROM t2 WHERE a IS NOT NULL) SELECT * FROM cte;
|
|
REINDEX;
|
|
VACUUM;
|
|
VACUUM main;
|
|
SELECT * FROM t1;
|
|
REINDEX T;
|
|
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<9) SELECT SUM(x) FROM cnt;
|
|
UPDATE t2 SET a = json_object('k', a) WHERE a IS NOT NULL;
|
|
ROLLBACK;
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
SELECT GROUP_CONCAT(a) FROM t1;
|
|
COMMIT;
|
|
SELECT COUNT(*) FROM T;
|
|
ANALYZE T;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_7577 BEFORE DELETE ON T BEGIN SELECT RAISE(ABORT, 'abort'); END;
|
|
COMMIT;
|
|
INSERT INTO T VALUES (NULL, NULL);
|
|
ALTER TABLE T ADD COLUMN extra_5604 REAL DEFAULT 0;
|
|
WITH cte AS (SELECT a FROM t2) SELECT * FROM cte;
|
|
ALTER TABLE t1 ADD COLUMN extra_6447 DATE DEFAULT 0;
|
|
SELECT * FROM T1;
|
|
ALTER TABLE T1 ADD COLUMN extra_2609 NATIVE CHARACTER(70)NVARCHAR(100) DEFAULT CURRENT_TIMESTAMP;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp2394;
|
|
RELEASE SAVEPOINT sp2394;
|
|
WITH RECURSIVE multi(a, b) AS (VALUES(1, 1.0) UNION ALL SELECT a+1, b*0.5 FROM multi WHERE a<4) SELECT * FROM multi;
|
|
SELECT NTH_VALUE(a, -1) OVER (PARTITION BY a ORDER BY a) FROM t2;
|
|
ALTER TABLE t1 RENAME COLUMN a TO a_r2686;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_6625 BEFORE UPDATE OF b ON T FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
|
|
SELECT LEAD(a, 2, a) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2;
|
|
REINDEX;
|
|
SELECT CUME_DIST() OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t1;
|
|
CREATE TRIGGER IF NOT EXISTS trg_t2_2356 AFTER INSERT ON t2 FOR EACH ROW BEGIN INSERT INTO t2(a) VALUES (NULL); END;
|
|
ALTER TABLE t2 RENAME TO t2_r8773;
|
|
UPDATE t1 SET a = NULL WHERE 1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
DELETE FROM T WHERE a > (SELECT AVG(a) FROM T);
|
|
SELECT * FROM t2;
|
|
INSERT INTO t1 DEFAULT VALUES;
|
|
DELETE FROM T WHERE 0 RETURNING *;
|
|
|
|
SELECT COUNT(*) FROM t1;
|
|
DELETE FROM t1 WHERE rowid = 62 RETURNING *;
|
|
ANALYZE;
|
|
SELECT SUM(a) OVER (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS) FROM t1;
|
|
CREATE INDEX IF NOT EXISTS idx_t2_2262 ON t2(lower(a)) WHERE a IS NOT NULL;
|
|
ALTER TABLE t1 ADD COLUMN extra_3935 DECIMAL(10,5) DEFAULT 0;
|
|
SELECT * FROM T AS a FULL JOIN t1 AS b ON a.rowid = b.rowid;
|
|
SELECT * FROM t1 AS a RIGHT JOIN t1 AS b ON a.rowid = b.rowid;
|
|
|
|
SELECT b FROM T UNION SELECT b FROM (SELECT * FROM T) AS sub;
|
|
ANALYZE;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT SUM(a) FROM t1;
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
ALTER TABLE t1 DROP COLUMN a;
|
|
ANALYZE T;
|
|
INSERT INTO T SELECT * FROM T;
|
|
|
|
SELECT COUNT(*) FROM T;
|
|
UPDATE t2 SET a = json_object('k', a) WHERE 1;
|
|
WITH cte AS (SELECT a FROM t2 WHERE a IS NULL) SELECT * FROM cte;
|
|
ALTER TABLE T ADD COLUMN extra_9103 REAL;
|
|
SELECT * FROM T AS a FULL JOIN t2 AS b ON a.rowid = b.rowid;
|
|
SELECT SUM(b) FILTER (WHERE b > 0) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) FROM T;
|
|
ROLLBACK TRANSACTION TO SAVEPOINT sp9712;
|
|
RELEASE SAVEPOINT sp9712;
|
|
DELETE FROM T WHERE rowid = 29 RETURNING *;
|
|
DETACH DATABASE aux80;
|
|
CREATE TRIGGER IF NOT EXISTS trg_T_722 BEFORE UPDATE OF a ON T BEGIN SELECT RAISE(ROLLBACK, 'rb'); END;
|
|
INSERT OR ABORT INTO T VALUES (6, 'x');
|
|
|
|
|
|
SELECT COUNT(*) FROM t2;
|
|
SELECT * FROM (SELECT * FROM T) AS t1 JOIN (SELECT * FROM T) AS t2 ON t1.a = t2.a;
|
|
ROLLBACK TO sp3737;
|
|
RELEASE sp3737;
|
|
INSERT INTO t2 DEFAULT VALUES;
|
|
DROP VIEW IF EXISTS t2;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
3.39.0 2022-05-10 23:28:12 764b71267e0b31ff7eaf2a0def7526a1a02dce4d5b456dea060d97ed342ealt1
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
3.51.1 2025-11-28 17:28:25 281fc0e9afc38674b9b0991943b9e9d1e64c6cbdb133d35f6f5c87ff6af38a88 (64-bit)
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
-version
|
|
```
|
|
|