/ Check-in [b4a9e09e]
Login

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

Overview
Comment: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].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b4a9e09e60213ccff925d09f0b6e549e2a3e3862856c710f108779e2867dec76
User & Date: drh 2019-06-14 12:28:21
Context
2019-06-14
13:24
Fix the implementation of the built-in RTRIM collating sequence so that it works for control characters at the end of the string. Ticket [f1580ba1b574e9e9] check-in: 86fa0087 user: drh tags: trunk
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
2019-06-13
16:14
Add a new requirement mark on the ctime.c code. check-in: c4b40568 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

   260    260           for(iFrom=iTo=0; iFrom<cnt; iFrom++){
   261    261             if( zNew[iFrom]==wc[3] ) iFrom++;
   262    262             zNew[iTo++] = zNew[iFrom];
   263    263           }
   264    264           zNew[iTo] = 0;
   265    265           assert( iTo>0 );
   266    266   
   267         -        /* If the RHS begins with a digit, a +/- sign or whitespace, then the
   268         -        ** LHS must be an ordinary column (not a virtual table column) with
   269         -        ** TEXT affinity. Otherwise the LHS might be numeric and "lhs >= rhs"
   270         -        ** would be false even though "lhs LIKE rhs" is true.  But if the RHS
   271         -        ** does not start with a digit or +/-, then "lhs LIKE rhs" will always
   272         -        ** be false if the LHS is numeric and so the optimization still works.
          267  +        /* If the LHS is not an ordinary column with TEXT affinity, then the
          268  +        ** pattern prefix boundaries (both the start and end boundaries) must
          269  +        ** not look like a number.  Otherwise the pattern might be treated as
          270  +        ** a number, which will invalidate the LIKE optimization.
   273    271           **
   274         -        ** 2018-09-10 ticket c94369cae9b561b1f996d0054bfab11389f9d033
   275         -        ** The RHS pattern must not be '/%' because the termination condition
   276         -        ** will then become "x<'0'" and if the affinity is numeric, will then
   277         -        ** be converted into "x<0", which is incorrect.
          272  +        ** Getting this right has been a persistent source of bugs in the
          273  +        ** LIKE optimization.  See, for example:
          274  +        **    2018-09-10 https://sqlite.org/src/info/c94369cae9b561b1
          275  +        **    2019-05-02 https://sqlite.org/src/info/b043a54c3de54b28
          276  +        **    2019-06-10 https://sqlite.org/src/info/fd76310a5e843e07
          277  +        **    2019-06-14 https://sqlite.org/src/info/ce8717f0885af975
   278    278           */
   279         -        if( sqlite3Isdigit(zNew[0])
   280         -         || sqlite3Isspace(zNew[0])
   281         -         || zNew[0]=='-'
   282         -         || zNew[0]=='+'
   283         -         || zNew[iTo-1]=='0'-1
          279  +        if( pLeft->op!=TK_COLUMN 
          280  +         || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
          281  +         || IsVirtual(pLeft->y.pTab)  /* Value might be numeric */
   284    282           ){
   285         -          if( pLeft->op!=TK_COLUMN 
   286         -           || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
   287         -           || IsVirtual(pLeft->y.pTab)  /* Value might be numeric */
   288         -          ){
          283  +          int isNum;
          284  +          double rDummy;
          285  +          isNum = sqlite3AtoF(zNew, &rDummy, iTo, SQLITE_UTF8);
          286  +          if( isNum<=0 ){
          287  +            zNew[iTo-1]++;
          288  +            isNum = sqlite3AtoF(zNew, &rDummy, iTo, SQLITE_UTF8);
          289  +            zNew[iTo-1]--;
          290  +          }
          291  +          if( isNum>0 ){
   289    292               sqlite3ExprDelete(db, pPrefix);
   290    293               sqlite3ValueFree(pVal);
   291    294               return 0;
   292    295             }
   293    296           }
   294    297         }
   295    298         *ppPrefix = pPrefix;

Changes to test/like3.test.

   193    193   #
   194    194   do_execsql_test like3-5.400 {
   195    195     DROP TABLE IF EXISTS t0;
   196    196     CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
   197    197     INSERT INTO t0(c0) VALUES ('./');
   198    198     SELECT * FROM t0 WHERE t0.c0 LIKE './';
   199    199   } {./}
          200  +
          201  +# 2019-06-14
          202  +# Ticket https://www.sqlite.org/src/info/ce8717f0885af975
          203  +do_execsql_test like3-5.410 {
          204  +  DROP TABLE IF EXISTS t0;
          205  +  CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
          206  +  INSERT INTO t0(c0) VALUES ('.1%');
          207  +  SELECT * FROM t0 WHERE t0.c0 LIKE '.1%';
          208  +} {.1%}
   200    209   
   201    210   
   202    211   # 2019-02-27
   203    212   # Verify that the LIKE optimization works with an ESCAPE clause when
   204    213   # using PRAGMA case_sensitive_like=ON.
   205    214   #
   206    215   ifcapable !icu {

Changes to test/tkt-78e04e52ea.test.

    37     37   } {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0}
    38     38   do_test tkt-78e04-1.3 {
    39     39     execsql {
    40     40       CREATE INDEX i1 ON ""("" COLLATE nocase);
    41     41     }
    42     42   } {}
    43     43   do_test tkt-78e04-1.4 {
    44         - db eval {EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE '1abc%';}
           44  + db eval {EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE '1e5%';}
    45     45   } {/*SCAN TABLE  USING COVERING INDEX i1*/}
    46     46   do_test tkt-78e04-1.5 {
    47     47     execsql {
    48     48       DROP TABLE "";
    49     49       SELECT name FROM sqlite_master;
    50     50     }
    51     51   } {t2}

Changes to test/vtab1.test.

  1307   1307     1.1 "SELECT a FROM e6 WHERE b>'8James'" {4 2 6 1 5}
  1308   1308       {xFilter {SELECT rowid, a, b FROM 't6' WHERE b > ?} 8James}
  1309   1309   
  1310   1310     1.2 "SELECT a FROM e6 WHERE b>='8' AND b<'9'" {3 4}
  1311   1311       {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ?} 8 9}
  1312   1312   
  1313   1313     1.3 "SELECT a FROM e6 WHERE b LIKE '8J%'" {3 4}
  1314         -    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8J%}
         1314  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8J 8k 8J%}
  1315   1315   
  1316   1316     1.4 "SELECT a FROM e6 WHERE b LIKE '8j%'" {3 4}
  1317         -    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8j%}
         1317  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8J 8k 8j%}
         1318  +
         1319  +  1.5 "SELECT a FROM e6 WHERE b LIKE '8%'" {3 4}
         1320  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8%}
  1318   1321   } {
  1319   1322     set echo_module {}
  1320   1323     do_execsql_test 18.$tn.1 $sql $res
  1321   1324     do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
  1322   1325   }
  1323   1326   
  1324   1327   do_execsql_test 18.2.0 {  PRAGMA case_sensitive_like = ON }
  1325   1328   foreach {tn sql res filter} {
  1326         -  2.1 "SELECT a FROM e6 WHERE b LIKE '8J%'" {3 4}
  1327         -    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8J%}
         1329  +  2.1 "SELECT a FROM e6 WHERE b LIKE '8%'" {3 4}
         1330  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8%}
  1328   1331   
  1329   1332     2.2 "SELECT a FROM e6 WHERE b LIKE '8j%'" {}
  1330         -    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8j%}
         1333  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8j 8k 8j%}
         1334  +
         1335  +  2.3 "SELECT a FROM e6 WHERE b LIKE '8J%'" {3 4}
         1336  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8J 8K 8J%}
  1331   1337   } {
  1332   1338     set echo_module {}
  1333   1339     do_execsql_test 18.$tn.1 $sql $res
  1334   1340     do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
  1335   1341   }
  1336   1342   do_execsql_test 18.2.x {  PRAGMA case_sensitive_like = OFF }
  1337   1343   

