/ Check-in [f191431d63]
Login

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

Overview
Comment:Fix the LIKE optimization so that it is disabled when the LHS of the expression does not have TEXT affinity and the RHS is the pattern '/%' or the RHS begins with the escape character. Fix for ticket [c94369cae9b561b1f996d0054b].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f191431d63aba187000b5cbaf3e072ff5f1b1eef6a2a407fb4d1629bda4e4e8f
User & Date: drh 2018-09-10 12:40:57
Context
2018-09-10
14:43
New testcase() macros on virtual table xBestIndex logic. check-in: 3bbd9c5f2f user: drh tags: trunk
12:40
Fix the LIKE optimization so that it is disabled when the LHS of the expression does not have TEXT affinity and the RHS is the pattern '/%' or the RHS begins with the escape character. Fix for ticket [c94369cae9b561b1f996d0054b]. check-in: f191431d63 user: drh tags: trunk
12:17
Fix a problem with processing a "vtab.col IS NULL" expression within the WHERE clause of a query when "vtab" is a virtual table on the rhs of a LEFT JOIN. check-in: 83da4d4104 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
...
275
276
277
278
279
280
281


























282
283
284
285
286
287
288
    sqlite3VdbeSetVarmask(pParse->pVdbe, iCol);
    assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
  }else if( op==TK_STRING ){
    z = (u8*)pRight->u.zToken;
  }
  if( z ){

    /* If the RHS begins with a digit or a minus sign, then the LHS must
    ** be an ordinary column (not a virtual table column) with TEXT affinity.
    ** Otherwise the LHS might be numeric and "lhs >= rhs" would be false
    ** even though "lhs LIKE rhs" is true.  But if the RHS does not start
    ** with a digit or '-', then "lhs LIKE rhs" will always be false if
    ** the LHS is numeric and so the optimization still works.
    */
    if( sqlite3Isdigit(z[0]) || z[0]=='-' ){
      if( pLeft->op!=TK_COLUMN 
       || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
       || IsVirtual(pLeft->pTab)  /* Value might be numeric */
      ){
        sqlite3ValueFree(pVal);
        return 0;
      }
    }

    /* Count the number of prefix characters prior to the first wildcard */
    cnt = 0;
    while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
      cnt++;
      if( c==wc[3] && z[cnt]!=0 ) cnt++;
    }

................................................................................
        char *zNew = pPrefix->u.zToken;
        zNew[cnt] = 0;
        for(iFrom=iTo=0; iFrom<cnt; iFrom++){
          if( zNew[iFrom]==wc[3] ) iFrom++;
          zNew[iTo++] = zNew[iFrom];
        }
        zNew[iTo] = 0;


























      }
      *ppPrefix = pPrefix;

      /* If the RHS pattern is a bound parameter, make arrangements to
      ** reprepare the statement when that parameter is rebound */
      if( op==TK_VARIABLE ){
        Vdbe *v = pParse->pVdbe;







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







226
227
228
229
230
231
232

















233
234
235
236
237
238
239
...
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
    sqlite3VdbeSetVarmask(pParse->pVdbe, iCol);
    assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
  }else if( op==TK_STRING ){
    z = (u8*)pRight->u.zToken;
  }
  if( z ){


















    /* Count the number of prefix characters prior to the first wildcard */
    cnt = 0;
    while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
      cnt++;
      if( c==wc[3] && z[cnt]!=0 ) cnt++;
    }

