SQLite

Check-in [94b58ab0]
Login

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

Overview
Comment:Do not attempt the LIKE optimization on a column with numeric affinity if the rhs of the operator begins with whitespace. Fix for ticket [fd76310a5e].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 94b58ab059cba9771e75f16d1460f313104a8fb879f9f8805725d362aa58cbcd
User & Date: dan 2019-06-10 13:46:42
References
2019-06-10
13:52 • Fixed ticket [b1d8c793]: LIKE malfunctions for INT PRIMARY KEY COLLATE NOCASE column plus 6 other changes (artifact: 974e3f5c user: dan)
Context
2019-06-10
15:34
Handle renaming a column or table when the schema contains a (meaningless) index on the constant expression ('text' IN ()) or ('text' NOT IN()). Fix for [fd76310a]. (check-in: 567b1309 user: dan tags: trunk)
13:46
Do not attempt the LIKE optimization on a column with numeric affinity if the rhs of the operator begins with whitespace. Fix for ticket [fd76310a5e]. (check-in: 94b58ab0 user: dan tags: trunk)
2019-06-07
22:51
Remove code in the round() SQL function that became unreachable due to the optimization of check-in [e95138f5f4febde5] (check-in: b141bae3 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

︙︙
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

        for(iFrom=iTo=0; iFrom<cnt; iFrom++){
          if( zNew[iFrom]==wc[3] ) iFrom++;
          zNew[iTo++] = zNew[iFrom];
        }
        zNew[iTo] = 0;
        assert( 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[iTo-1]=='0'-1
        ){
          if( pLeft->op!=TK_COLUMN 
           || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
           || IsVirtual(pLeft->y.pTab)  /* Value might be numeric */








|
|
|
|
|
|







>








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

        for(iFrom=iTo=0; iFrom<cnt; iFrom++){
          if( zNew[iFrom]==wc[3] ) iFrom++;
          zNew[iTo++] = zNew[iFrom];
        }
        zNew[iTo] = 0;
        assert( iTo>0 );

        /* If the RHS begins with a digit, a +/- sign or whitespace, 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])
         || sqlite3Isspace(zNew[0])
         || zNew[0]=='-'
         || zNew[0]=='+'
         || zNew[iTo-1]=='0'-1
        ){
          if( pLeft->op!=TK_COLUMN 
           || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
           || IsVirtual(pLeft->y.pTab)  /* Value might be numeric */
︙︙

Changes to test/like.test.

︙︙
13
14
15
16
17
18
19

20
21
22
23
24
25
26

# in particular the optimizations that occur to help those operators
# run faster.
#
# $Id: like.test,v 1.13 2009/06/07 23:45:11 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl


# Create some sample data to work with.
#
do_test like-1.0 {
  execsql {
    CREATE TABLE t1(x TEXT);
  }








>








13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# in particular the optimizations that occur to help those operators
# run faster.
#
# $Id: like.test,v 1.13 2009/06/07 23:45:11 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix like

# Create some sample data to work with.
#
do_test like-1.0 {
  execsql {
    CREATE TABLE t1(x TEXT);
  }
︙︙
1090
1091
1092
1093
1094
1095
1096
1097

















1098


  SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
} {22}
do_execsql_test like-15.121 {
  EXPLAIN QUERY PLAN
  SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
} {/SEARCH/}
}


















finish_test










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

>

1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117

  SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
} {22}
do_execsql_test like-15.121 {
  EXPLAIN QUERY PLAN
  SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
} {/SEARCH/}
}

#-------------------------------------------------------------------------
# Tests for ticket [b1d8c79314].
#
reset_db
do_execsql_test 16.0 {
  CREATE TABLE t1(a INTEGER COLLATE NOCASE);
  CREATE INDEX i1 ON t1(a);
  INSERT INTO t1 VALUES(' 1x');
  INSERT INTO t1 VALUES(' 1-');
}
do_execsql_test 16.1 {
  SELECT * FROM t1 WHERE a LIKE ' 1%';
} {{ 1x} { 1-}}
do_execsql_test 16.2 {
  SELECT * FROM t1 WHERE a LIKE ' 1-';
} {{ 1-}}

finish_test