aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/altertab2.test11
-rw-r--r--test/altertab3.test69
-rw-r--r--test/check.test69
-rw-r--r--test/conflict.test22
-rw-r--r--test/conflict3.test12
-rw-r--r--test/exclusive.test18
-rw-r--r--test/filter1.test20
-rw-r--r--test/fts3corrupt4.test54
-rw-r--r--test/fts4langid.test15
-rw-r--r--test/fuzzcheck.c39
-rw-r--r--test/fuzzdata8.dbbin1501184 -> 1620992 bytes
-rw-r--r--test/gencol1.test202
-rw-r--r--test/join.test49
-rw-r--r--test/join2.test14
-rw-r--r--test/nulls1.test50
-rw-r--r--test/pragma3.test29
-rw-r--r--test/rowvalue.test9
-rw-r--r--test/select1.test20
-rw-r--r--test/update.test72
-rw-r--r--test/upsert1.test8
-rw-r--r--test/whereG.test12
-rw-r--r--test/window1.test216
-rw-r--r--test/window2.tcl41
-rw-r--r--test/window2.test43
-rw-r--r--test/windowfault.test19
-rw-r--r--test/zipfile.test41
26 files changed, 1107 insertions, 47 deletions
diff --git a/test/altertab2.test b/test/altertab2.test
index f14dc13ff..9c1ad5813 100644
--- a/test/altertab2.test
+++ b/test/altertab2.test
@@ -343,22 +343,21 @@ do_execsql_test 8.4 {
CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2;
}
-# Do not rename branches of an expression tree that is optimized out by
-# the AND optimization.
+# Branches of an expression tree that are optimized out by the AND
+# optimization are renamed.
#
do_execsql_test 8.5 {
ALTER TABLE t4 RENAME a TO c;
SELECT sql FROM sqlite_master WHERE name = 'v4'
-} {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2}}
-# "a" is not renamed to "c" ---^
+} {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (c=1 AND 0) OR b=2}}
# 2019-06-10 https://www.sqlite.org/src/info/533010b8cacebe82
reset_db
-do_execsql_test 8.6 {
+do_catchsql_test 8.6 {
CREATE TABLE t0(c0);
CREATE INDEX i0 ON t0(LIKELIHOOD(1,2) AND 0);
ALTER TABLE t0 RENAME TO t1;
SELECT sql FROM sqlite_master WHERE name='i0';
-} {{CREATE INDEX i0 ON "t1"(LIKELIHOOD(1,2) AND 0)}}
+} {1 {error in index i0: second argument to likelihood() must be a constant between 0.0 and 1.0}}
finish_test
diff --git a/test/altertab3.test b/test/altertab3.test
index 88d882fcb..b39065589 100644
--- a/test/altertab3.test
+++ b/test/altertab3.test
@@ -517,5 +517,74 @@ do_catchsql_test 22.6 {
ALTER TABLE t1 RENAME TO t4;
} {0 {}}
+#------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 23.1 {
+ CREATE TABLE t1(x);
+ CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
+ UPDATE t1 SET (c,d)=((SELECT 1 FROM t1 JOIN t2 ON b=x),1);
+ END;
+}
+
+do_catchsql_test 23.2 {
+ ALTER TABLE t1 RENAME TO t1x;
+} {1 {error in trigger r1: no such table: main.t2}}
+
+#------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 23.1 {
+ CREATE TABLE v0 (a);
+ CREATE VIEW v2 (v3) AS
+ WITH x1 AS (SELECT * FROM v2)
+ SELECT v3 AS x, v3 AS y FROM v2;
+}
+
+do_catchsql_test 23.2 {
+ SELECT * FROM v2
+} {1 {view v2 is circularly defined}}
+
+db close
+sqlite3 db test.db
+
+do_catchsql_test 23.3 {
+ ALTER TABLE v0 RENAME TO t3 ;
+} {1 {error in view v2: view v2 is circularly defined}}
+
+#------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 24.1 {
+ CREATE TABLE v0 (v1);
+ CREATE TABLE v2 (v3 INTEGER UNIQUE ON CONFLICT ABORT);
+ CREATE TRIGGER x AFTER INSERT ON v2 WHEN (
+ ( SELECT v1 AS PROMO_REVENUE FROM v2 JOIN v0 USING ( VALUE ) ) AND 0 )
+ BEGIN
+ DELETE FROM v2;
+ END;
+}
+do_catchsql_test 24.2 {
+ ALTER TABLE v0 RENAME TO x ;
+} {1 {error in trigger x: cannot join using column VALUE - column not present in both tables}}
+
+do_execsql_test 24.3 {
+ DROP TRIGGER x;
+ CREATE TRIGGER x AFTER INSERT ON v2 WHEN (
+ 0 AND (SELECT rowid FROM v0)
+ ) BEGIN
+ DELETE FROM v2;
+ END;
+}
+
+do_execsql_test 24.4 {
+ ALTER TABLE v0 RENAME TO xyz;
+ SELECT sql FROM sqlite_master WHERE type='trigger'
+} {{CREATE TRIGGER x AFTER INSERT ON v2 WHEN (
+ 0 AND (SELECT rowid FROM "xyz")
+ ) BEGIN
+ DELETE FROM v2;
+ END}}
finish_test
+
diff --git a/test/check.test b/test/check.test
index ac0a9ac6b..3e16b9dcf 100644
--- a/test/check.test
+++ b/test/check.test
@@ -11,7 +11,6 @@
# This file implements regression tests for SQLite library. The
# focus of this file is testing CHECK constraints
#
-# $Id: check.test,v 1.13 2009/06/05 17:09:12 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -536,7 +535,71 @@ do_execsql_test 11.6 {
INSERT INTO t2(b, a) VALUES(2, 'abc');
}
-finish_test
-
+# 2019-12-24 ticket b383b90278186263
+#
+reset_db
+do_execsql_test 12.10 {
+ CREATE TABLE t1(a TEXT, CHECK(a=+a));
+ INSERT INTO t1(a) VALUES(NULL),('xyz'),(5),(x'303132'),(4.75);
+ SELECT quote(a) FROM t1 ORDER BY rowid;
+} {NULL 'xyz' '5' X'303132' '4.75'}
+do_execsql_test 12.20 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a TEXT, CHECK(a<>+a));
+ INSERT INTO t1(a) VALUES(NULL);
+} {}
+do_catchsql_test 12.21 {
+ INSERT INTO t1(a) VALUES('xyz');
+} {1 {CHECK constraint failed: t1}}
+do_catchsql_test 12.22 {
+ INSERT INTO t1(a) VALUES(123);
+} {1 {CHECK constraint failed: t1}}
+do_execsql_test 12.30 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a TEXT, CHECK(NOT(a=+a)));
+ INSERT INTO t1(a) VALUES(NULL);
+} {}
+do_catchsql_test 12.31 {
+ INSERT INTO t1(a) VALUES('xyz');
+} {1 {CHECK constraint failed: t1}}
+do_catchsql_test 12.32 {
+ INSERT INTO t1(a) VALUES(123);
+} {1 {CHECK constraint failed: t1}}
+do_execsql_test 12.40 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a TEXT, CHECK(NOT(a<>+a)));
+ INSERT INTO t1(a) VALUES(NULL),('xyz'),(5),(x'303132'),(4.75);
+ SELECT quote(a) FROM t1 ORDER BY rowid;
+} {NULL 'xyz' '5' X'303132' '4.75'}
+do_execsql_test 12.50 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a TEXT, CHECK(a BETWEEN 0 AND +a));
+ INSERT INTO t1(a) VALUES(NULL),('xyz'),(5),(x'303132'),(4.75);
+ SELECT quote(a) FROM t1 ORDER BY rowid;
+} {NULL 'xyz' '5' X'303132' '4.75'}
+do_execsql_test 12.60 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a TEXT, CHECK(a NOT BETWEEN 0 AND +a));
+ INSERT INTO t1(a) VALUES(NULL);
+ SELECT quote(a) FROM t1 ORDER BY rowid;
+} {NULL}
+do_catchsql_test 12.61 {
+ INSERT INTO t1(a) VALUES(456);
+} {1 {CHECK constraint failed: t1}}
+do_execsql_test 12.70 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a TEXT, CHECK(a BETWEEN +a AND 999999));
+ INSERT INTO t1(a) VALUES(NULL),(5);
+ SELECT quote(a) FROM t1 ORDER BY rowid;
+} {NULL '5'}
+do_execsql_test 12.80 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a TEXT, CHECK(a NOT BETWEEN +a AND 999999));
+ INSERT INTO t1(a) VALUES(NULL);
+ SELECT quote(a) FROM t1 ORDER BY rowid;
+} {NULL}
+do_catchsql_test 12.81 {
+ INSERT INTO t1(a) VALUES(456);
+} {1 {CHECK constraint failed: t1}}
finish_test
diff --git a/test/conflict.test b/test/conflict.test
index 136bc3fec..b86f86022 100644
--- a/test/conflict.test
+++ b/test/conflict.test
@@ -834,5 +834,27 @@ do_catchsql_test conflict-14.1 {
REPLACE INTO t1 DEFAULT VALUES;
} {1 {NOT NULL constraint failed: t1.x}}
+# 2019-12-15 gramfuzz1 find
+# Three UNIQUE constraints, where the third would is a duplicate except
+# that it adds ON CONFLICT REPLACE. Verify that the indexes end up
+# sorted in the correct order (REPLACE last) so that constraint processing
+# works correctly.
+#
+reset_db
+do_execsql_test conflict-15.10 {
+ CREATE TABLE t1(
+ x PRIMARY KEY,
+ UNIQUE(x,x),
+ UNIQUE(x,x) ON CONFLICT REPLACE
+ );
+ INSERT INTO t1(x) VALUES(1);
+ SELECT * FROM t1;
+} {1}
+do_catchsql_test conflict-15.20 {
+ INSERT INTO t1(x) VALUES(1);
+} {1 {UNIQUE constraint failed: t1.x}}
+do_execsql_test conflict-15.30 {
+ SELECT * FROM t1;
+} {1}
finish_test
diff --git a/test/conflict3.test b/test/conflict3.test
index cc3a51b85..8eb4c1b0f 100644
--- a/test/conflict3.test
+++ b/test/conflict3.test
@@ -380,15 +380,15 @@ ifcapable trigger {
INSERT INTO t0 VALUES(0, NULL);
}
- do_execsql_test 13.1.1 {
+ do_catchsql_test 13.1.1 {
UPDATE OR REPLACE t0 SET c1 = 1;
- }
+ } {1 {constraint failed}}
integrity_check 13.1.2
do_execsql_test 13.1.3 {
SELECT * FROM t0
- } {}
+ } {1 {} 0 {}}
do_execsql_test 13.2.0 {
CREATE TABLE t2 (a PRIMARY KEY, b UNIQUE, c UNIQUE) WITHOUT ROWID;
@@ -400,15 +400,15 @@ ifcapable trigger {
INSERT INTO t2 VALUES(2, 2, 2);
}
- do_execsql_test 13.2.1 {
+ do_catchsql_test 13.2.1 {
UPDATE OR REPLACE t2 SET c = 0;
- }
+ } {1 {constraint failed}}
integrity_check 13.2.2
do_execsql_test 13.2.3 {
SELECT * FROM t2
- } {}
+ } {1 1 1 2 2 2}
do_execsql_test 13.3.0 {
CREATE TABLE t1(a, b);
diff --git a/test/exclusive.test b/test/exclusive.test
index 04de52913..5168fa748 100644
--- a/test/exclusive.test
+++ b/test/exclusive.test
@@ -511,6 +511,24 @@ do_execsql_test exclusive-6.5 {
SELECT * FROM sqlite_master;
} {exclusive}
+# 2019-12-26 ticket fb3b3024ea238d5c
+do_test exclusive-7.1 {
+ db close
+ forcedelete test.db test.db-journal test.db-wal
+ sqlite3 db test.db
+ # The following sequence of pragmas would trigger an assert()
+ # associated with Pager.changeCountDone inside of assert_pager_state(),
+ # prior to the fix.
+ db eval {
+ PRAGMA locking_mode = EXCLUSIVE;
+ PRAGMA journal_mode = WAL;
+ PRAGMA locking_mode = NORMAL;
+ PRAGMA user_version;
+ PRAGMA journal_mode = DELETE;
+ }
+} {exclusive wal normal 0 delete}
+
+
} ;# atomic_batch_write==0
finish_test
diff --git a/test/filter1.test b/test/filter1.test
index eb5096347..ee17099d9 100644
--- a/test/filter1.test
+++ b/test/filter1.test
@@ -184,4 +184,24 @@ do_execsql_test 5.3 {
SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1)
} {0 1}
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 6.0 {
+ CREATE TABLE t1(a,b);
+ INSERT INTO t1 VALUES(1,1);
+ INSERT INTO t1 VALUES(2,2);
+ CREATE TABLE t2(x,y);
+ INSERT INTO t2 VALUES(1,1);
+}
+
+do_execsql_test 6.1 {
+ SELECT (SELECT COUNT(a) FILTER(WHERE x) FROM t2) FROM t1;
+} {1 1}
+do_execsql_test 6.2 {
+ SELECT (SELECT COUNT(a+x) FROM t2) FROM t1;
+} {1 1}
+do_execsql_test 6.3 {
+ SELECT (SELECT COUNT(a) FROM t2) FROM t1;
+} {2}
+
finish_test
diff --git a/test/fts3corrupt4.test b/test/fts3corrupt4.test
index 2334907f8..c244076c5 100644
--- a/test/fts3corrupt4.test
+++ b/test/fts3corrupt4.test
@@ -5759,25 +5759,57 @@ do_test 32.0 {
| end crash-74fdbc96edbc04.db
}]} {}
-do_execsql_test 32.1 {
+do_catchsql_test 32.1 {
UPDATE t1 SET b=quote(zeroblob(6.51158946e+5)) WHERE a MATCH '*t*';
-} {}
-
-do_catchsql_test 32.2 {
- UPDATE t1 SET b=((- '' )) WHERE a MATCH '0*t';
} {1 {database disk image is malformed}}
+#do_catchsql_test 32.2 {
+# UPDATE t1 SET b=((- '' )) WHERE a MATCH '0*t';
+#} {1 {database disk image is malformed}}
+
+#-------------------------------------------------------------------------
+#
+ifcapable icu {
+ reset_db
+ do_catchsql_test 33.0 {
+ CREATE VIRTUAL TABLE f USING fts3(a,b,tokenize=icu);
+ CREATE TABLE 'f_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
+ CREATE TABLE 'f_stat'(id INTEGER PRIMARY KEY, value BLOB);
+ INSERT INTO f VALUES (1, '1234');
+ INSERT INTO f_stat VALUES (1,x'0000000165656565db6569746565c5c52bc5c5c53e3a003bc502ffffffffc5c5c53e3a003bc502fffffffffb8b2afbfb6565f0740100650000000165656565db6569746565c5c52bc5c5c53e3a003bc502ffffffffc5c5c53e3a003b8b00c5c5c5c5c5bfc5');
+ INSERT INTO f(f) VALUES ('merge=198,49');
+ } {1 {database disk image is malformed}}
+}
+
#-------------------------------------------------------------------------
#
reset_db
-do_catchsql_test 32.0 {
- CREATE VIRTUAL TABLE f USING fts3(a,b,tokenize=icu);
- CREATE TABLE 'f_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
- CREATE TABLE 'f_stat'(id INTEGER PRIMARY KEY, value BLOB);
+do_execsql_test 34.0 {
+ CREATE VIRTUAL TABLE f USING fts3(a,b);
INSERT INTO f VALUES (1, '1234');
- INSERT INTO f_stat VALUES (1,x'0000000165656565db6569746565c5c52bc5c5c53e3a003bc502ffffffffc5c5c53e3a003bc502fffffffffb8b2afbfb6565f0740100650000000165656565db6569746565c5c52bc5c5c53e3a003bc502ffffffffc5c5c53e3a003b8b00c5c5c5c5c5bfc5');
- INSERT INTO f(f) VALUES ('merge=198,49');
+ INSERT INTO f_segdir VALUES (1,255,0,0,'1 255',x'00');
+ UPDATE f_segdir SET level = 0 WHERE level IN (
+ SELECT level FROM f_segdir LIMIT 1 OFFSET 1
+ );
+ INSERT INTO f_segdir VALUES (255,249,0,121,'0 0',x'00');
+ INSERT INTO f_content VALUES (255,0,x'ff');
+ INSERT INTO f_segdir VALUES (1,255,16,0,'1 255',x'00');
+}
+
+do_catchsql_test 34.1 {
+ UPDATE f SET b = x'00' WHERE b IN (SELECT b FROM f LIMIT 1 OFFSET 0);
} {1 {database disk image is malformed}}
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 35.0 {
+ CREATE VIRTUAL TABLE f USING fts3(a,b);
+ INSERT INTO f_segdir VALUES (1,255,0,0,'1 255',x'0001ff000001ff000001ff000001ff000001ff00c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5bec5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5');
+}
+
+do_catchsql_test 35.1 {
+ INSERT INTO f(f) VALUES ('integrity-check');
+} {1 {database disk image is malformed}}
finish_test
diff --git a/test/fts4langid.test b/test/fts4langid.test
index 9fffe9521..45e851f94 100644
--- a/test/fts4langid.test
+++ b/test/fts4langid.test
@@ -489,4 +489,19 @@ foreach lid [list 4 [expr 1<<30]] {
SELECT count(*) FROM t6_segments;
} {1 2}
}
+
+reset_db
+do_execsql_test 6.0 {
+ CREATE VIRTUAL TABLE vt0 USING fts4(c0, languageid="lid");
+ INSERT INTO vt0 VALUES ('a'), ('b');
+ BEGIN;
+ UPDATE vt0 SET lid = 1 WHERE lid=0;
+}
+do_execsql_test 6.1 {
+ INSERT INTO vt0(vt0) VALUES('integrity-check');
+}
+do_execsql_test 6.2 {
+ COMMIT;
+ INSERT INTO vt0(vt0) VALUES('integrity-check');
+}
finish_test
diff --git a/test/fuzzcheck.c b/test/fuzzcheck.c
index f1d2415de..4f3321f0a 100644
--- a/test/fuzzcheck.c
+++ b/test/fuzzcheck.c
@@ -134,6 +134,7 @@ struct Blob {
*/
static struct GlobalVars {
const char *zArgv0; /* Name of program */
+ const char *zDbFile; /* Name of database file */
VFile aFile[MX_FILE]; /* The virtual filesystem */
int nDb; /* Number of template databases */
Blob *pFirstDb; /* Content of first template database */
@@ -148,11 +149,10 @@ static struct GlobalVars {
*/
static void fatalError(const char *zFormat, ...){
va_list ap;
- if( g.zTestName[0] ){
- fprintf(stderr, "%s (%s): ", g.zArgv0, g.zTestName);
- }else{
- fprintf(stderr, "%s: ", g.zArgv0);
- }
+ fprintf(stderr, "%s", g.zArgv0);
+ if( g.zDbFile ) fprintf(stderr, " %s", g.zDbFile);
+ if( g.zTestName[0] ) fprintf(stderr, " (%s)", g.zTestName);
+ fprintf(stderr, ": ");
va_start(ap, zFormat);
vfprintf(stderr, zFormat, ap);
va_end(ap);
@@ -161,12 +161,21 @@ static void fatalError(const char *zFormat, ...){
}
/*
-** Timeout handler
+** signal handler
*/
#ifdef __unix__
-static void timeoutHandler(int NotUsed){
- (void)NotUsed;
- fatalError("timeout\n");
+static void signalHandler(int signum){
+ const char *zSig;
+ if( signum==SIGABRT ){
+ zSig = "abort";
+ }else if( signum==SIGALRM ){
+ zSig = "timeout";
+ }else if( signum==SIGSEGV ){
+ zSig = "segfault";
+ }else{
+ zSig = "signal";
+ }
+ fatalError(zSig);
}
#endif
@@ -1311,6 +1320,7 @@ static void showHelp(void){
" --sqlid N Use only SQL where sqlid=N\n"
" --timeout N Abort if any single test needs more than N seconds\n"
" -v|--verbose Increased output. Repeat for more output.\n"
+" --vdbe-debug Activate VDBE debugging.\n"
);
}
@@ -1360,7 +1370,9 @@ int main(int argc, char **argv){
sqlite3_initialize();
iBegin = timeOfDay();
#ifdef __unix__
- signal(SIGALRM, timeoutHandler);
+ signal(SIGALRM, signalHandler);
+ signal(SIGSEGV, signalHandler);
+ signal(SIGABRT, signalHandler);
#endif
g.zArgv0 = argv[0];
openFlags4Data = SQLITE_OPEN_READONLY;
@@ -1464,6 +1476,9 @@ int main(int argc, char **argv){
fatalError("timeout is not available on non-unix systems");
#endif
}else
+ if( strcmp(z,"vdbe-debug")==0 ){
+ bVdbeDebug = 1;
+ }else
if( strcmp(z,"verbose")==0 ){
quietFlag = 0;
verboseFlag++;
@@ -1506,6 +1521,7 @@ int main(int argc, char **argv){
/* Process each source database separately */
for(iSrcDb=0; iSrcDb<nSrcDb; iSrcDb++){
+ g.zDbFile = azSrcDb[iSrcDb];
rc = sqlite3_open_v2(azSrcDb[iSrcDb], &db,
openFlags4Data, pDfltVfs->zName);
if( rc ){
@@ -1809,6 +1825,9 @@ int main(int argc, char **argv){
#ifdef SQLITE_TESTCTRL_PRNG_SEED
sqlite3_test_control(SQLITE_TESTCTRL_PRNG_SEED, 1, db);
#endif
+ if( bVdbeDebug ){
+ sqlite3_exec(db, "PRAGMA vdbe_debug=ON", 0, 0, 0);
+ }
do{
runSql(db, (char*)pSql->a, runFlags);
}while( timeoutTest );
diff --git a/test/fuzzdata8.db b/test/fuzzdata8.db
index 820ff08a2..e103ef27e 100644
--- a/test/fuzzdata8.db
+++ b/test/fuzzdata8.db
Binary files differ
diff --git a/test/gencol1.test b/test/gencol1.test
index dbcfefe61..5276d9694 100644
--- a/test/gencol1.test
+++ b/test/gencol1.test
@@ -211,16 +211,81 @@ do_catchsql_test gencol1-6.10 {
REPLACE INTO t0(c1) VALUES(NULL);
} {1 {NOT NULL constraint failed: t0.c0}}
-# 2019-11-06 ticket b13b7dce76e9352b34e7
+# 2019-11-06 ticket https://www.sqlite.org/src/info/2399f5986134f79c
+# 2019-12-27 ticket https://www.sqlite.org/src/info/5fbc159eeb092130
+# 2019-12-27 ticket https://www.sqlite.org/src/info/37823501c68a09f9
+#
+# All of the above tickets deal with NOT NULL ON CONFLICT REPLACE
+# constraints on tables that have generated columns.
+#
+reset_db
do_execsql_test gencol1-7.10 {
- DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 GENERATED ALWAYS AS (1), c1 UNIQUE, c2 UNIQUE);
INSERT INTO t0(c1) VALUES (1);
SELECT quote(0 = t0.c2 OR t0.c1 BETWEEN t0.c2 AND 1) FROM t0;
} {NULL}
+do_execsql_test gencol1-7.11 {
+ DROP TABLE t0;
+ CREATE TABLE t0(c0 NOT NULL DEFAULT 'xyz', c1 AS(c0) NOT NULL);
+ REPLACE INTO t0(c0) VALUES(NULL);
+ SELECT * FROM t0;
+} {xyz xyz}
+do_execsql_test gencol1-7.12 {
+ DROP TABLE t0;
+ CREATE TABLE t0(c0 NOT NULL DEFAULT 'xyz', c1 AS(c0) STORED NOT NULL);
+ REPLACE INTO t0(c0) VALUES(NULL);
+ SELECT * FROM t0;
+} {xyz xyz}
do_execsql_test gencol1-7.20 {
- SELECT 99 FROM t0 WHERE 0 = t0.c2 OR t0.c1 BETWEEN t0.c2 AND 1;
-} {}
+ CREATE TABLE t1(
+ a NOT NULL DEFAULT 'aaa',
+ b AS(c) NOT NULL,
+ c NOT NULL DEFAULT 'ccc');
+ REPLACE INTO t1(a,c) VALUES(NULL,NULL);
+ SELECT * FROM t1;
+} {aaa ccc ccc}
+do_execsql_test gencol1-7.21 {
+ DROP TABLE t1;
+ CREATE TABLE t1(
+ a NOT NULL DEFAULT 'aaa',
+ b AS(c) STORED NOT NULL,
+ c NOT NULL DEFAULT 'ccc');
+ REPLACE INTO t1(a,c) VALUES(NULL,NULL);
+ SELECT * FROM t1;
+} {aaa ccc ccc}
+do_execsql_test gencol1-7.30 {
+ CREATE TABLE t2(
+ a NOT NULL DEFAULT 'aaa',
+ b AS(a) NOT NULL,
+ c NOT NULL DEFAULT 'ccc');
+ REPLACE INTO t2(a,c) VALUES(NULL,NULL);
+ SELECT * FROM t2;
+} {aaa aaa ccc}
+do_execsql_test gencol1-7.31 {
+ DROP TABLE t2;
+ CREATE TABLE t2(
+ a NOT NULL DEFAULT 'aaa',
+ b AS(a) STORED NOT NULL,
+ c NOT NULL DEFAULT 'ccc');
+ REPLACE INTO t2(a,c) VALUES(NULL,NULL);
+ SELECT * FROM t2;
+} {aaa aaa ccc}
+do_execsql_test gencol1-7.40 {
+ CREATE TABLE t3(a NOT NULL DEFAULT 123, b AS(a) UNIQUE);
+ REPLACE INTO t3 VALUES(NULL);
+ SELECT * FROM t3;
+} {123 123}
+do_execsql_test gencol1-7.41 {
+ SELECT * FROM t3 WHERE b=123;
+} {123 123}
+do_execsql_test gencol1-7.50 {
+ CREATE TABLE t4(a NOT NULL DEFAULT 123, b AS(a*10+4) STORED UNIQUE);
+ REPLACE INTO t4 VALUES(NULL);
+ SELECT * FROM t4;
+} {123 1234}
+do_execsql_test gencol1-7.51 {
+ SELECT * FROM t4 WHERE b=1234;
+} {123 1234}
# 2019-11-06 ticket 4fc08501f4e56692
do_execsql_test gencol1-8.10 {
@@ -245,9 +310,9 @@ do_catchsql_test gencol1-8.20 {
# 2019-11-21 Problems in the new generated column logic
# reported by Yongheng Chen and Rui Zhong
+reset_db
do_execsql_test gencol1-9.10 {
PRAGMA foreign_keys=OFF;
- DROP TABLE t1;
CREATE TABLE t1(aa , bb AS (17) UNIQUE);
INSERT INTO t1 VALUES(17);
CREATE TABLE t2(cc);
@@ -342,7 +407,7 @@ do_execsql_test gencol1-12.10 {
PRAGMA integrity_check;
} {ok}
-# 2019-12-09 but report from Yongheng Chen
+# 2019-12-09 bug report from Yongheng Chen
# Ensure that the SrcList_item.colUsed field is set correctly when a
# generated column appears in the USING clause of a join.
#
@@ -369,5 +434,130 @@ do_execsql_test gencol1-13.22 {
SELECT 456 FROM t1 JOIN t1 USING (x,x);
} {456}
+# 2019-12-14 ticket b439bfcfb7deedc6
+#
+sqlite3 db :memory:
+do_execsql_test gencol1-14.10 {
+ CREATE TABLE t0(c0 AS(1 >= 1), c1 UNIQUE AS(TYPEOF(c0)), c2);
+ INSERT INTO t0 VALUES(0);
+ REINDEX;
+ SELECT * FROM t0;
+} {1 integer 0}
+do_catchsql_test gencol1-14.10 {
+ INSERT INTO t0 VALUES(2);
+} {1 {UNIQUE constraint failed: t0.c1}}
+
+# 2019-12-14 gramfuzz1 find
+# The schema is malformed in that it has a subquery on a generated
+# column expression. This will be loaded if writable_schema=ON. SQLite
+# must not use such an expression during code generation as the code generator
+# will add bits of content to the expression tree that might be allocated
+# from lookaside. But the schema is not tied to a particular database
+# connection, so the use of lookaside memory is prohibited. The fix
+# is to change the generated column expression to NULL before adding it
+# to the schema.
+#
+reset_db
+do_test gencol1-15.10 {
+ sqlite3 db {}
+ db deserialize [decode_hexdb {
+| size 8192 pagesize 4096 filename c27.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 01 00 00 00 02 .....@ ........
+| 32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 ................
+| 48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 ................
+| 80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 ................
+| 96: 00 2e 3f d8 0d 00 00 00 01 0f ba 00 0f ba 00 00 ..?.............
+| 4016: 00 00 00 00 00 00 00 00 00 00 44 01 06 17 11 11 ..........D.....
+| 4032: 01 75 74 61 62 6c 65 74 31 74 31 02 43 52 45 41 .utablet1t1.CREA
+| 4048: 54 45 20 54 41 42 4c 45 20 74 31 28 61 20 49 4e TE TABLE t1(a IN
+| 4064: 54 2c 20 62 20 41 53 28 28 56 41 4c 55 45 53 28 T, b AS((VALUES(
+| 4080: 31 29 29 20 49 53 20 75 6e 6b 6e 6f 77 6e 29 29 1)) IS unknown))
+| page 2 offset 4096
+| 0: 0d 00 00 00 00 10 00 00 00 00 00 00 00 00 00 00 ................
+| end c27.db
+}]} {}
+do_execsql_test gencol1-15.20 {
+ PRAGMA writable_schema=ON;
+ REPLACE INTO t1 VALUES(9);
+ SELECT a, quote(b) FROM t1
+} {9 NULL}
+
+# 2019-12-16 ticket 3b84b42943644d6f
+# When a table is the right table of a LEFT JOIN and the ON clause is
+# false, make sure any generated columns evaluate to NULL.
+reset_db
+do_execsql_test gencol1-16.10 {
+ CREATE TABLE t0(c0);
+ CREATE TABLE t1(c1, c2 AS(1));
+ INSERT INTO t0 VALUES(0);
+ SELECT c0, c1, c2 FROM t0 LEFT JOIN t1;
+} {0 {} {}}
+do_execsql_test gencol1-16.20 {
+ DROP TABLE t1;
+ CREATE TABLE t1(c1, c2 AS (c1 ISNULL));
+ SELECT c0, c1, c2 FROM t0 LEFT JOIN t1;
+} {0 {} {}}
+do_execsql_test gencol1-16.30 {
+ INSERT INTO t1(c1) VALUES(1),(NULL);
+ SELECT * FROM t1;
+} {1 0 {} 1}
+do_execsql_test gencol1-16.40 {
+ SELECT c0, c1, c2 FROM t0 LEFT JOIN t1 ON c0=c1;
+} {0 {} {}}
+
+# 2019-12-20 ticket e0a8120553f4b082
+# Generated columns with REAL affinity need to have an OP_RealAffinity
+# opcode applied, even when the column value is extracted from an index.
+#
+reset_db
+do_execsql_test gencol1-17.10 {
+ CREATE TABLE t0(c0 REAL AS(1) UNIQUE, c1 INT);
+ INSERT INTO t0 VALUES('');
+ SELECT quote(c0), quote(c1) from t0;
+} {1.0 ''}
+do_execsql_test gencol1-17.20 {
+ SELECT *, (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0) FROM t0;
+} {1.0 {} 0}
+do_execsql_test gencol1-17.30 {
+ SELECT * FROM t0 WHERE (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0);
+} {}
+do_execsql_test gencol1-17.40 {
+ CREATE TABLE t1(a TEXT AS(b) COLLATE nocase, b TEXT, c INT, d DEFAULT 1);
+ INSERT INTO t1(b,c) VALUES('abc',11),('DEF',22),('ghi',33);
+ SELECT a FROM t1 WHERE b='DEF' AND a='def';
+} {DEF}
+do_execsql_test gencol1-17.50 {
+ CREATE INDEX t1bca ON t1(b,c,a);
+ SELECT a FROM t1 WHERE b='DEF' AND a='def';
+} {DEF}
+
+# 2019-12-26 ticket ec8abb025e78f40c
+# An index on a virtual column with a constant value (why would anybody
+# ever do such a thing?) can cause problems for a one-pass DELETE.
+#
+reset_db
+do_execsql_test gencol1-18.10 {
+ CREATE TABLE t0(c0 UNIQUE AS(0), c1, c2);
+ INSERT INTO t0(c1) VALUES(0);
+ SELECT * FROM t0;
+} {0 0 {}}
+do_execsql_test gencol1-18.20 {
+ UPDATE t0 SET c1=0, c2=0 WHERE c0>=0;
+ SELECT * FROM t0;
+} {0 0 0}
+
+# 2019-12-27 ticket de4b04149b9fdeae
+#
+reset_db
+do_catchsql_test gencol1-19.10 {
+ CREATE TABLE t0(
+ c0 INT AS(2) UNIQUE,
+ c1 TEXT UNIQUE,
+ FOREIGN KEY(c0) REFERENCES t0(c1)
+ );
+ INSERT INTO t0(c1) VALUES(0.16334143182538696), (0);
+} {1 {UNIQUE constraint failed: t0.c0}}
finish_test
diff --git a/test/join.test b/test/join.test
index 19a061d46..391e0681c 100644
--- a/test/join.test
+++ b/test/join.test
@@ -975,4 +975,53 @@ do_execsql_test join-21.10 {
SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
} {13 1 {} 14 1 {} 23 1 {} 24 1 {}}
+# 2019-12-18 problem with a LEFT JOIN where the RHS is a view.
+# Detected by Yongheng and Rui.
+# Follows from the optimization attempt of check-in 41c27bc0ff1d3135
+# on 2017-04-18
+#
+reset_db
+do_execsql_test join-22.10 {
+ CREATE TABLE t0(a, b);
+ CREATE INDEX t0a ON t0(a);
+ INSERT INTO t0 VALUES(10,10),(10,11),(10,12);
+ SELECT DISTINCT c FROM t0 LEFT JOIN (SELECT a+1 AS c FROM t0) ORDER BY c ;
+} {11}
+
+# 2019-12-22 ticket 7929c1efb2d67e98
+#
+reset_db
+do_execsql_test join-23.10 {
+ CREATE TABLE t0(c0);
+ INSERT INTO t0(c0) VALUES(123);
+ CREATE VIEW v0(c0) AS SELECT 0 GROUP BY 1;
+ SELECT t0.c0, v0.c0, vt0.name
+ FROM v0, t0 LEFT JOIN pragma_table_info('t0') AS vt0
+ ON vt0.name LIKE 'c0'
+ WHERE v0.c0 == 0;
+} {123 0 c0}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test join-24.1 {
+ CREATE TABLE t1(a PRIMARY KEY, x);
+ CREATE TABLE t2(b INT);
+ CREATE INDEX t1aa ON t1(a, a);
+
+ INSERT INTO t1 VALUES('abc', 'def');
+ INSERT INTO t2 VALUES(1);
+}
+
+do_execsql_test join-24.2 {
+ SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='def';
+} {1 abc def}
+do_execsql_test join-24.3 {
+ SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='abc';
+} {}
+
+do_execsql_test join-24.2 {
+ SELECT * FROM t2 LEFT JOIN t1 ON a=0 WHERE (x='x' OR x IS NULL);
+} {1 {} {}}
+
finish_test
+
diff --git a/test/join2.test b/test/join2.test
index 5a70573e0..bfcecda29 100644
--- a/test/join2.test
+++ b/test/join2.test
@@ -279,5 +279,19 @@ do_execsql_test 7.0 {
SELECT * FROM test;
} {3 4 {} {} {} x 5 6 {} {} {} x}
+#-------------------------------------------------------------------------
+# Ticket [dfd66334].
+#
+reset_db
+do_execsql_test 8.0 {
+ CREATE TABLE t0(c0);
+ CREATE TABLE t1(c0);
+}
+
+do_execsql_test 8.1 {
+ SELECT * FROM t0 LEFT JOIN t1
+ WHERE (t1.c0 BETWEEN 0 AND 0) > ('' AND t0.c0);
+}
+
finish_test
diff --git a/test/nulls1.test b/test/nulls1.test
index fb059a674..9f4402f91 100644
--- a/test/nulls1.test
+++ b/test/nulls1.test
@@ -248,4 +248,54 @@ do_execsql_test 7.0 {
SELECT * FROM t71 ORDER BY a DESC NULLS FIRST;
}
+# 2019-12-18 gramfuzz1 find
+# NULLS LAST not allows on an INTEGER PRIMARY KEY.
+#
+do_catchsql_test 8.0 {
+ CREATE TABLE t80(a, b INTEGER, PRIMARY KEY(b NULLS LAST)) WITHOUT ROWID;
+} {1 {unsupported use of NULLS LAST}}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 9.0 {
+ CREATE TABLE v0 (c1, c2, c3);
+ CREATE INDEX v3 ON v0 (c1, c2, c3);
+}
+do_execsql_test 9.1 {
+ ANALYZE sqlite_master;
+ INSERT INTO sqlite_stat1 VALUES('v0','v3','648 324 81');
+ ANALYZE sqlite_master;
+}
+
+do_execsql_test 9.2 {
+ INSERT INTO v0 VALUES
+ (1, 10, 'b'),
+ (1, 10, 'd'),
+ (1, 10, NULL),
+ (2, 10, 'a'),
+ (2, 10, NULL),
+ (1, 10, 'c'),
+ (2, 10, 'b'),
+ (1, 10, 'a'),
+ (1, 10, NULL),
+ (2, 10, NULL),
+ (2, 10, 'd'),
+ (2, 10, 'c');
+}
+
+do_execsql_test 9.3 {
+ SELECT c1, c2, ifnull(c3, 'NULL') FROM v0
+ WHERE c2=10 ORDER BY c1, c3 NULLS LAST
+} {
+ 1 10 a 1 10 b 1 10 c 1 10 d 1 10 NULL 1 10 NULL
+ 2 10 a 2 10 b 2 10 c 2 10 d 2 10 NULL 2 10 NULL
+}
+
+do_eqp_test 9.4 {
+ SELECT c1, c2, ifnull(c3, 'NULL') FROM v0
+ WHERE c2=10 ORDER BY c1, c3 NULLS LAST
+} {SEARCH TABLE v0 USING COVERING INDEX v3 (ANY(c1) AND c2=?)}
+
+
+
finish_test
diff --git a/test/pragma3.test b/test/pragma3.test
index eebbcbb9c..c4794c743 100644
--- a/test/pragma3.test
+++ b/test/pragma3.test
@@ -255,4 +255,33 @@ if {[permutation]!="inmemory_journal"} {
}
}
+#-------------------------------------------------------------------------
+# Check that empty write transactions do not cause the return of "PRAGMA
+# data_version" to be decremented with journal_mode=PERSIST and
+# locking_mode=EXCLUSIVE
+#
+foreach {tn sql} {
+ A {
+ }
+ B {
+ PRAGMA journal_mode = PERSIST;
+ PRAGMA locking_mode = EXCLUSIVE;
+ }
+} {
+ reset_db
+ execsql $sql
+
+ do_execsql_test pragma3-510$tn {
+ CREATE TABLE t1(x, y);
+ INSERT INTO t1 VALUES(1, 2);
+ PRAGMA data_version;
+ } {1}
+
+ do_execsql_test pragma3-520$tn {
+ BEGIN EXCLUSIVE;
+ COMMIT;
+ PRAGMA data_version;
+ } {1}
+}
+
finish_test
diff --git a/test/rowvalue.test b/test/rowvalue.test
index f0a32b18c..e3b66a109 100644
--- a/test/rowvalue.test
+++ b/test/rowvalue.test
@@ -636,7 +636,12 @@ do_execsql_test 26.30 {
SELECT 3 FROM t1 LEFT JOIN t0 WHERE (c0, x'') != (NULL, 0);
} {3}
-
-
+# 2019-12-30 ticket 892575cdba4e1e36
+#
+reset_db
+do_catchsql_test 27.10 {
+ CREATE TABLE t0(c0 CHECK(((0, 0) > (0, c0))));
+ INSERT INTO t0(c0) VALUES(0) ON CONFLICT(c0) DO UPDATE SET c0 = 3;
+} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
finish_test
diff --git a/test/select1.test b/test/select1.test
index 27191caf6..47dcd0fe4 100644
--- a/test/select1.test
+++ b/test/select1.test
@@ -1165,4 +1165,24 @@ do_execsql_test select1-18.4 {
);
} {1}
+# 2019-12-17 gramfuzz find
+#
+do_execsql_test select-19.10 {
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(x);
+} {}
+do_catchsql_test select-19.20 {
+ INSERT INTO t1
+ SELECT 1,2,3,4,5,6,7
+ UNION ALL SELECT 1,2,3,4,5,6,7
+ ORDER BY 1;
+} {1 {table t1 has 1 columns but 7 values were supplied}}
+do_catchsql_test select-19.21 {
+ INSERT INTO t1
+ SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
+ UNION ALL SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
+ ORDER BY 1;
+} {1 {table t1 has 1 columns but 15 values were supplied}}
+
+
finish_test
diff --git a/test/update.test b/test/update.test
index 76aba65cd..dd96124b4 100644
--- a/test/update.test
+++ b/test/update.test
@@ -656,6 +656,78 @@ do_execsql_test update-17.10 {
SELECT * FROM t1;
} {2 3}
+# 2019-12-22 ticket 5ad2aa6921faa1ee
+# Make a hard-copy of values that need to be run through OP_RealAffinity
+# rather than a soft-copy. This is not strictly necessary, but it avoids
+# a memory-accounting assert().
+#
+reset_db
+do_execsql_test update-18.10 {
+ PRAGMA encoding = 'UTF16';
+ CREATE TABLE t0(c0 REAL, c1);
+ INSERT INTO t0(c0,c1) VALUES('xyz',11),('uvw',22);
+ CREATE INDEX i0 ON t0(c1) WHERE c0 GLOB 3;
+ CREATE INDEX i1 ON t0(c0,c1) WHERE typeof(c0)='text' AND typeof(c1)='integer';
+ UPDATE t0 SET c1=345;
+ SELECT * FROM t0;
+} {xyz 345 uvw 345}
+
+# 2019-12-22 ticket c62c5e58524b204d
+# This is really the same underlying problem as 5ad2aa6921faa1ee
+#
+reset_db
+do_execsql_test update-18.20 {
+ PRAGMA encoding = 'utf16';
+ CREATE TABLE t0(c0 TEXT);
+ CREATE INDEX i0 ON t0(0 LIKE COALESCE(c0, 0));
+ INSERT INTO t0(c0) VALUES (0), (0);
+ SELECT * FROM t0;
+} {0 0}
+
+# 2019-12-28 assertion fault reported by Yongheng
+# Similar to ticket ec8abb025e78f40c
+# An UPDATE was reaching the OP_Delete after running OP_DeferredSeek
+# without ever hitting an OP_Column. The enhanced solution is to
+# fix OP_Delete so that it can do the seek itself.
+#
+reset_db
+do_execsql_test update-19.10 {
+ CREATE TABLE t1(
+ a TEXT,
+ b INTEGER PRIMARY KEY UNIQUE
+ );
+ INSERT INTO t1 VALUES(1,2);
+ UPDATE t1 SET a = quote(b) WHERE b>=2;
+ SELECT * FROM t1;
+} {2 2}
+# 2019-12-29 ticket https://www.sqlite.org/src/info/314cc133e5ada126
+# REPLACE conflict resolution during an UPDATE causes a DELETE trigger
+# to fire. If that DELETE trigger subsequently modifies the row
+# being updated, bad things can happen. Prevent this by prohibiting
+# triggers from making changes to the table being updated while doing
+# REPLACE conflict resolution on the UPDATE.
+#
+# See also tickets:
+# https://www.sqlite.org/src/info/c1e19e12046d23fe 2019-10-25
+# https://www.sqlite.org/src/info/a8a4847a2d96f5de 2019-10-16
+#
+reset_db
+do_execsql_test update-20.10 {
+ PRAGMA recursive_triggers = true;
+ CREATE TABLE t1(a UNIQUE ON CONFLICT REPLACE, b);
+ INSERT INTO t1(a,b) VALUES(4,12),(9,13);
+ CREATE INDEX i0 ON t1(b);
+ CREATE TRIGGER tr0 DELETE ON t1 BEGIN
+ UPDATE t1 SET b = a;
+ END;
+ PRAGMA integrity_check;
+} {ok}
+do_catchsql_test update-20.20 {
+ UPDATE t1 SET a=0;
+} {1 {constraint failed}}
+do_execsql_test update-20.30 {
+ PRAGMA integrity_check;
+} {ok}
finish_test
diff --git a/test/upsert1.test b/test/upsert1.test
index 6b5be23b7..5250a5d2f 100644
--- a/test/upsert1.test
+++ b/test/upsert1.test
@@ -233,4 +233,12 @@ do_catchsql_test upsert1-910 {
INSERT INTO t1 VALUES(3) ON CONFLICT(x) DO NOTHING;
} {1 {cannot UPSERT a view}}
+# 2019-12-26 ticket 7c13db5c3bf74001
+reset_db
+do_catchsql_test upsert1-1000 {
+ CREATE TABLE t0(c0 PRIMARY KEY, c1, c2 UNIQUE) WITHOUT ROWID;
+ INSERT OR FAIL INTO t0(c2) VALUES (0), (NULL)
+ ON CONFLICT(c2) DO UPDATE SET c1 = c0;
+} {1 {NOT NULL constraint failed: t0.c0}}
+
finish_test
diff --git a/test/whereG.test b/test/whereG.test
index 595de116a..9d4cde7b4 100644
--- a/test/whereG.test
+++ b/test/whereG.test
@@ -306,7 +306,15 @@ do_execsql_test 8.10 {
SELECT * FROM t0 WHERE likelihood(t0.rowid <= '0', 0.5);
} {}
-
-
+# 2019-12-31: assertion fault discovered by Yongheng's fuzzer.
+# Harmless memIsValid() due to the code generators failure to
+# release the registers used by OP_ResultRow.
+#
+do_execsql_test 9.10 {
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(a, b FLOAT);
+ INSERT INTO t1(a) VALUES(''),(NULL),('X'),(NULL);
+ SELECT coalesce(max(quote(a)),10) FROM t1 GROUP BY a;
+} {NULL '' 'X'}
finish_test
diff --git a/test/window1.test b/test/window1.test
index b4542891b..0b7884784 100644
--- a/test/window1.test
+++ b/test/window1.test
@@ -1314,5 +1314,221 @@ do_execsql_test 36.40 {
VALUES(2),(3),(count(*)OVER()),(4),(5);
} {2 3 1 4 5}
+# 2019-12-17 crash test case found by Yongheng and Rui
+# See check-in 1ca0bd982ab1183b
+#
+reset_db
+do_execsql_test 37.10 {
+ CREATE TABLE t0(a UNIQUE, b PRIMARY KEY);
+ CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1))) FROM t0;
+ SELECT c FROM v0 WHERE c BETWEEN 10 AND 20;
+} {}
+do_execsql_test 37.20 {
+ DROP VIEW v0;
+ CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0;
+ SELECT c FROM v0 WHERE c BETWEEN -10 AND 20;
+} {}
+
+# 2019-12-20 mrigger reported problem with a FILTER clause on an aggregate
+# in a join.
+#
+reset_db
+do_catchsql_test 38.10 {
+ CREATE TABLE t0(c0);
+ CREATE TABLE t1(c0, c1 UNIQUE);
+ INSERT INTO t0(c0) VALUES(1);
+ INSERT INTO t1(c0,c1) VALUES(2,3);
+ SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(0) FILTER(WHERE t1.c1));
+} {1 {misuse of aggregate: AVG()}}
+do_execsql_test 38.20 {
+ SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1;
+} {1 1.0}
+do_catchsql_test 38.30 {
+ SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1));
+} {1 {misuse of aggregate: AVG()}}
+
+reset_db
+do_execsql_test 39.1 {
+ CREATE TABLE t0(c0 UNIQUE);
+}
+do_execsql_test 39.2 {
+ SELECT FIRST_VALUE(0) OVER();
+} {0}
+do_execsql_test 39.3 {
+ SELECT * FROM t0 WHERE(c0, 0) IN(SELECT FIRST_VALUE(0) OVER(), 0);
+}
+do_execsql_test 39.4 {
+ SELECT * FROM t0 WHERE (t0.c0, 1) IN(SELECT NTILE(1) OVER(), 0 FROM t0);
+}
+
+ifcapable rtree {
+ # 2019-12-25 ticket d87336c81c7d0873
+ #
+ reset_db
+ do_catchsql_test 40.1 {
+ CREATE VIRTUAL TABLE t0 USING rtree(c0, c1, c2);
+ SELECT * FROM t0
+ WHERE ((0,0) IN (SELECT COUNT(*),LAG(5)OVER(PARTITION BY 0) FROM t0),0)<=(c1,0);
+ } {0 {}}
+}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 41.1 {
+ CREATE TABLE t1(a, b, c);
+ INSERT INTO t1 VALUES(NULL,'bb',355);
+ INSERT INTO t1 VALUES('CC','aa',158);
+ INSERT INTO t1 VALUES('GG','bb',929);
+ INSERT INTO t1 VALUES('FF','Rb',574);
+}
+
+do_execsql_test 41.2 {
+ SELECT min(c) OVER (
+ ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
+ ) FROM t1
+} {355 158 574 929}
+
+do_execsql_test 41.2 {
+ SELECT min(c) OVER (
+ ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
+ ) << 100 FROM t1
+} {0 0 0 0}
+
+do_execsql_test 41.3 {
+ SELECT
+ min(c) OVER win3 << first_value(c) OVER win3,
+ min(c) OVER win3 << first_value(c) OVER win3
+ FROM t1
+ WINDOW win3 AS (
+ PARTITION BY 6 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
+ );
+} {0 0 0 0 0 0 0 0}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 42.1 {
+ CREATE TABLE t1(a, b, c);
+ INSERT INTO t1 VALUES(1, 1, 1);
+ INSERT INTO t1 VALUES(2, 2, 2);
+}
+do_execsql_test 42.2 {
+ SELECT * FROM t1 WHERE (0, 0) IN ( SELECT count(*), 0 FROM t1 )
+} {}
+do_execsql_test 42.3 {
+ SELECT * FROM t1 WHERE (2, 0) IN ( SELECT count(*), 0 FROM t1 )
+} {1 1 1 2 2 2}
+
+do_execsql_test 42.3 {
+ SELECT count(*), max(a) OVER () FROM t1 GROUP BY c;
+} {1 2 1 2}
+
+do_execsql_test 42.4 {
+ SELECT sum(a), max(b) OVER () FROM t1;
+} {3 1}
+
+do_execsql_test 42.5 {
+ CREATE TABLE t2(a, b);
+ INSERT INTO t2 VALUES('a', 1);
+ INSERT INTO t2 VALUES('a', 2);
+ INSERT INTO t2 VALUES('a', 3);
+ INSERT INTO t2 VALUES('b', 4);
+ INSERT INTO t2 VALUES('b', 5);
+ INSERT INTO t2 VALUES('b', 6);
+}
+
+do_execsql_test 42.6 {
+ SELECT a, sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2 GROUP BY a;
+} {a 6 6 b 15 21}
+
+do_execsql_test 42.7 {
+ SELECT sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2;
+} {21 21}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 43.1.1 {
+ CREATE TABLE t1(x INTEGER PRIMARY KEY);
+ INSERT INTO t1 VALUES (10);
+}
+do_catchsql_test 43.1.2 {
+ SELECT count() OVER() AS m FROM t1 ORDER BY (SELECT m);
+} {1 {misuse of aliased window function m}}
+
+reset_db
+do_execsql_test 43.2.1 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
+ INSERT INTO t1(a, b) VALUES(1, 10); -- 10
+ INSERT INTO t1(a, b) VALUES(2, 15); -- 25
+ INSERT INTO t1(a, b) VALUES(3, -5); -- 20
+ INSERT INTO t1(a, b) VALUES(4, -5); -- 15
+ INSERT INTO t1(a, b) VALUES(5, 20); -- 35
+ INSERT INTO t1(a, b) VALUES(6, -11); -- 24
+}
+
+do_execsql_test 43.2.2 {
+ SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY 2
+} {
+ 1 10 4 15 3 20 6 24 2 25 5 35
+}
+
+do_execsql_test 43.2.3 {
+ SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc
+} {
+ 1 10 4 15 3 20 6 24 2 25 5 35
+}
+
+do_execsql_test 43.2.4 {
+ SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc+5
+} {
+ 1 10 4 15 3 20 6 24 2 25 5 35
+}
+
+do_catchsql_test 43.2.5 {
+ SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc)
+} {1 {misuse of aliased window function abc}}
+
+do_catchsql_test 43.2.6 {
+ SELECT a, 1+sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc)
+} {1 {misuse of aliased window function abc}}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 44.1 {
+ CREATE TABLE t0(c0);
+}
+
+do_catchsql_test 44.2.1 {
+ SELECT ntile(0) OVER ();
+} {1 {argument of ntile must be a positive integer}}
+do_catchsql_test 44.2.2 {
+ SELECT (0, 0) IN(SELECT MIN(c0), NTILE(0) OVER()) FROM t0;
+} {1 {argument of ntile must be a positive integer}}
+
+do_execsql_test 44.3.1 {
+ SELECT ntile(1) OVER ();
+} {1}
+do_execsql_test 44.3.2 {
+ SELECT (0, 0) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0;
+} {0}
+
+do_execsql_test 44.4.2 {
+ INSERT INTO t0 VALUES(2), (1), (0);
+ SELECT (0, 1) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0;
+} {1}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 45.1 {
+ CREATE TABLE t0(x);
+ CREATE TABLE t1(a);
+ INSERT INTO t1 VALUES(1000);
+ INSERT INTO t1 VALUES(1000);
+ INSERT INTO t0 VALUES(10000);
+}
+do_execsql_test 45.2 {
+ SELECT * FROM (
+ SELECT sum (a) OVER() FROM t1 UNION ALL SELECT x FROM t0
+ );
+} {2000 2000 10000}
finish_test
diff --git a/test/window2.tcl b/test/window2.tcl
index 5edc53680..4c18b7970 100644
--- a/test/window2.tcl
+++ b/test/window2.tcl
@@ -448,6 +448,47 @@ execsql_float_test 5.1 {
SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z;
}
+==========
+
+execsql_test 6.0 {
+ DROP TABLE IF EXISTS t0;
+ CREATE TABLE t0(c0 INTEGER UNIQUE);
+ INSERT INTO t0 VALUES(0);
+}
+execsql_test 6.1 {
+ SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0;
+}
+execsql_test 6.2 {
+ SELECT * FROM t0 WHERE
+ (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
+}
+
+==========
+
+execsql_test 7.0 {
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
+ INSERT INTO t1 VALUES(1, 1, 1);
+ INSERT INTO t1 VALUES(1, 2, 2);
+ INSERT INTO t1 VALUES(3, 3, 3);
+ INSERT INTO t1 VALUES(3, 4, 4);
+}
+
+execsql_test 7.1 {
+ SELECT c, sum(c) OVER win1 FROM t1
+ WINDOW win1 AS (ORDER BY b)
+}
+
+execsql_test 7.2 {
+ SELECT c, sum(c) OVER win1 FROM t1
+ WINDOW win1 AS (PARTITION BY 1 ORDER BY b)
+}
+
+execsql_test 7.3 {
+ SELECT c, sum(c) OVER win1 FROM t1
+ WINDOW win1 AS (ORDER BY 1)
+}
+
finish_test
diff --git a/test/window2.test b/test/window2.test
index 5f028eb72..e241d5964 100644
--- a/test/window2.test
+++ b/test/window2.test
@@ -930,4 +930,47 @@ do_test 5.1 {
set {} {}
} {}
+#==========================================================================
+
+do_execsql_test 6.0 {
+ DROP TABLE IF EXISTS t0;
+ CREATE TABLE t0(c0 INTEGER UNIQUE);
+ INSERT INTO t0 VALUES(0);
+} {}
+
+do_execsql_test 6.1 {
+ SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0;
+} {1 {}}
+
+do_execsql_test 6.2 {
+ SELECT * FROM t0 WHERE
+ (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
+} {}
+
+#==========================================================================
+
+do_execsql_test 7.0 {
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
+ INSERT INTO t1 VALUES(1, 1, 1);
+ INSERT INTO t1 VALUES(1, 2, 2);
+ INSERT INTO t1 VALUES(3, 3, 3);
+ INSERT INTO t1 VALUES(3, 4, 4);
+} {}
+
+do_execsql_test 7.1 {
+ SELECT c, sum(c) OVER win1 FROM t1
+ WINDOW win1 AS (ORDER BY b)
+} {1 1 2 3 3 6 4 10}
+
+do_execsql_test 7.2 {
+ SELECT c, sum(c) OVER win1 FROM t1
+ WINDOW win1 AS (PARTITION BY 1 ORDER BY b)
+} {1 1 2 3 3 6 4 10}
+
+do_execsql_test 7.3 {
+ SELECT c, sum(c) OVER win1 FROM t1
+ WINDOW win1 AS (ORDER BY 1)
+} {1 10 2 10 3 10 4 10}
+
finish_test
diff --git a/test/windowfault.test b/test/windowfault.test
index e037c467b..e97544f4c 100644
--- a/test/windowfault.test
+++ b/test/windowfault.test
@@ -230,7 +230,7 @@ do_execsql_test 10.0 {
CREATE TABLE t2(a, b, c, d);
}
-do_faultsim_test 1 -faults oom* -prep {
+do_faultsim_test 10 -faults oom* -prep {
} -body {
execsql {
SELECT row_number() OVER win
@@ -246,4 +246,21 @@ do_faultsim_test 1 -faults oom* -prep {
faultsim_test_result {0 {}}
}
+reset_db
+do_execsql_test 11.0 {
+ DROP TABLE IF EXISTS t0;
+ CREATE TABLE t0(c0 INTEGER UNIQUE);
+ INSERT INTO t0 VALUES(0);
+} {}
+
+do_faultsim_test 11 -faults oom* -prep {
+} -body {
+ execsql {
+ SELECT * FROM t0 WHERE
+ (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
+ }
+} -test {
+ faultsim_test_result {0 {}}
+}
+
finish_test
diff --git a/test/zipfile.test b/test/zipfile.test
index 2bab066df..6ea9ac6a0 100644
--- a/test/zipfile.test
+++ b/test/zipfile.test
@@ -795,4 +795,45 @@ if {$tcl_platform(platform)!="windows"} {
} {. ./x1.txt ./x2.txt}
}
+# 2019-12-18 Yongheng and Rui fuzzer
+#
+do_execsql_test 13.10 {
+ DROP TABLE IF EXISTS t0;
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t0(a,b,c,d,e,f,g);
+ REPLACE INTO t0(c,b,f) VALUES(10,10,10);
+ CREATE VIRTUAL TABLE t1 USING zipfile('h.zip');
+ REPLACE INTO t1 SELECT * FROM t0;
+ SELECT quote(name),quote(mode),quote(mtime),quote(sz),quote(rawdata),
+ quote(data),quote(method) FROM t1;
+} {'' 10 10 2 X'3130' X'3130' 0}
+
+# 2019-12-23 Yongheng and Rui fuzzer
+# Run using valgrind to see the problem.
+#
+do_execsql_test 14.10 {
+ DROP TABLE t1;
+ CREATE TABLE t1(x char);
+ INSERT INTO t1(x) VALUES('1');
+ INSERT INTO t1(x) SELECT zipfile(x, 'xyz') FROM t1;
+ INSERT INTO t1(x) SELECT zipfile(x, 'uvw') FROM t1;
+ SELECT count(*) FROM t1;
+ PRAGMA integrity_check;
+} {3 ok}
+
+# 2019-12-26 More problems in zipfile from the Yongheng and Rui fuzzer
+#
+do_execsql_test 15.10 {
+ DROP TABLE IF EXISTS t1;
+ CREATE VIRTUAL TABLE t1 USING zipfile(null);
+ REPLACE INTO t1 VALUES(null,null,0,null,null,null,null);
+} {}
+do_execsql_test 15.20 {
+ DROP TABLE IF EXISTS t2;
+ CREATE VIRTUAL TABLE t2 USING zipfile(null);
+ REPLACE INTO t2 values(null,null,null,null,null,10,null);
+} {}
+
+
+
finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-18 17:45:18 +0000