/ Check-in [fa3a89fc]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:The OR optimization is usable on virtual tables with LIKE, REGEXP and/or GLOB terms in the WHERE clause.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fa3a89fc0b88d5ad7f5c232198847b3483eef611
User & Date: drh 2016-06-03 18:59:37
Context
2016-06-04
13:57
Remove an unreachable branch in the UNIQUE constraint parsing. check-in: 313e990c user: drh tags: trunk
2016-06-03
18:59
The OR optimization is usable on virtual tables with LIKE, REGEXP and/or GLOB terms in the WHERE clause. check-in: fa3a89fc user: drh tags: trunk
18:44
Add support for virtual tables using a WITHOUT ROWID schema. This merge also includes enhancements to the CSV extension, which is used for testing of the new WITHOUT ROWID virtual table mechanism. check-in: aa7e9d0c user: drh tags: trunk
2016-05-28
18:53
Experimental change to allow virtual tables to take advantage of LIKE, REGEXP and GLOB terms that are part of OR expressions within WHERE clauses. Closed-Leaf check-in: 242507b4 user: dan tags: vtab-experimental
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

564
565
566
567
568
569
570
571


572
573
574
575
576
577
578
....
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
        sqlite3WhereClauseInit(pAndWC, pWC->pWInfo);
        sqlite3WhereSplit(pAndWC, pOrTerm->pExpr, TK_AND);
        sqlite3WhereExprAnalyze(pSrc, pAndWC);
        pAndWC->pOuter = pWC;
        if( !db->mallocFailed ){
          for(j=0, pAndTerm=pAndWC->a; j<pAndWC->nTerm; j++, pAndTerm++){
            assert( pAndTerm->pExpr );
            if( allowedOp(pAndTerm->pExpr->op) ){


              b |= sqlite3WhereGetMask(&pWInfo->sMaskSet, pAndTerm->leftCursor);
            }
          }
        }
        indexable &= b;
      }
    }else if( pOrTerm->wtFlags & TERM_COPIED ){
................................................................................
#ifndef SQLITE_OMIT_VIRTUALTABLE
  /* Add a WO_MATCH auxiliary term to the constraint set if the
  ** current expression is of the form:  column MATCH expr.
  ** This information is used by the xBestIndex methods of
  ** virtual tables.  The native query optimizer does not attempt
  ** to do anything with MATCH functions.
  */
  if( isMatchOfColumn(pExpr, &eOp2) ){
    int idxNew;
    Expr *pRight, *pLeft;
    WhereTerm *pNewTerm;
    Bitmask prereqColumn, prereqExpr;

    pRight = pExpr->x.pList->a[0].pExpr;
    pLeft = pExpr->x.pList->a[1].pExpr;







|
>
>







 







|







564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
....
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
        sqlite3WhereClauseInit(pAndWC, pWC->pWInfo);
        sqlite3WhereSplit(pAndWC, pOrTerm->pExpr, TK_AND);
        sqlite3WhereExprAnalyze(pSrc, pAndWC);
        pAndWC->pOuter = pWC;
        if( !db->mallocFailed ){
          for(j=0, pAndTerm=pAndWC->a; j<pAndWC->nTerm; j++, pAndTerm++){
            assert( pAndTerm->pExpr );
            if( allowedOp(pAndTerm->pExpr->op) 
             || pAndTerm->eOperator==WO_MATCH 
            ){
              b |= sqlite3WhereGetMask(&pWInfo->sMaskSet, pAndTerm->leftCursor);
            }
          }
        }
        indexable &= b;
      }
    }else if( pOrTerm->wtFlags & TERM_COPIED ){
................................................................................
#ifndef SQLITE_OMIT_VIRTUALTABLE
  /* Add a WO_MATCH auxiliary term to the constraint set if the
  ** current expression is of the form:  column MATCH expr.
  ** This information is used by the xBestIndex methods of
  ** virtual tables.  The native query optimizer does not attempt
  ** to do anything with MATCH functions.
  */
  if( pWC->op==TK_AND && isMatchOfColumn(pExpr, &eOp2) ){
    int idxNew;
    Expr *pRight, *pLeft;
    WhereTerm *pNewTerm;
    Bitmask prereqColumn, prereqExpr;

    pRight = pExpr->x.pList->a[0].pExpr;
    pLeft = pExpr->x.pList->a[1].pExpr;

Added test/bestindex3.test.





















































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
# 2016 May 29
#
# 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 bestindex3

ifcapable !vtab {
  finish_test
  return
}

#-------------------------------------------------------------------------
# Virtual table callback for a virtual table named $tbl.
#
# The table created is:
#
#      "CREATE TABLE t1 (a, b, c)"
#
# This virtual table supports both LIKE and = operators on all columns.
#  
proc vtab_cmd {bOmit method args} {
  switch -- $method {
    xConnect {
      return "CREATE TABLE t1(a, b, c)"
    }

    xBestIndex {
      foreach {clist orderby mask} $args {}

      set ret [list]
      set use use
      if {$bOmit} {set use omit}

      for {set i 0} {$i < [llength $clist]} {incr i} {
        array unset C
        array set C [lindex $clist $i]
        if {$C(usable) && ($C(op)=="like" || $C(op)=="eq")} {
          lappend ret $use $i
          lappend ret idxstr 
          lappend ret "[lindex {a b c} $C(column)] [string toupper $C(op)] ?"
          break
        }
      }

      if {$ret==""} {
        lappend ret cost 1000000 rows 1000000
      } else {
        lappend ret cost 100 rows 10
      }
      return $ret
    }

    xFilter {
      foreach {idxnum idxstr param} $args {}
      set where ""
      if {$bOmit && $idxstr != ""} {
        set where " WHERE [string map [list ? '$param' EQ =] $idxstr]"
      }
      return [list sql "SELECT rowid, * FROM ttt$where"]
    }
  }
  return ""
}

register_tcl_module db

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0");
}

do_eqp_test 1.1 {
  SELECT * FROM t1 WHERE a LIKE 'abc';
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
}

do_eqp_test 1.2 {
  SELECT * FROM t1 WHERE a = 'abc';
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
}

do_eqp_test 1.3 {
  SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?}
}

