aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/colname.test55
-rw-r--r--test/like.test50
-rw-r--r--test/ossfuzz.c25
-rw-r--r--test/schema6.test163
-rw-r--r--test/without_rowid1.test14
5 files changed, 305 insertions, 2 deletions
diff --git a/test/colname.test b/test/colname.test
index e16304d4a..1497fc275 100644
--- a/test/colname.test
+++ b/test/colname.test
@@ -13,7 +13,6 @@
# The focus of this file is testing how SQLite generates the names
# of columns in a result set.
#
-# $Id: colname.test,v 1.7 2009/06/02 15:47:38 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -326,4 +325,58 @@ do_test colname-8.1 {
}
} {123}
+# 2017-07-29: Interaction between column naming and query flattening.
+# For years now, the query flattener has inserted AS clauses on the
+# outer query that were the original SQL text of the column. This caused
+# column-name shifts when the query flattener was enhanced, breaking
+# legacy applications. See https://sqlite.org/src/info/41c27bc0ff1d3135
+# for details.
+#
+# To fix this, the column naming logic was moved ahead of the query
+# flattener so that column names are assigned before the query flattener
+# runs.
+#
+db close
+sqlite3 db :memory:
+do_test colname-9.100 {
+ db eval {
+ CREATE TABLE t1(a,b);
+ INSERT INTO t1 VALUES(1,2);
+ CREATE VIEW v1(x,y) AS SELECT a,b FROM t1;
+ }
+ execsql2 {SELECT v1.x, (Y) FROM v1}
+ # Prior to the fix, this would return: "v1.x 1 (Y) 2"
+} {x 1 y 2}
+do_test colname-9.110 {
+ execsql2 {SELECT * FROM v1}
+} {x 1 y 2}
+do_test colname-9.120 {
+ db eval {
+ CREATE VIEW v2(x,y) AS SELECT a,b FROM t1 LIMIT 10;
+ }
+ execsql2 {SELECT * FROM v2 WHERE 1}
+} {x 1 y 2}
+do_test colname-9.130 {
+ execsql2 {SELECT v2.x, [v2].[y] FROM v2 WHERE 1}
+} {x 1 y 2}
+do_test colname-9.140 {
+ execsql2 {SELECT +x, +y FROM v2 WHERE 1}
+} {+x 1 +y 2}
+
+do_test colname-9.200 {
+ db eval {
+ CREATE TABLE t2(c,d);
+ INSERT INTO t2 VALUES(3,4);
+ CREATE VIEW v3 AS SELECT c AS a, d AS b FROM t2;
+ }
+ execsql2 {SELECT t1.a, v3.a AS n FROM t1 LEFT JOIN v3}
+} {a 1 n 3}
+do_test colname-9.211 {
+ execsql2 {SELECT t1.a AS n, v3.a FROM t1 JOIN v3}
+} {n 1 a 3}
+do_test colname-9.210 {
+ execsql2 {SELECT t1.a, v3.a AS n FROM t1 JOIN v3}
+} {a 1 n 3}
+
+
finish_test
diff --git a/test/like.test b/test/like.test
index bae770b07..1702dde71 100644
--- a/test/like.test
+++ b/test/like.test
@@ -207,7 +207,7 @@ do_test like-3.3.100 {
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
}
} {abc abcd nosort {} i1}
-do_test like-3.3.101 {
+do_test like-3.3.100.cnt {
set sqlite_like_count
} 0
@@ -1048,4 +1048,52 @@ ifcapable !icu {
} {1}
}
+# As of 2017-07-27 (3.21.0) the LIKE optimization works with ESCAPE as
+# long as the ESCAPE is a single-byte literal.
+#
+db close
+sqlite3 db :memory:
+do_execsql_test like-15.100 {
+ CREATE TABLE t15(x TEXT COLLATE nocase, y, PRIMARY KEY(x));
+ INSERT INTO t15(x,y) VALUES
+ ('abcde',1), ('ab%de',2), ('a_cde',3),
+ ('uvwxy',11),('uvwx%',12),('uvwx_',13),
+ ('_bcde',21),('%bcde',22),
+ ('abcd_',31),('abcd%',32),
+ ('ab%xy',41);
+ SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';
+} {2}
+do_execsql_test like-15.101 {
+ EXPLAIN QUERY PLAN
+ SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';
+} {/SEARCH/}
+do_execsql_test like-15.102 {
+ EXPLAIN QUERY PLAN
+ SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '//';
+} {/SCAN/}
+do_execsql_test like-15.103 {
+ EXPLAIN QUERY PLAN
+ SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '';
+} {/SCAN/}
+do_execsql_test like-15.110 {
+ SELECT y FROM t15 WHERE x LIKE 'abcdx%%' ESCAPE 'x';
+} {32}
+do_execsql_test like-15.111 {
+ SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y
+} {2 41}
+do_execsql_test like-15.112 {
+ EXPLAIN QUERY PLAN
+ SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y
+} {/SEARCH/}
+do_execsql_test like-15.120 {
+ SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
+} {22}
+do_execsql_test like-15.121 {
+ EXPLAIN QUERY PLAN
+ SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
+} {/SEARCH/}
+
+
+
+
finish_test
diff --git a/test/ossfuzz.c b/test/ossfuzz.c
index 51983548b..7b28cf6a7 100644
--- a/test/ossfuzz.c
+++ b/test/ossfuzz.c
@@ -71,6 +71,28 @@ static int progress_handler(void *pClientData) {
#endif
/*
+** Disallow debugging pragmas such as "PRAGMA vdbe_debug" and
+** "PRAGMA parser_trace" since they can dramatically increase the
+** amount of output without actually testing anything useful.
+*/
+static int block_debug_pragmas(
+ void *Notused,
+ int eCode,
+ const char *zArg1,
+ const char *zArg2,
+ const char *zArg3,
+ const char *zArg4
+){
+ if( eCode==SQLITE_PRAGMA
+ && (sqlite3_strnicmp("vdbe_", zArg1, 5)==0
+ || sqlite3_stricmp("parser_trace", zArg1)==0)
+ ){
+ return SQLITE_DENY;
+ }
+ return SQLITE_OK;
+}
+
+/*
** Callback for sqlite3_exec().
*/
static int exec_handler(void *pCnt, int argc, char **argv, char **namev){
@@ -128,6 +150,9 @@ int LLVMFuzzerTestOneInput(const uint8_t* data, size_t size) {
sqlite3_db_config(cx.db, SQLITE_DBCONFIG_ENABLE_FKEY, uSelector&1, &rc);
uSelector >>= 1;
+ /* Do not allow debugging pragma statements that might cause excess output */
+ sqlite3_set_authorizer(cx.db, block_debug_pragmas, 0);
+
/* Remaining bits of the selector determine a limit on the number of
** output rows */
execCnt = uSelector + 1;
diff --git a/test/schema6.test b/test/schema6.test
new file mode 100644
index 000000000..7de04d51c
--- /dev/null
+++ b/test/schema6.test
@@ -0,0 +1,163 @@
+# 2017-07-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 implements tests to show that certain CREATE TABLE statements
+# generate identical database files. For example, changes in identifier
+# names, white-space, and formatting of the CREATE TABLE statement should
+# produce identical table content.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set ::testprefix schema6
+
+# Command: check_same_database_content TESTNAME SQL1 SQL2 SQL3 ...
+#
+# This command creates fresh databases using SQL1 and subsequent arguments
+# and checks to make sure the content of all database files is byte-for-byte
+# identical. Page 1 of the database files is allowed to be different, since
+# page 1 contains the sqlite_master table which is expected to vary.
+#
+proc check_same_database_content {basename args} {
+ set i 0
+ set hash {}
+ foreach sql $args {
+ catch {db close}
+ forcedelete test.db
+ sqlite3 db test.db
+ db eval $sql
+ set pgsz [db one {PRAGMA page_size}]
+ db close
+ set sz [file size test.db]
+ set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]]
+ if {$i==0} {
+ set hash $thishash
+ } else {
+ do_test $basename-$i "set x $thishash" $hash
+ }
+ incr i
+ }
+}
+
+# Command: check_different_database_content TESTNAME SQL1 SQL2 SQL3 ...
+#
+# This command creates fresh databases using SQL1 and subsequent arguments
+# and checks to make sure the content of all database files is different
+# in ways other than on page 1.
+#
+proc check_different_database_content {basename args} {
+ set i 0
+ set hashes {}
+ foreach sql $args {
+ forcedelete test.db
+ sqlite3 db test.db
+ db eval $sql
+ set pgsz [db one {PRAGMA page_size}]
+ db close
+ set sz [file size test.db]
+ set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]]
+ set j [lsearch $hashes $thishash]
+ if {$j>=0} {
+ do_test $basename-$i "set x {$i is the same as $j}" "All are different"
+ } else {
+ do_test $basename-$i "set x {All are different}" "All are different"
+ }
+ lappend hashes $thishash
+ incr i
+ }
+}
+
+check_same_database_content 100 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc));
+ INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz));
+ INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE UNIQUE INDEX t1b ON t1(b);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+ CREATE UNIQUE INDEX t1b ON t1(b);
+}
+
+check_same_database_content 110 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a));
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b);
+ CREATE UNIQUE INDEX t1b ON t1(b);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+ CREATE UNIQUE INDEX t1b ON t1(b);
+}
+
+check_same_database_content 120 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc))WITHOUT ROWID;
+ INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz))WITHOUT ROWID;
+ INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE) WITHOUT ROWID;
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE) WITHOUT ROWID;
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a))
+ WITHOUT ROWID;
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
+ CREATE UNIQUE INDEX t1b ON t1(b);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+ CREATE UNIQUE INDEX t1b ON t1(b);
+}
+
+check_different_database_content 130 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE);
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+} {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
+ INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
+}
+
+
+finish_test
diff --git a/test/without_rowid1.test b/test/without_rowid1.test
index 0c77773ab..c7899cfb3 100644
--- a/test/without_rowid1.test
+++ b/test/without_rowid1.test
@@ -328,5 +328,19 @@ do_catchsql_test 7.3 {
) WITHOUT ROWID;
} {1 {no such column: rowid}}
+# 2017-07-30: OSSFuzz discovered that an extra entry was being
+# added in the sqlite_master table for an "INTEGER PRIMARY KEY UNIQUE"
+# WITHOUT ROWID table. Make sure this has now been fixed.
+#
+db close
+sqlite3 db :memory:
+do_execsql_test 8.1 {
+ CREATE TABLE t1(x INTEGER PRIMARY KEY UNIQUE, b) WITHOUT ROWID;
+ CREATE INDEX t1x ON t1(x);
+ INSERT INTO t1(x,b) VALUES('funny','buffalo');
+ SELECT type, name, '|' FROM sqlite_master;
+} {table t1 | index t1x |}
+
+
finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-22 11:37:22 +0000