diff options
Diffstat (limited to 'testdata')
50 files changed, 2479 insertions, 303 deletions
diff --git a/testdata/.gitignore b/testdata/.gitignore index c69a055..8da9ddb 100644 --- a/testdata/.gitignore +++ b/testdata/.gitignore @@ -1 +1,2 @@ sqlite-* +SQLite-* diff --git a/testdata/tcl/altertab.test b/testdata/tcl/altertab.test index c99010d..435620d 100644 --- a/testdata/tcl/altertab.test +++ b/testdata/tcl/altertab.test @@ -658,5 +658,23 @@ do_catchsql_test 21.3 { ALTER TABLE a RENAME TO e; } {1 {error in view c: 1st ORDER BY term does not match any column in the result set}} +# After forum thread https://sqlite.org/forum/forumpost/ddbe1c7efa +# Ensure that PRAGMA schema_version=N causes a full schema reload. +# +reset_db +do_execsql_test 22.0 { + CREATE TABLE t1(a INT, b TEXT NOT NULL); + INSERT INTO t1 VALUES(1,2),('a','b'); + BEGIN; + PRAGMA writable_schema=ON; + UPDATE sqlite_schema SET sql='CREATE TABLE t1(a INT, b TEXT)' WHERE name LIKE 't1'; + PRAGMA schema_version=1234; + COMMIT; + PRAGMA integrity_check; +} {ok} +do_execsql_test 22.1 { + ALTER TABLE t1 ADD COLUMN c INT DEFAULT 78; + SELECT * FROM t1; +} {1 2 78 a b 78} finish_test diff --git a/testdata/tcl/altertab3.test b/testdata/tcl/altertab3.test index b390655..005a0ee 100644 --- a/testdata/tcl/altertab3.test +++ b/testdata/tcl/altertab3.test @@ -586,5 +586,19 @@ do_execsql_test 24.4 { DELETE FROM v2; END}} +#------------------------------------------------------------------------ +# +reset_db +do_execsql_test 25.1 { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(a, b, c); + CREATE TRIGGER ttt AFTER INSERT ON t1 BEGIN + UPDATE t1 SET a=t2.a FROM t2 WHERE t1.a=t2.a; + END; +} +#do_execsql_test 25.2 { +# ALTER TABLE t2 RENAME COLUMN a TO aaa; +#} + finish_test diff --git a/testdata/tcl/busy2.test b/testdata/tcl/busy2.test new file mode 100644 index 0000000..fb9ef23 --- /dev/null +++ b/testdata/tcl/busy2.test @@ -0,0 +1,135 @@ +# 2020 June 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. +# +#*********************************************************************** +# This file test the busy handler +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/lock_common.tcl +set testprefix busy2 + +do_multiclient_test tn { + do_test 1.$tn.0 { + sql2 { + CREATE TABLE t1(a, b); + PRAGMA journal_mode = wal; + INSERT INTO t1 VALUES('A', 'B'); + } + } {wal} + + do_test 1.$tn.1 { + code1 { db timeout 1000 } + sql1 { SELECT * FROM t1 } + } {A B} + + do_test 1.$tn.2 { + sql2 { + BEGIN; + INSERT INTO t1 VALUES('C', 'D'); + } + } {} + + do_test 1.$tn.3 { + set us [lindex [time { catch { sql1 { BEGIN EXCLUSIVE } } }] 0] + expr {$us>950000 && $us<1500000} + } {1} + + do_test 1.$tn.4 { + sql2 { + COMMIT + } + } {} +} + +#------------------------------------------------------------------------- + +do_multiclient_test tn { + # Make the db a WAL mode db. And add a table and a row to it. Then open + # a second connection within process 1. Process 1 now has connections + # [db] and [db1.2], process 2 has connection [db2] only. + # + # Configure all connections to use a 1000 ms timeout. + # + do_test 2.$tn.0 { + code1 { + sqlite3 db1.2 test.db + } + sql1 { + PRAGMA auto_vacuum = off; + PRAGMA journal_mode = wal; + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + } + code2 { + db2 timeout 1000 + } + code1 { + db1.2 timeout 1000 + db timeout 1000 + db1.2 eval {SELECT * FROM t1} + } + } {1 2} + + # Take a read lock with [db] in process 1. + # + do_test 2.$tn.1 { + sql1 { + BEGIN; + SELECT * FROM t1; + } + } {1 2} + + # Insert a row using [db2] in process 2. Then try a passive checkpoint. + # It fails to checkpoint the final frame (due to the readlock taken by + # [db]), and returns in less than 250ms. + do_test 2.$tn.2 { + sql2 { INSERT INTO t1 VALUES(3, 4) } + set us [lindex [time { + set res [code2 { db2 eval { PRAGMA wal_checkpoint } }] + }] 0] + list [expr $us < 250000] $res + } {1 {0 4 3}} + + # Now try a FULL checkpoint with [db2]. It returns SQLITE_BUSY. And takes + # over 950ms to do so. + do_test 2.$tn.3 { + set us [lindex [time { + set res [code2 { db2 eval { PRAGMA wal_checkpoint = FULL } }] + }] 0] + list [expr $us > 950000] $res + } {1 {1 4 3}} + + # Passive checkpoint with [db1.2] (process 1). No SQLITE_BUSY, returns + # in under 250ms. + do_test 2.$tn.4 { + set us [lindex [time { + set res [code1 { db1.2 eval { PRAGMA wal_checkpoint } }] + }] 0] + list [expr $us < 250000] $res + } {1 {0 4 3}} + + # Full checkpoint with [db1.2] (process 1). SQLITE_BUSY returned in + # a bit over 950ms. + do_test 2.$tn.5 { + set us [lindex [time { + set res [code1 { db1.2 eval { PRAGMA wal_checkpoint = FULL } }] + }] 0] + list [expr $us > 950000] $res + } {1 {1 4 3}} + + code1 { + db1.2 close + } +} + +finish_test + diff --git a/testdata/tcl/corrupt3.test b/testdata/tcl/corrupt3.test index 3c911da..7a2e174 100644 --- a/testdata/tcl/corrupt3.test +++ b/testdata/tcl/corrupt3.test @@ -94,7 +94,7 @@ do_test corrupt3-1.9 { catchsql { SELECT substr(x,1,10) FROM t1 } -} [list 0 0123456789] +} [list 1 {database disk image is malformed}] do_test corrupt3-1.10 { catchsql { PRAGMA integrity_check diff --git a/testdata/tcl/corruptL.test b/testdata/tcl/corruptL.test index 3c26f1d..9af9fd2 100644 --- a/testdata/tcl/corruptL.test +++ b/testdata/tcl/corruptL.test @@ -1174,6 +1174,7 @@ do_test 14.0 { | end clusterfuzz-testcase-minimized-sqlite3_dbfuzz2_fuzzer-4806406219825152 }]} {} +extra_schema_checks 0 do_catchsql_test 14.1 { PRAGMA integrity_check; } {1 {database disk image is malformed}} @@ -1181,6 +1182,7 @@ do_catchsql_test 14.1 { do_catchsql_test 14.2 { ALTER TABLE t1 RENAME TO alkjalkjdfiiiwuer987lkjwer82mx97sf98788s9789s; } {1 {database disk image is malformed}} +extra_schema_checks 1 #------------------------------------------------------------------------- reset_db @@ -1263,8 +1265,77 @@ do_test 15.0 { | end crash-3afa1ca9e9c1bd.db }]} {} +extra_schema_checks 0 do_execsql_test 15.1 { + PRAGMA cell_size_check = 0; UPDATE c1 SET c= NOT EXISTS(SELECT 1 FROM c1 ORDER BY (SELECT 1 FROM c1 ORDER BY a)) +10 WHERE d BETWEEN 4 AND 7; } {} -finish_test +extra_schema_checks 1 + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 16.0 { + CREATE TABLE t1(w, x, y, z, UNIQUE(w, x), UNIQUE(y, z)); + INSERT INTO t1 VALUES(1, 1, 1, 1); + + CREATE TABLE t1idx(x, y, i INTEGER, PRIMARY KEY(x)) WITHOUT ROWID; + INSERT INTO t1idx VALUES(10, NULL, 5); + PRAGMA writable_schema = 1; + UPDATE sqlite_master SET rootpage = ( + SELECT rootpage FROM sqlite_master WHERE name='t1idx' + ) WHERE type = 'index'; +} + +extra_schema_checks 0 +db close +sqlite3 db test.db +extra_schema_checks 1 + +do_catchsql_test 16.1 { + PRAGMA writable_schema = ON; + INSERT INTO t1(rowid, w, x, y, z) VALUES(5, 10, 11, 10, NULL); +} {1 {database disk image is malformed}} + +#------------------------------------------------------------------------- +# Test that corruption is reported from within a checkpoint if the +# expected final size of the database (according to the last commit +# frame in the wal file) is greater than the combined initial sizes +# of the database and wal file. +# +if {[wal_is_capable]} { + reset_db + do_execsql_test 17.0 { + CREATE TABLE t1(o INTEGER PRIMARY KEY, t UNIQUE); + INSERT INTO t1(t) VALUES(randomblob(123)); + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + INSERT INTO t1(t) SELECT randomblob(123) FROM t1; + + PRAGMA journal_mode = wal; + INSERT INTO t1 VALUES(-1, 'b'); + } {wal} + + do_test 17.1 { + set fd [open test.db r+] + chan truncate $fd 2048 + file size test.db + } {2048} + + do_catchsql_test 17.2 { + PRAGMA wal_checkpoint + } {1 {database disk image is malformed}} + + do_test 17.3 { + close $fd + } {} +} + + +finish_test diff --git a/testdata/tcl/cost.test b/testdata/tcl/cost.test index 2922a0a..592973a 100644 --- a/testdata/tcl/cost.test +++ b/testdata/tcl/cost.test @@ -230,10 +230,10 @@ do_test 9.2 { set L [list a=? b=? c=? d=? e=? f=? g=? h=? i=? j=?] foreach {tn nTerm nRow} { 1 1 10 - 2 2 9 + 2 2 10 3 3 8 4 4 7 - 5 5 6 + 5 5 7 6 6 5 7 7 5 8 8 5 diff --git a/testdata/tcl/dbfuzz001.test b/testdata/tcl/dbfuzz001.test index 7ef4cd2..2a430de 100644 --- a/testdata/tcl/dbfuzz001.test +++ b/testdata/tcl/dbfuzz001.test @@ -361,6 +361,7 @@ do_test dbfuzz001-310 { }] } {} +extra_schema_checks 0 do_catchsql_test dbfuzz001-320 { PRAGMA integrity_check; } {1 {database disk image is malformed}} @@ -368,5 +369,6 @@ do_catchsql_test dbfuzz001-320 { do_catchsql_test dbfuzz001-330 { DELETE FROM t3 WHERE x IN (SELECT x FROM t4); } {1 {database disk image is malformed}} +extra_schema_checks 1 finish_test diff --git a/testdata/tcl/decimal.test b/testdata/tcl/decimal.test new file mode 100644 index 0000000..a2e5799 --- /dev/null +++ b/testdata/tcl/decimal.test @@ -0,0 +1,186 @@ +# 2017 December 9 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix decimal + +if {[catch {load_static_extension db decimal} error]} { + puts "Skipping decimal tests, hit load error: $error" + finish_test; return +} + +do_execsql_test 1000 { + SELECT decimal(1); +} {1} +do_execsql_test 1010 { + SELECT decimal(1.0); +} {1.0} +do_execsql_test 1020 { + SELECT decimal(0001.0); +} {1.0} +do_execsql_test 1030 { + SELECT decimal(+0001.0); +} {1.0} +do_execsql_test 1040 { + SELECT decimal(-0001.0); +} {-1.0} +do_execsql_test 1050 { + SELECT decimal(1.0e72); +} {1000000000000000000000000000000000000000000000000000000000000000000000000} +# 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123 +do_execsql_test 1060 { + SELECT decimal(1.0e-72); +} {0.0000000000000000000000000000000000000000000000000000000000000000000000010} +# 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123 +do_execsql_test 1070 { + SELECT decimal(-123e-4); +} {-0.0123} +do_execsql_test 1080 { + SELECT decimal(+123e+4); +} {1230000.0} + + +do_execsql_test 2000 { + CREATE TABLE t1(seq INTEGER PRIMARY KEY, val TEXT); + INSERT INTO t1 VALUES + (1, '-9999e99'), + (2, '-9998.000e+99'), + (3, '-9999.0'), + (4, '-1'), + (5, '-9999e-20'), + (6, '0'), + (7, '1e-30'), + (8, '1e-29'), + (9, '1'), + (10,'1.00000000000000001'), + (11,'+1.00001'), + (12,'99e+99'); + SELECT *, '|' + FROM t1 AS a, t1 AS b + WHERE a.seq<b.seq + AND decimal_cmp(a.val,b.val)>=0; +} {} +do_execsql_test 2010 { + SELECT *, '|' + FROM t1 AS a, t1 AS b + WHERE a.seq<>b.seq + AND decimal_cmp(a.val,b.val)==0; +} {} +do_execsql_test 2020 { + SELECT *, '|' + FROM t1 AS a, t1 AS b + WHERE a.seq>b.seq + AND decimal_cmp(a.val,b.val)<=0; +} {} +do_execsql_test 2030 { + SELECT seq FROM t1 ORDER BY val COLLATE decimal; +} {1 2 3 4 5 6 7 8 9 10 11 12} +do_execsql_test 2040 { + SELECT seq FROM t1 ORDER BY val COLLATE decimal DESC; +} {12 11 10 9 8 7 6 5 4 3 2 1} + +do_execsql_test 3000 { + CREATE TABLE t3(seq INTEGER PRIMARY KEY, val TEXT); + WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<10) + INSERT INTO t3(seq, val) SELECT x, x FROM c; + WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5) + INSERT INTO t3(seq, val) SELECT x+10, x*1000 FROM c; + SELECT decimal(val) FROM t3 ORDER BY seq; +} {1 2 3 4 5 6 7 8 9 10 1000 2000 3000 4000 5000} +do_execsql_test 3020 { + SELECT decimal_add(val,'0.5') FROM t3 WHERE seq>5 ORDER BY seq +} {6.5 7.5 8.5 9.5 10.5 1000.5 2000.5 3000.5 4000.5 5000.5} +do_execsql_test 3030 { + SELECT decimal_add(val,'-10') FROM t3 ORDER BY seq; +} {-9 -8 -7 -6 -5 -4 -3 -2 -1 0 990 1990 2990 3990 4990} + +do_execsql_test 4000 { + SELECT decimal_sum(val) FROM t3; +} {15055} +do_execsql_test 4010 { + SELECT decimal_sum(decimal_add(val,val||'e+10')) FROM t3; +} {150550000015055} +do_execsql_test 4010 { + SELECT decimal_sum(decimal_add(val||'e+20',decimal_add(val,val||'e-20'))) + FROM t3; +} {1505500000000000000015055.00000000000000015055} + +do_execsql_test 5000 { + WITH RECURSIVE c(x,y,z) AS ( + VALUES(0,'1','1') + UNION ALL + SELECT x+1, decimal_mul(y,'2'), decimal_mul(z,'0.5') + FROM c WHERE x<32 + ) + SELECT count(*) FROM c WHERE decimal_mul(y,z)='1'; +} {33} + +do_execsql_test 5100 { + SELECT decimal_mul('1234.00','2.00'); +} {2468.00} +do_execsql_test 5101 { + SELECT decimal_mul('1234.00','2.0000'); +} {2468.00} +do_execsql_test 5102 { + SELECT decimal_mul('1234.0000','2.000'); +} {2468.000} +do_execsql_test 5103 { + SELECT decimal_mul('1234.0000','2'); +} {2468} + +if {[catch {load_static_extension db ieee754} error]} { + puts "Skipping ieee754 tests, hit load error: $error" + finish_test; return +} + +do_execsql_test 6000 { + CREATE TABLE pow2(x INTEGER PRIMARY KEY, v TEXT); + WITH RECURSIVE c(x,v) AS ( + VALUES(0,'1') + UNION ALL + SELECT x+1, decimal_mul(v,'2') FROM c WHERE x+1<=971 + ) INSERT INTO pow2(x,v) SELECT x, v FROM c; + WITH RECURSIVE c(x,v) AS ( + VALUES(-1,'0.5') + UNION ALL + SELECT x-1, decimal_mul(v,'0.5') FROM c WHERE x-1>=-1075 + ) INSERT INTO pow2(x,v) SELECT x, v FROM c; +} {} +do_execsql_test 6010 { + WITH c(n) AS (SELECT ieee754_from_blob(x'0000000000000001')) +SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v) + FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n); +} {0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004940656458412465441765687928682213723650598026143247644255856825006755072702087518652998363616359923797965646954457177309266567103559397963987747960107818781263007131903114045278458171678489821036887186360569987307230500063874091535649843873124733972731696151400317153853980741262385655911710266585566867681870395603106249319452715914924553293054565444011274801297099995419319894090804165633245247571478690147267801593552386115501348035264934720193790268107107491703332226844753335720832431936092382893458368060106011506169809753078342277318329247904982524730776375927247874656084778203734469699533647017972677717585125660551199131504891101451037862738167250955837389733598993664809941164205702637090279242767544565229087538682506419718265533447265625} +do_execsql_test 6020 { + WITH c(n) AS (SELECT ieee754_from_blob(x'7fefffffffffffff')) +SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v) + FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n); +} {179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368} + +do_execsql_test 6100 { + SELECT ieee754(ieee754_from_blob(x'0000000000000001')); +} {ieee754(1,-1074)} +do_execsql_test 6110 { + SELECT ieee754(ieee754_from_blob(x'7fefffffffffffff')); +} {ieee754(9007199254740991,971)} +do_execsql_test 6120 { + SELECT printf('%.8e',ieee754_from_blob(x'0000000000000001')); +} {4.94065646e-324} +do_execsql_test 6130 { + SELECT printf('%.8e',ieee754_from_blob(x'ffefffffffffffff')); +} {-1.79769313e+308} + + + + +finish_test diff --git a/testdata/tcl/e_droptrigger.test b/testdata/tcl/e_droptrigger.test index 84dfe72..5cd5d0a 100644 --- a/testdata/tcl/e_droptrigger.test +++ b/testdata/tcl/e_droptrigger.test @@ -127,8 +127,8 @@ foreach {tn tbl droptrigger before after} { } $after } -# EVIDENCE-OF: R-50239-29811 Once removed, the trigger definition is no -# longer present in the sqlite_master (or sqlite_temp_master) table and +# EVIDENCE-OF: R-04950-25529 Once removed, the trigger definition is no +# longer present in the sqlite_schema (or sqlite_temp_schema) table and # is not fired by any subsequent INSERT, UPDATE or DELETE statements. # # Test cases e_droptrigger-1.* test the first part of this statement diff --git a/testdata/tcl/e_fkey.test b/testdata/tcl/e_fkey.test index 3636bef..c5ac5fd 100644 --- a/testdata/tcl/e_fkey.test +++ b/testdata/tcl/e_fkey.test @@ -2528,8 +2528,8 @@ test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1 # # Test that these adjustments are visible in the sqlite_master table. # -# EVIDENCE-OF: R-63827-54774 The text of the child CREATE TABLE -# statement or statements stored in the sqlite_master table are modified +# EVIDENCE-OF: R-43040-62530 The text of the child CREATE TABLE +# statement or statements stored in the sqlite_schema table are modified # to reflect the new parent table name. # do_test e_fkey-56.1 { diff --git a/testdata/tcl/filter1.test b/testdata/tcl/filter1.test index ee17099..7b2cf9c 100644 --- a/testdata/tcl/filter1.test +++ b/testdata/tcl/filter1.test @@ -204,4 +204,22 @@ do_execsql_test 6.3 { SELECT (SELECT COUNT(a) FROM t2) FROM t1; } {2} +#------------------------------------------------------------------------- +reset_db +do_execsql_test 7.0 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + INSERT INTO t1 VALUES(321, 100000); + INSERT INTO t1 VALUES(111, 110000); + INSERT INTO t1 VALUES(444, 120000); + INSERT INTO t1 VALUES(222, 130000); +} + +do_execsql_test 7.1 { + SELECT max(a), max(a) FILTER (WHERE b<12345), b FROM t1; +} { + 444 {} 120000 +} + + + finish_test diff --git a/testdata/tcl/fkey5.test b/testdata/tcl/fkey5.test index 3c44cd3..d467a64 100644 --- a/testdata/tcl/fkey5.test +++ b/testdata/tcl/fkey5.test @@ -15,10 +15,10 @@ # EVIDENCE-OF: R-15402-03103 PRAGMA schema.foreign_key_check; PRAGMA # schema.foreign_key_check(table-name); # -# EVIDENCE-OF: R-23918-17301 The foreign_key_check pragma checks the +# EVIDENCE-OF: R-41653-15278 The foreign_key_check pragma checks the # database, or the table called "table-name", for foreign key -# constraints that are violated and returns one row of output for each -# violation. +# constraints that are violated. The foreign_key_check pragma returns +# one row output for each foreign key violation. set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -430,4 +430,63 @@ do_catchsql_test 11.1 { PRAGMA foreign_key_check; } {1 {foreign key mismatch - "c11" referencing "tt"}} +# 2020-07-03 Bug in foreign_key_check discovered while working on the +# forum reports that pragma_foreign_key_check does not accept an argument: +# If two separate schemas seem to reference one another, that causes +# problems for foreign_key_check. +# +reset_db +do_execsql_test 12.0 { + ATTACH ':memory:' as aux; + CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b TEXT REFERENCES t2); + CREATE TABLE main.t2(x TEXT PRIMARY KEY, y INT); + INSERT INTO main.t2 VALUES('abc',11),('def',22),('xyz',99); + INSERT INTO aux.t1 VALUES(5,'abc'),(7,'xyz'),(9,'oops'); + PRAGMA foreign_key_check=t1; +} {t1 5 t2 0 t1 7 t2 0 t1 9 t2 0} +do_execsql_test 12.1 { + CREATE TABLE aux.t2(x TEXT PRIMARY KEY, y INT); + INSERT INTO aux.t2 VALUES('abc',11),('def',22),('xyz',99); + PRAGMA foreign_key_check=t1; +} {t1 9 t2 0} + +# 2020-07-03: the pragma_foreign_key_check virtual table should +# accept arguments for the table name and/or schema name. +# +ifcapable vtab { + do_execsql_test 13.0 { + SELECT *, 'x' FROM pragma_foreign_key_check('t1'); + } {t1 9 t2 0 x} + do_catchsql_test 13.1 { + SELECT *, 'x' FROM pragma_foreign_key_check('t1','main'); + } {1 {no such table: main.t1}} + do_execsql_test 13.2 { + SELECT *, 'x' FROM pragma_foreign_key_check('t1','aux'); + } {t1 9 t2 0 x} +} + +ifcapable vtab { + reset_db + do_execsql_test 13.10 { + PRAGMA foreign_keys=OFF; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT REFERENCES t2); + CREATE TABLE t2(x TEXT PRIMARY KEY, y INT); + CREATE TABLE t3(w TEXT, z INT REFERENCES t1); + INSERT INTO t2 VALUES('abc',11),('def',22),('xyz',99); + INSERT INTO t1 VALUES(5,'abc'),(7,'xyz'),(9,'oops'); + INSERT INTO t3 VALUES(11,7),(22,19); + } {} + do_execsql_test 13.11 { + SELECT x.*, '|' + FROM sqlite_schema, pragma_foreign_key_check(name) AS x + WHERE type='table' + ORDER BY x."table"; + } {t1 9 t2 0 | t3 2 t1 0 |} + do_execsql_test 13.12 { + SELECT *, '|' + FROM pragma_foreign_key_check AS x + ORDER BY x."table"; + } {t1 9 t2 0 | t3 2 t1 0 |} +} + finish_test diff --git a/testdata/tcl/fts3corrupt4.test b/testdata/tcl/fts3corrupt4.test index 27b4eb5..bddcc9f 100644 --- a/testdata/tcl/fts3corrupt4.test +++ b/testdata/tcl/fts3corrupt4.test @@ -27,6 +27,7 @@ ifcapable !fts3 { sqlite3_fts3_may_be_corrupt 1 database_may_be_corrupt +extra_schema_checks 0 do_execsql_test 1.0 { BEGIN; @@ -6123,4 +6124,178 @@ do_execsql_test 41.2 { SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'rtree ner "json1^enable"'; } +#------------------------------------------------------------------------- +do_execsql_test 42.1 { + CREATE VIRTUAL TABLE f USING fts3(a, b); +} +do_execsql_test 42.2 { + INSERT INTO f_segdir VALUES(0,2,1111,0,0,X'00'); + INSERT INTO f_segdir VALUES(0,3,0 ,0,0,X'00013003010200'); +} +do_execsql_test 42.3 { + INSERT INTO f(f) VALUES ('merge=107,2'); +} + +#------------------------------------------------------------------------- +reset_db +set saved $sqlite_fts3_enable_parentheses +set sqlite_fts3_enable_parentheses 1 +do_execsql_test 43.1 { + CREATE VIRTUAL TABLE def USING fts3(xyz); + INSERT INTO def_segdir VALUES(0,0,0,0,0, X'0001310301c9000103323334050d81'); +} {} + +do_execsql_test 43.2 { + SELECT rowid FROM def WHERE def MATCH '1 NEAR 1' +} {1} + +set sqlite_fts3_enable_parentheses $saved + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 44.1 { + CREATE VIRTUAL TABLE t0 USING fts3(col0 INTEGER PRIMARY KEY,col1 VARCHAR(8),col2 BINARY,col3 BINARY); + INSERT INTO t0_content VALUES(0,NULL,NULL,NULL,NULL); + INSERT INTO t0_segdir VALUES(0,0,0,0,'0 42',X'00013103010200010332333405010201ba00000461616161050101020200000462626262050101030200'); +} + +do_execsql_test 44.2 { + SELECT matchinfo(t0, t0) IS NULL FROM t0 WHERE t0 MATCH '1*' +} {0} + +#------------------------------------------------------------------------- +# +reset_db +do_test 45.0 { + sqlite3 db {} + db deserialize [decode_hexdb { +.open --hexdb +| size 24576 pagesize 4096 filename crash-65c98512cc9e49.db +| page 1 offset 0 +| 0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3. +| 16: 10 00 01 01 00 40 20 20 00 00 00 00 00 00 00 06 .....@ ........ +| 96: 00 00 00 00 0d 0e fc 00 06 0d bc 00 0f ca 0f 6c ...............l +| 112: 0f 04 0e 13 0e c9 0d bc 00 00 00 00 00 00 00 00 ................ +| 3504: 00 00 00 00 00 00 00 00 00 00 00 00 55 06 07 17 ............U... +| 3520: 1b 1b 01 81 01 74 61 62 6c 65 78 31 5f 73 74 61 .....tablex1_sta +| 3536: 74 78 31 5f 73 74 61 74 06 43 52 45 41 54 45 20 tx1_stat.CREATE +| 3552: 54 41 42 4c 45 20 27 78 31 5f 73 74 61 74 27 28 TABLE 'x1_stat'( +| 3568: 69 64 20 49 4e 54 45 47 45 52 20 50 52 49 4d 41 id INTEGER PRIMA +| 3584: 52 59 20 4b 45 59 2c 20 76 61 6c 75 65 20 42 4c RY KEY, value BL +| 3600: 41 82 29 81 33 04 07 17 1f 1f 01 82 35 74 61 62 A.).3.......5tab +| 3616: 6c 65 78 31 5f 73 65 67 64 69 72 78 31 5f 73 65 lex1_segdirx1_se +| 3632: 67 64 69 72 04 43 52 45 41 54 45 20 54 41 42 4c gdir.CREATE TABL +| 3648: 45 20 27 78 31 5f 73 65 67 64 69 72 27 28 6c 65 E 'x1_segdir'(le +| 3664: 76 65 6c 20 49 4e 54 45 47 45 52 2c 69 64 78 20 vel INTEGER,idx +| 3680: 49 4e 54 45 47 45 52 2c 73 74 61 72 74 5f 62 6c INTEGER,start_bl +| 3696: 6f 63 6b 20 49 4e 54 45 47 45 52 2c 6c 65 61 76 ock INTEGER,leav +| 3712: 65 73 5f 65 6e 64 5f 62 6c 6f 63 6b 20 49 4e 54 es_end_block INT +| 3728: 45 47 45 52 2c 65 6e 64 5f 62 6c 6f 63 6b 20 49 EGER,end_block I +| 3744: 4e 54 45 47 45 52 2c 72 6f 6f 74 20 42 4c 4f 42 NTEGER,root BLOB +| 3760: 2c 50 52 49 4d 41 52 59 20 4b 45 59 28 6c 65 76 ,PRIMARY KEY(lev +| 3776: 65 6c 2c 20 69 64 78 29 29 31 05 06 17 45 1f 01 el, idx))1...E.. +| 3792: 00 69 6e 64 65 78 73 71 6c 69 74 65 5f 61 75 74 .indexsqlite_aut +| 3808: 6f 69 6e 64 65 78 5f 78 31 5f 73 65 67 64 69 72 oindex_x1_segdir +| 3824: 5f 31 78 31 5f 73 65 67 64 69 72 05 00 00 00 08 _1x1_segdir..... +| 3840: 60 00 00 00 66 03 07 17 23 23 01 81 13 74 61 62 `...f...##...tab +| 3856: 6c 65 78 31 5f 73 65 67 6d 65 6e 74 73 78 31 5f lex1_segmentsx1_ +| 3872: 73 65 67 6d 65 6e 74 73 03 43 52 45 41 54 45 20 segments.CREATE +| 3888: 54 41 42 4c 45 20 27 78 31 5f 73 65 67 6d 65 6e TABLE 'x1_segmen +| 3904: 74 73 27 28 62 6c 6f 63 6b 69 64 20 49 4e 54 45 ts'(blockid INTE +| 3920: 47 45 52 20 50 52 49 4d 41 52 59 20 4b 45 59 2c GER PRIMARY KEY, +| 3936: 20 62 6c 6f 63 6b 20 42 4c 4f 42 29 5c 02 07 17 block BLOB).... +| 3952: 21 21 01 81 03 74 61 62 6c 65 78 31 5f 63 6f 6e !!...tablex1_con +| 3968: 74 65 6e 74 78 31 5f 63 6f 6e 74 65 6e 74 02 43 tentx1_content.C +| 3984: 52 45 41 54 45 20 54 41 42 4c 45 20 27 78 31 5f REATE TABLE 'x1_ +| 4000: 63 6f 6e 74 65 6e 74 27 28 64 6f 63 69 64 20 49 content'(docid I +| 4016: 4e 54 45 47 45 52 20 50 52 49 4d 41 52 59 20 4b NTEGER PRIMARY K +| 4032: 45 59 2c 20 27 63 30 78 27 29 34 01 06 17 11 11 EY, 'c0x')4..... +| 4048: 08 57 74 61 62 6c 65 78 31 78 31 43 52 45 41 54 .Wtablex1x1CREAT +| 4064: 45 20 56 49 52 54 55 41 4c 20 54 41 42 4c 45 20 E VIRTUAL TABLE +| 4080: 78 31 20 55 53 49 4e 47 20 66 74 73 33 28 78 29 x1 USING fts3(x) +| page 2 offset 4096 +| 0: 0d 00 00 00 11 0f 77 f0 0f f8 0f f0 0f e8 0f e0 ......w......... +| 16: 0f d8 0f d0 0f c8 0f c0 00 00 00 00 00 00 00 00 ................ +| 3952: 00 00 00 00 00 00 00 00 06 11 03 00 13 77 78 79 .............wxy +| 3968: 06 10 03 00 13 74 75 76 06 0f 03 00 13 71 33 73 .....tuv.....q3s +| 3984: 06 0e 03 00 13 6e 6f 70 06 0d 03 00 13 6b 6c 6d .....nop.....klm +| 4000: 06 0c 03 04 c3 68 69 6a 06 0b 03 00 13 65 66 67 .....hij.....efg +| 4016: 06 0a 03 00 13 62 63 64 06 09 03 00 13 79 7a 61 .....bcd.....yza +| 4032: 06 08 03 00 13 76 77 78 06 07 03 00 13 73 74 75 .....vwx.....stu +| 4048: 06 06 03 00 13 70 71 72 06 05 03 00 13 6d 6e 6f .....pqr.....mno +| 4064: 06 03 03 00 13 6a 6b 6c 06 03 03 00 13 67 68 69 .....jkl.....ghi +| 4080: 06 02 02 00 03 64 65 66 06 01 03 00 13 61 52 63 .....def.....aRc +| page 3 offset 8192 +| 0: 0d 00 00 00 03 0f a7 00 0f b5 0f a7 0f fa 01 00 ................ +| 4000: 00 00 00 00 00 00 00 0c 02 03 00 1e 00 03 6b 6c ..............kl +| 4016: 6d 03 0d 02 00 43 01 04 00 81 0a 00 03 61 62 63 m....C.......abc +| 4032: 03 0b 32 00 00 03 62 63 64 03 0a 02 00 00 03 64 ..2...bcd......d +| 4048: 69 26 03 02 02 00 00 03 65 66 67 03 0b 02 00 00 i&......efg..... +| 4064: 03 67 68 69 03 03 02 00 00 03 68 69 6a 03 0c 02 .ghi......hij... +| 4080: 00 00 03 6a 6a 2c 03 04 02 00 03 81 00 03 00 00 ...jj,.......... +| page 4 offset 12288 +| 0: 0d 0f 3a 00 05 0f 25 00 0f 9e 0f 88 0f 43 0f 25 ..:...%......C.% +| 16: 0f 72 00 00 00 00 00 00 00 00 00 00 00 00 00 00 .r.............. +| 3856: 00 00 00 00 00 00 00 00 00 56 01 08 08 13 1e 03 .........V...... +| 3872: 30 20 39 00 03 13 05 07 08 08 18 08 13 1e 30 20 0 9...........0 +| 3888: 39 00 03 77 78 79 03 11 02 00 0f 6c 00 09 01 08 9..wxy.....l.... +| 3904: 08 15 54 27 04 07 09 01 08 08 15 42 02 30 20 33 ..T'.......B.0 3 +| 3920: 36 00 03 6e 6f 70 03 0e 02 00 00 03 71 72 73 03 6..nop......qrs. +| 3936: 0f 02 00 00 03 74 75 76 03 10 02 00 0f cf b1 06 .....tuv........ +| 3952: 01 08 14 06 07 01 08 09 01 1b 14 02 02 31 32 38 .............128 +| 3968: 20 2d 37 32 10 01 01 6b 14 03 07 09 09 08 08 15 -72...k........ +| 3984: 1e 30 20 33 36 00 03 79 7a 61 03 09 02 00 2f 02 .0 36..yza..../. +| 4000: 07 09 08 08 08 15 54 30 20 33 36 00 03 6d 6e 6f ......T0 36..mno +| 4016: 03 05 02 00 00 03 70 71 72 03 06 02 00 00 03 73 ......pqr......s +| 4032: 74 75 03 07 02 00 00 03 76 77 78 03 08 02 00 00 tu......vwx..... +| 4048: 00 00 4a 08 08 08 15 54 30 20 33 36 00 03 61 62 ..J....T0 36..ab +| 4064: 63 03 01 02 00 00 03 64 65 66 03 02 02 00 00 03 c......def...... +| 4080: 67 68 69 03 03 67 00 00 03 6a 6b 6c 03 04 02 00 ghi..g...jkl.... +| page 5 offset 16384 +| 0: 0a 0f e7 00 05 0f da 00 0f e1 0f fa 0f f4 0f ed ................ +| 16: 0f da 0f 00 00 00 00 00 00 00 00 00 00 00 00 00 ................ +| 4048: 00 00 00 00 00 00 00 1a 01 03 06 04 01 08 01 02 ................ +| 4064: 06 05 04 08 08 01 05 00 00 00 06 01 03 06 04 09 ................ +| 4080: 02 01 02 04 05 04 09 09 01 03 05 04 09 08 01 02 ................ +| page 6 offset 20480 +| 0: 0d 00 10 00 01 0f f9 00 0f f9 00 00 00 00 00 00 ................ +| 4080: 00 00 00 00 00 00 00 00 00 05 01 03 00 10 01 03 ................ +| end crash-65c98512cc9e49.db +}]} {} + +do_catchsql_test 45.2 { + INSERT INTO x1(x1) VALUES( 'merge=1' ) +} {1 {database disk image is malformed}} + +#------------------------------------------------------------------------- +reset_db +set saved $sqlite_fts3_enable_parentheses +set sqlite_fts3_enable_parentheses 1 +do_execsql_test 46.1 { + CREATE VIRTUAL TABLE t0 USING fts3(a INTEGER PRIMARY KEY,b,c,d); + INSERT INTO t0_segdir VALUES(0,0,0,0,'0 42',X'0001310301c9000103323334050d8000f200000461616161050101020200000462626262050101030200'); +} {} + +do_catchsql_test 46.2 { + SELECT * FROM t0 + WHERE t0 MATCH x'2b0a312b0a312a312a2a0b5d0a0b0b0a312a0a0b0b0a312a0b310a392a0b0a27312a2a0b5d0a312a0b310a31315d0b310a312a316d2a0b313b15bceaa50a312a0b0a27312a2a0b5d0a312a0b310a312b0b2a310a312a0b2a0b2a0b2e5d0a0bff313336e34a2a312a0b0a3c310b0a0b4b4b0b4b2a4bec40322b2a0b310a0a312a0a0a0a0a0a0a0a0a0b310a312a2a2a0b5d0a0b0b0a312a0b310a312a0b0a4e4541530b310a5df5ced70a0a0a0a0a4f520a0a0a0a0a0a0a312a0b0a4e4541520b310a5d616161610a0a0a0a4f520a0a0a0a0a0a312b0a312a312a0a0a0a0a0a0a004a0b0a310b220a0b0a310a4a22310a0b0a7e6fe0e0e030e0e0e0e0e01176e02000e0e0e0e0e01131320226310a0b0a310a4a22310a0b0a310a766f8b8b4ee0e0300ae0090909090909090909090909090909090909090909090909090909090909090947aaaa540b09090909090909090909090909090909090909090909090909090909090909fae0e0f2f22164e0e0f273e07fefefef7d6dfafafafa6d6d6d6d'; +} {1 {database disk image is malformed}} + +set sqlite_fts3_enable_parentheses $saved +extra_schema_checks 1 + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 47.1 { + CREATE VIRTUAL TABLE t1 USING fts3(a,b,c); +} +do_execsql_test 47.2 { + INSERT INTO t1_segdir VALUES(0,0,0,0,0,X'000130120106000106000106001f030001030001030000083230313630363039090107000107000107000001340901050001050001050000013509010400010400010400010730303030303030091c0400010400010400000662696e6172793c0301020200030102020003010202000301020200030102020003010202000301020200030102020003010202000301020200030102020003010202000008636f6d70696c657209010200010200010200000664627374617409070300010300010300010465627567090402000102000102000006656e61626c653f07020001020001020001020001020001020001020001020001020001020001020001020001010001020001020001020001020001020001020001020001020001087874656e73696f6e091f0400010400010400000466747334090a0300010300010300030135090d03000103000103000003676363090103000103000103000106656f706f6c790910030001030001030000056a736f6e310913030001030001030000046c6f6164091f030001030001030000036d6178091c02000102000102000105656d6f7279091c03000103000103000304737973350916030001030001030000066e6f636173653c02010202000301020200030102020003010202000301020200030102020003010202000301020200030102020003010202000301020200030102020000046f6d6974091f020001020001020000057274726565091903000103000103000302696d3c01010202000301020200030102020003010202000301020200030102020003010202000301a202000301020200030102020003010202000301020200000a746872656164736166650922020001020001020000047674616209070400010400010400000178b401010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200010101020001010102000101010200'); + INSERT INTO t1_segdir VALUES(0,1,0,0,0,X'0001300425061b000008323031363036303903250700000134032505000001350325040001073030303030303003251a000008636f6d70696c657203250200000664627374617403250a00010465627567032508000006656e61626c650925090504040404040001087874656e73696f6e03251d0000046674733403250d0003013503250f000003676363032503000106656f706f6c790325110000056a736f6e310325130000046c6f616403251c0000036d6178032518000105656d6f7279032519000304737973350325150000046f6d697403251b000005727472656503251700000a7468726561647361666503251e0000047674616333250b00'); +} + +do_catchsql_test 47.3 { + SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH '"json1 enable"'; +} {1 {database disk image is malformed}} + finish_test diff --git a/testdata/tcl/fts3corrupt6.test b/testdata/tcl/fts3corrupt6.test new file mode 100644 index 0000000..9e22bdf --- /dev/null +++ b/testdata/tcl/fts3corrupt6.test @@ -0,0 +1,60 @@ +# 2020 June 8 +# +# 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. +# +#************************************************************************* +# This file implements regression tests for SQLite library. The +# focus of this script is testing the FTS3 module. +# +# $Id: fts3aa.test,v 1.1 2007/08/20 17:38:42 shess Exp $ +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/fts3_common.tcl +set testprefix fts3corrupt6 + +# If SQLITE_ENABLE_FTS3 is defined, omit this file. +ifcapable !fts3 { + finish_test + return +} + +set ::saved_sqlite_fts3_enable_parentheses $::sqlite_fts3_enable_parentheses +set sqlite_fts3_enable_parentheses 1 +sqlite3_fts3_may_be_corrupt 1 +database_may_be_corrupt + +do_execsql_test 1.0 { + BEGIN TRANSACTION; + CREATE TABLE t_content(col0 INTEGER); + PRAGMA writable_schema=ON; + CREATE VIRTUAL TABLE t0 USING fts3(col0 INTEGER PRIMARY KEY,col1 VARCHAR(8),col2 BINARY,col3 BINARY); + INSERT INTO t0_content VALUES(0,NULL,NULL,NULL,NULL); + INSERT INTO t0_segdir VALUES(0,0,0,0,'0 42',X'000131030102000103323334050101010200000461616161050101020200000462626262050101030200'); + COMMIT; +} + +do_execsql_test 1.1 { + SELECT 0+matchinfo(t0,'yxyyxy') FROM t0 WHERE t0 MATCH CAST( x'2b0a312b0a312a312a2a0b5d0a0b0b0a312a0a0b0b0a312a0b310a392a0b0a27312a2a0b5d0a312a0b310a31315d0b310a312a316d2a0b313b15bceaa50a312a0b0a27312a2a0b5d0a312a0b310a312b0b2a310a312a0b2a0b2a0b2e5d0a0bff313336e34a2a312a0b0a3c310b0a0b4b4b0b4b2a4bec40322b2a0b310a0a312a0a0a0a0a0a0a0a0a0b310a312a2a2a0b5d0a0b0b0a312a0b310a312a0b0a4e4541530b310a5df5ced70a0a0a0a0a4f520a0a0a0a0a0a0a312a0b0a4e4541520b310a5d616161610a0a0a0a4f520a0a0a0a0a0a312b0a312a312a0a0a0a0a0a0a004a0b0a310b220a0b0a310a4a22310a0b0a7e6fe0e0e030e0e0e0e0e01176e02000e0e0e0e0e01131320226310a0b0a310a4a22310a0b0a310a766f8b8b4ee0e0300ae0090909090909090909090909090909090909090909090909090909090909090947aaaa540b09090909090909090909090909090909090909090909090909090909090909fae0e0f2f22164e0e0f273e07fefefef7d6dfafafafa6d6d6d6d' AS TEXT); +} {0} + +do_execsql_test 1.2 { + CREATE VIRTUAL TABLE t1 USING fts3(col0 INTEGER PRIMARY KEY,col1 VARCHAR(8),col2 BINARY,col3 BINARY); + INSERT INTO t1_content VALUES(0,NULL,NULL,NULL,NULL); + INSERT INTO t1_segdir VALUES(0,0,0,0,'0 42',X'000131030102000103323334050101010200000461616161050101020200000462626262050101030200'); +} + +do_execsql_test 1.3 { + SELECT 42+matchinfo(t1,'yxyyxy') FROM t1 WHERE t1 MATCH x'2b0a312b0a312a312a2a0b5d0a0b0b0a312a0a0b0b0a312a0b310a392a0b0a27312a2a0b5d0a312a0b310a31315d0b310a312a316d2a0b313b15bceaa50a312a0b0a27312a2a0b5d0a312a0b310a312b0b2a310a312a0b2a0b2a0b2e5d0a0bff313336e34a2a312a0b0a3c310b0a0b4b4b0b4b2a4bec40322b2a0b310a0a312a0a0a0a0a0a0a0a0a0b310a312a2a2a0b5d0a0b0b0a312a0b310a312a0b0a4e4541530b310a5df5ced70a0a0a0a0a4f520a0a0a0a0a0a0a312a0b0a4e4541520b310a5d616161610a0a0a0a4f520a0a0a0a0a0a312b0a312a312a0a0a0a0a0a0a004a0b0a310b220a0b0a310a4a22310a0b0a7e6fe0e0e030e0e0e0e0e01176e02000e0e0e0e0e01131320226310a0b0a310a4a22310a0b0a310a766f8b8b4ee0e0300ae0090909090909090909090909090909090909090909090909090909090909090947aaaa540b09090909090909090909090909090909090909090909090909090909090909fae0e0f2f22164e0e0f273e07fefefef7d6dfafafafa6d6d6d6d'; +} {42} + +set sqlite_fts3_enable_parentheses $saved_sqlite_fts3_enable_parentheses +finish_test + + diff --git a/testdata/tcl/fts4upfrom.test b/testdata/tcl/fts4upfrom.test new file mode 100644 index 0000000..b1b43a0 --- /dev/null +++ b/testdata/tcl/fts4upfrom.test @@ -0,0 +1,140 @@ +# 2020 February 24 +# +# 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. +# +#************************************************************************* +# This file implements regression tests for SQLite library. The +# focus of this script is testing UPDATE statements with FROM clauses +# against FTS4 tables. +# +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix fts4upfrom + +# If SQLITE_ENABLE_FTS3 is defined, omit this file. +ifcapable !fts3 { + finish_test + return +} + +foreach {tn create_table} { + 0 { CREATE VIRTUAL TABLE ft USING fts5(a, b, c) } + 1 { CREATE VIRTUAL TABLE ft USING fts3(a, b, c) } + 2 { CREATE TABLE ft(a, b, c) } + 3 { + CREATE TABLE real(a, b, c); + CREATE INDEX i1 ON real(a); + CREATE VIEW ft AS SELECT rowid, a, b, c FROM real; + CREATE TRIGGER tr1 INSTEAD OF INSERT ON ft BEGIN + INSERT INTO real(rowid, a, b, c) VALUES(new.rowid, new.a, new.b, new.c); + END; + CREATE TRIGGER tr2 INSTEAD OF UPDATE ON ft BEGIN + UPDATE real SET rowid=new.rowid, a=new.a, b=new.b, c=new.c + WHERE rowid=old.rowid; + END; + } +} { + if {$tn==0} { ifcapable !fts5 { continue } } + catchsql { DROP VIEW IF EXISTS changes } + catchsql { DROP TABLE IF EXISTS ft } + catchsql { DROP VIEW IF EXISTS ft } + execsql $create_table + + do_execsql_test 1.$tn.0 { + INSERT INTO ft(a, b, c) VALUES('a', NULL, 'apple'); + INSERT INTO ft(a, b, c) VALUES('b', NULL, 'banana'); + INSERT INTO ft(a, b, c) VALUES('c', NULL, 'cherry'); + INSERT INTO ft(a, b, c) VALUES('d', NULL, 'damson plum'); + } + + do_execsql_test 1.$tn.1 { + SELECT a, b, c FROM ft ORDER BY rowid; + } { + a {} apple + b {} banana + c {} cherry + d {} {damson plum} + } + + do_execsql_test 1.$tn.2 { + UPDATE ft SET b=o.c FROM ft AS o WHERE (ft.a == char(unicode(o.a)+1)) + } + + do_execsql_test 1.$tn.3 { + SELECT a, b, c FROM ft ORDER BY rowid; + } { + a {} apple + b apple banana + c banana cherry + d cherry {damson plum} + } + + do_catchsql_test 1.$tn.4 { + UPDATE ft SET c=v FROM changes WHERE a=k; + } {1 {no such table: changes}} + + do_execsql_test 1.$tn.5 { + create view changes(k, v) AS + VALUES( 'd', 'dewberry' ) UNION ALL + VALUES( 'c', 'clementine' ) UNION ALL + VALUES( 'b', 'blueberry' ) UNION ALL + VALUES( 'a', 'apricot' ) + ; + } + + do_execsql_test 1.$tn.6 { + UPDATE ft SET c=v FROM changes WHERE a=k; + } + + do_execsql_test 1.$tn.7 { + SELECT rowid, a, b, c FROM ft ORDER BY rowid; + } { + 1 a {} apricot + 2 b apple blueberry + 3 c banana clementine + 4 d cherry dewberry + } + + do_execsql_test 1.$tn.8 " + WITH x1(o, n) AS ( + VALUES(1, 11) UNION ALL + VALUES(2, 12) UNION ALL + VALUES(3, 13) UNION ALL + VALUES(4, 14) + ) + SELECT ft.rowid, a, b, c, o, n FROM ft, x1 WHERE ft.rowid = o; + " { + 1 a {} apricot 1 11 + 2 b apple blueberry 2 12 + 3 c banana clementine 3 13 + 4 d cherry dewberry 4 14 + } + + set ROWID rowid + if {$tn==1} { set ROWID docid } + do_execsql_test 1.$tn.9 " + WITH x1(o, n) AS ( + VALUES(1, 11) UNION ALL + VALUES(2, 12) UNION ALL + VALUES(3, 13) UNION ALL + VALUES(4, 14) + ) + UPDATE ft SET $ROWID = n FROM x1 WHERE ft.rowid = o; + SELECT rowid, a, b, c FROM ft ORDER BY rowid; + " { + 11 a {} apricot + 12 b apple blueberry + 13 c banana clementine + 14 d cherry dewberry + } +} + +finish_test + diff --git a/testdata/tcl/gencol1.test b/testdata/tcl/gencol1.test index 5276d96..43f48df 100644 --- a/testdata/tcl/gencol1.test +++ b/testdata/tcl/gencol1.test @@ -560,4 +560,30 @@ do_catchsql_test gencol1-19.10 { INSERT INTO t0(c1) VALUES(0.16334143182538696), (0); } {1 {UNIQUE constraint failed: t0.c0}} +# 2020-06-29 forum bug report. +# https://sqlite.org/forum/forumpost/73b9a8ccfb +# +do_execsql_test gencol1-20.1 { + CREATE TEMPORARY TABLE tab ( + prim DATE PRIMARY KEY, + a INTEGER, + comp INTEGER AS (a), + b INTEGER, + x INTEGER + ); + -- Add some data + INSERT INTO tab (prim, a, b) VALUES ('2001-01-01', 0, 0); + -- Check that each column is 0 like I expect + SELECT * FROM tab; +} {2001-01-01 0 0 0 {}} +do_execsql_test gencol1-20.2 { + -- Do an UPSERT on the b column + INSERT INTO tab (prim, b) + VALUES ('2001-01-01',5) + ON CONFLICT(prim) DO UPDATE SET b=excluded.b; + -- Now b is NULL rather than 5 + SELECT * FROM tab; +} {2001-01-01 0 0 5 {}} + + finish_test diff --git a/testdata/tcl/hook.test b/testdata/tcl/hook.test index 1c9145b..d137e90 100644 --- a/testdata/tcl/hook.test +++ b/testdata/tcl/hook.test @@ -142,9 +142,8 @@ do_test hook-4.1.1a { set ::update_hook {} db update_hook [list lappend ::update_hook] # - # EVIDENCE-OF: R-52223-27275 The update hook is not invoked when - # internal system tables are modified (i.e. sqlite_master and - # sqlite_sequence). + # EVIDENCE-OF: R-24531-54682 The update hook is not invoked when + # internal system tables are modified (i.e. sqlite_sequence). # execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); diff --git a/testdata/tcl/ieee754.test b/testdata/tcl/ieee754.test index bf06764..bd806d2 100644 --- a/testdata/tcl/ieee754.test +++ b/testdata/tcl/ieee754.test @@ -23,8 +23,8 @@ foreach {id float rep} { 3 0.5 1,-1 4 1.5 3,-1 5 0.0 0,-1075 - 6 4.9406564584124654e-324 4503599627370497,-1075 - 7 2.2250738585072009e-308 9007199254740991,-1075 + 6 4.9406564584124654e-324 1,-1074 + 7 2.2250738585072009e-308 4503599627370495,-1074 8 2.2250738585072014e-308 1,-1022 } { do_test ieee754-100-$id-1 { diff --git a/testdata/tcl/index7.test b/testdata/tcl/index7.test index f57792e..084e8c3 100644 --- a/testdata/tcl/index7.test +++ b/testdata/tcl/index7.test @@ -339,5 +339,17 @@ do_execsql_test index7-7.1 { SELECT * FROM t6 WHERE y IS TRUE ORDER BY x; } {1 1} +# 2020-05-27. tag-20200527-1. +# Incomplete stat1 information on a table with few rows should still use the +# index. +reset_db +do_execsql_test index7-8.1 { + CREATE TABLE t1(x INTEGER PRIMARY KEY, y); + CREATE INDEX t1y ON t1(y) WHERE y IS NOT NULL; + INSERT INTO t1(x) VALUES(1),(2); + ANALYZE; + EXPLAIN QUERY PLAN SELECT 1 FROM t1 WHERE y=5; +} {/SEARCH TABLE t1 USING COVERING INDEX t1y/} + finish_test diff --git a/testdata/tcl/indexedby.test b/testdata/tcl/indexedby.test index 8624b10..18f7bb8 100644 --- a/testdata/tcl/indexedby.test +++ b/testdata/tcl/indexedby.test @@ -95,7 +95,7 @@ do_test indexedby-2.4 { # an error. do_test indexedby-2.4.1 { catchsql { SELECT b FROM t1 INDEXED BY i1 WHERE b = 'two' } -} {1 {no query solution}} +} {0 {}} do_test indexedby-2.5 { catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'} @@ -135,10 +135,10 @@ do_eqp_test indexedby-3.3 { } {SEARCH TABLE t1 USING INDEX i2 (b=?)} do_test indexedby-3.4 { catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } -} {1 {no query solution}} +} {0 {}} do_test indexedby-3.5 { catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } -} {1 {no query solution}} +} {0 {}} do_test indexedby-3.6 { catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' } } {0 {}} @@ -154,7 +154,7 @@ do_eqp_test indexedby-3.9 { } {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)} do_test indexedby-3.10 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } -} {1 {no query solution}} +} {0 {}} do_test indexedby-3.11 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } } {1 {no such index: sqlite_autoindex_t3_2}} @@ -172,19 +172,19 @@ do_eqp_test indexedby-4.2 { SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c } { QUERY PLAN - |--SCAN TABLE t2 - `--SEARCH TABLE t1 USING INDEX i1 (a=?) + |--SCAN TABLE t1 USING INDEX i1 + `--SEARCH TABLE t2 USING INDEX i3 (c=?) } do_test indexedby-4.3 { catchsql { SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c } -} {1 {no query solution}} +} {0 {}} do_test indexedby-4.4 { catchsql { SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c } -} {1 {no query solution}} +} {0 {}} # Test embedding an INDEXED BY in a CREATE VIEW statement. This block # also tests that nothing bad happens if an index refered to by @@ -205,7 +205,7 @@ do_test indexedby-5.4 { # Recreate index i1 in such a way as it cannot be used by the view query. execsql { CREATE INDEX i1 ON t1(b) } catchsql { SELECT * FROM v2 } -} {1 {no query solution}} +} {0 {}} do_test indexedby-5.5 { # Drop and recreate index i1 again. This time, create it so that it can # be used by the query. @@ -245,7 +245,7 @@ do_eqp_test indexedby-7.5 { } {SEARCH TABLE t1 USING INDEX i2 (b=?)} do_test indexedby-7.6 { catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} -} {1 {no query solution}} +} {0 {}} # Test that "INDEXED BY" can be used in an UPDATE statement. # @@ -266,7 +266,7 @@ do_eqp_test indexedby-8.5 { } {SEARCH TABLE t1 USING INDEX i2 (b=?)} do_test indexedby-8.6 { catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} -} {1 {no query solution}} +} {0 {}} # Test that bug #3560 is fixed. # @@ -284,10 +284,10 @@ do_test indexedby-9.2 { joinme as j indexed by joinme_id_text_idx on ( m.id = j.id_int) } -} {1 {no query solution}} +} {0 {}} do_test indexedby-9.3 { catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx } -} {1 {no query solution}} +} {0 {}} # Make sure we can still create tables, indices, and columns whose name # is "indexed". diff --git a/testdata/tcl/misc7.test b/testdata/tcl/misc7.test index e75a684..25f96af 100644 --- a/testdata/tcl/misc7.test +++ b/testdata/tcl/misc7.test @@ -455,12 +455,13 @@ if {$tcl_platform(platform)!="windows"} { catchsql { SELECT count(*) FROM t3; } - } {1 {database disk image is malformed}} + } {1 {malformed database schema (t3) - invalid rootpage}} } } # Ticket #2470 # +reset_db do_test misc7-18.1 { execsql { CREATE TABLE table_1 (col_10); diff --git a/testdata/tcl/mutex1.test b/testdata/tcl/mutex1.test index f567a0d..aac04bf 100644 --- a/testdata/tcl/mutex1.test +++ b/testdata/tcl/mutex1.test @@ -38,7 +38,7 @@ proc mutex_counters {varname} { #------------------------------------------------------------------------- # Tests mutex1-1.* test that sqlite3_config() returns SQLITE_MISUSE if # is called at the wrong time. And that the first time sqlite3_initialize -# is called it obtains the 'static_master' mutex 3 times and a recursive +# is called it obtains the 'static_main' mutex 3 times and a recursive # mutex (sqlite3Config.pInitMutex) twice. Subsequent calls are no-ops # that do not require any mutexes. # @@ -75,7 +75,7 @@ do_test mutex1-1.6 { do_test mutex1-1.7 { mutex_counters counters - # list $counters(total) $counters(static_master) + # list $counters(total) $counters(static_main) expr {$counters(total)>0} } {1} @@ -86,7 +86,7 @@ do_test mutex1-1.8 { do_test mutex1-1.9 { mutex_counters counters - list $counters(total) $counters(static_master) + list $counters(total) $counters(static_main) } {0 0} #------------------------------------------------------------------------- @@ -103,13 +103,13 @@ ifcapable threadsafe1&&shared_cache { singlethread {} multithread { fast static_app1 static_app2 static_app3 - static_lru static_master static_mem static_open + static_lru static_main static_mem static_open static_prng static_pmem static_vfs1 static_vfs2 static_vfs3 } serialized { fast recursive static_app1 static_app2 - static_app3 static_lru static_master static_mem + static_app3 static_lru static_main static_mem static_open static_prng static_pmem static_vfs1 static_vfs2 static_vfs3 } diff --git a/testdata/tcl/pager1.test b/testdata/tcl/pager1.test index 8216b46..20fd8bd 100644 --- a/testdata/tcl/pager1.test +++ b/testdata/tcl/pager1.test @@ -1930,6 +1930,7 @@ do_test pager1-18.4 { catchsql { SELECT length(x||'') FROM t2 } db2 } {1 {database disk image is malformed}} db2 close +extra_schema_checks 0 do_test pager1-18.5 { sqlite3 db "" sqlite3_db_config db DEFENSIVE 0 @@ -1944,6 +1945,7 @@ do_test pager1-18.5 { catchsql { SELECT * FROM x1 } } {1 {database disk image is malformed}} db close +extra_schema_checks 1 do_test pager1-18.6 { faultsim_delete_and_reopen diff --git a/testdata/tcl/permutations.test b/testdata/tcl/permutations.test index d8ec9e4..4ea6cd2 100644 --- a/testdata/tcl/permutations.test +++ b/testdata/tcl/permutations.test @@ -136,9 +136,24 @@ if {[info exists ::env(QUICKTEST_INCLUDE)]} { set allquicktests [concat $allquicktests $::env(QUICKTEST_INCLUDE)] } if {[info exists ::env(QUICKTEST_OMIT)]} { - foreach x [split $::env(QUICKTEST_OMIT) ,] { - regsub -all \\y$x\\y $allquicktests {} allquicktests + # If environment variable QUICKTEST_OMIT is set, it is a comma-separated + # list of regular expressions to match against test file names in + # the "allquicktests" set. Any matches are excluded. Only the filename + # is matched, not any directory component of the path. + set all [list] + foreach a $allquicktests { + set bIn 1 + foreach x [split $::env(QUICKTEST_OMIT) ,] { + if {[regexp $x [file tail $a]]} { + set bIn 0 + break + } + } + if {$bIn} { + lappend all $a + } } + set allquicktests $all } # If the TEST_FAILURE environment variable is set, it means that we what to @@ -171,6 +186,12 @@ test_suite "veryquick" -prefix "" -description { *fts5corrupt* *fts5big* *fts5aj* ] +test_suite "shell" -prefix "" -description { + Run tests of the command-line shell +} -files [ + test_set [glob $testdir/shell*.test] +] + test_suite "extraquick" -prefix "" -description { "Extra" quick test suite. Runs in a few minutes on a workstation. This test suite is the same as the "veryquick" tests, except that @@ -968,6 +989,7 @@ test_suite "journaltest" -description { pager1.test syscall.test tkt3457.test *malloc* mmap* multiplex* nolock* pager2.test *fault* rowal* snapshot* superlock* symlink.test delete_db.test shmlock.test chunksize.test + busy2.test }] if {[info commands register_demovfs] != ""} { diff --git a/testdata/tcl/pg_common.tcl b/testdata/tcl/pg_common.tcl index b3f35cd..dd16659 100644 --- a/testdata/tcl/pg_common.tcl +++ b/testdata/tcl/pg_common.tcl @@ -18,6 +18,8 @@ sqlite3 sqlite "" proc execsql {sql} { + set sql [string map {{WITHOUT ROWID} {}} $sql] + set lSql [list] set frag "" while {[string length $sql]>0} { diff --git a/testdata/tcl/pragma.test b/testdata/tcl/pragma.test index 1881a5c..04f5bd0 100644 --- a/testdata/tcl/pragma.test +++ b/testdata/tcl/pragma.test @@ -387,11 +387,15 @@ ifcapable attach { PRAGMA integrity_check=4 } } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2}} - do_test pragma-3.6 { - execsql { - PRAGMA integrity_check=xyz - } - } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} + do_catchsql_test pragma-3.6 { + PRAGMA integrity_check=xyz + } {1 {no such table: xyz}} + do_catchsql_test pragma-3.6b { + PRAGMA integrity_check=t2 + } {0 {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}} + do_catchsql_test pragma-3.6c { + PRAGMA integrity_check=sqlite_schema + } {0 ok} do_test pragma-3.7 { execsql { PRAGMA integrity_check=0 @@ -423,7 +427,7 @@ ifcapable attach { do_test pragma-3.8.2 { execsql {PRAGMA QUICK_CHECK} } {ok} - do_test pragma-3.9 { + do_test pragma-3.9a { execsql { ATTACH 'testerr.db' AS t2; PRAGMA integrity_check @@ -432,6 +436,12 @@ ifcapable attach { Page 4 is never used Page 5 is never used Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} + do_execsql_test pragma-3.9b { + PRAGMA t2.integrity_check=t2; + } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} + do_execsql_test pragma-3.9c { + PRAGMA t2.integrity_check=sqlite_schema; + } {ok} do_test pragma-3.10 { execsql { PRAGMA integrity_check=1 diff --git a/testdata/tcl/pragma4.test b/testdata/tcl/pragma4.test index 2eef060..b82df81 100644 --- a/testdata/tcl/pragma4.test +++ b/testdata/tcl/pragma4.test @@ -120,8 +120,15 @@ do_test 4.1.4 { sqlite3 db2 test.db2 execsql { DROP TABLE t1 } db3 execsql { DROP TABLE t2 } db2 -} {} -do_execsql_test 4.1.5 { PRAGMA table_info(t1) } +} {} +if {[permutation]=="prepare"} { + do_catchsql_test 4.1.5a { + PRAGMA table_info(t1) + } {1 {database schema has changed}} +} +do_execsql_test 4.1.5 { + PRAGMA table_info(t1) +} do_execsql_test 4.1.6 { PRAGMA table_info(t2) } db2 close diff --git a/testdata/tcl/printf.test b/testdata/tcl/printf.test index d6acacb..445470f 100644 --- a/testdata/tcl/printf.test +++ b/testdata/tcl/printf.test @@ -538,9 +538,11 @@ do_test printf-2.1.2.8 { do_test printf-2.1.2.9 { sqlite3_mprintf_double {abc: %d %d (%1.1g) :xyz} 1 1 1.0e-20 } {abc: 1 1 (1e-20) :xyz} -do_test printf-2.1.2.10 { - sqlite3_mprintf_double {abc: %*.*f} 2000000000 1000000000 1.0e-20 -} {} +if {$SQLITE_MAX_LENGTH<=[expr 1000*1000*1000]} { + do_test printf-2.1.2.10 { + sqlite3_mprintf_double {abc: %*.*f} 2000000000 1000000000 1.0e-20 + } {} +} do_test printf-2.1.3.1 { sqlite3_mprintf_double {abc: (%*.*f) :xyz} 1 1 1.0 } {abc: (1.0) :xyz} diff --git a/testdata/tcl/shell1.test b/testdata/tcl/shell1.test index c142ea7..678feba 100644 --- a/testdata/tcl/shell1.test +++ b/testdata/tcl/shell1.test @@ -199,10 +199,10 @@ do_test shell1-2.2.4 { } {0 {}} do_test shell1-2.2.5 { catchcmd "test.db" ".mode \"insert FOO" -} {1 {Error: mode should be one of: ascii column csv html insert line list quote tabs tcl}} +} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} do_test shell1-2.2.6 { catchcmd "test.db" ".mode \'insert FOO" -} {1 {Error: mode should be one of: ascii column csv html insert line list quote tabs tcl}} +} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} # check multiple tokens, and quoted tokens do_test shell1-2.3.1 { @@ -230,7 +230,7 @@ do_test shell1-2.3.7 { # check quoted args are unquoted do_test shell1-2.4.1 { catchcmd "test.db" ".mode FOO" -} {1 {Error: mode should be one of: ascii column csv html insert line list quote tabs tcl}} +} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} do_test shell1-2.4.2 { catchcmd "test.db" ".mode csv" } {0 {}} @@ -430,7 +430,7 @@ do_test shell1-3.13.1 { } {0 {current output mode: list}} do_test shell1-3.13.2 { catchcmd "test.db" ".mode FOO" -} {1 {Error: mode should be one of: ascii column csv html insert line list quote tabs tcl}} +} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} do_test shell1-3.13.3 { catchcmd "test.db" ".mode csv" } {0 {}} @@ -463,10 +463,10 @@ do_test shell1-3.13.11 { # don't allow partial mode type matches do_test shell1-3.13.12 { catchcmd "test.db" ".mode l" -} {1 {Error: mode should be one of: ascii column csv html insert line list quote tabs tcl}} +} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} do_test shell1-3.13.13 { catchcmd "test.db" ".mode li" -} {1 {Error: mode should be one of: ascii column csv html insert line list quote tabs tcl}} +} {1 {Error: mode should be one of: ascii box column csv html insert json line list markdown quote table tabs tcl}} do_test shell1-3.13.14 { catchcmd "test.db" ".mode lin" } {0 {}} @@ -709,11 +709,11 @@ do_test shell1-3.26.4 { # this should be treated the same as a '1' width for col 1 and 2 } {0 {}} do_test shell1-3.26.5 { - catchcmd "test.db" ".mode column\n.width 10 -10\nSELECT 'abcdefg', 123456;" + catchcmd "test.db" ".mode column\n.header off\n.width 10 -10\nSELECT 'abcdefg', 123456;" # this should be treated the same as a '1' width for col 1 and 2 } {0 {abcdefg 123456}} do_test shell1-3.26.6 { - catchcmd "test.db" ".mode column\n.width -10 10\nSELECT 'abcdefg', 123456;" + catchcmd "test.db" ".mode column\n.header off\n.width -10 10\nSELECT 'abcdefg', 123456;" # this should be treated the same as a '1' width for col 1 and 2 } {0 { abcdefg 123456 }} @@ -1166,4 +1166,57 @@ do_test shell1-7.1.7 { } +# Test case for the ieee754 and decimal extensions in the shell. +# See the "floatingpoint.html" file in the documentation for more +# information. +# +do_test shell1-8.1 { + catchcmd ":memory:" { + -- The pow2 table will hold all the necessary powers of two. + CREATE TABLE pow2(x INTEGER PRIMARY KEY, v TEXT); + WITH RECURSIVE c(x,v) AS ( + VALUES(0,'1') + UNION ALL + SELECT x+1, decimal_mul(v,'2') FROM c WHERE x+1<=971 + ) INSERT INTO pow2(x,v) SELECT x, v FROM c; + WITH RECURSIVE c(x,v) AS ( + VALUES(-1,'0.5') + UNION ALL + SELECT x-1, decimal_mul(v,'0.5') FROM c WHERE x-1>=-1075 + ) INSERT INTO pow2(x,v) SELECT x, v FROM c; + + -- This query finds the decimal representation of each value in the "c" table. + WITH c(n) AS (VALUES(47.49)) + ----XXXXX----------- Replace with whatever you want + SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v) + FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n); + } +} {0 47.49000000000000198951966012828052043914794921875} +do_test shell1-8.2 { + catchcmd :memory: { +.mode box +SELECT ieee754(47.49) AS x; + } +} {0 {┌───────────────────────────────┐ +│ x │ +├───────────────────────────────┤ +│ ieee754(6683623321994527,-47) │ +└───────────────────────────────┘}} +do_test shell1-8.3 { + catchcmd ":memory: --box" { + select ieee754(6683623321994527,-47) as x; + } +} {0 {┌───────┐ +│ x │ +├───────┤ +│ 47.49 │ +└───────┘}} +do_test shell1-8.4 { + catchcmd ":memory: --table" {SELECT ieee754_mantissa(47.49) AS M, ieee754_exponent(47.49) AS E;} +} {0 {+------------------+-----+ +| M | E | ++------------------+-----+ +| 6683623321994527 | -47 | ++------------------+-----+}} + finish_test diff --git a/testdata/tcl/stat.test b/testdata/tcl/stat.test index 105169d..5eb7d6f 100644 --- a/testdata/tcl/stat.test +++ b/testdata/tcl/stat.test @@ -59,7 +59,7 @@ if {[wal_is_capable]} { PRAGMA journal_mode = delete; SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat; - } {wal delete sqlite_master / 1 leaf 0 0 916 0} + } {wal delete sqlite_schema / 1 leaf 0 0 916 0} } do_test stat-1.0 { @@ -85,9 +85,9 @@ do_test stat-1.2 { do_test stat-1.3 { execsql { SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload - FROM stat WHERE name = 'sqlite_master'; + FROM stat WHERE name = 'sqlite_schema'; } -} {sqlite_master / 1 leaf 2 77 831 40} +} {sqlite_schema / 1 leaf 2 77 831 40} do_test stat-1.4 { execsql { DROP TABLE t1; @@ -108,7 +108,7 @@ do_execsql_test stat-2.1 { INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 ORDER BY rowid; SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload - FROM stat WHERE name != 'sqlite_master' ORDER BY name; + FROM stat WHERE name != 'sqlite_schema' ORDER BY name; } [list \ sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \ sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \ @@ -138,7 +138,7 @@ do_execsql_test stat-2.1agg { SELECT * FROM dbstat WHERE aggregate=TRUE ORDER BY name; } [list \ sqlite_autoindex_t3_1 {} 5 {} 32 3898 1065 132 {} 5120 \ - sqlite_master {} 1 {} 2 84 824 49 {} 1024 \ + sqlite_schema {} 1 {} 2 84 824 49 {} 1024 \ t3 {} 17 {} 47 11188 5815 370 {} 17408 \ ] @@ -158,7 +158,7 @@ do_execsql_test stat-3.1 { CREATE INDEX i4 ON t4(x); INSERT INTO t4(rowid, x) VALUES(2, a_string(7777)); SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload - FROM stat WHERE name != 'sqlite_master' ORDER BY name; + FROM stat WHERE name != 'sqlite_schema' ORDER BY name; } [list \ i4 / 3 leaf 1 103 905 7782 \ i4 /000+000000 4 overflow 0 1020 0 0 \ @@ -183,7 +183,7 @@ do_execsql_test stat-3.2 { SELECT *, '|' FROM dbstat WHERE aggregate=TRUE ORDER BY name; } [list \ i4 {} 9 {} 1 7782 1386 7782 {} 9216 | \ - sqlite_master {} 1 {} 2 74 834 40 {} 1024 | \ + sqlite_schema {} 1 {} 2 74 834 40 {} 1024 | \ t4 {} 8 {} 1 7780 367 7780 {} 8192 | \ ] @@ -221,11 +221,11 @@ do_execsql_test stat-5.1 { do_execsql_test stat-5.20 { SELECT name, quote(path), pageno, quote(pagetype), ncell, payload, unused, mx_payload, '|' FROM dbstat('main',1); -} {sqlite_master NULL 1 NULL 1 34 878 34 | tx NULL 1 NULL 0 0 1016 0 |} +} {sqlite_schema NULL 1 NULL 1 34 878 34 | tx NULL 1 NULL 0 0 1016 0 |} do_execsql_test stat-5.21 { SELECT name, quote(path), pageno, quote(pagetype), ncell, payload, unused, mx_payload, '|' FROM dbstat('aux1',1); -} {sqlite_master NULL 1 NULL 1 34 878 34 | t1 NULL 3 NULL 2 3033 5 1517 |} +} {sqlite_schema NULL 1 NULL 1 34 878 34 | t1 NULL 3 NULL 2 3033 5 1517 |} do_catchsql_test stat-6.1 { @@ -247,27 +247,27 @@ do_execsql_test 7.1 { do_execsql_test 7.1.1 { SELECT * FROM dbstat('123'); } { - sqlite_master / 1 leaf 1 37 875 37 0 1024 + sqlite_schema / 1 leaf 1 37 875 37 0 1024 x1 / 2 leaf 1 4 1008 4 1024 1024 } do_execsql_test 7.1.2 { SELECT * FROM dbstat(123); } { - sqlite_master / 1 leaf 1 37 875 37 0 1024 + sqlite_schema / 1 leaf 1 37 875 37 0 1024 x1 / 2 leaf 1 4 1008 4 1024 1024 } do_execsql_test 7.1.3 { CREATE VIRTUAL TABLE x2 USING dbstat('123'); SELECT * FROM x2; } { - sqlite_master / 1 leaf 1 37 875 37 0 1024 + sqlite_schema / 1 leaf 1 37 875 37 0 1024 x1 / 2 leaf 1 4 1008 4 1024 1024 } do_execsql_test 7.1.4 { CREATE VIRTUAL TABLE x3 USING dbstat(123); SELECT * FROM x3; } { - sqlite_master / 1 leaf 1 37 875 37 0 1024 + sqlite_schema / 1 leaf 1 37 875 37 0 1024 x1 / 2 leaf 1 4 1008 4 1024 1024 } @@ -280,7 +280,7 @@ do_execsql_test 7.2 { do_execsql_test 7.2.1 { SELECT * FROM dbstat('123corp'); } { - sqlite_master / 1 leaf 1 37 875 37 0 1024 + sqlite_schema / 1 leaf 1 37 875 37 0 1024 x1 / 2 leaf 1 4 1008 4 1024 1024 } do_catchsql_test 7.2.2 { @@ -290,7 +290,7 @@ do_execsql_test 7.2.3 { CREATE VIRTUAL TABLE x2 USING dbstat('123corp'); SELECT * FROM x2; } { - sqlite_master / 1 leaf 1 37 875 37 0 1024 + sqlite_schema / 1 leaf 1 37 875 37 0 1024 x1 / 2 leaf 1 4 1008 4 1024 1024 } do_catchsql_test 7.2.4 { diff --git a/testdata/tcl/tester.tcl b/testdata/tcl/tester.tcl index cc7f913..b1acb06 100644 --- a/testdata/tcl/tester.tcl +++ b/testdata/tcl/tester.tcl @@ -129,6 +129,7 @@ if {[info command sqlite_orig]==""} { set ::dbhandle [lindex $args 0] uplevel #0 $::G(perm:dbconfig) } + [lindex $args 0] cache size 3 set res } else { # This command is not opening a new database connection. Pass the @@ -2478,6 +2479,7 @@ set sqlite_fts3_enable_parentheses 0 # this setting by invoking "database_can_be_corrupt" # database_never_corrupt +extra_schema_checks 1 source $testdir/thread_common.tcl source $testdir/malloc_common.tcl diff --git a/testdata/tcl/triggerupfrom.test b/testdata/tcl/triggerupfrom.test new file mode 100644 index 0000000..9bfacb8 --- /dev/null +++ b/testdata/tcl/triggerupfrom.test @@ -0,0 +1,174 @@ +# 2020 July 14 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix triggerupfrom + +do_execsql_test 1.0 { + CREATE TABLE map(k, v); + INSERT INTO map VALUES(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'); + + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + + CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN + UPDATE t1 SET c = v FROM map WHERE k=new.a AND a=new.a; + END; +} + +do_execsql_test 1.1 { + INSERT INTO t1(a) VALUES(1); +} + +do_execsql_test 1.2 { + SELECT a, c FROM t1 ORDER BY a; +} {1 one} + +do_execsql_test 1.3 { + INSERT INTO t1(a) VALUES(2), (3), (4), (5); + SELECT a, c FROM t1 ORDER BY a; +} {1 one 2 two 3 three 4 four 5 {}} + +forcedelete test.db2 +do_execsql_test 2.0 { + ATTACH 'test.db2' AS aux; + CREATE TABLE aux.t3(x, y); + INSERT INTO aux.t3 VALUES('x', 'y'); +} + +do_catchsql_test 2.1 { + CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN + UPDATE t1 SET b = y FROM aux.t3 WHERE k=new.a; + END; +} {1 {trigger tr2 cannot reference objects in database aux}} + +do_execsql_test 2.2 { + CREATE TEMP TRIGGER tr2 AFTER INSERT ON t1 BEGIN + UPDATE t1 SET b = y FROM aux.t3 WHERE a=new.a; + END; + INSERT INTO t1(a) VALUES(10), (20); + SELECT * FROM t1; +} { + 1 {} one + 2 {} two + 3 {} three + 4 {} four + 5 {} {} + 10 y {} + 20 y {} +} + +do_execsql_test 2.3 { + CREATE TABLE link(f, t); + INSERT INTO link VALUES(5, 2), (20, 10), (2, 1); + CREATE TRIGGER tr3 BEFORE DELETE ON t1 BEGIN + UPDATE t1 SET b=coalesce(old.b,old.c) FROM main.link WHERE a=t AND old.a=f; + END; + DELETE FROM t1 WHERE a=2; + SELECT * FROM t1; +} { + 1 two one + 3 {} three + 4 {} four + 5 {} {} + 10 y {} + 20 y {} +} + +db close +sqlite3 db "" +do_catchsql_test 2.4 { + ATTACH 'test.db' AS yyy; + SELECT * FROM t1; +} {1 {malformed database schema (tr3) - trigger tr3 cannot reference objects in database main}} + +#------------------------------------------------------------------------- +reset_db +forcedelete test.db2 +do_execsql_test 3.0 { + CREATE TABLE mmm(x, y); + INSERT INTO mmm VALUES(1, 'one'); + INSERT INTO mmm VALUES(2, 'two'); + INSERT INTO mmm VALUES(3, 'three'); + + ATTACH 'test.db2' AS aux; + CREATE TABLE aux.t1(a, b); + CREATE TABLE aux.mmm(x, y); + INSERT INTO aux.mmm VALUES(1, 'ONE'); + INSERT INTO aux.mmm VALUES(2, 'TWO'); + INSERT INTO aux.mmm VALUES(3, 'THREE'); + + CREATE TRIGGER aux.ttt AFTER INSERT ON t1 BEGIN + UPDATE t1 SET b=y FROM mmm WHERE x=new.a AND a=new.a; + END; + + INSERT INTO t1(a) VALUES (2); + SELECT * FROM t1; +} {2 TWO} + +#------------------------------------------------------------------------- +# Test that INSTEAD OF UPDATE triggers on views work with UPDATE...FROM +# statements. Including, if the library is built with ENABLE_HIDDEN_COLUMNS, +# that they work correctly on views with hidden columns. +# +reset_db +do_execsql_test 4.0 { + CREATE TABLE t1(k, a, b); + INSERT INTO t1 VALUES('a', 1, 'one'); + INSERT INTO t1 VALUES('b', 2, 'two'); + INSERT INTO t1 VALUES('c', 3, 'three'); + INSERT INTO t1 VALUES('d', 4, 'four'); + + CREATE TABLE log(x); + CREATE VIEW v1 AS SELECT k, a, b AS __hidden__b FROM t1; + CREATE TRIGGER tr1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO log VALUES( + '('||old.a||','||old.__hidden__b||')->('||new.a||','||new.__hidden__b||')' + ); + END; +} + +ifcapable hiddencolumns { + do_execsql_test 4.1-hc-enabled { + SELECT * FROM v1 + } {a 1 b 2 c 3 d 4} +} else { + do_execsql_test 4.1-hc-disabled { + SELECT * FROM v1 + } {a 1 one b 2 two c 3 three d 4 four} +} + +do_execsql_test 4.2 { + UPDATE v1 SET a='xyz' WHERE k IN ('a', 'c'); + SELECT * FROM log; + DELETE FROM log; +} { + (1,one)->(xyz,one) + (3,three)->(xyz,three) +} + +do_execsql_test 4.3 { + CREATE TABLE map(k, v); + INSERT INTO map VALUES('b', 'twelve'); + INSERT INTO map VALUES('d', 'fourteen'); + UPDATE v1 SET a=map.v FROM map WHERE v1.k=map.k; + SELECT * FROM log; + DELETE FROM log; +} { + (2,two)->(twelve,two) + (4,four)->(fourteen,four) +} + + + +finish_test + diff --git a/testdata/tcl/upfrom1.tcl b/testdata/tcl/upfrom1.tcl new file mode 100644 index 0000000..22fc68a --- /dev/null +++ b/testdata/tcl/upfrom1.tcl @@ -0,0 +1,115 @@ +# 2020 April 22 +# +# 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. +# +#*********************************************************************** +# + +source [file join [file dirname $argv0] pg_common.tcl] + +#========================================================================= + +start_test upfrom1 "2020 April 22" + +foreach {tn wo} { + 1 "WITHOUT ROWID" + 2 "" +} { +eval [string map [list %TN% $tn %WITHOUT_ROWID% $wo] { +execsql_test 1.%TN%.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) %WITHOUT_ROWID%; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); + + DROP TABLE IF EXISTS chng; + CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); + INSERT INTO chng VALUES(1, 100, 1000); + INSERT INTO chng VALUES(7, 700, 7000); +} + +execsql_test 1.%TN%.1 { + SELECT * FROM t2; +} + +execsql_test 1.%TN%.2 { + UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; + SELECT * FROM t2 ORDER BY a; +} + +execsql_test 1.%TN%.3 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); +} + +execsql_test 1.%TN%.4 { + UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) + WHERE a IN (SELECT a FROM chng); + SELECT * FROM t2 ORDER BY a; +} + +execsql_test 1.%TN%.5 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) %WITHOUT_ROWID%; + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, 'two'); + INSERT INTO t3 VALUES(3, 3, 'three'); + + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x TEXT); + INSERT INTO t4 VALUES('five'); + + SELECT * FROM t3 ORDER BY a; +} + +execsql_test 1.%TN%.6 { + UPDATE t3 SET c=x FROM t4; + SELECT * FROM t3 ORDER BY a; +} +}]} + +execsql_test 2.1 { + DROP TABLE IF EXISTS t5; + DROP TABLE IF EXISTS m1; + DROP TABLE IF EXISTS m2; + CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT); + CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT); + + INSERT INTO t5 VALUES(1, 'one', 'ONE'); + INSERT INTO t5 VALUES(2, 'two', 'TWO'); + INSERT INTO t5 VALUES(3, 'three', 'THREE'); + INSERT INTO t5 VALUES(4, 'four', 'FOUR'); + + INSERT INTO m1 VALUES(1, 'i'); + INSERT INTO m1 VALUES(2, 'ii'); + INSERT INTO m1 VALUES(3, 'iii'); + + INSERT INTO m2 VALUES(1, 'I'); + INSERT INTO m2 VALUES(3, 'II'); + INSERT INTO m2 VALUES(4, 'III'); +} + +execsql_test 2.2 { + UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a; + SELECT * FROM t5 ORDER BY a; +} + +errorsql_test 2.3.1 { + UPDATE t5 SET b=1 FROM t5; +} +errorsql_test 2.3.2 { + UPDATE t5 AS apples SET b=1 FROM t5 AS apples; +} + + +finish_test + diff --git a/testdata/tcl/upfrom1.test b/testdata/tcl/upfrom1.test new file mode 100644 index 0000000..7996f97 --- /dev/null +++ b/testdata/tcl/upfrom1.test @@ -0,0 +1,178 @@ +# 2020 April 22 +# +# 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. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfrom1 + +do_execsql_test 1.1.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) WITHOUT ROWID; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); + + DROP TABLE IF EXISTS chng; + CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); + INSERT INTO chng VALUES(1, 100, 1000); + INSERT INTO chng VALUES(7, 700, 7000); +} {} + +do_execsql_test 1.1.1 { + SELECT * FROM t2; +} {1 2 3 4 5 6 7 8 9} + +do_execsql_test 1.1.2 { + UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.1.3 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); +} {} + +do_execsql_test 1.1.4 { + UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) + WHERE a IN (SELECT a FROM chng); + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.1.5 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) WITHOUT ROWID; + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, 'two'); + INSERT INTO t3 VALUES(3, 3, 'three'); + + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x TEXT); + INSERT INTO t4 VALUES('five'); + + SELECT * FROM t3 ORDER BY a; +} {1 1 one 2 2 two 3 3 three} + +do_execsql_test 1.1.6 { + UPDATE t3 SET c=x FROM t4; + SELECT * FROM t3 ORDER BY a; +} {1 1 five 2 2 five 3 3 five} + +do_execsql_test 1.2.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) ; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); + + DROP TABLE IF EXISTS chng; + CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); + INSERT INTO chng VALUES(1, 100, 1000); + INSERT INTO chng VALUES(7, 700, 7000); +} {} + +do_execsql_test 1.2.1 { + SELECT * FROM t2; +} {1 2 3 4 5 6 7 8 9} + +do_execsql_test 1.2.2 { + UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.2.3 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); +} {} + +do_execsql_test 1.2.4 { + UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) + WHERE a IN (SELECT a FROM chng); + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.2.5 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) ; + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, 'two'); + INSERT INTO t3 VALUES(3, 3, 'three'); + + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x TEXT); + INSERT INTO t4 VALUES('five'); + + SELECT * FROM t3 ORDER BY a; +} {1 1 one 2 2 two 3 3 three} + +do_execsql_test 1.2.6 { + UPDATE t3 SET c=x FROM t4; + SELECT * FROM t3 ORDER BY a; +} {1 1 five 2 2 five 3 3 five} + +do_execsql_test 2.1 { + DROP TABLE IF EXISTS t5; + DROP TABLE IF EXISTS m1; + DROP TABLE IF EXISTS m2; + CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT); + CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT); + + INSERT INTO t5 VALUES(1, 'one', 'ONE'); + INSERT INTO t5 VALUES(2, 'two', 'TWO'); + INSERT INTO t5 VALUES(3, 'three', 'THREE'); + INSERT INTO t5 VALUES(4, 'four', 'FOUR'); + + INSERT INTO m1 VALUES(1, 'i'); + INSERT INTO m1 VALUES(2, 'ii'); + INSERT INTO m1 VALUES(3, 'iii'); + + INSERT INTO m2 VALUES(1, 'I'); + INSERT INTO m2 VALUES(3, 'II'); + INSERT INTO m2 VALUES(4, 'III'); +} {} + +do_execsql_test 2.2 { + UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a; + SELECT * FROM t5 ORDER BY a; +} {1 i I 2 ii {} 3 iii II 4 four FOUR} + +# PG says ERROR: table name "t5" specified more than once +do_test 2.3.1 { catch { execsql { + UPDATE t5 SET b=1 FROM t5; +} } } 1 + +# PG says ERROR: table name "apples" specified more than once +do_test 2.3.2 { catch { execsql { + UPDATE t5 AS apples SET b=1 FROM t5 AS apples; +} } } 1 + +# Problem found by OSSFuzz on 2020-07-20 +# https://bugs.chromium.org/p/oss-fuzz/issues/detail?id=24282 +# +reset_db +do_execsql_test 3.1 { + CREATE TABLE t0(a); + CREATE TABLE t1(b); + UPDATE t1 SET b=sum(a) FROM t0; + SELECT * FROM t0, t1; +} {} + +finish_test diff --git a/testdata/tcl/upfrom2.test b/testdata/tcl/upfrom2.test new file mode 100644 index 0000000..f903c1f --- /dev/null +++ b/testdata/tcl/upfrom2.test @@ -0,0 +1,371 @@ +# 2020 April 29 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfrom2 + +# Test cases: +# +# 1.*: Test that triggers are fired correctly for UPDATE FROM statements, +# and only once for each row. Except for INSTEAD OF triggers on +# views - these are fired once for each row returned by the join, +# including duplicates. +# +# 2.*: Test adding ORDER BY and LIMIT clauses with UPDATE FROM statements. +# +# 5.*: Test that specifying the target table name or alias in the FROM +# clause of an UPDATE statement is an error. +# + +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + + eval [string map [list %WO% $wo %TN% $tn] { + do_execsql_test 1.%TN%.0 { + CREATE TABLE log(t TEXT); + CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE) %WO%; + CREATE INDEX t1y ON t1(y); + + INSERT INTO t1 VALUES(1, 'i', 'one'); + INSERT INTO t1 VALUES(2, 'ii', 'two'); + INSERT INTO t1 VALUES(3, 'iii', 'three'); + INSERT INTO t1 VALUES(4, 'iv', 'four'); + + CREATE TRIGGER tr1 BEFORE UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + END; + CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.y || '->' || new.y); + END; + } + + do_execsql_test 1.%TN%.1 { + WITH data(k, v) AS ( + VALUES(3, 'thirty'), (1, 'ten') + ) + UPDATE t1 SET z=v FROM data WHERE x=k; + + SELECT * FROM t1; + SELECT * FROM log; + } { + 1 i ten 2 ii two 3 iii thirty 4 iv four + one->ten i->i + three->thirty iii->iii + } + + do_execsql_test 1.%TN%.2 { + CREATE TABLE t2(a, b); + CREATE TABLE t3(k, v); + + INSERT INTO t3 VALUES(5, 'v'); + INSERT INTO t3 VALUES(12, 'xii'); + + INSERT INTO t2 VALUES(2, 12); + INSERT INTO t2 VALUES(3, 5); + + DELETE FROM log; + UPDATE t1 SET y=v FROM t2, t3 WHERE t1.x=t2.a AND t3.k=t2.b; + + SELECT * FROM t1; + SELECT * FROM log; + } { + 1 i ten 2 xii two 3 v thirty 4 iv four + two->two ii->xii + thirty->thirty iii->v + } + + do_execsql_test 1.%TN%.3 { + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(1, 'seven'), (1, 'eight'), (2, 'eleven'), (2, 'twelve') + ) + UPDATE t1 SET z=v FROM data WHERE x=k; + + SELECT * FROM t1; + SELECT * FROM log; + } { + 1 i eight 2 xii twelve 3 v thirty 4 iv four + ten->eight i->i + two->twelve xii->xii + } + + do_test 1.%TN%.4 { db changes } {2} + + do_execsql_test 1.%TN%.5 { + CREATE VIEW v1 AS SELECT * FROM t1; + CREATE TRIGGER v1tr INSTEAD OF UPDATE ON v1 BEGIN + UPDATE t1 SET y=new.y, z=new.z WHERE x=new.x; + END; + + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(3, 'thirteen'), (3, 'fourteen'), (4, 'fifteen'), (4, 'sixteen') + ) + UPDATE v1 SET z=v FROM data WHERE x=k; + } + + do_execsql_test 1.%TN%.6 { + SELECT * FROM v1; + SELECT * FROM log; + } { + 1 i eight 2 xii twelve 3 v fourteen 4 iv sixteen + thirty->thirteen v->v + thirteen->fourteen v->v + four->fifteen iv->iv + fifteen->sixteen iv->iv + } + + #-------------------------------------------------------------- + + do_execsql_test 1.%TN%.7 { + CREATE TABLE o1(w, x, y, z UNIQUE, PRIMARY KEY(w, x)) %WO%; + CREATE INDEX o1y ON t1(y); + + INSERT INTO o1 VALUES(0, 0, 'i', 'one'); + INSERT INTO o1 VALUES(0, 1, 'ii', 'two'); + INSERT INTO o1 VALUES(1, 0, 'iii', 'three'); + INSERT INTO o1 VALUES(1, 1, 'iv', 'four'); + + CREATE TRIGGER tro1 BEFORE UPDATE ON o1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + END; + CREATE TRIGGER tro2 AFTER UPDATE ON o1 BEGIN + INSERT INTO log VALUES(old.y || '->' || new.y); + END; + } + + do_execsql_test 1.%TN%.8 { + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(3, 'thirty'), (1, 'ten') + ) + UPDATE o1 SET z=v FROM data WHERE (1+x+w*2)=k; + + SELECT * FROM o1; + SELECT * FROM log; + } { + 0 0 i ten 0 1 ii two 1 0 iii thirty 1 1 iv four + one->ten i->i + three->thirty iii->iii + } + + do_execsql_test 1.%TN%.9 { + DELETE FROM log; + UPDATE o1 SET y=v FROM t2, t3 WHERE (1+o1.w*2+o1.x)=t2.a AND t3.k=t2.b; + + SELECT * FROM o1; + SELECT * FROM log; + } { + 0 0 i ten 0 1 xii two 1 0 v thirty 1 1 iv four + two->two ii->xii + thirty->thirty iii->v + } + + do_execsql_test 1.%TN%.10 { + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(1, 'seven'), (1, 'eight'), (2, 'eleven'), (2, 'twelve') + ) + UPDATE o1 SET z=v FROM data WHERE (1+w*2+x)=k; + + SELECT * FROM o1; + SELECT * FROM log; + } { + 0 0 i eight 0 1 xii twelve 1 0 v thirty 1 1 iv four + ten->eight i->i + two->twelve xii->xii + } + + do_test 1.%TN%.11 { db changes } {2} + + do_execsql_test 1.%TN%.12 { + CREATE VIEW w1 AS SELECT * FROM o1; + CREATE TRIGGER w1tr INSTEAD OF UPDATE ON w1 BEGIN + UPDATE o1 SET y=new.y, z=new.z WHERE w=new.w AND x=new.x; + END; + + DELETE FROM log; + WITH data(k, v) AS ( + VALUES(3, 'thirteen'), (3, 'fourteen'), (4, 'fifteen'), (4, 'sixteen') + ) + UPDATE w1 SET z=v FROM data WHERE (1+w*2+x)=k; + } + + do_execsql_test 1.%TN%.13 { + SELECT * FROM w1; + SELECT * FROM log; + } { + 0 0 i eight 0 1 xii twelve 1 0 v fourteen 1 1 iv sixteen + thirty->thirteen v->v + thirteen->fourteen v->v + four->fifteen iv->iv + fifteen->sixteen iv->iv + } + +}] +} + +ifcapable update_delete_limit { +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + +eval [string map [list %WO% $wo %TN% $tn] { + do_execsql_test 2.%TN%.1 { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b) %WO%; + INSERT INTO x1 VALUES + (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), + (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight'); + } + + do_execsql_test 2.%TN%.2 { + CREATE TABLE data1(x, y); + INSERT INTO data1 VALUES + (1, 'eleven'), (1, 'twenty-one'), (2, 'twelve'), (2, 'twenty-two'), + (3, 'thirteen'), (3, 'twenty-three'), (4, 'fourteen'), (4, 'twenty-four'); + } + + do_execsql_test 2.%TN%.3 { + UPDATE x1 SET b=y FROM data1 WHERE a=x ORDER BY a LIMIT 3; + SELECT * FROM x1; + } { + 1 eleven 2 twelve 3 thirteen 4 four 5 five 6 six 7 seven 8 eight + } + + do_execsql_test 2.%TN%.4 { + UPDATE x1 SET b=b||y FROM data1 WHERE a=x ORDER BY b LIMIT 3; + SELECT * FROM x1; + } { + 1 eleveneleven 2 twelve 3 thirteenthirteen 4 fourfourteen + 5 five 6 six 7 seven 8 eight + } + + do_catchsql_test 2.%TN%.5 { + UPDATE x1 SET b=b||b ORDER BY b; + } {1 {ORDER BY without LIMIT on UPDATE}} + do_catchsql_test 2.%TN%.6 { + UPDATE x1 SET b=b||y FROM data1 WHERE a=x ORDER BY b; + } {1 {ORDER BY without LIMIT on UPDATE}} + + #----------------------------------------------------------------------- + + do_execsql_test 2.%TN%.6 { + DROP TABLE x1; + CREATE TABLE x1(u, v, b, PRIMARY KEY(u, v)) %WO%; + INSERT INTO x1 VALUES + (0, 1, 'one'), (1, 0, 'two'), (1, 1, 'three'), (2, 0, 'four'), + (2, 1, 'five'), (3, 0, 'six'), (3, 1, 'seven'), (4, 0, 'eight'); + } + + do_execsql_test 2.%TN%.7 { + UPDATE x1 SET b=y FROM data1 WHERE (u*2+v)=x ORDER BY u, v LIMIT 3; + SELECT * FROM x1; + } { + 0 1 eleven 1 0 twelve 1 1 thirteen 2 0 four + 2 1 five 3 0 six 3 1 seven 4 0 eight + } + + do_execsql_test 2.%TN%.8 { + UPDATE x1 SET b=b||y FROM data1 WHERE (u*2+v)=x ORDER BY b LIMIT 3; + SELECT * FROM x1; + } { + 0 1 eleveneleven 1 0 twelve 1 1 thirteenthirteen 2 0 fourfourteen + 2 1 five 3 0 six 3 1 seven 4 0 eight + } + + +}] +}} + +reset_db +do_execsql_test 3.0 { + CREATE TABLE data(x, y, z); + CREATE VIEW t1 AS SELECT * FROM data; + CREATE TRIGGER t1_insert INSTEAD OF INSERT ON t1 BEGIN + INSERT INTO data VALUES(new.x, new.y, new.z); + END; + CREATE TRIGGER t1_update INSTEAD OF UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + END; + + CREATE TABLE log(t TEXT); + + INSERT INTO t1 VALUES(1, 'i', 'one'); + INSERT INTO t1 VALUES(2, 'ii', 'two'); + INSERT INTO t1 VALUES(3, 'iii', 'three'); + INSERT INTO t1 VALUES(4, 'iv', 'four'); +} + +do_execsql_test 3.1 { + WITH input(k, v) AS ( + VALUES(3, 'thirty'), (1, 'ten') + ) + UPDATE t1 SET z=v FROM input WHERE x=k; +} + +foreach {tn sql} { + 2 { + CREATE TABLE x1(a INT PRIMARY KEY, b, c) WITHOUT ROWID; + } + 1 { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c); + } + 3 { + CREATE TABLE x1(a INT PRIMARY KEY, b, c); + } +} { + + reset_db + execsql $sql + + do_execsql_test 4.$tn.0 { + INSERT INTO x1 VALUES(1, 1, 1); + INSERT INTO x1 VALUES(2, 2, 2); + INSERT INTO x1 VALUES(3, 3, 3); + INSERT INTO x1 VALUES(4, 4, 4); + INSERT INTO x1 VALUES(5, 5, 5); + CREATE TABLE map(o, t); + INSERT INTO map VALUES(3, 30), (4, 40), (1, 10); + } + + do_execsql_test 4.$tn.1 { + UPDATE x1 SET a=t FROM map WHERE a=o; + SELECT * FROM x1 ORDER BY a; + } {2 2 2 5 5 5 10 1 1 30 3 3 40 4 4} +} + +reset_db +do_execsql_test 5.0 { + CREATE TABLE x1(a, b, c); + CREATE TABLE x2(a, b, c); +} + +foreach {tn update nm} { + 1 "UPDATE x1 SET a=5 FROM x1" x1 + 2 "UPDATE x1 AS grapes SET a=5 FROM x1 AS grapes" grapes + 3 "UPDATE x1 SET a=5 FROM x2, x1" x1 + 4 "UPDATE x1 AS grapes SET a=5 FROM x2, x1 AS grapes" grapes +} { + do_catchsql_test 5.$tn $update \ + "1 {target object/alias may not appear in FROM clause: $nm}" +} + + +finish_test + + diff --git a/testdata/tcl/upfrom3.test b/testdata/tcl/upfrom3.test new file mode 100644 index 0000000..d30b3fa --- /dev/null +++ b/testdata/tcl/upfrom3.test @@ -0,0 +1,262 @@ +# 2020 July 14 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfrom3 + +# Test plan: +# +# 1.*: Test UPDATE ... FROM statements that modify IPK fields. And that +# modify "INTEGER PRIMARY KEY" fields on WITHOUT ROWID tables. +# +# 2.*: Test UPDATE ... FROM statements that modify PK fields of WITHOUT +# ROWID tables. +# +# 3.*: Test that UPDATE ... FROM statements are not confused if there +# are multiple tables of the same name in attached databases. +# +# 4.*: Tests for UPDATE ... FROM statements and foreign keys. +# + +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + eval [string map [list %WO% $wo %TN% $tn] { + + do_execsql_test 1.%TN%.0 { + CREATE TABLE log(t TEXT); + CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z UNIQUE) %WO%; + CREATE INDEX t1y ON t1(y); + + INSERT INTO t1 VALUES(1, 'i', 'one'); + INSERT INTO t1 VALUES(2, 'ii', 'two'); + INSERT INTO t1 VALUES(3, 'iii', 'three'); + INSERT INTO t1 VALUES(4, 'iv', 'four'); + } + + do_execsql_test 1.%TN%.1 { + CREATE TABLE x1(o, n); + INSERT INTO x1 VALUES(1, 11); + INSERT INTO x1 VALUES(2, 12); + INSERT INTO x1 VALUES(3, 13); + INSERT INTO x1 VALUES(4, 14); + UPDATE t1 SET x=n FROM x1 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 11 i one + 12 ii two + 13 iii three + 14 iv four + } + + do_test 1.%TN%.2 { db changes } 4 + + do_execsql_test 1.%TN%.3 { + INSERT INTO x1 VALUES(11, 21); + INSERT INTO x1 VALUES(12, 22); + INSERT INTO x1 VALUES(13, 23); + INSERT INTO x1 VALUES(14, 24); + + INSERT INTO x1 VALUES(21, 31); + INSERT INTO x1 VALUES(22, 32); + INSERT INTO x1 VALUES(23, 33); + INSERT INTO x1 VALUES(24, 34); + UPDATE t1 SET x=n FROM x1 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 21 i one + 22 ii two + 23 iii three + 24 iv four + } + + do_execsql_test 1.%TN%.4 { + UPDATE t1 SET x=n FROM x1 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 31 i one + 32 ii two + 33 iii three + 34 iv four + } + + do_execsql_test 1.%TN%.5 { + INSERT INTO x1 VALUES(31, 32); + INSERT INTO x1 VALUES(33, 34); + UPDATE OR REPLACE t1 SET x=n FROM x1 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 32 i one + 34 iii three + } + + do_execsql_test 1.%TN%.6 { + INSERT INTO t1 VALUES(33, 'ii', 'two'); + INSERT INTO t1 VALUES(35, 'iv', 'four'); + } + + do_execsql_test 1.%TN%.7 { + CREATE TABLE x2(o, n, zz); + INSERT INTO x2 VALUES(32, 41, 'four'); + INSERT INTO x2 VALUES(33, 42, 'three'); + UPDATE OR IGNORE t1 SET x=n, z=zz FROM x2 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 32 i one + 33 ii two + 34 iii three + 35 iv four + } + + do_execsql_test 1.%TN%.8 { + UPDATE OR REPLACE t1 SET x=n, z=zz FROM x2 WHERE x=o; + SELECT x, y, z FROM t1 ORDER BY 1; + } { + 41 i four + 42 ii three + } + + }] +} + +do_execsql_test 2.1.1 { + CREATE TABLE u1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; + INSERT INTO u1 VALUES(0, 0, 0); + INSERT INTO u1 VALUES(1, 0, 1); + INSERT INTO u1 VALUES(2, 1, 0); + INSERT INTO u1 VALUES(3, 1, 1); +} + +do_execsql_test 2.1.2 { + CREATE TABLE map(f, t); + INSERT INTO map VALUES(0, 10); + INSERT INTO map VALUES(1, 11); + UPDATE u1 SET c=t FROM map WHERE c=f; + SELECT * FROM u1 ORDER BY a; +} { + 0 0 10 + 1 0 11 + 2 1 10 + 3 1 11 +} + +do_execsql_test 2.1.3 { + UPDATE u1 SET b=t FROM map WHERE b=f; + SELECT * FROM u1 ORDER BY a; +} { + 0 10 10 + 1 10 11 + 2 11 10 + 3 11 11 +} + +do_execsql_test 2.1.4 { + CREATE TABLE map2(o1, o2, n1, n2); + INSERT INTO map2 VALUES + (10, 10, 50, 50), (10, 11, 50, 60), + (11, 10, 60, 50), (11, 11, 60, 60); + UPDATE u1 SET b=n1, c=n2 FROM map2 WHERE b=o1 AND c=o2; + SELECT * FROM u1 ORDER BY a; +} { + 0 50 50 + 1 50 60 + 2 60 50 + 3 60 60 +} + +#------------------------------------------------------------------------- +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + forcedelete test.db2 + eval [string map [list %WO% $wo %TN% $tn] { + do_execsql_test 3.$tn.1 { + CREATE TABLE g1(a, b, c, PRIMARY KEY(a, b)) %WO%; + INSERT INTO g1 VALUES(1, 1, 1); + + ATTACH 'test.db2' AS aux; + CREATE TABLE aux.g1(a, b, c, PRIMARY KEY(a, b)) %WO%; + INSERT INTO aux.g1 VALUES(10, 1, 10); + INSERT INTO aux.g1 VALUES(20, 2, 20); + INSERT INTO aux.g1 VALUES(30, 3, 30); + } + + do_execsql_test 3.$tn.2 { + UPDATE aux.g1 SET c=101 FROM main.g1; + } + do_execsql_test 3.$tn.3 { + SELECT * FROM aux.g1; + } {10 1 101 20 2 101 30 3 101} + + do_execsql_test 3.$tn.4 { + UPDATE g1 SET c=101 FROM g1 AS g2; + } + do_execsql_test 3.$tn.5 { + SELECT * FROM g1; + } {1 1 101} + }] +} + +#------------------------------------------------------------------------- +reset_db +foreach {tn wo} { + 1 "" + 2 "WITHOUT ROWID" +} { + reset_db + forcedelete test.db2 + eval [string map [list %WO% $wo %TN% $tn] { + + do_execsql_test 4.$tn.1 { + CREATE TABLE p1(a INTEGER PRIMARY KEY, b) %WO%; + CREATE TABLE c1(x PRIMARY KEY, y REFERENCES p1 ON UPDATE CASCADE) %WO%; + PRAGMA foreign_keys = 1; + + INSERT INTO p1 VALUES(1, 'one'); + INSERT INTO p1 VALUES(11, 'eleven'); + INSERT INTO p1 VALUES(111, 'eleventyone'); + + INSERT INTO c1 VALUES('a', 1); + INSERT INTO c1 VALUES('b', 11); + INSERT INTO c1 VALUES('c', 111); + } + + do_execsql_test 4.$tn.2 { + CREATE TABLE map(f, t); + INSERT INTO map VALUES('a', 111); + INSERT INTO map VALUES('c', 112); + } + + do_catchsql_test 4.$tn.3 { + UPDATE c1 SET y=t FROM map WHERE x=f; + } {1 {FOREIGN KEY constraint failed}} + + do_execsql_test 4.$tn.4 { + INSERT INTO map VALUES('eleven', 12); + INSERT INTO map VALUES('eleventyone', 112); + UPDATE p1 SET a=t FROM map WHERE b=f; + } + + do_execsql_test 4.$tn.5 { + SELECT * FROM c1 + } {a 1 b 12 c 112} + + }] +} + +finish_test + diff --git a/testdata/tcl/upfromfault.test b/testdata/tcl/upfromfault.test new file mode 100644 index 0000000..fcb5956 --- /dev/null +++ b/testdata/tcl/upfromfault.test @@ -0,0 +1,140 @@ +# 2020 April 29 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfromfault + +foreach {tn sql} { + 1 { + CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE); + CREATE INDEX t1y ON t1(y); + } + 2 { + CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE) WITHOUT ROWID; + CREATE INDEX t1y ON t1(y); + } + 3 { + CREATE TABLE t1(x, y, z UNIQUE, PRIMARY KEY(x,y)) WITHOUT ROWID; + } + 4 { + CREATE VIRTUAL TABLE t1 USING fts5(x, y, z); + } + 5 { + CREATE TABLE real(x, y, z); + CREATE VIEW t1 AS SELECT * FROM real; + CREATE TRIGGER t1_insert INSTEAD OF INSERT ON t1 BEGIN + INSERT INTO real VALUES(new.x, new.y, new.z); + END; + CREATE TRIGGER t1_update INSTEAD OF UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + UPDATE real SET y=new.y, z=new.z WHERE x=old.x; + END; + } +} { +if {$tn<5} continue + reset_db + + ifcapable !fts5 { if {$tn==4} continue } + + execsql $sql + do_execsql_test 1.$tn.0 { + CREATE TABLE log(t TEXT); + + INSERT INTO t1 VALUES(1, 'i', 'one'); + INSERT INTO t1 VALUES(2, 'ii', 'two'); + INSERT INTO t1 VALUES(3, 'iii', 'three'); + INSERT INTO t1 VALUES(4, 'iv', 'four'); + } + if {$tn!=4 && $tn!=5} { + do_execsql_test 1.$tn.0b { + CREATE TRIGGER tr1 BEFORE UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.z || '->' || new.z); + END; + CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.y || '->' || new.y); + END; + } + } + + faultsim_save_and_close + + do_faultsim_test 1.$tn -prep { + faultsim_restore_and_reopen + execsql { SELECT * FROM t1 } + } -body { + execsql { + WITH data(k, v) AS ( + VALUES(3, 'thirty'), (1, 'ten') + ) + UPDATE t1 SET z=v FROM data WHERE x=k; + } + } -test { + faultsim_test_result {0 {}} {1 {vtable constructor failed: t1}} + if {$testrc==0} { + set res [execsql { SELECT * FROM t1 }] + if {$res!="1 i ten 2 ii two 3 iii thirty 4 iv four"} { + error "unexpected result: $res" + } + } + } +} + +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(x, y, z); +} +faultsim_save_and_close +do_faultsim_test 2.1 -prep { + faultsim_restore_and_reopen +} -body { + execsql { + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN + UPDATE t2 SET x=a FROM t1 WHERE c=z; + END; + } +} -test { + faultsim_test_result {0 {}} +} + +faultsim_restore_and_reopen +do_execsql_test 2.2 { + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN + UPDATE t1 SET a=x FROM t2 WHERE c=z; + END; + + INSERT INTO t2 VALUES(1, 1, 1); + INSERT INTO t2 VALUES(2, 2, 2); + INSERT INTO t2 VALUES(3, 3, 3); +} +faultsim_save_and_close + +do_faultsim_test 2.3 -prep { + faultsim_restore_and_reopen +} -body { + execsql { + INSERT INTO t1 VALUES(NULL, NULL, 1), (NULL, NULL, 3); + } +} -test { + faultsim_test_result {0 {}} + if {$testrc==0} { + set res [execsql { SELECT * FROM t1 }] + if {$res!="1 {} 1 3 {} 3"} { + error "unexpected result: $res" + } + } +} + + +finish_test + diff --git a/testdata/tcl/wal2.test b/testdata/tcl/wal2.test index 9a56eb4..ae6134d 100644 --- a/testdata/tcl/wal2.test +++ b/testdata/tcl/wal2.test @@ -122,8 +122,12 @@ do_test wal2-1.1 { } {4 10} set RECOVER [list \ - {0 1 lock exclusive} {1 2 lock exclusive} {4 4 lock exclusive} \ - {1 2 unlock exclusive} {4 4 unlock exclusive} {0 1 unlock exclusive} \ + {0 1 lock exclusive} {1 2 lock exclusive} \ + {4 1 lock exclusive} {4 1 unlock exclusive} \ + {5 1 lock exclusive} {5 1 unlock exclusive} \ + {6 1 lock exclusive} {6 1 unlock exclusive} \ + {7 1 lock exclusive} {7 1 unlock exclusive} \ + {1 2 unlock exclusive} {0 1 unlock exclusive} \ ] set READ [list \ {4 1 lock shared} {4 1 unlock shared} \ @@ -394,9 +398,17 @@ set expected_locks [list] lappend expected_locks {1 1 lock exclusive} ;# Lock checkpoint lappend expected_locks {0 1 lock exclusive} ;# Lock writer lappend expected_locks {2 1 lock exclusive} ;# Lock recovery -lappend expected_locks {4 4 lock exclusive} ;# Lock all aReadMark[] +# lappend expected_locks {4 4 lock exclusive} ;# Lock all aReadMark[] +lappend expected_locks {4 1 lock exclusive} ;# Lock aReadMark[1] +lappend expected_locks {4 1 unlock exclusive} ;# Unlock aReadMark[1] +lappend expected_locks {5 1 lock exclusive} +lappend expected_locks {5 1 unlock exclusive} +lappend expected_locks {6 1 lock exclusive} +lappend expected_locks {6 1 unlock exclusive} +lappend expected_locks {7 1 lock exclusive} +lappend expected_locks {7 1 unlock exclusive} lappend expected_locks {2 1 unlock exclusive} ;# Unlock recovery -lappend expected_locks {4 4 unlock exclusive} ;# Unlock all aReadMark[] +# lappend expected_locks {4 4 unlock exclusive} ;# Unlock all aReadMark[] lappend expected_locks {0 1 unlock exclusive} ;# Unlock writer lappend expected_locks {3 1 lock exclusive} ;# Lock aReadMark[0] lappend expected_locks {3 1 unlock exclusive} ;# Unlock aReadMark[0] @@ -625,8 +637,12 @@ do_test wal2-6.4.1 { } {} set RECOVERY { - {0 1 lock exclusive} {1 2 lock exclusive} {4 4 lock exclusive} - {1 2 unlock exclusive} {4 4 unlock exclusive} {0 1 unlock exclusive} + {0 1 lock exclusive} {1 2 lock exclusive} + {4 1 lock exclusive} {4 1 unlock exclusive} + {5 1 lock exclusive} {5 1 unlock exclusive} + {6 1 lock exclusive} {6 1 unlock exclusive} + {7 1 lock exclusive} {7 1 unlock exclusive} + {1 2 unlock exclusive} {0 1 unlock exclusive} } set READMARK0_READ { {3 1 lock shared} {3 1 unlock shared} diff --git a/testdata/tcl/walprotocol.test b/testdata/tcl/walprotocol.test index b1d9e8c..a262cdd 100644 --- a/testdata/tcl/walprotocol.test +++ b/testdata/tcl/walprotocol.test @@ -52,18 +52,28 @@ do_test 1.1 { set ::locks [list] sqlite3 db test.db -vfs T execsql { SELECT * FROM x } - lrange $::locks 0 5 -} [list {0 1 lock exclusive} {1 2 lock exclusive} {4 4 lock exclusive} \ - {1 2 unlock exclusive} {4 4 unlock exclusive} {0 1 unlock exclusive} \ + lrange $::locks 0 11 +} [list {0 1 lock exclusive} {1 2 lock exclusive} \ + {4 1 lock exclusive} {4 1 unlock exclusive} \ + {5 1 lock exclusive} {5 1 unlock exclusive} \ + {6 1 lock exclusive} {6 1 unlock exclusive} \ + {7 1 lock exclusive} {7 1 unlock exclusive} \ + {1 2 unlock exclusive} \ + {0 1 unlock exclusive} \ ] do_test 1.2 { db close set ::locks [list] sqlite3 db test.db -vfs T execsql { SELECT * FROM x } - lrange $::locks 0 5 -} [list {0 1 lock exclusive} {1 2 lock exclusive} {4 4 lock exclusive} \ - {1 2 unlock exclusive} {4 4 unlock exclusive} {0 1 unlock exclusive} \ + lrange $::locks 0 11 +} [list {0 1 lock exclusive} {1 2 lock exclusive} \ + {4 1 lock exclusive} {4 1 unlock exclusive} \ + {5 1 lock exclusive} {5 1 unlock exclusive} \ + {6 1 lock exclusive} {6 1 unlock exclusive} \ + {7 1 lock exclusive} {7 1 unlock exclusive} \ + {1 2 unlock exclusive} \ + {0 1 unlock exclusive} \ ] proc lock_callback {method filename handle lock} { if {$lock == "1 2 lock exclusive"} { return SQLITE_BUSY } @@ -101,7 +111,7 @@ do_test 1.5 { set ::locks [list] sqlite3 db test.db -vfs T catchsql { SELECT * FROM x } -} {1 {locking protocol}} +} {0 z} db close T delete @@ -160,7 +170,7 @@ do_test 2.5 { } {Tehran Qom Markazi Qazvin Gilan Ardabil} do_test 2.6 { set ::r -} {1 {locking protocol}} +} {0 {Tehran Qom Markazi Qazvin Gilan Ardabil}} db close db2 close @@ -182,7 +192,7 @@ do_test 2.7 { } {Tehran Qom Markazi Qazvin Gilan Ardabil} do_test 2.8 { set ::r -} {1 {locking protocol}} +} {0 {Tehran Qom Markazi Qazvin Gilan Ardabil}} db close db2 close diff --git a/testdata/tcl/walvfs.test b/testdata/tcl/walvfs.test index da0f43c..f21b65e 100644 --- a/testdata/tcl/walvfs.test +++ b/testdata/tcl/walvfs.test @@ -145,7 +145,7 @@ proc xWrite {method file args} { if {[file tail $file]=="test.db"} { incr ::cnt -1 if {$::cnt==0} { - sqlite3_memdebug_fail 5 -repeat 0 + sqlite3_memdebug_fail 1 -repeat 0 catchsql { SELECT 'a big long string!' } sqlite3_interrupt db } diff --git a/testdata/tcl/wapptest.tcl b/testdata/tcl/wapptest.tcl index 201078e..b7e16e7 100644 --- a/testdata/tcl/wapptest.tcl +++ b/testdata/tcl/wapptest.tcl @@ -164,7 +164,7 @@ proc count_tests_and_errors {name logfile} { } if {[regexp {runtime error: +(.*)} $line all msg]} { # skip over "value is outside range" errors - if {[regexp {value .* is outside the range of representable} $line]} { + if {[regexp {.* is outside the range of representable} $line]} { # noop } else { incr G(test.$name.nError) @@ -894,4 +894,3 @@ if {$G(noui)==0} { do_some_stuff vwait forever } - diff --git a/testdata/tcl/where.test b/testdata/tcl/where.test index 26bf3a0..9b072da 100644 --- a/testdata/tcl/where.test +++ b/testdata/tcl/where.test @@ -1496,8 +1496,8 @@ do_execsql_test where-25.0 { INSERT INTO t2 VALUES(3, 'three', 'iii'); PRAGMA writable_schema = 1; - UPDATE sqlite_master SET rootpage = ( - SELECT rootpage FROM sqlite_master WHERE name = 'i2' + UPDATE sqlite_schema SET rootpage = ( + SELECT rootpage FROM sqlite_schema WHERE name = 'i2' ) WHERE name = 'i1'; } db close @@ -1524,8 +1524,8 @@ do_execsql_test where-25.3 { INSERT INTO t2 VALUES(3, 'three', 'iii'); PRAGMA writable_schema = 1; - UPDATE sqlite_master SET rootpage = ( - SELECT rootpage FROM sqlite_master WHERE name = 'i2' + UPDATE sqlite_schema SET rootpage = ( + SELECT rootpage FROM sqlite_schema WHERE name = 'i2' ) WHERE name = 'i1'; } db close diff --git a/testdata/tcl/where9.test b/testdata/tcl/where9.test index b274609..0f770df 100644 --- a/testdata/tcl/where9.test +++ b/testdata/tcl/where9.test @@ -426,7 +426,7 @@ do_test where9-4.5 { AND (c=31031 OR d IS NULL) ORDER BY +a } -} {1 {no query solution}} +} {0 {92 93 97}} do_test where9-4.6 { count_steps { SELECT a FROM t1 NOT INDEXED @@ -442,7 +442,7 @@ do_test where9-4.7 { AND (c=31031 OR d IS NULL) ORDER BY +a } -} {1 {no query solution}} +} {0 {92 93 97}} do_test where9-4.8 { catchsql { SELECT a FROM t1 INDEXED BY t1d @@ -450,7 +450,7 @@ do_test where9-4.8 { AND (c=31031 OR d IS NULL) ORDER BY +a } -} {1 {no query solution}} +} {0 {92 93 97}} # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because # the former is an equality test which is expected to return fewer rows. @@ -776,7 +776,7 @@ do_test where9-6.8.1 { OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } -} {1 {no query solution}} +} {0 {}} do_test where9-6.8.2 { catchsql { UPDATE t1 INDEXED BY t1b SET a=a+100 @@ -784,7 +784,7 @@ do_test where9-6.8.2 { OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } -} {1 {no query solution}} +} {0 {}} set solution_possible 0 ifcapable stat4 { @@ -818,7 +818,7 @@ if $solution_possible { OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } - } {1 {no query solution}} + } {0 {}} do_test where9-6.8.4 { catchsql { DELETE FROM t1 INDEXED BY t1b @@ -826,7 +826,7 @@ if $solution_possible { OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } - } {1 {no query solution}} + } {0 {}} } ############################################################################ # Test cases where terms inside an OR series are combined with AND terms diff --git a/testdata/tcl/wherelimit2.test b/testdata/tcl/wherelimit2.test index 83c04b1..8e39127 100644 --- a/testdata/tcl/wherelimit2.test +++ b/testdata/tcl/wherelimit2.test @@ -218,18 +218,22 @@ do_execsql_test 4.1 { ROLLBACK; } {3 4 5 6} -do_catchsql_test 4.2 { - DELETE FROM x1 INDEXED BY x1bc WHERE d=3 LIMIT 1; -} {1 {no query solution}} +# 2020-06-03: Query planner improved so that a solution is possible. +# +#do_catchsql_test 4.2 { +# DELETE FROM x1 INDEXED BY x1bc WHERE d=3 LIMIT 1; +#} {1 {no query solution}} do_execsql_test 4.3 { DELETE FROM x1 INDEXED BY x1bc WHERE b=3 LIMIT 1; SELECT a FROM x1; } {1 2 3 4 6} -do_catchsql_test 4.4 { - UPDATE x1 INDEXED BY x1bc SET d=5 WHERE d=3 LIMIT 1; -} {1 {no query solution}} +# 2020-06-03: Query planner improved so that a solution is possible. +# +#do_catchsql_test 4.4 { +# UPDATE x1 INDEXED BY x1bc SET d=5 WHERE d=3 LIMIT 1; +#} {1 {no query solution}} do_execsql_test 4.5 { UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1; diff --git a/testdata/tcl/window1.test b/testdata/tcl/window1.test index 618d95e..dbaf438 100644 --- a/testdata/tcl/window1.test +++ b/testdata/tcl/window1.test @@ -1947,4 +1947,58 @@ do_execsql_test 63.3 { FROM t1; } {{}} +#------------------------------------------------------------------------- +reset_db +do_execsql_test 64.1 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + INSERT INTO t1 VALUES(1, 'abcd'); + INSERT INTO t1 VALUES(2, 'BCDE'); + INSERT INTO t1 VALUES(3, 'cdef'); + INSERT INTO t1 VALUES(4, 'DEFG'); +} + +do_execsql_test 64.2 { + SELECT rowid, max(b COLLATE nocase)||'' + FROM t1 + GROUP BY rowid + ORDER BY max(b COLLATE nocase)||''; +} {1 abcd 2 BCDE 3 cdef 4 DEFG} + +do_execsql_test 64.3 { + SELECT count() OVER (), rowid, max(b COLLATE nocase)||'' + FROM t1 + GROUP BY rowid + ORDER BY max(b COLLATE nocase)||''; +} {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG} + +do_execsql_test 64.4 { + SELECT count() OVER (), rowid, max(b COLLATE nocase) + FROM t1 + GROUP BY rowid + ORDER BY max(b COLLATE nocase); +} {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 65.1 { + CREATE TABLE t1(c1); + INSERT INTO t1 VALUES('abcd'); +} +do_execsql_test 65.2 { + SELECT max(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1; +} {1} + +do_execsql_test 65.3 { + SELECT + count() OVER (), + group_concat(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1; +} {1 1} + +do_execsql_test 65.4 { + SELECT COUNT() OVER () LIKE lead(102030) OVER( + ORDER BY sum('abcdef' COLLATE nocase) IN (SELECT 54321) + ) + FROM t1; +} {{}} + finish_test diff --git a/testdata/tcl/without_rowid3.test b/testdata/tcl/without_rowid3.test index a9839e1..eae7e3c 100644 --- a/testdata/tcl/without_rowid3.test +++ b/testdata/tcl/without_rowid3.test @@ -942,7 +942,7 @@ ifcapable altertable { PRAGMA foreign_keys = off; ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; PRAGMA foreign_keys = on; - SELECT sql FROM sqlite_master WHERE name='t2'; + SELECT sql FROM sqlite_schema WHERE name='t2'; } } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} @@ -976,7 +976,7 @@ ifcapable altertable { WITHOUT rowid; CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); } - execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} + execsql { SELECT sql FROM sqlite_schema WHERE type = 'table'} } [list \ {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid} \ {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2) @@ -985,7 +985,7 @@ ifcapable altertable { ] do_test without_rowid3-14.2.2.2 { execsql { ALTER TABLE t1 RENAME TO t4 } - execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} + execsql { SELECT sql FROM sqlite_schema WHERE type = 'table'} } [list \ {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid} \ {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2) @@ -1037,7 +1037,7 @@ ifcapable altertable { PRAGMA foreign_keys = off; ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; PRAGMA foreign_keys = on; - SELECT sql FROM temp.sqlite_master WHERE name='t2'; + SELECT sql FROM temp.sqlite_schema WHERE name='t2'; } } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} @@ -1063,7 +1063,7 @@ ifcapable altertable { WITHOUT rowid; CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); } - execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'} + execsql { SELECT sql FROM sqlite_temp_schema WHERE type = 'table'} } [list \ {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid} \ {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2) @@ -1072,7 +1072,7 @@ ifcapable altertable { ] do_test without_rowid3-14.2tmp.2.2 { execsql { ALTER TABLE t1 RENAME TO t4 } - execsql { SELECT sql FROM temp.sqlite_master WHERE type = 'table'} + execsql { SELECT sql FROM temp.sqlite_schema WHERE type = 'table'} } [list \ {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid} \ {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2) @@ -1125,7 +1125,7 @@ ifcapable altertable { PRAGMA foreign_keys = off; ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; PRAGMA foreign_keys = on; - SELECT sql FROM aux.sqlite_master WHERE name='t2'; + SELECT sql FROM aux.sqlite_schema WHERE name='t2'; } } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} @@ -1151,7 +1151,7 @@ ifcapable altertable { WITHOUT rowid; CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); } - execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} + execsql { SELECT sql FROM aux.sqlite_schema WHERE type = 'table'} } [list \ {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid} \ {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2) @@ -1160,7 +1160,7 @@ ifcapable altertable { ] do_test without_rowid3-14.2aux.2.2 { execsql { ALTER TABLE t1 RENAME TO t4 } - execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} + execsql { SELECT sql FROM aux.sqlite_schema WHERE type = 'table'} } [list \ {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid} \ {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2) diff --git a/testdata/testfixture_linux_amd64-extraquick.golden b/testdata/testfixture_linux_amd64-extraquick.golden new file mode 100644 index 0000000..1f13701 --- /dev/null +++ b/testdata/testfixture_linux_amd64-extraquick.golden @@ -0,0 +1,12 @@ +$ make extraquick + +SQLite 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f +0 errors out of 210754 tests on 3900x Linux 64-bit little-endian +WARNING: Multi-threaded tests skipped: Linked against a non-threadsafe Tcl build +All memory allocations freed - no leaks +Maximum memory usage: 9156360 bytes +Current memory usage: 0 bytes +Number of malloc() : -1 calls +--- PASS: TestTclTest (136.14s) +PASS +ok modernc.org/sqlite 136.142s diff --git a/testdata/testfixture_linux_amd64-full.golden b/testdata/testfixture_linux_amd64-full.golden new file mode 100644 index 0000000..8c42e59 --- /dev/null +++ b/testdata/testfixture_linux_amd64-full.golden @@ -0,0 +1,12 @@ +$ make full + +SQLite 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f +0 errors out of 928271 tests on 3900x Linux 64-bit little-endian +WARNING: Multi-threaded tests skipped: Linked against a non-threadsafe Tcl build +All memory allocations freed - no leaks +Maximum memory usage: 9156360 bytes +Current memory usage: 0 bytes +Number of malloc() : -1 calls +--- PASS: TestTclTest (1785.04s) +PASS +ok modernc.org/sqlite 1785.041s diff --git a/testdata/testfixture_linux_amd64.golden b/testdata/testfixture_linux_amd64.golden deleted file mode 100644 index cd9ba68..0000000 --- a/testdata/testfixture_linux_amd64.golden +++ /dev/null @@ -1,188 +0,0 @@ -SQLite 2020-06-18 14:00:33 7ebdfa80be8e8e73324b8d66b3460222eb74c7e9dfd655b48d6ca7e1933cc8fd -176 errors out of 204817 tests on Linux 64-bit little-endian -!Failures on these tests: -bigmmap-2.0.0.3 -bigmmap-2.0.1.3 -bigmmap-2.0.2.3 -bigmmap-2.0.3.3 -bigmmap-2.0.4.3 -bigmmap-2.0.5.3 -bigmmap-2.0.6.3 -bigmmap-2.0.7.3 -bigmmap-2.1.0.3 -bigmmap-2.1.1.3 -bigmmap-2.1.2.3 -bigmmap-2.1.3.3 -bigmmap-2.1.4.3 -bigmmap-2.1.5.3 -bigmmap-2.1.6.3 -bigmmap-2.1.7.3 -bigmmap-2.2.0.3 -bigmmap-2.2.1.3 -bigmmap-2.2.2.3 -bigmmap-2.2.3.3 -bigmmap-2.2.4.3 -bigmmap-2.2.5.3 -bigmmap-2.2.6.3 -bigmmap-2.2.7.3 -bigmmap-2.3.0.3 -bigmmap-2.3.1.3 -bigmmap-2.3.2.3 -bigmmap-2.3.3.3 -bigmmap-2.3.4.3 -bigmmap-2.3.5.3 -bigmmap-2.3.6.3 -bigmmap-2.3.7.3 -bigmmap-2.4.0.3 -bigmmap-2.4.1.3 -bigmmap-2.4.2.3 -bigmmap-2.4.3.3 -bigmmap-2.4.4.3 -bigmmap-2.4.5.3 -bigmmap-2.4.6.3 -bigmmap-2.4.7.3 -bigmmap-2.5.0.3 -bigmmap-2.5.1.3 -bigmmap-2.5.2.3 -bigmmap-2.5.3.3 -bigmmap-2.5.4.3 -bigmmap-2.5.5.3 -bigmmap-2.5.6.3 -bigmmap-2.5.7.3 -bigmmap-2.6.0.3 -bigmmap-2.6.1.3 -bigmmap-2.6.2.3 -bigmmap-2.6.3.3 -bigmmap-2.6.4.3 -bigmmap-2.6.5.3 -bigmmap-2.6.6.3 -bigmmap-2.6.7.3 -bigmmap-2.7.0.3 -bigmmap-2.7.1.3 -bigmmap-2.7.2.3 -bigmmap-2.7.3.3 -bigmmap-2.7.4.3 -bigmmap-2.7.5.3 -bigmmap-2.7.6.3 -bigmmap-2.7.7.3 -bigmmap-2.8.0.3 -bigmmap-2.8.1.3 -bigmmap-2.8.2.3 -bigmmap-2.8.3.3 -bigmmap-2.8.4.3 -bigmmap-2.8.5.3 -bigmmap-2.8.6.3 -bigmmap-2.8.7.3 -check-2.1 -check-2.2 -check-2.3 -check-2.4 -check-2.5 -check-2.5b -check-2.6 -check-3.2 -check-3.4 -check-3.6 -collate1-6.1 -corruptL-1.1 -corruptL-1.2 -corruptL-1.3 -corruptL-1.4 -corruptL-2.1 -corruptL-2.2 -corruptL-4.1 -corruptL-5.1 -corruptL-5.2 -corruptL-5.3 -corruptL-6.1 -corruptL-7.1 -corruptL-8.1 -corruptL-9.3 -corruptL-10.1 -corruptL-11.1 -corruptL-12.1 -corruptL-13.1 -corruptL-14.1 -corruptL-14.2 -corruptL-15.1 -crash8-1.2 -crash8.2.1 -crash8-4.4 -crash8-4.7 -crash8-4.8 -crash8-4.10 -ctime-1.4.3 -ctime-2.1.3 -dbfuzz001-101a -dbfuzz001-110 -dbfuzz001-200 -dbfuzz001-320 -dbfuzz001-330 -eval-4.1 -expr-13.8 -expr-13.9 -func-21.3 -func-21.4 -func-21.5 -func-21.6 -func-21.7 -func-21.8 -func-29.1 -func-29.3 -func-29.5 -gencol1-15.20 -in-8.2 -join-7.1 -journal3-1.2.2.4 -lock2-1.1 -lock2-1.3 -lock2-1.4 -lock2-1.5 -lock2-1.6 -lock2-1.7 -lock2-1.8 -lock2-1.10 -lock4-1.2 -lock4-1.3 -lock4-999.1 -pragma3-201 -pragma3-400 -pragma3-410 -pragma3-420 -pragma3-430 -select6-3.2 -shared9-3.2 -shared9-3.4 -shared9-3.5 -shared9-3.6 -subquery-5.1 -subquery-5.2 -swarmvtab-3.1 -swarmvtab-3.2 -swarmvtab-3.3.1 -swarmvtab-3.3.2 -symlink-1.3 -tkt-7bbfb7d442-2.3 -tkt3442-1.3 -tkt3841.1 -tkt4018-1.3 -tkt4018-1.4 -tkt4018-2.2 -update-15.1 -utf16align-1.0 -utf16align-1.2 -vtab6-7.1 -vtabH-3.0 -wal2-12.2.2.4 -walvfs-5.3 -window6-6.1 -writecrash-1.1.1 -WARNING: Multi-threaded tests skipped: Linked against a non-threadsafe Tcl build -All memory allocations freed - no leaks -Memory used: now 0 max 807440 max-size 100000016 -Allocation count: now 0 max 1311135 -Page-cache used: now 0 max 13 max-size 16648 -Page-cache overflow: now 0 max 6165632 -Maximum memory usage: 807440 bytes -Current memory usage: 0 bytes -Number of malloc() : -1 calls |