## Summary **No review yet** ## Minimized query ```sql .restore emplyeee.db .backup main output.db ATTACH DATABASE ':memory:' AS aux54; PRAGMA reverse_unordered_selects = NO; BEGIN EXCLUSIVE; ATTACH DATABASE ':memory:' AS aux94; CREATE TABLE T ( a DATE, b REAL, c REAL ); INSERT INTO T VALUES (jsonb_array(round(printf('%s', strftime('%s', (acosh(length(randomblob(coalesce(10, 10, 10, 10, 10)))) - 0)))), 1, NULL),2.0,10.5), (1,-3.5,20.25), (2,9e999,-1.0); WITH d AS (SELECT a,SUM(b*c) AS e FROM T GROUP BY a) SELECT * FROM d; SAVEPOINT sp375; SAVEPOINT sp8202; SAVEPOINT sp9896; PRAGMA incremental_vacuum(100); .check BEGIN DEFERRED; PRAGMA journal_mode = '' || ('WAL'); BEGIN DEFERRED TRANSACTION; BEGIN DEFERRED TRANSACTION; ATTACH DATABASE ':memory:' AS aux13; PRAGMA writable_schema = RESET; PRAGMA checkpoint_fullsync = FALSE; PRAGMA checkpoint_fullsync = YES; .log stderr CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); CREATE VIRTUAL TABLE t2 USING rtree(id,x0,x1); INSERT INTO t1 VALUES(-(-(NULL)),'apple'),(x'25c7be63','fig'),(jsonb_array_insert(CAST(unlikely(x'c4') AS BLOB), '$[0]', typeof(randomblob(-(-(NULL))))),'pear'); INSERT INTO t2 VALUES(json(1),1.0,2.0),(2,2.0,3.0),(3,1.5,3.5); CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN DELETE FROM t2 WHERE +id = OLD.a; eND; ALTER TABLE t1 RENAME TO t3; UPDATE t3 SET b='peach' WHERE +a=2; DETACH DATABASE aux13; ALTER TABLE t1 DROP COLUMN /**/ b; END TRANSACTION; COMMIT; END TRANSACTION; ANALYZE; ANALYZE; .check BEGIN DEFERRED; PRAGMA journal_mode = 'WAL'; BEGIN DEFERRED TRANSACTION; BEGIN DEFERRED TRANSACTION; ATTACH DATABASE ':memory:' AS aux13; PRAGMA writable_schema = RESET; PRAGMA checkpoint_fullsync = FALSE; PRAGMA checkpoint_fullsync = YES; .log stderr CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); CREATE VIRTUAL TABLE t2 USING rtree(id,x0,x1); INSERT INTO t1 VALUES(-(-(NULL)),'apple'),(x'25c7be63','fig'),(jsonb_array_insert(CAST(unlikely(x'c4') AS BLOB), '$[0]', 3),'pear'); INSERT INTO t2 VALUES(1,1.0,2.0),(2,2.0,3.0),(3,1.5,3.5); CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN DELETE FROM t2 WHERE id = OLD.a; END; ALTER TABLE t1 RENAME TO t3; UPDATE t3 SET b='peach' WHERE a=2; DETACH DATABASE aux13; ALTER TABLE t1 DROP COLUMN b; END TRANSACTION; COMMIT; END TRANSACTION; ANALYZE; ANALYZE; .check BEGIN DEFERRED; PRAGMA journal_mode = 'WAL'; BEGIN DEFERRED TRANSACTION; BEGIN DEFERRED TRANSACTION; ATTACH DATABASE ':memory:' AS aux13; PRAGMA writable_schema = RESET; PRAGMA checkpoint_fullsync = FALSE; PRAGMA checkpoint_fullsync = YES; .log stderr CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); CREATE VIRTUAL TABLE t2 USING rtree(id,x0,x1); INSERT INTO t1 VALUES(-(-(NULL)),'apple'),(x'25c7be63','fig'),(jsonb_array_insert(CAST(unlikely(x'c4') AS BLOB), '$[0]', 3),'pear'); INSERT INTO t2 VALUES(1,1.0,2.0),(2,2.0,3.0),(3,1.5,3.5); CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN DELETE FROM t2 WHERE id = OLD.a; END; ALTER TABLE t1 RENAME TO t3; UPDATE t3 SET b='peach' WHERE a=2; DETACH DATABASE aux13; ALTER TABLE t1 DROP COLUMN b; END TRANSACTION; COMMIT; END TRANSACTION; ANALYZE; ANALYZE; .check BEGIN DEFERRED; PRAGMA journal_mode = 'WAL'; BEGIN DEFERRED TRANSACTION; BEGIN DEFERRED TRANSACTION; ATTACH DATABASE ':memory:' AS aux13; PRAGMA writable_schema = RESET; PRAGMA checkpoint_fullsync = FALSE; PRAGMA checkpoint_fullsync = YES; .log stderr CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); CREATE VIRTUAL TABLE t2 USING rtree(id,x0,x1); INSERT INTO t1 VALUES(-(-(NULL)),'apple'),(x'25c7be63','fig'),(jsonb_array_insert(CAST(unlikely(x'c4') AS BLOB), '$[0]', 3),'pear'); INSERT INTO t2 VALUES(1,1.0,2.0),(2,2.0,3.0),(3,1.5,3.5); CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN DELETE FROM t2 WHERE id = OLD.a; END; ALTER TABLE t1 RENAME TO t3; UPDATE t3 SET b='peach' WHERE a=2; DETACH DATABASE aux13; ALTER TABLE t1 DROP COLUMN b; END TRANSACTION; COMMIT; END TRANSACTION; ANALYZE; ANALYZE; .check BEGIN DEFERRED; PRAGMA journal_mode = 'WAL'; BEGIN DEFERRED TRANSACTION; BEGIN DEFERRED TRANSACTION; ATTACH DATABASE ':memory:' AS aux13; PRAGMA writable_schema = RESET; PRAGMA checkpoint_fullsync = FALSE; PRAGMA checkpoint_fullsync = YES; .log stderr CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); CREATE VIRTUAL TABLE t2 USING rtree(id,x0,x1); INSERT INTO t1 VALUES(-(-(NULL)),'apple'),(x'25c7be63','fig'),(jsonb_array_insert(CAST(unlikely(x'c4') AS BLOB), '$[0]', 3),'pear'); INSERT INTO t2 VALUES(1,1.0,2.0),(2,2.0,3.0),(3,1.5,3.5); CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN DELETE FROM t2 WHERE id = OLD.a; END; ALTER TABLE t1 RENAME TO t3; UPDATE t3 SET b='peach' WHERE a=2; DETACH DATABASE aux13; ALTER TABLE t1 DROP COLUMN b; END TRANSACTION; COMMIT; END TRANSACTION; ANALYZE; ANALYZE; .check BEGIN DEFERRED; PRAGMA journal_mode = 'WAL'; BEGIN DEFERRED TRANSACTION; BEGIN DEFERRED TRANSACTION; ATTACH DATABASE ':memory:' AS aux13; PRAGMA writable_schema = RESET; PRAGMA checkpoint_fullsync = FALSE; PRAGMA checkpoint_fullsync = YES; .log stderr CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b); CREATE VIRTUAL TABLE t2 USING rtree(id,x0,x1); INSERT INTO t1 VALUES(-(-(NULL)),'apple'),(x'25c7be63','fig'),(jsonb_array_insert(CAST(unlikely(x'c4') AS BLOB), '$[0]', 3),'pear'); INSERT INTO t2 VALUES(1,1.0,2.0),(2,2.0,3.0),(3,1.5,3.5); CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN DELETE FROM t2 WHERE id = OLD.a; END; ALTER TABLE t1 RENAME TO t3; UPDATE t3 SET b='peach' WHERE a=2; DETACH DATABASE aux13; ALTER TABLE t1 DROP COLUMN b; END TRANSACTION; COMMIT; END TRANSACTION; ANALYZE; ANALYZE; DELETE FROM t1 WHERE a > (SELECT AVG(a) FROM t1) RETURNING *; DROP TRIGGER IF EXISTS t1; INSERT INTO t1 SELECT * FROM t1; SELECT COUNT(*) FROM t1; INSERT INTO t1 SELECT * FROM t1; ALTER TABLE t1 RENAME TO t1_r4426; INSERT INTO t1 SELECT * FROM t1; CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_1529 ON t1(b COLLATE NOCASE); RELEASE sp9896; CREATE TEMPORARY /* printf({v}, {v}) */ TABLE t0(x, y, z); SELECT -99999999999999999999999999999999999999999999999999; SELECT * FROM sqlite_temp_master WHERE sql GLOB '000[]***'; DROP TABLE t0; SELECT MIN(b) FROM t1; WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<0) SELECT * FROM cnt; UPDATE t1 SET a = 'x'; CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT); INSERT INTO t1 VALUES (1, 'Wernher', 10, 100); INSERT INTO t1 VALUES (2, 'von', 20, 200); INSERT INTO t1 VALUES (3, 'Braun', 30, 300); CREATE INDEX t1bc ON t1(b, c); PRAGMA writable_schema = ON; .imposter t1bc t2 SELECT * FROM t2; SELECT b, c FROM t1 ORDER BY b, c; .quit UPDATE t1 SET c = -25 WHERE 1; CREATE TABLE T ( a TEXT, b TEXT ); INSERT INTO T VALUES ('a','2020-01-01'), ('b','2026-01-01'); SELECT a, CAST( (julianday('now')-julianday(b))/365.25 AS INTEGER ) AS c FROM T; SELECT MIN(c) FROM t1; SELECT COUNT(*) FILTER (WHERE a IS NOT NULL), SUM(rowid) FILTER (WHERE a > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(a) FILTER (WHERE a > 0 AND a < 100), COUNT(*) FILTER (WHERE typeof(a) = "text") FROM T; CREATE TRIGGER IF NOT EXISTS trg_t0_6930 AFTER UPDATE OF x ON t0 BEGIN SELECT RAISE(ROLLBACK, 'rb'); END; INSERT INTO t1 DEFAULT VALUES; DROP TABLE IF EXISTS t0; DELETE FROM t0 WHERE 0; ANALYZE; SELECT COUNT(*) FROM T; RELEASE sp8202; PRAGMA foreign_keys = 1; ROLLBACK TO sp375; RELEASE sp375; ALTER TABLE T ADD COLUMN extra_6309 CHARACTER(20) DEFAULT (random()); DETACH DATABASE aux94; COMMIT TRANSACTION; ALTER TABLE T RENAME TO T_r4287; SELECT COUNT(*) FROM T; INSERT OR REPLACE INTO t1 VALUES (-5, '', 0, 10); DELETE FROM t1 WHERE d IS NULL; SELECT COUNT(*) FILTER (WHERE c IS NOT NULL), SUM(rowid) FILTER (WHERE c > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(c) FILTER (WHERE c > 0 AND c < 100), COUNT(*) FILTER (WHERE typeof(c) = "text") FROM t1; DETACH DATABASE aux54; ALTER TABLE T RENAME TO T_r8747; VACUUM; INSERT INTO t1 VALUES (NULL, NULL, NULL, NULL); INSERT INTO T SELECT * FROM T; DELETE FROM t1 WHERE a > (SELECT AVG(a) FROM t1); SELECT MAX(c) FROM t1; PRAGMA parser_trace = 1; ALTER TABLE t1 RENAME TO t1_r9493; SELECT COUNT(*) FROM T; ALTER TABLE t1 RENAME TO t1_r1231; SELECT * FROM t1 WHERE d < ALL (SELECT d FROM t1); WITH cte AS (SELECT * FROM t1) SELECT (SELECT COUNT(*) FROM cte) AS total, * FROM cte LIMIT 1; PRAGMA cell_size_check = OFF; SELECT * FROM T NATURAL JOIN t1; ALTER TABLE T RENAME TO T_r2623; DROP TRIGGER IF EXISTS T; SELECT COUNT(*) FROM t1; SELECT LAG(a, 2, a) OVER (PARTITION BY a ORDER BY a) FROM T; ALTER TABLE t1 RENAME TO t1_r1311; INSERT INTO T SELECT * FROM T; SELECT * FROM T CROSS JOIN T; ``` ## Actual output ```sql Usage: /home/test/sqlite3-src/build/sqlite3 [OPTIONS] FILENAME [SQL] FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist. OPTIONS include: -append append the database to the end of the file -ascii set output mode to 'ascii' -bail stop after hitting an error -batch force batch I/O -box set output mode to 'box' -column set output mode to 'column' -cmd COMMAND run "COMMAND" before reading stdin -csv set output mode to 'csv' -deserialize open the database using sqlite3_deserialize() -echo print commands before execution -init FILENAME read/process named file -[no]header turn headers on or off -help show this message -html set output mode to HTML -interactive force interactive I/O -json set output mode to 'json' -line set output mode to 'line' -list set output mode to 'list' -lookaside SIZE N use N entries of SZ bytes for lookaside memory -markdown set output mode to 'markdown' -maxsize N maximum size for a --deserialize database -memtrace trace all memory allocations and deallocations -mmap N default mmap size set to N -newline SEP set output row separator. Default: '\n' -nofollow refuse to open symbolic links to database files -nonce STRING set the safe-mode escape nonce -nullvalue TEXT set text string for NULL values. Default '' -pagecache SIZE N use N slots of SZ bytes each for page cache memory -quote set output mode to 'quote' -readonly open the database read-only -safe enable safe-mode -separator SEP set output column separator. Default: '|' -stats print memory stats before each finalize -table set output mode to 'table' -tabs set output mode to 'tabs' -version show SQLite version -vfs NAME use NAME as the default VFS ``` ## Expectation ```sql ``` ## Flag ``` -help ```