diff options
Diffstat (limited to 'test')
| -rw-r--r-- | test/join.test | 27 | ||||
| -rw-r--r-- | test/walcksum.test | 148 |
2 files changed, 175 insertions, 0 deletions
diff --git a/test/join.test b/test/join.test index ef2f6335c..b33a7560a 100644 --- a/test/join.test +++ b/test/join.test @@ -1342,4 +1342,31 @@ do_execsql_test join-31.8 { SELECT * FROM t3 LEFT JOIN t2 ON true JOIN t4 ON true NATURAL LEFT JOIN t1; } {3 NULL 4 NULL} +# 2025-06-16 https://sqlite.org/forum/forumpost/68f29a2005 +# +# The transitive-constraint optimization was not working for RIGHT JOIN. +# +reset_db +db null NULL +do_execsql_test join-32.1 { + CREATE TABLE t0(w INT); + CREATE TABLE t1(x INT); + CREATE TABLE t2(y INT UNIQUE); + CREATE VIEW v0(z) AS SELECT CAST(x AS INT) FROM t1 LEFT JOIN t2 ON true; + INSERT INTO t1(x) VALUES(123); + INSERT INTO t2(y) VALUES(NULL); +} +do_execsql_test join-32.2 { + SELECT * + FROM t0 JOIN v0 ON w=z + RIGHT JOIN t1 ON true + INNER JOIN t2 ON y IS z; +} {NULL NULL 123 NULL} +do_execsql_test join-32.3 { + SELECT * + FROM t0 JOIN v0 ON w=z + RIGHT JOIN t1 ON true + INNER JOIN t2 ON +y IS z; +} {NULL NULL 123 NULL} + finish_test diff --git a/test/walcksum.test b/test/walcksum.test index 10329ba6c..0c9a7e55c 100644 --- a/test/walcksum.test +++ b/test/walcksum.test @@ -16,6 +16,7 @@ source $testdir/lock_common.tcl source $testdir/wal_common.tcl ifcapable !wal {finish_test ; return } +set testprefix walcksum # Read and return the contents of file $filename. Treat the content as # binary data. @@ -331,5 +332,152 @@ do_test walcksum-2.1 { catch { db close } catch { db2 close } +#------------------------------------------------------------------------- +# Test cases based on the bug reported at: +# +# <https://sqlite.org/forum/forumpost/b490f726db> +# +reset_db + +do_execsql_test 3.0 { + PRAGMA auto_vacuum = 0; + PRAGMA synchronous = NORMAL; + PRAGMA journal_mode = WAL; + PRAGMA cache_size = 1; + + CREATE TABLE t1 (i INTEGER PRIMARY KEY, b BLOB, t TEXT); + PRAGMA wal_checkpoint; + INSERT INTO t1 VALUES(1, randomblob(2048), 'one'); +} {wal 0 2 2} + +do_execsql_test 3.1 { + BEGIN; + INSERT INTO t1 VALUES(2, randomblob(2048), 'two'); + SAVEPOINT one; + INSERT INTO t1 VALUES(3, randomblob(2048), 'three'); + INSERT INTO t1 VALUES(4, randomblob(2048), 'four'); + INSERT INTO t1 VALUES(5, randomblob(2048), 'five'); + INSERT INTO t1 VALUES(6, randomblob(2048), 'six'); + INSERT INTO t1 VALUES(7, randomblob(2048), 'seven'); + + UPDATE t1 SET b=randomblob(2048) WHERE i=5; + UPDATE t1 SET b=randomblob(2048) WHERE i=6; + UPDATE t1 SET b=randomblob(2048) WHERE i=7; + ROLLBACK TO one; + INSERT INTO t1 VALUES(8, NULL, 'eight'); + COMMIT; +} {} + +do_execsql_test 3.2 { + SELECT i, t FROM t1 +} {1 one 2 two 8 eight} + +forcecopy test.db test2.db +forcecopy test.db-wal test2.db-wal + +sqlite3 db2 test2.db +do_test 1.3 { + execsql { + SELECT i, t FROM t1 + } db2 +} {1 one 2 two 8 eight} + +catch { db2 close } + +#------------------------------------------------------------------------- +reset_db + +do_execsql_test 4.0 { + PRAGMA auto_vacuum = 0; + PRAGMA synchronous = NORMAL; + PRAGMA journal_mode = WAL; + PRAGMA cache_size = 1; + + CREATE TABLE t1 (i INTEGER PRIMARY KEY, b BLOB, t TEXT); + PRAGMA wal_checkpoint; + INSERT INTO t1 VALUES(1, randomblob(2048), 'one'); +} {wal 0 2 2} + +do_execsql_test 4.1.1 { + SAVEPOINT one; + INSERT INTO t1 VALUES(2, randomblob(2048), 'two'); + INSERT INTO t1 VALUES(3, randomblob(2048), 'three'); + INSERT INTO t1 VALUES(4, randomblob(2048), 'four'); + INSERT INTO t1 VALUES(5, randomblob(2048), 'five'); + INSERT INTO t1 VALUES(6, randomblob(2048), 'six'); + INSERT INTO t1 VALUES(7, randomblob(2048), 'seven'); + + UPDATE t1 SET b=randomblob(2048) WHERE i=5; + UPDATE t1 SET b=randomblob(2048) WHERE i=6; + UPDATE t1 SET b=randomblob(2048) WHERE i=7; +} + +do_execsql_test 4.1.2 { + ROLLBACK TO one; + INSERT INTO t1 VALUES(8, NULL, 'eight'); + RELEASE one; +} {} + +do_execsql_test 4.2 { + SELECT i, t FROM t1 +} {1 one 8 eight} + +forcecopy test.db test2.db +forcecopy test.db-wal test2.db-wal + +sqlite3 db2 test2.db +do_test 4.3 { + execsql { + SELECT i, t FROM t1 + } db2 +} {1 one 8 eight} + +catch { db2 close } + +#------------------------------------------------------------------------- +reset_db + +do_execsql_test 5.0 { + PRAGMA auto_vacuum = 0; + PRAGMA synchronous = NORMAL; + PRAGMA journal_mode = WAL; + PRAGMA cache_size = 1; + + CREATE TABLE t1 (i INTEGER PRIMARY KEY, b BLOB, t TEXT); + INSERT INTO t1 VALUES(1, randomblob(2048), 'one'); + INSERT INTO t1 VALUES(2, randomblob(2048), 'two'); + INSERT INTO t1 VALUES(3, randomblob(2048), 'three'); + PRAGMA wal_checkpoint; +} {wal 0 14 14} + +do_execsql_test 5.1 { + BEGIN; + SELECT count(*) FROM t1; + SAVEPOINT one; + INSERT INTO t1 VALUES(4, randomblob(2048), 'four'); + INSERT INTO t1 VALUES(5, randomblob(2048), 'five'); + INSERT INTO t1 VALUES(6, randomblob(2048), 'six'); + INSERT INTO t1 VALUES(7, randomblob(2048), 'seven'); + ROLLBACK TO one; + INSERT INTO t1 VALUES(8, randomblob(2048), 'eight'); + INSERT INTO t1 VALUES(9, randomblob(2048), 'nine'); + COMMIT; +} {3} + +forcecopy test.db test2.db +forcecopy test.db-wal test2.db-wal + +sqlite3 db2 test2.db +do_test 5.2 { + execsql { + SELECT i, t FROM t1 + } db2 +} {1 one 2 two 3 three 8 eight 9 nine} +db2 close + +do_execsql_test 5.3 { + SELECT i, t FROM t1 +} {1 one 2 two 3 three 8 eight 9 nine} + finish_test |