184 lines
5.3 KiB
Markdown
184 lines
5.3 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
|
|
BEGIN DEFERRED;
|
|
PRAGMA cache_spill = ON;
|
|
ATTACH DATABASE ':memory:' AS aux25;
|
|
PRAGMA journal_mode;
|
|
PRAGMA trusted_schema = YES;
|
|
PRAGMA encoding = 'UTF-16be';
|
|
PRAGMA defer_foreign_keys = NO;
|
|
PRAGMA trusted_schema = TRUE;
|
|
.fullschema
|
|
CREATE TABLE A(Name text);
|
|
CREATE TABLE Items(ItemName text , Name text);
|
|
INSERT INTO Items /* -maxsize 5 */ VALUES('Item1','Parent');
|
|
INSERT INTO Items VALUES('Item2','Parent');
|
|
CREATE TABLE B(Name text);
|
|
SELECT Items.ItemName FROM Items LEFT OUTER JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy') LEFT JOIN B ON (B.Name = Items.ItemName) WHERE NOT NOT (Items.Name = 'Parent') ORDER BY Items.ItemName;
|
|
|
|
PRAGMA compile_options;
|
|
DELETE FROM A WHERE Name > (SELECT AVG(Name) FROM A);
|
|
REINDEX;
|
|
SELECT AVG(Name) FILTER (WHERE Name != CAST(-(-(-10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000)) AS TEXT)) OVER (ORDER BY Name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) FROM B;
|
|
CREATE TABLE T (
|
|
A VARCHAR(20),
|
|
B VARCHAR(10),
|
|
C /**/ INTEGER,
|
|
PRIMARY KEY (A, B)
|
|
);
|
|
INSERT INTO T VALUES ('a', 'p', -2147483648);
|
|
INSERT INTO T VALUES ('b', 'q', 100);
|
|
INSERT INTO T VALUES ('b', 'r', 200);
|
|
INSERT INTO T VALUES ('c', 's', 300);
|
|
INSERT INTO T VALUES ('d', 't', 2147483647);
|
|
INSERT INTO T VALUES ('b', 't', 50);
|
|
SELECT A, COUNT(*) AS D, SUM(C) AS E FROM T GROUP BY A HAVING COUNT(*) > 1;
|
|
SELECT * FROM B;
|
|
SELECT * FROM B AS a LEFT OUTER JOIN A AS b ON a.rowid = b.rowid;
|
|
WITH cte AS (SELECT * FROM A) SELECT * FROM cte;
|
|
DETACH DATABASE aux25;
|
|
SELECT LEAD(Name, 2, 'default') OVER (PARTITION BY Name ORDER BY Name RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM A;
|
|
|
|
SELECT COUNT(*) FROM A;
|
|
|
|
SELECT COUNT(*) FROM Items;
|
|
SELECT COUNT(Name) FILTER (WHERE NULL) OVER (ORDER BY Name GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM B;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_A_5218 ON A(Name) WHERE Name IS NOT NULL;
|
|
COMMIT TRANSACTION;
|
|
CREATE VIEW IF NOT EXISTS v_B_962 AS SELECT Name FROM B;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
memory
|
|
/* No STAT tables available */
|
|
Item1
|
|
Item2
|
|
ATOMIC_INTRINSICS=1
|
|
COMPILER=gcc-14.2.0
|
|
DEFAULT_AUTOVACUUM
|
|
DEFAULT_CACHE_SIZE=-2000
|
|
DEFAULT_FILE_FORMAT=4
|
|
DEFAULT_JOURNAL_SIZE_LIMIT=-1
|
|
DEFAULT_MMAP_SIZE=0
|
|
DEFAULT_PAGE_SIZE=4096
|
|
DEFAULT_PCACHE_INITSZ=20
|
|
DEFAULT_RECURSIVE_TRIGGERS
|
|
DEFAULT_SECTOR_SIZE=4096
|
|
DEFAULT_SYNCHRONOUS=2
|
|
DEFAULT_WAL_AUTOCHECKPOINT=1000
|
|
DEFAULT_WAL_SYNCHRONOUS=2
|
|
DEFAULT_WORKER_THREADS=0
|
|
ENABLE_BYTECODE_VTAB
|
|
ENABLE_DBPAGE_VTAB
|
|
ENABLE_DBSTAT_VTAB
|
|
ENABLE_EXPLAIN_COMMENTS
|
|
ENABLE_FTS3
|
|
ENABLE_FTS4
|
|
ENABLE_MATH_FUNCTIONS
|
|
ENABLE_OFFSET_SQL_FUNC
|
|
ENABLE_RTREE
|
|
ENABLE_STMTVTAB
|
|
ENABLE_UNKNOWN_SQL_FUNCTION
|
|
HAVE_ISNAN
|
|
MALLOC_SOFT_LIMIT=1024
|
|
MAX_ATTACHED=10
|
|
MAX_COLUMN=2000
|
|
MAX_COMPOUND_SELECT=500
|
|
MAX_DEFAULT_PAGE_SIZE=8192
|
|
MAX_EXPR_DEPTH=1000
|
|
MAX_FUNCTION_ARG=127
|
|
MAX_LENGTH=1000000000
|
|
MAX_LIKE_PATTERN_LENGTH=50000
|
|
MAX_MMAP_SIZE=0x7fff0000
|
|
MAX_PAGE_COUNT=1073741823
|
|
MAX_PAGE_SIZE=65536
|
|
MAX_SQL_LENGTH=1000000000
|
|
MAX_TRIGGER_DEPTH=1000
|
|
MAX_VARIABLE_NUMBER=32766
|
|
MAX_VDBE_OP=250000000
|
|
MAX_WORKER_THREADS=8
|
|
MUTEX_PTHREADS
|
|
SYSTEM_MALLOC
|
|
TEMP_STORE=1
|
|
THREADSAFE=1
|
|
b|3|350
|
|
0
|
|
2
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
memory
|
|
/* No STAT tables available */
|
|
Item1
|
|
Item2
|
|
ATOMIC_INTRINSICS=1
|
|
COMPILER=gcc-11.4.0
|
|
DEFAULT_AUTOVACUUM
|
|
DEFAULT_CACHE_SIZE=-2000
|
|
DEFAULT_FILE_FORMAT=4
|
|
DEFAULT_JOURNAL_SIZE_LIMIT=-1
|
|
DEFAULT_MMAP_SIZE=0
|
|
DEFAULT_PAGE_SIZE=4096
|
|
DEFAULT_PCACHE_INITSZ=20
|
|
DEFAULT_RECURSIVE_TRIGGERS
|
|
DEFAULT_SECTOR_SIZE=4096
|
|
DEFAULT_SYNCHRONOUS=2
|
|
DEFAULT_WAL_AUTOCHECKPOINT=1000
|
|
DEFAULT_WAL_SYNCHRONOUS=2
|
|
DEFAULT_WORKER_THREADS=0
|
|
DIRECT_OVERFLOW_READ
|
|
DQS=0
|
|
ENABLE_BYTECODE_VTAB
|
|
ENABLE_DBPAGE_VTAB
|
|
ENABLE_DBSTAT_VTAB
|
|
ENABLE_EXPLAIN_COMMENTS
|
|
ENABLE_FTS3
|
|
ENABLE_FTS4
|
|
ENABLE_MATH_FUNCTIONS
|
|
ENABLE_OFFSET_SQL_FUNC
|
|
ENABLE_PERCENTILE
|
|
ENABLE_RTREE
|
|
ENABLE_STMTVTAB
|
|
ENABLE_UNKNOWN_SQL_FUNCTION
|
|
HAVE_ISNAN
|
|
MALLOC_SOFT_LIMIT=1024
|
|
MAX_ATTACHED=10
|
|
MAX_COLUMN=2000
|
|
MAX_COMPOUND_SELECT=500
|
|
MAX_DEFAULT_PAGE_SIZE=8192
|
|
MAX_EXPR_DEPTH=1000
|
|
MAX_FUNCTION_ARG=1000
|
|
MAX_LENGTH=1000000000
|
|
MAX_LIKE_PATTERN_LENGTH=50000
|
|
MAX_MMAP_SIZE=0x7fff0000
|
|
MAX_PAGE_COUNT=0xfffffffe
|
|
MAX_PAGE_SIZE=65536
|
|
MAX_SQL_LENGTH=1000000000
|
|
MAX_TRIGGER_DEPTH=1000
|
|
MAX_VARIABLE_NUMBER=32766
|
|
MAX_VDBE_OP=250000000
|
|
MAX_WORKER_THREADS=8
|
|
MUTEX_PTHREADS
|
|
SYSTEM_MALLOC
|
|
TEMP_STORE=1
|
|
THREADSAFE=1
|
|
b|3|350
|
|
0
|
|
2
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
|
|
```
|
|
|