third_party/perfetto/buildtools/sqlite_src/test/bestindex1.test - cobalt - Git at Google

 # 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
	# 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