/ Check-in [b043a54c]
Login

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

Overview
Comment:The "LIKE Optimization" (which converts a LIKE into a BETWEEN operator that is able to use an index) does not work and cannot be used if the collating sequence of the column is not TEXT and if the pattern starts with a "+" sign. This is another case of ticket [c94369cae9b561b1f996d005]. The new test case was discovered by Manuel Rigger.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b043a54c3de54b286c4eae564eab6b99118a410d99bdb63480faba3123d2ca11
User & Date: drh 2019-05-02 01:41:53
Original Comment: The collating sequence of the column must be TEXT if the LIKE or GLOB pattern starts with a "+" sign. This is another case of ticket [c94369cae9b561b1f996d005] that was discovered by Manuel Rigger.
References
2019-06-14
12:28
Refactor the LIKE optimization decision logic so that it uses sqlite3AtoF() on both boundary keys to determine if the optimization can be used when the LHS is something that might not have TEXT affinity. Ticket [ce8717f0885af975]. See also [c94369cae9b561b1], [b043a54c3de54b28], [fd76310a5e843e07], and [158290c0abafde67]. check-in: b4a9e09e user: drh tags: trunk
Context
2019-05-02
15:56
Earlier detection of a database corruption case in balance_nonroot(), to prevent a possible use of an uninitialized variable. check-in: c509d8a8 user: drh tags: trunk
14:15
Merge enhancements from trunk Leaf check-in: 3a4751a9 user: drh tags: hard-heap-limit
01:41
The "LIKE Optimization" (which converts a LIKE into a BETWEEN operator that is able to use an index) does not work and cannot be used if the collating sequence of the column is not TEXT and if the pattern starts with a "+" sign. This is another case of ticket [c94369cae9b561b1f996d005]. The new test case was discovered by Manuel Rigger. check-in: b043a54c user: drh tags: trunk
00:52
Fix an issue (discovered by OSSFuzz) in the enhanced OP_Concat operator from check-in [713caa382cf7dd] earlier today. check-in: 3e897702 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

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
        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->y.pTab)  /* Value might be numeric */
          ){
            sqlite3ExprDelete(db, pPrefix);







|



|









>







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
        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 +/- 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]=='+'
         || (zNew[0]+1=='0' && iTo==1)
        ){
          if( pLeft->op!=TK_COLUMN 
           || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
           || IsVirtual(pLeft->y.pTab)  /* Value might be numeric */
          ){
            sqlite3ExprDelete(db, pPrefix);

Changes to test/like3.test.

173
174
175
176
177
178
179










180
181
182
183
184
185
186
} {/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<?)
}











# 2019-02-27
# Verify that the LIKE optimization works with an ESCAPE clause when
# using PRAGMA case_sensitive_like=ON.
#
ifcapable !icu {
do_execsql_test like3-6.100 {







>
>
>
>
>
>
>
>
>
>







173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
} {/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<?)
}

# 2019-05-01
# another case of the above reported on the mailing list by Manual Rigger.
#
do_execsql_test like3-5.300 {
  CREATE TABLE t5c (c0 REAL);
  CREATE INDEX t5c_0 ON t5c(c0 COLLATE NOCASE);
  INSERT INTO t5c(rowid, c0) VALUES (99,'+/');
  SELECT * FROM t5c WHERE (c0 LIKE '+/');
} {+/}

# 2019-02-27
# Verify that the LIKE optimization works with an ESCAPE clause when
# using PRAGMA case_sensitive_like=ON.
#
ifcapable !icu {
do_execsql_test like3-6.100 {