diff options
Diffstat (limited to 'test/vtab1.test')
| -rw-r--r-- | test/vtab1.test | 91 |
1 files changed, 86 insertions, 5 deletions
diff --git a/test/vtab1.test b/test/vtab1.test index 16f1b43..38aec09 100644 --- a/test/vtab1.test +++ b/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; @@ -1193,5 +1222,57 @@ do_test vtab1-17.1 { } } {} +#------------------------------------------------------------------------- +# 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 } + finish_test |