Index: third_party/sqlite/src/test/vtab1.test |
diff --git a/third_party/sqlite/src/test/vtab1.test b/third_party/sqlite/src/test/vtab1.test |
index a9aca503c445e6597425d1bb34b713cf088f3532..0542ee6fdda3f0241bd2bccae7b02b407cc9c4ab 100644 |
--- a/third_party/sqlite/src/test/vtab1.test |
+++ b/third_party/sqlite/src/test/vtab1.test |
@@ -15,6 +15,7 @@ |
set testdir [file dirname $argv0] |
source $testdir/tester.tcl |
+set testprefix vtab1 |
ifcapable !vtab||!schema_pragmas { |
finish_test |
@@ -43,6 +44,9 @@ ifcapable !vtab||!schema_pragmas { |
# |
# vtab1-14.*: Test 'IN' constraints - i.e. "SELECT * FROM t1 WHERE id IN(...)" |
# |
+# vtab1-18.*: Check that the LIKE optimization is not applied when the lhs |
+# is a virtual table column. |
+# |
#---------------------------------------------------------------------- |
@@ -51,7 +55,7 @@ ifcapable !vtab||!schema_pragmas { |
# We cannot create a virtual table if the module has not been registered. |
# |
-do_test vtab1-1.1 { |
+do_test vtab1-1.1.1 { |
explain { |
CREATE VIRTUAL TABLE t1 USING echo; |
} |
@@ -59,6 +63,11 @@ do_test vtab1-1.1 { |
CREATE VIRTUAL TABLE t1 USING echo; |
} |
} {1 {no such module: echo}} |
+do_test vtab1-1.1.2 { |
+ catchsql { |
+ CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo; |
+ } |
+} {1 {no such module: echo}} |
do_test vtab1-1.2 { |
execsql { |
SELECT name FROM sqlite_master ORDER BY 1 |
@@ -75,11 +84,16 @@ register_echo_module [sqlite3_connection_pointer db] |
# The "echo" module does not invoke sqlite3_declare_vtab() if it is |
# passed zero arguments. |
# |
-do_test vtab1-1.3 { |
+do_test vtab1-1.3.1 { |
catchsql { |
CREATE VIRTUAL TABLE t1 USING echo; |
} |
} {1 {vtable constructor did not declare schema: t1}} |
+do_test vtab1-1.3.2 { |
+ catchsql { |
+ CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo; |
+ } |
+} {1 {vtable constructor did not declare schema: t1}} |
do_test vtab1-1.4 { |
execsql { |
SELECT name FROM sqlite_master ORDER BY 1 |
@@ -90,11 +104,16 @@ do_test vtab1-1.4 { |
# the virtual table if it is passed an argument that does not correspond |
# to an existing real table in the same database. |
# |
-do_test vtab1-1.5 { |
+do_test vtab1-1.5.1 { |
catchsql { |
CREATE VIRTUAL TABLE t1 USING echo(no_such_table); |
} |
} {1 {vtable constructor failed: t1}} |
+do_test vtab1-1.5.2 { |
+ catchsql { |
+ CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo(no_such_table); |
+ } |
+} {1 {vtable constructor failed: t1}} |
do_test vtab1-1.6 { |
execsql { |
SELECT name FROM sqlite_master ORDER BY 1 |
@@ -128,17 +147,27 @@ do_test vtab-1.2152.4 { |
# select an illegal table-name (i.e a reserved name or the name of a |
# table that already exists). |
# |
-do_test vtab1-1.7 { |
+do_test vtab1-1.7.1 { |
catchsql { |
CREATE VIRTUAL TABLE sqlite_master USING echo; |
} |
} {1 {object name reserved for internal use: sqlite_master}} |
-do_test vtab1-1.8 { |
+do_test vtab1-1.7.2 { |
+ catchsql { |
+ CREATE VIRTUAL TABLE IF NOT EXISTS sqlite_master USING echo; |
+ } |
+} {1 {object name reserved for internal use: sqlite_master}} |
+do_test vtab1-1.8.1 { |
catchsql { |
CREATE TABLE treal(a, b, c); |
CREATE VIRTUAL TABLE treal USING echo(treal); |
} |
} {1 {table treal already exists}} |
+do_test vtab1-1.8.2 { |
+ catchsql { |
+ CREATE VIRTUAL TABLE IF NOT EXISTS treal USING echo(treal); |
+ } |
+} {0 {}} |
do_test vtab1-1.9 { |
execsql { |
DROP TABLE treal; |
@@ -589,8 +618,9 @@ do_test vtab1-5-6 { |
do_test vtab1-5-7 { |
filter $::echo_module |
} [list \ |
- xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \ |
xFilter {SELECT rowid, * FROM 't1'} \ |
+ xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \ |
+ xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \ |
] |
execsql { |
@@ -724,8 +754,8 @@ do_test vtab1-6-8.4 { |
} {} |
execsql {PRAGMA count_changes=OFF} |
-file delete -force test2.db |
-file delete -force test2.db-journal |
+forcedelete test2.db |
+forcedelete test2.db-journal |
sqlite3 db2 test2.db |
execsql { |
CREATE TABLE techo(a PRIMARY KEY, b, c); |
@@ -1010,10 +1040,10 @@ do_test vtab1.12-1 { |
# First test outside of a transaction. |
do_test vtab1.12-2 { |
catchsql { INSERT INTO echo_c SELECT * FROM b; } |
-} {1 {echo-vtab-error: column a is not unique}} |
+} {1 {echo-vtab-error: UNIQUE constraint failed: c.a}} |
do_test vtab1.12-2.1 { |
sqlite3_errmsg db |
-} {echo-vtab-error: column a is not unique} |
+} {echo-vtab-error: UNIQUE constraint failed: c.a} |
do_test vtab1.12-3 { |
execsql { SELECT * FROM c } |
} {3 G H} |
@@ -1022,7 +1052,7 @@ do_test vtab1.12-3 { |
do_test vtab1.12-4 { |
execsql {BEGIN} |
catchsql { INSERT INTO echo_c SELECT * FROM b; } |
-} {1 {echo-vtab-error: column a is not unique}} |
+} {1 {echo-vtab-error: UNIQUE constraint failed: c.a}} |
do_test vtab1.12-5 { |
execsql { SELECT * FROM c } |
} {3 G H} |
@@ -1062,12 +1092,54 @@ do_test vtab1.13-3 { |
} {15 {} 16} |
-do_test vtab1-14.1 { |
- execsql { DELETE FROM c } |
- set echo_module "" |
- execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) } |
- set echo_module |
-} [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}] |
+do_test vtab1-14.001 { |
+ execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (1,2,3)} |
+} {1 3 G H 2 {} 15 16 3 15 {} 16} |
+do_test vtab1-14.002 { |
+ execsql {SELECT rowid, * FROM echo_c WHERE rowid IN (1,2,3)} |
+} {1 3 G H 2 {} 15 16 3 15 {} 16} |
+do_test vtab1-14.003 { |
+ execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (0,1,5,2,'a',3,NULL)} |
+} {1 3 G H 2 {} 15 16 3 15 {} 16} |
+do_test vtab1-14.004 { |
+ execsql {SELECT rowid, * FROM echo_c WHERE rowid IN (0,1,5,'a',2,3,NULL)} |
+} {1 3 G H 2 {} 15 16 3 15 {} 16} |
+do_test vtab1-14.005 { |
+ execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,1,5,'a',2,3)} |
+} {} |
+do_test vtab1-14.006 { |
+ execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,5,'a',2,3)} |
+} {1 3 G H} |
+do_test vtab1-14.007 { |
+ execsql {SELECT rowid, * FROM echo_c WHERE +rowid NOT IN (0,5,'a',2,3,NULL)} |
+} {} |
+do_test vtab1-14.008 { |
+ execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,5,'a',2,3,NULL)} |
+} {} |
+do_test vtab1-14.011 { |
+ execsql {SELECT * FROM echo_c WHERE +a IN (1,3,8,'x',NULL,15,24)} |
+} {3 G H 15 {} 16} |
+do_test vtab1-14.012 { |
+ execsql {SELECT * FROM echo_c WHERE a IN (1,3,8,'x',NULL,15,24)} |
+} {3 G H 15 {} 16} |
+do_test vtab1-14.013 { |
+ execsql {SELECT * FROM echo_c WHERE a NOT IN (1,8,'x',15,24)} |
+} {3 G H} |
+do_test vtab1-14.014 { |
+ execsql {SELECT * FROM echo_c WHERE a NOT IN (1,8,'x',NULL,15,24)} |
+} {} |
+do_test vtab1-14.015 { |
+ execsql {SELECT * FROM echo_c WHERE +a NOT IN (1,8,'x',NULL,15,24)} |
+} {} |
+ |
+ |
+ |
+#do_test vtab1-14.1 { |
+# execsql { DELETE FROM c } |
+# set echo_module "" |
+# execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) } |
+# set echo_module |
+#} {/.*xBestIndex {SELECT rowid, . FROM 'c' WHERE rowid = .} xFilter {SELECT rowid, . FROM 'c'} 1/} |
do_test vtab1-14.2 { |
set echo_module "" |
@@ -1081,11 +1153,11 @@ do_test vtab1-14.3 { |
set echo_module |
} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1] |
-do_test vtab1-14.4 { |
- set echo_module "" |
- execsql { SELECT * FROM echo_c WHERE a IN (1, 2) } |
- set echo_module |
-} [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}] |
+#do_test vtab1-14.4 { |
+# set echo_module "" |
+# execsql { SELECT * FROM echo_c WHERE a IN (1, 2) } |
+# set echo_module |
+#} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE a = .} xFilter {SELECT rowid, . FROM 'c' WHERE a = .} 1/} |
do_test vtab1-15.1 { |
execsql { |
@@ -1178,5 +1250,149 @@ do_test vtab1-17.1 { |
catchsql { CREATE VIRTUAL TABLE t4 USING echo(t3); } |
} {1 {vtable constructor failed: t4}} |
+# This test verifies that ticket 48f29963 is fixed. |
+# |
+do_test vtab1-17.1 { |
+ execsql { |
+ CREATE TABLE t5(a, b); |
+ CREATE VIRTUAL TABLE e5 USING echo_v2(t5); |
+ BEGIN; |
+ INSERT INTO e5 VALUES(1, 2); |
+ DROP TABLE e5; |
+ SAVEPOINT one; |
+ ROLLBACK TO one; |
+ COMMIT; |
+ } |
+} {} |
+ |
+do_test vtab1-17.2 { |
+ execsql { DELETE FROM sqlite_master WHERE sql LIKE 'insert%' } |
+} {} |
+ |
+#------------------------------------------------------------------------- |
+# The following tests - vtab1-18.* - test that the optimization of LIKE |
+# constraints in where.c plays well with virtual tables. |
+# |
+# 18.1.*: Case-insensitive LIKE. |
+# 18.2.*: Case-sensitive LIKE. |
+# |
unset -nocomplain echo_module_begin_fail |
+ |
+do_execsql_test 18.1.0 { |
+ CREATE TABLE t6(a, b TEXT); |
+ CREATE INDEX i6 ON t6(b, a); |
+ INSERT INTO t6 VALUES(1, 'Peter'); |
+ INSERT INTO t6 VALUES(2, 'Andrew'); |
+ INSERT INTO t6 VALUES(3, 'James'); |
+ INSERT INTO t6 VALUES(4, 'John'); |
+ INSERT INTO t6 VALUES(5, 'Phillip'); |
+ INSERT INTO t6 VALUES(6, 'Bartholomew'); |
+ CREATE VIRTUAL TABLE e6 USING echo(t6); |
+} |
+ |
+foreach {tn sql res filter} { |
+ 1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5} |
+ {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James} |
+ |
+ 1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4} |
+ {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K} |
+ |
+ 1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4} |
+ {xFilter {SELECT rowid, * FROM 't6'}} |
+ |
+ 1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4} |
+ {xFilter {SELECT rowid, * FROM 't6'}} |
+} { |
+ set echo_module {} |
+ do_execsql_test 18.$tn.1 $sql $res |
+ do_test 18.$tn.2 { lrange $::echo_module 2 end } $filter |
+} |
+ |
+do_execsql_test 18.2.0 { PRAGMA case_sensitive_like = ON } |
+foreach {tn sql res filter} { |
+ 2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4} |
+ {xFilter {SELECT rowid, * FROM 't6'}} |
+ |
+ 2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {} |
+ {xFilter {SELECT rowid, * FROM 't6'}} |
+} { |
+ set echo_module {} |
+ do_execsql_test 18.$tn.1 $sql $res |
+ do_test 18.$tn.2 { lrange $::echo_module 2 end } $filter |
+} |
+do_execsql_test 18.2.x { PRAGMA case_sensitive_like = OFF } |
+ |
+#------------------------------------------------------------------------- |
+# Test that an existing module may not be overridden. |
+# |
+do_test 19.1 { |
+ sqlite3 db2 test.db |
+ register_echo_module [sqlite3_connection_pointer db2] |
+} SQLITE_OK |
+do_test 19.2 { |
+ register_echo_module [sqlite3_connection_pointer db2] |
+} SQLITE_MISUSE |
+do_test 19.3 { |
+ db2 close |
+} {} |
+ |
+#------------------------------------------------------------------------- |
+# Test that the bug fixed by [b0c1ba655d69] really is fixed. |
+# |
+do_execsql_test 20.1 { |
+ CREATE TABLE t7 (a, b); |
+ CREATE TABLE t8 (c, d); |
+ CREATE INDEX i2 ON t7(a); |
+ CREATE INDEX i3 ON t7(b); |
+ CREATE INDEX i4 ON t8(c); |
+ CREATE INDEX i5 ON t8(d); |
+ |
+ CREATE VIRTUAL TABLE t7v USING echo(t7); |
+ CREATE VIRTUAL TABLE t8v USING echo(t8); |
+} |
+ |
+do_test 20.2 { |
+ for {set i 0} {$i < 1000} {incr i} { |
+ db eval {INSERT INTO t7 VALUES($i, $i)} |
+ db eval {INSERT INTO t8 VALUES($i, $i)} |
+ } |
+} {} |
+ |
+do_execsql_test 20.3 { |
+ SELECT a, b FROM ( |
+ SELECT a, b FROM t7 WHERE a=11 OR b=12 |
+ UNION ALL |
+ SELECT c, d FROM t8 WHERE c=5 OR d=6 |
+ ) |
+ ORDER BY 1, 2; |
+} {5 5 6 6 11 11 12 12} |
+ |
+do_execsql_test 20.4 { |
+ SELECT a, b FROM ( |
+ SELECT a, b FROM t7v WHERE a=11 OR b=12 |
+ UNION ALL |
+ SELECT c, d FROM t8v WHERE c=5 OR d=6 |
+ ) |
+ ORDER BY 1, 2; |
+} {5 5 6 6 11 11 12 12} |
+ |
+#------------------------------------------------------------------------- |
+# |
+do_execsql_test 21.1 { |
+ CREATE TABLE t9(a,b,c); |
+ CREATE VIRTUAL TABLE t9v USING echo(t9); |
+ |
+ INSERT INTO t9 VALUES(1,2,3); |
+ INSERT INTO t9 VALUES(3,2,1); |
+ INSERT INTO t9 VALUES(2,2,2); |
+} |
+ |
+do_execsql_test 21.2 { |
+ SELECT * FROM t9v WHERE a<b; |
+} {1 2 3} |
+ |
+do_execsql_test 21.3 { |
+ SELECT * FROM t9v WHERE a=b; |
+} {2 2 2} |
+ |
finish_test |