diff options
Diffstat (limited to 'test/bestindex3.test')
| -rw-r--r-- | test/bestindex3.test | 185 |
1 files changed, 185 insertions, 0 deletions
diff --git a/test/bestindex3.test b/test/bestindex3.test new file mode 100644 index 0000000..6aa3321 --- /dev/null +++ b/test/bestindex3.test @@ -0,0 +1,185 @@ +# 2016 May 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 bestindex3 + +ifcapable !vtab { + finish_test + return +} + +#------------------------------------------------------------------------- +# Virtual table callback for a virtual table named $tbl. +# +# The table created is: +# +# "CREATE TABLE t1 (a, b, c)" +# +# This virtual table supports both LIKE and = operators on all columns. +# +proc vtab_cmd {bOmit method args} { + switch -- $method { + xConnect { + return "CREATE TABLE t1(a, b, c)" + } + + xBestIndex { + set hdl [lindex $args 0] + set clist [$hdl constraints] + set orderby [$hdl orderby] + set mask [$hdl mask] + + set ret [list] + set use use + if {$bOmit} {set use omit} + + for {set i 0} {$i < [llength $clist]} {incr i} { + array unset C + array set C [lindex $clist $i] + if {$C(usable) && ($C(op)=="like" || $C(op)=="eq")} { + lappend ret $use $i + lappend ret idxstr + lappend ret "[lindex {a b c} $C(column)] [string toupper $C(op)] ?" + break + } + } + + if {$ret==""} { + lappend ret cost 1000000 rows 1000000 + } else { + lappend ret cost 100 rows 10 + } + return $ret + } + + xFilter { + foreach {idxnum idxstr param} $args {} + set where "" + if {$bOmit && $idxstr != ""} { + set where " WHERE [string map [list ? '$param' EQ =] $idxstr]" + } + return [list sql "SELECT rowid, * FROM ttt$where"] + } + } + return "" +} + +register_tcl_module db + +do_execsql_test 1.0 { + CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0"); +} + +do_eqp_test 1.1 { + SELECT * FROM t1 WHERE a LIKE 'abc'; +} {SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?} + +do_eqp_test 1.2 { + SELECT * FROM t1 WHERE a = 'abc'; +} {SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?} + +do_eqp_test 1.3 { + SELECT * FROM t1 WHERE a = 'abc' OR b = 'def'; +} { + QUERY PLAN + `--MULTI-INDEX OR + |--INDEX 1 + | `--SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ? + `--INDEX 2 + `--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ? +} + +do_eqp_test 1.4 { + SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def'; +} { + QUERY PLAN + `--MULTI-INDEX OR + |--INDEX 1 + | `--SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ? + `--INDEX 2 + `--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ? +} + +do_execsql_test 1.5 { + CREATE TABLE ttt(a, b, c); + + INSERT INTO ttt VALUES(1, 'two', 'three'); + INSERT INTO ttt VALUES(2, 'one', 'two'); + INSERT INTO ttt VALUES(3, 'three', 'one'); + INSERT INTO ttt VALUES(4, 'y', 'one'); + INSERT INTO ttt VALUES(5, 'x', 'two'); + INSERT INTO ttt VALUES(6, 'y', 'three'); +} + +foreach omit {0 1} { + do_execsql_test 1.6.$omit.0 " + DROP TABLE t1; + CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $omit'); + " + do_execsql_test 1.6.$omit.1 { + SELECT rowid FROM t1 WHERE c LIKE 'o%' + } {3 4} + + do_execsql_test 1.6.$omit.2 { + SELECT rowid FROM t1 WHERE c LIKE 'o%' OR b='y' + } {3 4 6} + + do_execsql_test 1.6.$omit.3 { + SELECT rowid FROM t1 WHERE c = 'three' OR c LIKE 'o%' + } {1 6 3 4} +} + +#------------------------------------------------------------------------- +# Test the same pattern works with ordinary tables. +# +# This test does not work if the ICU extension is enabled. ICU overrides +# LIKE - and this optimization only works with the built-in LIKE function. +# +ifcapable !icu { + do_execsql_test 2.1 { + CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT); + CREATE INDEX t2x ON t2(x COLLATE nocase); + CREATE INDEX t2y ON t2(y); + } + + do_eqp_test 2.2 { + SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def' + } [string map {"\n " \n} { + QUERY PLAN + `--MULTI-INDEX OR + |--INDEX 1 + | `--SEARCH t2 USING INDEX t2x (x>? AND x<?) + `--INDEX 2 + `--SEARCH t2 USING INDEX t2y (y=?) + }] +} + +#------------------------------------------------------------------------- +# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE +# statement is currently ignored. +# +proc vvv_command {method args} { + switch -- $method { + xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" } + } +} +proc yyy_command {method args} { + switch -- $method { + xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" } + } +} + +do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') } +do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') } + +finish_test |