diff options
Diffstat (limited to 'test')
-rw-r--r-- | test/dblwidth-a.sql | 20 | ||||
-rw-r--r-- | test/fts3join.test | 5 | ||||
-rw-r--r-- | test/join.test | 22 | ||||
-rw-r--r-- | test/joinH.test | 70 | ||||
-rw-r--r-- | test/snapshot3.test | 3 | ||||
-rw-r--r-- | test/tclsqlite.test | 153 | ||||
-rw-r--r-- | test/vacuum.test | 21 | ||||
-rw-r--r-- | test/walsetlk_recover.test | 104 | ||||
-rw-r--r-- | test/walsetlk_snapshot.test | 109 |
9 files changed, 452 insertions, 55 deletions
diff --git a/test/dblwidth-a.sql b/test/dblwidth-a.sql new file mode 100644 index 000000000..38c219698 --- /dev/null +++ b/test/dblwidth-a.sql @@ -0,0 +1,20 @@ +/* +** Run this script using "sqlite3" to confirm that the command-line +** shell properly handles the output of double-width characters. +** +** https://sqlite.org/forum/forumpost/008ac80276 +*/ +.mode box +CREATE TABLE data(word TEXT, description TEXT); +INSERT INTO data VALUES('〈οὐκέτι〉','Greek without dblwidth <...>'); +.print .mode box +SELECT * FROM data; +.mode table +.print .mode table +SELECT * FROM data; +.mode qbox +.print .mode qbox +SELECT * FROM data; +.mode column +.print .mode column +SELECT * FROM data; diff --git a/test/fts3join.test b/test/fts3join.test index cbd08b63f..9171c817b 100644 --- a/test/fts3join.test +++ b/test/fts3join.test @@ -97,11 +97,8 @@ do_eqp_test 4.2 { WHERE t4.y = ?; } { QUERY PLAN - |--MATERIALIZE rr - | `--SCAN ft4 VIRTUAL TABLE INDEX 3: |--SCAN t4 - |--BLOOM FILTER ON rr (docid=?) - `--SEARCH rr USING AUTOMATIC COVERING INDEX (docid=?) LEFT-JOIN + `--SCAN ft4 VIRTUAL TABLE INDEX 3: LEFT-JOIN } finish_test diff --git a/test/join.test b/test/join.test index 789ae0124..ef2f6335c 100644 --- a/test/join.test +++ b/test/join.test @@ -1316,20 +1316,30 @@ do_execsql_test join-31.1 { CREATE TABLE t1(c0 INT , c1 INT); INSERT INTO t1(c0, c1) VALUES(NULL,11); CREATE TABLE t2(c0 INT NOT NULL); CREATE TABLE t2n(c0 INT); - CREATE TABLE t3(x INT); INSERT INTO t3(x) VALUES(4); - CREATE TABLE t5(c0 INT, x INT); INSERT INTO t5 VALUES(NULL, 4); + CREATE TABLE t3(x INT); INSERT INTO t3(x) VALUES(3); + CREATE TABLE t4(y INT); INSERT INTO t4(y) VALUES(4); + CREATE TABLE t5(c0 INT, x INT); INSERT INTO t5 VALUES(NULL, 5); } do_execsql_test join-31.2 { SELECT * FROM t2 RIGHT JOIN t3 ON true LEFT JOIN t1 USING(c0); -} {NULL 4 NULL} +} {NULL 3 NULL} do_execsql_test join-31.3 { SELECT * FROM t2 RIGHT JOIN t3 ON true NATURAL LEFT JOIN t1; -} {NULL 4 NULL} +} {NULL 3 NULL} do_execsql_test join-31.4 { SELECT * FROM t2n RIGHT JOIN t3 ON true LEFT JOIN t1 USING(c0); -} {NULL 4 NULL} +} {NULL 3 NULL} do_execsql_test join-31.5 { SELECT * FROM t5 LEFT JOIN t1 USING(c0); -} {NULL 4 NULL} +} {NULL 5 NULL} +do_execsql_test join-31.6 { + SELECT * FROM t3 LEFT JOIN t2 ON true LEFT JOIN t1 USING(c0); +} {3 NULL NULL} +do_execsql_test join-31.7 { + SELECT * FROM t3 LEFT JOIN t2 ON true NATURAL LEFT JOIN t1; +} {3 NULL NULL} +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} finish_test diff --git a/test/joinH.test b/test/joinH.test index 908b93dee..339200968 100644 --- a/test/joinH.test +++ b/test/joinH.test @@ -341,4 +341,74 @@ do_execsql_test 13.4 { GROUP BY a1.a ORDER BY 1; } {-1480 240 480} +#------------------------------------------------------------------------- +# 2025-05-30 +# https://sqlite.org/forum/forumpost/5028c785b6 +# +reset_db + +do_execsql_test 14.0 { + CREATE TABLE t1(c0 INT); + CREATE TABLE t2(c0 BLOB); + CREATE TABLE t3(c0 BLOB); + CREATE TABLE t4(c4 BLOB); + INSERT INTO t1(c0) VALUES(0); + INSERT INTO t3(c0) VALUES('0'); +} + +do_execsql_test 14.1.1 { + SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3; +} {0} + +do_execsql_test 14.1.2 { + SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3 FULL JOIN t4 ON true; +} {0 {}} + +do_execsql_test 14.1.3 { + SELECT * FROM (t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3) FULL JOIN t4 ON true; +} {0 {}} + +do_execsql_test 14.1.4 { + SELECT * + FROM (t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3) AS qq FULL JOIN t4 ON true; +} {0 {}} + +do_execsql_test 14.2.1 { + SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1; +} {0} + +do_execsql_test 14.2.2 { + SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1 FULL JOIN t4 ON true; +} {0 {}} + +do_execsql_test 14.2.3 { + SELECT * FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) FULL JOIN t4 ON true; +} {0 {}} + +do_execsql_test 14.2.4 { + SELECT * + FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) AS qq FULL JOIN t4 ON true; +} {0 {}} + +# 2025-06-01 +# +reset_db +do_execsql_test 15.1 { + CREATE TABLE t0(c0); + CREATE TABLE t1(c0); + CREATE TABLE t2(c0); + INSERT INTO t0 VALUES ('1.0'); + INSERT INTO t2(c0) VALUES (9); + SELECT t0.c0,t2.c0 FROM (SELECT CAST(t0.c0 as REAL) AS c0 FROM t0) as subquery NATURAL LEFT JOIN t1 NATURAL JOIN t0 RIGHT JOIN t2 ON 1; +} {1.0 9} +do_execsql_test 15.2 { + CREATE TABLE x1(x COLLATE nocase); + CREATE TABLE x2(x); + CREATE TABLE x3(x); + CREATE TABLE t4(y); + INSERT INTO x1 VALUES('ABC'); + INSERT INTO x3 VALUES('abc'); + SELECT lower(x), quote(y) FROM x1 LEFT JOIN x2 USING (x) JOIN x3 USING (x) FULL JOIN t4; +} {abc NULL} + finish_test diff --git a/test/snapshot3.test b/test/snapshot3.test index 470d463a6..6d57b1d0c 100644 --- a/test/snapshot3.test +++ b/test/snapshot3.test @@ -96,6 +96,9 @@ do_test 1.8 { list [catch { sqlite3_snapshot_open_blob db3 main $snap } msg] $msg } {1 SQLITE_ERROR_SNAPSHOT} +db3 close +db2 close + #------------------------------------------------------------------------- reset_db do_execsql_test 2.0 { diff --git a/test/tclsqlite.test b/test/tclsqlite.test index 0758abd82..5f373ea18 100644 --- a/test/tclsqlite.test +++ b/test/tclsqlite.test @@ -9,7 +9,7 @@ # #*********************************************************************** # This file implements regression tests for TCL interface to the -# SQLite library. +# SQLite library. # # Actually, all tests are based on the TCL interface, so the main # interface is pretty well tested. This file contains some addition @@ -121,7 +121,7 @@ ifcapable {complete} { do_test tcl-1.14 { set v [catch {db eval} msg] lappend v $msg -} {1 {wrong # args: should be "db eval ?OPTIONS? SQL ?ARRAY-NAME? ?SCRIPT?"}} +} {1 {wrong # args: should be "db eval ?OPTIONS? SQL ?VAR-NAME? ?SCRIPT?"}} do_test tcl-1.15 { set v [catch {db function} msg] lappend v $msg @@ -359,6 +359,19 @@ do_test tcl-9.3 { execsql {SELECT typeof(ret_int())} } {integer} +proc breakAsNullUdf args { + if {"1" eq [lindex $args 0]} {return -code break} +} +do_test tcl-9.4 { + db function banu breakAsNullUdf + execsql {SELECT typeof(banu()), typeof(banu(1))} +} {text null} +do_test tcl-9.5 { + db nullvalue banunull + db eval {SELECT banu(), banu(1)} +} {{} banunull} + + # Recursive calls to the same user-defined function # ifcapable tclvar { @@ -465,7 +478,7 @@ do_test tcl-10.13 { db eval {SELECT * FROM t4} } {1 2 5 6 7} -# Now test that [db transaction] commands may be nested with +# Now test that [db transaction] commands may be nested with # the expected results. # do_test tcl-10.14 { @@ -475,7 +488,7 @@ do_test tcl-10.14 { INSERT INTO t4 VALUES('one'); } - catch { + catch { db transaction { db eval { INSERT INTO t4 VALUES('two') } db transaction { @@ -674,11 +687,11 @@ do_test tcl-15.5 { } {0} -# 2017-06-26: The --withoutnulls flag to "db eval". +# 2017-06-26: The -withoutnulls flag to "db eval". # -# In the "db eval --withoutnulls SQL ARRAY" form, NULL results cause the -# corresponding array entry to be unset. The default behavior (without -# the -withoutnulls flags) is for the corresponding array value to get +# In the "db eval -withoutnulls SQL TARGET" form, NULL results cause the +# corresponding target entry to be unset. The default behavior (without +# the -withoutnulls flags) is for the corresponding target value to get # the [db nullvalue] string. # catch {db close} @@ -720,64 +733,64 @@ reset_db proc add {a b} { return [expr $a + $b] } proc ret {a} { return $a } -db function add_i -returntype integer add +db function add_i -returntype integer add db function add_r -ret real add -db function add_t -return text add -db function add_b -returntype blob add -db function add_a -returntype any add +db function add_t -return text add +db function add_b -returntype blob add +db function add_a -returntype any add -db function ret_i -returntype int ret +db function ret_i -returntype int ret db function ret_r -returntype real ret -db function ret_t -returntype text ret -db function ret_b -returntype blob ret -db function ret_a -r any ret +db function ret_t -returntype text ret +db function ret_b -returntype blob ret +db function ret_a -r any ret do_execsql_test 17.0 { SELECT quote( add_i(2, 3) ); - SELECT quote( add_r(2, 3) ); - SELECT quote( add_t(2, 3) ); - SELECT quote( add_b(2, 3) ); - SELECT quote( add_a(2, 3) ); + SELECT quote( add_r(2, 3) ); + SELECT quote( add_t(2, 3) ); + SELECT quote( add_b(2, 3) ); + SELECT quote( add_a(2, 3) ); } {5 5.0 '5' X'35' 5} do_execsql_test 17.1 { SELECT quote( add_i(2.2, 3.3) ); - SELECT quote( add_r(2.2, 3.3) ); - SELECT quote( add_t(2.2, 3.3) ); - SELECT quote( add_b(2.2, 3.3) ); - SELECT quote( add_a(2.2, 3.3) ); + SELECT quote( add_r(2.2, 3.3) ); + SELECT quote( add_t(2.2, 3.3) ); + SELECT quote( add_b(2.2, 3.3) ); + SELECT quote( add_a(2.2, 3.3) ); } {5.5 5.5 '5.5' X'352E35' 5.5} do_execsql_test 17.2 { SELECT quote( ret_i(2.5) ); - SELECT quote( ret_r(2.5) ); - SELECT quote( ret_t(2.5) ); - SELECT quote( ret_b(2.5) ); - SELECT quote( ret_a(2.5) ); + SELECT quote( ret_r(2.5) ); + SELECT quote( ret_t(2.5) ); + SELECT quote( ret_b(2.5) ); + SELECT quote( ret_a(2.5) ); } {2.5 2.5 '2.5' X'322E35' 2.5} do_execsql_test 17.3 { SELECT quote( ret_i('2.5') ); - SELECT quote( ret_r('2.5') ); - SELECT quote( ret_t('2.5') ); - SELECT quote( ret_b('2.5') ); - SELECT quote( ret_a('2.5') ); + SELECT quote( ret_r('2.5') ); + SELECT quote( ret_t('2.5') ); + SELECT quote( ret_b('2.5') ); + SELECT quote( ret_a('2.5') ); } {2.5 2.5 '2.5' X'322E35' '2.5'} do_execsql_test 17.4 { SELECT quote( ret_i('abc') ); - SELECT quote( ret_r('abc') ); - SELECT quote( ret_t('abc') ); - SELECT quote( ret_b('abc') ); - SELECT quote( ret_a('abc') ); + SELECT quote( ret_r('abc') ); + SELECT quote( ret_t('abc') ); + SELECT quote( ret_b('abc') ); + SELECT quote( ret_a('abc') ); } {'abc' 'abc' 'abc' X'616263' 'abc'} do_execsql_test 17.5 { SELECT quote( ret_i(X'616263') ); - SELECT quote( ret_r(X'616263') ); - SELECT quote( ret_t(X'616263') ); - SELECT quote( ret_b(X'616263') ); - SELECT quote( ret_a(X'616263') ); + SELECT quote( ret_r(X'616263') ); + SELECT quote( ret_t(X'616263') ); + SELECT quote( ret_b(X'616263') ); + SELECT quote( ret_a(X'616263') ); } {'abc' 'abc' 'abc' X'616263' X'616263'} do_test 17.6.1 { @@ -848,21 +861,70 @@ do_catchsql_test 19.911 { } {1 {invalid command name "bind_fallback_does_not_exist"}} db bind_fallback {} -#------------------------------------------------------------------------- +# 2025-05-05: the -asdict eval flag +# do_test 20.0 { + execsql {CREATE TABLE tad(a,b)} + execsql {INSERT INTO tad(a,b) VALUES('aa','bb'),('AA','BB')} + db eval -asdict { + SELECT a, b FROM tad WHERE 0 + } D {} + set D +} {* {a b}} + +do_test 20.1 { + unset D + set i 0 + set res {} + set colNames {} + db eval -asdict { + SELECT a, b FROM tad ORDER BY a + } D { + dict set D i [incr i] + lappend res $i [dict get $D a] [dict get $D b] + if {1 == $i} { + set colNames [dict get $D *] + } + } + lappend res $colNames + unset D + set res +} {1 AA BB 2 aa bb {a b}} + +do_test 20.2 { + set res {} + db eval -asdict -withoutnulls { + SELECT n, a, b FROM ( + SELECT 1 as n, 'aa' as a, NULL as b + UNION ALL + SELECT 2 as n, NULL as a, 'bb' as b + ) + ORDER BY n + } D { + dict unset D * + lappend res [dict values $D] + } + unset D + execsql {DROP TABLE tad} + set res +} {{1 aa} {2 bb}} + +#------------------------------------------------------------------------- +do_test 21.0 { db transaction { db close } } {} -do_test 20.1 { +do_test 21.1 { sqlite3 db test.db set rc [catch { db eval {SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3} { db close } } msg] list $rc $msg } {1 {invalid command name "db"}} - + + proc closedb {} { db close @@ -874,7 +936,7 @@ sqlite3 db test.db db func closedb closedb db func func1 func1 -do_test 20.2 { +do_test 21.2 { set rc [catch { db eval { SELECT closedb(),func1() UNION ALL SELECT 20,30 UNION ALL SELECT 30,40 @@ -884,9 +946,10 @@ do_test 20.2 { } {0 {10 1 20 30 30 40}} sqlite3 db :memory: -do_test 21.1 { +do_test 22.1 { catch {db eval {SELECT 1 2 3;}} msg db erroroffset } {9} + finish_test diff --git a/test/vacuum.test b/test/vacuum.test index 57429c29e..82dd00d09 100644 --- a/test/vacuum.test +++ b/test/vacuum.test @@ -401,4 +401,25 @@ do_test vacuum-10.1 { } {} do_test vacuum-10.2 { execsql VACUUM } {} +# Verify that VACUUM still works if ATTACH is disabled. +# +do_execsql_test vacuum-11.1 { + PRAGMA page_size=1024; + VACUUM; + PRAGMA page_size; +} {1024} +sqlite3_db_config db ATTACH_CREATE 0 +do_execsql_test vacuum-11.2 { + PRAGMA page_size=2048; + VACUUM; + PRAGMA page_size; +} {2048} +sqlite3_db_config db ATTACH_CREATE 1 +sqlite3_db_config db ATTACH_WRITE 0 +do_execsql_test vacuum-11.3 { + PRAGMA page_size=4096; + VACUUM; + PRAGMA page_size; +} {4096} + finish_test diff --git a/test/walsetlk_recover.test b/test/walsetlk_recover.test new file mode 100644 index 000000000..1daece747 --- /dev/null +++ b/test/walsetlk_recover.test @@ -0,0 +1,104 @@ +# 2025 May 30 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# TESTRUNNER: slow +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/lock_common.tcl +set testprefix walsetlk_recover + +ifcapable !wal {finish_test ; return } +# ifcapable !setlk_timeout {finish_test ; return } + +do_execsql_test 1.0 { + PRAGMA journal_mode = wal; + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(3, 4); + INSERT INTO t1 VALUES(5, 6); +} {wal} + +db_save_and_close +db_restore + +testfixture_nb myvar { + + testvfs tvfs -fullshm 1 + sqlite3 db test.db -vfs tvfs + tvfs script vfs_callback + tvfs filter xRead + + set ::done 0 + proc vfs_callback {method file args} { + if {$::done==0 && [string match *wal $file]} { + after 4000 + set ::done 1 + } + return "SQLITE_OK" + } + + db eval { + SELECT * FROM t1 + } + + db close +} + +# Give the [testfixture_nb] command time to start +after 1000 {set xyz 1} +vwait xyz + +testvfs tvfs -fullshm 1 +sqlite3 db test.db -vfs tvfs + +tvfs script sleep_callback +tvfs filter xSleep +set ::sleep_count 0 +proc sleep_callback {args} { + incr ::sleep_count +} + +sqlite3 db test.db -vfs tvfs +db timeout 500 +set tm [lindex [time { + catch { + db eval {SELECT * FROM t1} + } msg +}] 0] + +do_test 1.2 { set ::msg } {database is locked} +do_test 1.3.($::tm) { expr $::tm>400000 && $::tm<2000000 } 1 + +vwait myvar + +do_execsql_test 1.4 { + SELECT * FROM t1 +} {1 2 3 4 5 6} + +db close +tvfs delete + +# All SQLite builds should pass the tests above. SQLITE_ENABLE_SETLK_TIMEOUT=1 +# builds do so without calling the VFS xSleep method. +if {$::sqlite_options(setlk_timeout)==1} { + do_test 1.5.1 { + set ::sleep_count + } 0 +} else { + do_test 1.5.2 { + expr $::sleep_count>0 + } 1 +} + +finish_test + diff --git a/test/walsetlk_snapshot.test b/test/walsetlk_snapshot.test new file mode 100644 index 000000000..e05ad69cc --- /dev/null +++ b/test/walsetlk_snapshot.test @@ -0,0 +1,109 @@ +# 2025 May 30 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# TESTRUNNER: slow +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/lock_common.tcl +set testprefix walsetlk_snapshot + +ifcapable !wal {finish_test ; return } +ifcapable !snapshot {finish_test; return} + +db close +testvfs tvfs -fullshm 1 +sqlite3 db test.db -vfs tvfs +tvfs script sleep_callback +tvfs filter xSleep +set ::sleep_count 0 +proc sleep_callback {args} { + incr ::sleep_count +} + +do_execsql_test 1.0 { + PRAGMA journal_mode = wal; + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(3, 4); + INSERT INTO t1 VALUES(5, 6); +} {wal} + +do_test 1.1 { + db eval BEGIN + set ::snap [sqlite3_snapshot_get db main] + db eval { + INSERT INTO t1 VALUES(7, 8); + COMMIT; + } +} {} + +testfixture_nb myvar { + + testvfs tvfs -fullshm 1 + sqlite3 db test.db -vfs tvfs + tvfs script vfs_callback + tvfs filter {xWrite} + + set ::done 0 + proc vfs_callback {args} { + if {$::done==0} { + after 4000 + set ::done 1 + } + return "SQLITE_OK" + } + + db eval { + PRAGMA wal_checkpoint; + } + + db close +} + +# Give the [testfixture_nb] command time to start +after 1000 {set xyz 1} +vwait xyz + +db timeout 500 +set tm [lindex [time { + catch { + db eval BEGIN + sqlite3_snapshot_open db main $::snap + } msg +}] 0] + +do_test 1.2 { set ::msg } {SQLITE_BUSY} +do_test 1.3.($::tm) { expr $::tm<2000000 } 1 + +do_execsql_test 1.4 { + SELECT * FROM t1 +} {1 2 3 4 5 6 7 8} + +sqlite3_snapshot_free $::snap + +vwait myvar + +# All SQLite builds should pass the tests above. SQLITE_ENABLE_SETLK_TIMEOUT=1 +# builds do so without calling the VFS xSleep method. +if {$::sqlite_options(setlk_timeout)==1} { + do_test 1.5.1 { + set ::sleep_count + } 0 +} else { + do_test 1.5.2 { + expr $::sleep_count>0 + } 1 +} + +finish_test + |