xref: /sqlite-3.40.0/test/bestindex6.test (revision d96ab995) 
1# 2018-09-09
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
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15set testprefix bestindex6
16
17ifcapable !vtab {
18  finish_test
19  return
20}
21
22register_tcl_module db
23
24proc vtab_command {src method args} {
25  switch -- $method {
26    xConnect {
27      return [db one {SELECT sql FROM sqlite_master where name = $src}]
28    }
29
30    xBestIndex {
31      set hdl [lindex $args 0]
32      set clist [$hdl constraints]
33      set orderby [$hdl orderby]
34      set mask [$hdl mask]
35
36      set wlist 1
37
38      set iCons 0
39      set ret [list]
40      foreach cons $clist {
41        catch { array unset C }
42        array set C $cons
43
44        if {$C(usable)} {
45          set col [db one {
46            SELECT name FROM pragma_table_info($src) WHERE cid=$C(column)
47          }]
48          switch $C(op) {
49            isnull {
50              lappend wlist "$col IS NULL"
51              lappend ret omit $iCons
52            }
53            eq {
54              lappend wlist "$col = %$iCons%"
55              lappend ret omit $iCons
56            }
57          }
58        }
59        incr iCons
60      }
61      #puts "xBestIndex: $ret"
62      lappend ret idxStr [join $wlist " AND "]
63      return $ret
64    }
65
66    xFilter {
67      foreach {idxnum idxstr aa} $args {}
68      set map [list]
69      for {set iCons 0} {$iCons < [llength $aa]} {incr iCons} {
70        lappend map %$iCons% [lindex $aa $iCons]
71      }
72      set ret [list sql \
73          "SELECT rowid, * FROM $src WHERE [string map $map $idxstr]"
74      ]
75      # puts "xFilter: $ret"
76      return $ret
77    }
78
79  }
80
81  return {}
82}
83
84do_execsql_test 1.0 {
85  CREATE TABLE t1(id int, value text);
86  CREATE TABLE t2(ctx int, id int, value text);
87
88  INSERT INTO t1 VALUES(1,'try');
89  INSERT INTO t2 VALUES(1,1,'good');
90  INSERT INTO t2 VALUES(2,2,'evil');
91
92  CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);
93  CREATE VIRTUAL TABLE vt2 USING tcl(vtab_command t2);
94}
95
96do_execsql_test 1.1 {
97  select * from t2 left join t1 on t1.id=t2.ctx where t1.value is null;
98} {2 2 evil {} {}}
99
100do_execsql_test 1.2 {
101  select * from vt2 left join vt1 on vt1.id=vt2.ctx where vt1.value is null;
102} {2 2 evil {} {}}
103
104unset -nocomplain xxx
105do_execsql_test 1.3 {
106  select * from vt2 left join vt1 on vt1.id=vt2.ctx where vt1.value is $xxx;
107} {2 2 evil {} {}}
108
109do_execsql_test 1.4 {
110  select * from t2 left join vt1 on vt1.id=t2.ctx where vt1.value = 3
111} {}
112
113finish_test
114