summaryrefslogtreecommitdiffstats
path: root/test/bestindex1.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/bestindex1.test')
-rw-r--r--test/bestindex1.test344
1 files changed, 344 insertions, 0 deletions
diff --git a/test/bestindex1.test b/test/bestindex1.test
new file mode 100644
index 0000000..e505a8b
--- /dev/null
+++ b/test/bestindex1.test
@@ -0,0 +1,344 @@
+# 2016-03-01
+#
+# 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 bestindex1
+
+ifcapable !vtab {
+ finish_test
+ return
+}
+
+register_tcl_module db
+
+proc vtab_command {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]
+
+ if {[llength $clist]!=1} { error "unexpected constraint list" }
+ catch { array unset C }
+ array set C [lindex $clist 0]
+ if {$C(usable)} {
+ return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!"
+ } else {
+ return "cost 1000000 rows 0 idxnum 0 idxstr scan..."
+ }
+ }
+
+ }
+
+ return {}
+}
+
+do_execsql_test 1.0 {
+ CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
+} {}
+
+do_eqp_test 1.1 {
+ SELECT * FROM x1 WHERE a = 'abc'
+} {SCAN x1 VIRTUAL TABLE INDEX 555:eq!}
+
+do_eqp_test 1.2 {
+ SELECT * FROM x1 WHERE a IN ('abc', 'def');
+} {SCAN x1 VIRTUAL TABLE INDEX 555:eq!}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+register_tcl_module db
+
+# Parameter $mode may be one of:
+#
+# "omit" - Implement filtering. Set the omit flag.
+# "use" - Implement filtering. Use the constraint, but do not set omit.
+# "use2" - Do not implement filtering. Use the constraint anyway.
+#
+#
+proc t1_vtab {mode method args} {
+ switch -- $method {
+ xConnect {
+ return "CREATE TABLE t1(a, b)"
+ }
+
+ xBestIndex {
+ set hdl [lindex $args 0]
+ set clist [$hdl constraints]
+ set orderby [$hdl orderby]
+
+ set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'}
+ set SQL_SCAN {SELECT * FROM t1x}
+
+ set idx 0
+ for {set idx 0} {$idx < [llength $clist]} {incr idx} {
+ array unset C
+ array set C [lindex $clist $idx]
+ if {$C(column)==0 && $C(op)=="eq" && $C(usable)} {
+ switch -- $mode {
+ "omit" {
+ return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER]
+ }
+ "use" {
+ return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER]
+ }
+ "use2" {
+ return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN]
+ }
+ default {
+ error "Bad mode - $mode"
+ }
+ }
+ }
+ }
+
+ return [list idxstr {SELECT * FROM t1x}]
+ }
+
+ xFilter {
+ set map [list %1% [lindex $args 2 0]]
+ set sql [string map $map [lindex $args 1]]
+ return [list sql $sql]
+ }
+ }
+
+ return {}
+}
+
+do_execsql_test 2.1 {
+ CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b);
+ INSERT INTO t1x VALUES(1, 'one', 1);
+ INSERT INTO t1x VALUES(2, 'two', 2);
+ INSERT INTO t1x VALUES(3, 'three', 3);
+ INSERT INTO t1x VALUES(4, 'four', 4);
+}
+
+foreach {tn mode} {
+ 1 use 2 omit 3 use2
+} {
+ do_execsql_test 2.2.$mode.1 "
+ DROP TABLE IF EXISTS t1;
+ CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode);
+ "
+
+ do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4}
+ do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4}
+ do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2}
+
+ do_execsql_test 2.2.$mode.5 {
+ SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
+ } {1 4}
+
+ set plan(use) {
+ QUERY PLAN
+ |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'
+ `--USE TEMP B-TREE FOR ORDER BY
+ }
+ set plan(omit) {
+ QUERY PLAN
+ |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'
+ `--USE TEMP B-TREE FOR ORDER BY
+ }
+ set plan(use2) {
+ QUERY PLAN
+ |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x
+ `--USE TEMP B-TREE FOR ORDER BY
+ }
+
+ do_eqp_test 2.2.$mode.6 {
+ SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
+ } [string map {"\n " "\n"} $plan($mode)]
+}
+
+# 2016-04-09.
+# Demonstrate a register overwrite problem when using two virtual
+# tables where the outer loop uses the IN operator.
+#
+set G(collist) [list PrimaryKey flagA columnA]
+set G(cols) [join $G(collist) ,]
+set G(nulls) "NULL"
+
+proc vtab_command {method args} {
+ global G
+
+ switch -- $method {
+ xConnect {
+ return "CREATE TABLE t1($G(cols))"
+ }
+
+ xBestIndex {
+ set hdl [lindex $args 0]
+ set clist [$hdl constraints]
+ set orderby [$hdl orderby]
+
+ #puts $clist
+ set W [list]
+ set U [list]
+
+ set i 0
+ for {set idx 0} {$idx < [llength $clist]} {incr idx} {
+ array set c [lindex $clist $idx]
+ if {$c(op)=="eq" && $c(usable)} {
+ lappend W "[lindex $G(collist) $c(column)] = %$i%"
+ lappend U use $idx
+ incr i
+ }
+ }
+
+ if {$W==""} {
+ set sql "SELECT rowid, * FROM t1"
+ } else {
+ set sql "SELECT rowid, * FROM t1 WHERE [join $W { AND }]"
+ }
+
+ return [concat [list idxstr $sql] $U]
+ }
+
+ xFilter {
+ foreach {idxnum idxstr vals} $args {}
+
+ set map [list]
+ for {set i 0} {$i < [llength $vals]} {incr i} {
+ lappend map "%$i%"
+ set v [lindex $vals $i]
+ if {[string is integer $v]} {
+ lappend map $v
+ } else {
+ lappend map "'$v'"
+ }
+ }
+ set sql [string map $map $idxstr]
+
+ #puts "SQL: $sql"
+ return [list sql $sql]
+ }
+ }
+
+ return {}
+}
+
+db close
+forcedelete test.db
+sqlite3 db test.db
+register_tcl_module db
+
+do_execsql_test 3.1 "
+ CREATE TABLE t1($G(cols));
+ INSERT INTO t1 VALUES(1, 0, 'ValueA');
+ INSERT INTO t1 VALUES(2, 0, 'ValueA');
+ INSERT INTO t1 VALUES(3, 0, 'ValueB');
+ INSERT INTO t1 VALUES(4, 0, 'ValueB');
+"
+
+do_execsql_test 3.2 {
+ CREATE VIRTUAL TABLE VirtualTableA USING tcl(vtab_command);
+ CREATE VIRTUAL TABLE VirtualTableB USING tcl(vtab_command);
+}
+
+do_execsql_test 3.3 { SELECT primarykey FROM VirtualTableA } {1 2 3 4}
+
+do_execsql_test 3.4 {
+ SELECT * FROM
+ VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey
+ WHERE a.ColumnA IN ('ValueA', 'ValueB') AND a.FlagA=0
+} {
+ 1 0 ValueA 1 0 ValueA
+ 2 0 ValueA 2 0 ValueA
+ 3 0 ValueB 3 0 ValueB
+ 4 0 ValueB 4 0 ValueB
+}
+
+do_execsql_test 3.5 {
+ SELECT * FROM
+ VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey
+ WHERE a.FlagA=0 AND a.ColumnA IN ('ValueA', 'ValueB')
+} {
+ 1 0 ValueA 1 0 ValueA
+ 2 0 ValueA 2 0 ValueA
+ 3 0 ValueB 3 0 ValueB
+ 4 0 ValueB 4 0 ValueB
+}
+
+#-------------------------------------------------------------------------
+# If there is an IN(..) condition in the WHERE clause of a query on a
+# virtual table, the xBestIndex method is first invoked with the IN(...)
+# represented by a "usable" SQLITE_INDEX_CONSTRAINT_EQ constraint. If
+# the virtual table elects to use the IN(...) constraint, then the
+# xBestIndex method is invoked again, this time with the IN(...) marked
+# as "not usable". Depending on the relative costs of the two plans as
+# defined by the virtual table implementation, and the cardinality of the
+# IN(...) operator, SQLite chooses the most efficient plan.
+#
+# At one point the second invocation of xBestIndex() was only being made
+# for join queries. The following tests check that this problem has been
+# fixed.
+#
+proc vtab_command {method args} {
+ switch -- $method {
+ xConnect {
+ return "CREATE TABLE t1(a, b, c, d)"
+ }
+
+ xBestIndex {
+ set hdl [lindex $args 0]
+ set clist [$hdl constraints]
+ set orderby [$hdl orderby]
+
+ lappend ::bestindex_calls $clist
+ set ret "cost 1000000 idxnum 555"
+ for {set i 0} {$i < [llength $clist]} {incr i} {
+ array set C [lindex $clist $i]
+ if {$C(usable)} {
+ lappend ret use $i
+ }
+ }
+ return $ret
+ }
+ }
+ return {}
+}
+
+do_execsql_test 4.0 {
+ CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
+} {}
+
+do_test 4.1 {
+ set ::bestindex_calls [list]
+ execsql {
+ SELECT * FROM x1 WHERE a=? AND b BETWEEN ? AND ? AND c IN (1, 2, 3, 4);
+ }
+ set ::bestindex_calls
+} [list \
+ [list {op eq column 0 usable 1} \
+ {op eq column 2 usable 1} \
+ {op ge column 1 usable 1} \
+ {op le column 1 usable 1} \
+ ] \
+ [list {op eq column 0 usable 1} \
+ {op eq column 2 usable 0} \
+ {op ge column 1 usable 1} \
+ {op le column 1 usable 1}
+ ]
+]
+
+do_catchsql_test 5.0 {
+ SELECT * FROM tcl('abc');
+} {1 {wrong number of arguments}}
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-19 23:22:46 +0000