ast-project/part1/results/2/bug_5182c3f9-9524-44c9-9314-d5e7e3715aff_unexpected_error.md at main

admin/ast-project

Fork 0

Files

T

admin fd930e15cb ---

2026-06-24 13:47:14 +02:00

28 KiB

Raw Permalink Blame History

Summary

**No review yet**

Minimized query

.echo on
CREATE TABLE T (
  A VARCHAR(20),
  B VARCHAR(30),
  C VARCHAR(30),
  PRIMARY KEY (A, B),
  UNIQUE (A, C)
);
-- max({v}, {v})

INSERT INTO T VALUES ('a', 'p', 'x');
INSERT INTO T VALUES ('a', 'q', 'y');
INSERT INTO T VALUES ('b', 'p', 'z');
INSERT INTO T VALUES ('c', 'r', 'w');
INSERT INTO T VALUES ('d', 's', 'v');
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
PRAGMA automatic_index = 0;
ATTACH DATABASE ':memory:' AS aux61;
.log OFF
ATTACH DATABASE ':memory:' AS aux10;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -atanh(1785583442693606558), 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -9223372036854775808, 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -9223372036854775808, 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -9223372036854775808, 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -9223372036854775808, 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -9223372036854775808, 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -9223372036854775808, 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -9223372036854775808, 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), 'Wernher', -9223372036854775808, 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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
INSERT INTO t1 VALUES (unistr(NULL), '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
PRAGMA reverse_unordered_selects = YES;
CREATE TABLE t1(n int, log int);
BEGIN;
INSERT INTO /**/ t1 VALUES(like('%2%', 1.7976931348623157e+308, '%'), 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);
INSERT INTO t1 VALUES(4, 3);
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1;
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log;
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y);
SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log;
SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0;


DELETE FROM t1 WHERE NOT NOT (1) ;
ANALYZE;
VACUUM main;
WITH cte(x) AS (VALUES(1),(2),(3)) SELECT * FROM cte;
CREATE  VIEW IF NOT EXISTS v_t1_1308 AS SELECT n FROM t1;
CREATE  INDEX IF NOT EXISTS idx_t1_3077 ON t1(log);

CREATE  INDEX IF NOT EXISTS idx_t1_5776 ON t1((log + 1)) WHERE log IS NOT NULL;
SELECT COUNT(*) FILTER (WHERE log IS NOT NULL), SUM(rowid) FILTER (WHERE log > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(log) FILTER (WHERE log > 0 AND log < 100), COUNT(*) FILTER (WHERE typeof(log) = "text") FROM t1;

ALTER TABLE t1 RENAME COLUMN log TO log_r7139;
CREATE TRIGGER IF NOT EXISTS trg_t1_3483 AFTER UPDATE OF n ON t1 FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'no'); END;
CREATE TRIGGER IF NOT EXISTS trg_t1_7296 AFTER INSERT ON t1 BEGIN INSERT INTO t1(log) VALUES (NULL); END;
DETACH DATABASE aux10;
SELECT n, (SELECT SUM(n) FROM t1) AS sum_val FROM t1;
SELECT * FROM t1 AS a RIGHT JOIN t1 AS b ON a.rowid = b.rowid;
CREATE UNIQUE  INDEX IF NOT EXISTS idx_t1_4982 ON t1(log) WHERE log > 0;
SELECT log, (SELECT MAX(log) FROM t1) AS max_val FROM t1;
SELECT * FROM t1;
DETACH DATABASE aux61;
INSERT INTO t1 VALUES (NULL, NULL);
INSERT INTO t1 VALUES (NULL, NULL) ON CONFLICT(n) DO UPDATE SET n = excluded.n, log = excluded.log;
ALTER TABLE t1 ADD COLUMN extra_5625 BIGINT UNIQUE;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 DEFAULT VALUES;
SELECT COUNT(*) FILTER (WHERE n IS NOT NULL), SUM(rowid) FILTER (WHERE n > 0), COUNT(*) FILTER (WHERE 1=0), COUNT(*) FILTER (WHERE 1=1), COUNT(*) FILTER (WHERE NULL), AVG(n) FILTER (WHERE n > 0 AND n < 100), COUNT(*) FILTER (WHERE typeof(n) = "text") FROM t1;
ALTER TABLE t1 RENAME COLUMN n TO n_r3718;
SELECT * FROM t1 NATURAL JOIN t1;
VACUUM main;
CREATE  INDEX IF NOT EXISTS idx_T_304 ON T(A COLLATE NOCASE) WHERE A IS NOT NULL;
SELECT * FROM t1 AS a JOIN T AS b ON a.rowid = b.rowid;

Actual output

CREATE TABLE T (
  A VARCHAR(20),
  B VARCHAR(30),
  C VARCHAR(30),
  PRIMARY KEY (A, B),
  UNIQUE (A, C)
);
-- max({v}, {v})

INSERT INTO T VALUES ('a', 'p', 'x');
INSERT INTO T VALUES ('a', 'q', 'y');
INSERT INTO T VALUES ('b', 'p', 'z');
INSERT INTO T VALUES ('c', 'r', 'w');
INSERT INTO T VALUES ('d', 's', 'v');
SELECT A, COUNT(DISTINCT B) AS D FROM T GROUP BY A HAVING COUNT(DISTINCT B) >= 1;
a|2
b|1
c|1
d|1
PRAGMA automatic_index = 0;
ATTACH DATABASE ':memory:' AS aux61;
.log OFF
ATTACH DATABASE ':memory:' AS aux10;
PRAGMA vdbe_addoptrace = TRUE;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
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
CREATE TABLE "t2"("b","c","_ROWID_",PRIMARY KEY("b","c","_ROWID_"))WITHOUT ROWID;
WARNING: writing to an imposter table will corrupt the "t1bc" index!

SELECT * FROM t2;
Braun|30|3
von|20|2

SELECT b, c FROM t1 ORDER BY b, c;
Braun|30
von|20

.quit

28 KiB

Raw Permalink Blame History

Summary

Minimized query

Actual output

Expectation

Flag

28 KiB Raw Permalink Blame History

Summary

Minimized query

Actual output

Expectation

Flag

28 KiB

Raw Permalink Blame History