Changes to test/vtabH.test.

    28     28     CREATE TABLE t6(a, b TEXT);
    29     29     CREATE INDEX i6 ON t6(b, a);
    30     30     CREATE VIRTUAL TABLE e6 USING echo(t6);
    31     31   }
    32     32   
    33     33   foreach {tn sql expect} {
    34     34     1 "SELECT * FROM e6 WHERE b LIKE '8abc'" {
    35         -    xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b like ?}
    36         -    xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8abc
           35  +    xBestIndex 
           36  +       {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?}
           37  +    xFilter
           38  +       {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?}
           39  +       8ABC 8abd 8abc
    37     40     }
    38     41   
    39     42     2 "SELECT * FROM e6 WHERE b GLOB '8abc'" {
           43  +     xBestIndex
           44  +       {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b glob ?}
           45  +     xFilter
           46  +       {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b glob ?}
           47  +       8abc 8abd 8abc
           48  +  }
           49  +  3 "SELECT * FROM e6 WHERE b LIKE '8e/'" {
           50  +    xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b like ?}
           51  +    xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8e/
           52  +  }
           53  +  4 "SELECT * FROM e6 WHERE b GLOB '8e/'" {
    40     54       xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b glob ?}
    41         -    xFilter {SELECT rowid, a, b FROM 't6' WHERE b glob ?} 8abc
           55  +    xFilter {SELECT rowid, a, b FROM 't6' WHERE b glob ?} 8e/
    42     56     }
    43     57   } {
    44     58     do_test 1.$tn {
    45     59       set echo_module {}
    46     60       execsql $sql
    47     61       set ::echo_module
    48     62     } [list {*}$expect]