# 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 clist [lindex $args 0]
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'
} {
0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
}
do_eqp_test 1.2 {
SELECT * FROM x1 WHERE a IN ('abc', 'def');
} {
0 0 0 {SCAN TABLE 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 SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'}
set SQL_SCAN {SELECT * FROM t1x}
set clist [lindex $args 0]
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) {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
set plan(omit) {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
set plan(use2) {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}
0 0 0 {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
} $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 clist [lindex $args 0]
#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
}
finish_test