github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/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        foreach {clist orderby mask} $args {}
    51  
    52        set cons [list]
    53        set used [list]
    54  
    55        for {set i 0} {$i < [llength $clist]} {incr i} {
    56          array unset C
    57          array set C [lindex $clist $i]
    58          if {$C(op)=="eq" && $C(usable) && [lsearch $cons $C(column)]<0} {
    59            lappend used use $i
    60            lappend cons $C(column)
    61          }
    62        }
    63  
    64        set nCons [llength $cons]
    65        if {$nCons==0} {
    66          return "cost 1000000 rows 1000000"
    67        } else {
    68          set cost [expr (11-$nCons) * 1000]
    69          set ret [concat $used "cost $cost rows $cost"]
    70  
    71          set txt [list]
    72          foreach c $cons { lappend txt "[lindex $cols $c]=?" }
    73          lappend ret idxstr "indexed([join $txt { AND }])"
    74  
    75          return $ret
    76        }
    77      }
    78    }
    79    return ""
    80  }
    81  
    82  register_tcl_module db
    83  
    84  do_execsql_test 1.0 {
    85    CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd t1 {a b}");
    86    CREATE VIRTUAL TABLE t2 USING tcl("vtab_cmd t2 {c d}");
    87    CREATE VIRTUAL TABLE t3 USING tcl("vtab_cmd t3 {e f}");
    88  }
    89  
    90  do_eqp_test 1.1 {
    91    SELECT * FROM t1 WHERE a='abc'
    92  } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
    93  
    94  do_eqp_test 1.2 {
    95    SELECT * FROM t1 WHERE a='abc' AND b='def'
    96  } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)}
    97  
    98  do_eqp_test 1.3 {
    99    SELECT * FROM t1 WHERE a='abc' AND a='def'
   100  } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
   101  
   102  do_eqp_test 1.4 {
   103    SELECT * FROM t1,t2 WHERE c=a
   104  } {
   105    QUERY PLAN
   106    |--SCAN t1 VIRTUAL TABLE INDEX 0:
   107    `--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   108  }
   109  
   110  do_eqp_test 1.5 {
   111    SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d
   112  } {
   113    QUERY PLAN
   114    |--SCAN t1 VIRTUAL TABLE INDEX 0:
   115    |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   116    `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   117  }
   118  
   119  do_eqp_test 1.6 {
   120    SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d
   121  } {
   122    QUERY PLAN
   123    |--SCAN t1 VIRTUAL TABLE INDEX 0:
   124    |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   125    `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   126  }
   127  
   128  do_execsql_test 1.7.1 {
   129    CREATE TABLE x1(a, b);
   130  }
   131  do_eqp_test 1.7.2 {
   132    SELECT * FROM x1 CROSS JOIN t1, t2, t3 
   133      WHERE t1.a = t2.c AND t1.b = t3.e
   134  } {
   135    QUERY PLAN
   136    |--SCAN x1
   137    |--SCAN t1 VIRTUAL TABLE INDEX 0:
   138    |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   139    `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   140  }
   141  
   142  finish_test