do_eqp_test 1.4 {
  SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?}
}

do_execsql_test 1.5 {
  CREATE TABLE ttt(a, b, c);

  INSERT INTO ttt VALUES(1, 'two',   'three');
  INSERT INTO ttt VALUES(2, 'one',   'two');
  INSERT INTO ttt VALUES(3, 'three', 'one');
  INSERT INTO ttt VALUES(4, 'y',     'one');
  INSERT INTO ttt VALUES(5, 'x',     'two');
  INSERT INTO ttt VALUES(6, 'y',     'three');
}

foreach omit {0 1} {
  do_execsql_test 1.6.$omit.0 "
    DROP TABLE t1;
    CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $omit');
  "
  do_execsql_test 1.6.$omit.1 { 
    SELECT rowid FROM t1 WHERE c LIKE 'o%'
  } {3 4}

  do_execsql_test 1.6.$omit.2 { 
    SELECT rowid FROM t1 WHERE c LIKE 'o%' OR b='y'
  } {3 4 6}

  do_execsql_test 1.6.$omit.3 { 
    SELECT rowid FROM t1 WHERE c = 'three' OR c LIKE 'o%'
  } {1 6 3 4}
}

#-------------------------------------------------------------------------
# Test the same pattern works with ordinary tables.
#
do_execsql_test 2.1 {
  CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT);
  CREATE INDEX t2x ON t2(x COLLATE nocase);
  CREATE INDEX t2y ON t2(y);
}

do_eqp_test 2.2 {
  SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
} {
  0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)}
  0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)}
}

#-------------------------------------------------------------------------
# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE 
# statement is currently ignored.
#
proc vvv_command {method args} {
  switch -- $method {
    xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" }
  }
}
proc yyy_command {method args} {
  switch -- $method {
    xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" }
  }
}

do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') }
do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') }

finish_test