modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/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  } {
    93    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
    94  }
    95  do_eqp_test 1.2 {
    96    SELECT * FROM t1 WHERE a='abc' AND b='def'
    97  } {
    98    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)}
    99  }
   100  do_eqp_test 1.3 {
   101    SELECT * FROM t1 WHERE a='abc' AND a='def'
   102  } {
   103    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
   104  }
   105  do_eqp_test 1.4 {
   106    SELECT * FROM t1,t2 WHERE c=a
   107  } {
   108    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
   109    0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)}
   110  }
   111  
   112  do_eqp_test 1.5 {
   113    SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d
   114  } {
   115    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
   116    0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
   117    0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
   118  }
   119  
   120  do_eqp_test 1.6 {
   121    SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d
   122  } {
   123    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
   124    0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
   125    0 2 2 {SCAN TABLE 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    0 0 0 {SCAN TABLE x1} 
   136    0 1 1 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:}
   137    0 2 2 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
   138    0 3 3 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
   139  }
   140  
   141  finish_test