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