................................................................................
        char *zNew = pPrefix->u.zToken;
        zNew[cnt] = 0;
        for(iFrom=iTo=0; iFrom<cnt; iFrom++){
          if( zNew[iFrom]==wc[3] ) iFrom++;
          zNew[iTo++] = zNew[iFrom];
        }
        zNew[iTo] = 0;

        /* If the RHS begins with a digit or a minus sign, then the LHS must be
        ** an ordinary column (not a virtual table column) with TEXT affinity.
        ** Otherwise the LHS might be numeric and "lhs >= rhs" would be false
        ** even though "lhs LIKE rhs" is true.  But if the RHS does not start
        ** with a digit or '-', then "lhs LIKE rhs" will always be false if
        ** the LHS is numeric and so the optimization still works.
        **
        ** 2018-09-10 ticket c94369cae9b561b1f996d0054bfab11389f9d033
        ** The RHS pattern must not be '/%' because the termination condition
        ** will then become "x<'0'" and if the affinity is numeric, will then
        ** be converted into "x<0", which is incorrect.
        */
        if( sqlite3Isdigit(zNew[0])
         || zNew[0]=='-'
         || (zNew[0]+1=='0' && iTo==1)
        ){
          if( pLeft->op!=TK_COLUMN 
           || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
           || IsVirtual(pLeft->pTab)  /* Value might be numeric */
          ){
            sqlite3ExprDelete(db, pPrefix);
            sqlite3ValueFree(pVal);
            return 0;
          }
        }
      }
      *ppPrefix = pPrefix;

      /* If the RHS pattern is a bound parameter, make arrangements to
      ** reprepare the statement when that parameter is rebound */
      if( op==TK_VARIABLE ){
        Vdbe *v = pParse->pVdbe;

Changes to test/like3.test.

107
108
109
110
111
112
113
114
































































115
} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
do_execsql_test like3-4.2 {
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
do_execsql_test like3-4.2ck {
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}

































































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

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
171
172
173
174
175
176
177
178
179
} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
do_execsql_test like3-4.2 {
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
do_execsql_test like3-4.2ck {
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}

# 2018-09-10 ticket https://www.sqlite.org/src/tktview/c94369cae9b561b1f996
# The like optimization fails for a column with numeric affinity if
# the pattern '/%' or begins with the escape character.
#
do_execsql_test like3-5.100 {
  CREATE TABLE t5a(x INT UNIQUE COLLATE nocase);
  INSERT INTO t5a(x) VALUES('/abc'),(123),(-234);
  SELECT x FROM t5a WHERE x LIKE '/%';
} {/abc}
do_eqp_test like3-5.101 {
  SELECT x FROM t5a WHERE x LIKE '/%';
} {
  QUERY PLAN
  `--SCAN TABLE t5a
}
do_execsql_test like3-5.110 {
  SELECT x FROM t5a WHERE x LIKE '/a%';
} {/abc}
do_eqp_test like3-5.111 {
  SELECT x FROM t5a WHERE x LIKE '/a%';
} {
  QUERY PLAN
  `--SEARCH TABLE t5a USING COVERING INDEX sqlite_autoindex_t5a_1 (x>? AND x<?)
}
do_execsql_test like3-5.120 {
  SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
} {123}
do_eqp_test like3-5.121 {
  SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
} {
  QUERY PLAN
  `--SCAN TABLE t5a
}
do_execsql_test like3-5.122 {
  SELECT x FROM t5a WHERE x LIKE '^-2%' ESCAPE '^';
} {-234}
do_eqp_test like3-5.123 {
  SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
} {
  QUERY PLAN
  `--SCAN TABLE t5a
}

do_execsql_test like3-5.200 {
  CREATE TABLE t5b(x INT UNIQUE COLLATE binary);
  INSERT INTO t5b(x) VALUES('/abc'),(123),(-234);
  SELECT x FROM t5b WHERE x GLOB '/*';
} {/abc}
do_eqp_test like3-5.201 {
  SELECT x FROM t5b WHERE x GLOB '/*';
} {
  QUERY PLAN
  `--SCAN TABLE t5b
}
do_execsql_test like3-5.210 {
  SELECT x FROM t5b WHERE x GLOB '/a*';
} {/abc}
do_eqp_test like3-5.211 {
  SELECT x FROM t5b WHERE x GLOB '/a*';
} {
  QUERY PLAN
  `--SEARCH TABLE t5b USING COVERING INDEX sqlite_autoindex_t5b_1 (x>? AND x<?)
}

finish_test