gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/bestindex2.test (about)

     1  # 2016 March 3
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  
    12  set testdir [file dirname $argv0]
    13  source $testdir/tester.tcl
    14  set testprefix bestindex2
    15  
    16  ifcapable !vtab {
    17    finish_test
    18    return
    19  }
    20  
    21  #-------------------------------------------------------------------------
    22  # Virtual table callback for table named $tbl, with the columns specified
    23  # by list argument $cols. e.g. if the function is invoked as:
    24  #
    25  #   vtab_cmd t1 {a b c} ...
    26  #
    27  # The table created is:
    28  #
    29  #      "CREATE TABLE t1 (a, b, c)"
    30  #
    31  # The tables xBestIndex method behaves as if all possible combinations of
    32  # "=" constraints (but no others) may be optimized. The cost of a full table
    33  # scan is:
    34  #
    35  #      "WHERE 1"                "cost 1000000 rows 1000000"
    36  #
    37  # If one or more "=" constraints are in use, the cost and estimated number
    38  # of rows returned are both is (11 - nCons)*1000, where nCons is the number
    39  # of constraints used. e.g.
    40  #
    41  #   "WHERE a=? AND b=?"    ->   "cost  900 rows  900"
    42  #   "WHERE c=? AND b<?"    ->   "cost 1000 rows 1000"
    43  #  
    44  proc vtab_cmd {tbl cols method args} {
    45    switch -- $method {
    46      xConnect {
    47        return "CREATE TABLE $tbl ([join $cols ,])"
    48      }
    49      xBestIndex {
    50        set hdl [lindex $args 0]
    51        set clist [$hdl constraints]
    52        set orderby [$hdl orderby]
    53        set mask [$hdl mask]
    54  
    55        set cons [list]
    56        set used [list]
    57  
    58        for {set i 0} {$i < [llength $clist]} {incr i} {
    59          array unset C
    60          array set C [lindex $clist $i]
    61          if {$C(op)=="eq" && $C(usable) && [lsearch $cons $C(column)]<0} {
    62            lappend used use $i
    63            lappend cons $C(column)
    64          }
    65        }
    66  
    67        set nCons [llength $cons]
    68        if {$nCons==0} {
    69          return "cost 1000000 rows 1000000"
    70        } else {
    71          set cost [expr (11-$nCons) * 1000]
    72          set ret [concat $used "cost $cost rows $cost"]
    73  
    74          set txt [list]
    75          foreach c $cons { lappend txt "[lindex $cols $c]=?" }
    76          lappend ret idxstr "indexed([join $txt { AND }])"
    77  
    78          return $ret
    79        }
    80      }
    81    }
    82    return ""
    83  }
    84  
    85  register_tcl_module db
    86  
    87  do_execsql_test 1.0 {
    88    CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd t1 {a b}");
    89    CREATE VIRTUAL TABLE t2 USING tcl("vtab_cmd t2 {c d}");
    90    CREATE VIRTUAL TABLE t3 USING tcl("vtab_cmd t3 {e f}");
    91  }
    92  
    93  do_eqp_test 1.1 {
    94    SELECT * FROM t1 WHERE a='abc'
    95  } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
    96  
    97  do_eqp_test 1.2 {
    98    SELECT * FROM t1 WHERE a='abc' AND b='def'
    99  } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)}
   100  
   101  do_eqp_test 1.3 {
   102    SELECT * FROM t1 WHERE a='abc' AND a='def'
   103  } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
   104  
   105  do_eqp_test 1.4 {
   106    SELECT * FROM t1,t2 WHERE c=a
   107  } {
   108    QUERY PLAN
   109    |--SCAN t1 VIRTUAL TABLE INDEX 0:
   110    `--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   111  }
   112  
   113  do_eqp_test 1.5 {
   114    SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d
   115  } {
   116    QUERY PLAN
   117    |--SCAN t1 VIRTUAL TABLE INDEX 0:
   118    |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   119    `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   120  }
   121  
   122  do_eqp_test 1.6 {
   123    SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d
   124  } {
   125    QUERY PLAN
   126    |--SCAN t1 VIRTUAL TABLE INDEX 0:
   127    |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   128    `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   129  }
   130  
   131  do_execsql_test 1.7.1 {
   132    CREATE TABLE x1(a, b);
   133  }
   134  do_eqp_test 1.7.2 {
   135    SELECT * FROM x1 CROSS JOIN t1, t2, t3 
   136      WHERE t1.a = t2.c AND t1.b = t3.e
   137  } {
   138    QUERY PLAN
   139    |--SCAN x1
   140    |--SCAN t1 VIRTUAL TABLE INDEX 0:
   141    |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   142    `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   143  }
   144  
   145  finish_test