/ Check-in [6fe0367f]
Login

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

Overview
Comment:Do not attempt the LIKE optimization for non-text columns and a pattern prefix of "-". Ticket [0f0428096f17252a]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 6fe0367f9a337b7c62886b7771f3ce0642faa13f4e4f3d9a0c848abbab514cd0
User & Date: drh 2019-09-16 18:19:41
Context
2019-09-16
20:16
Improved type information display when tracing VDBE execution. check-in: ee83d8e3 user: drh tags: trunk
18:19
Do not attempt the LIKE optimization for non-text columns and a pattern prefix of "-". Ticket [0f0428096f17252a] check-in: 6fe0367f user: drh tags: trunk
15:15
Add recent interesting cases from dbsqlfuzz. check-in: f06ef3d7 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

   280    280           **
   281    281           ** Getting this right has been a persistent source of bugs in the
   282    282           ** LIKE optimization.  See, for example:
   283    283           **    2018-09-10 https://sqlite.org/src/info/c94369cae9b561b1
   284    284           **    2019-05-02 https://sqlite.org/src/info/b043a54c3de54b28
   285    285           **    2019-06-10 https://sqlite.org/src/info/fd76310a5e843e07
   286    286           **    2019-06-14 https://sqlite.org/src/info/ce8717f0885af975
          287  +        **    2019-09-03 https://sqlite.org/src/info/0f0428096f17252a
   287    288           */
   288    289           if( pLeft->op!=TK_COLUMN 
   289    290            || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
   290    291            || IsVirtual(pLeft->y.pTab)  /* Value might be numeric */
   291    292           ){
   292    293             int isNum;
   293    294             double rDummy;
   294    295             isNum = sqlite3AtoF(zNew, &rDummy, iTo, SQLITE_UTF8);
   295    296             if( isNum<=0 ){
   296         -            zNew[iTo-1]++;
   297         -            isNum = sqlite3AtoF(zNew, &rDummy, iTo, SQLITE_UTF8);
   298         -            zNew[iTo-1]--;
          297  +            if( iTo==1 && zNew[0]=='-' ){
          298  +              isNum = +1;
          299  +            }else{
          300  +              zNew[iTo-1]++;
          301  +              isNum = sqlite3AtoF(zNew, &rDummy, iTo, SQLITE_UTF8);
          302  +              zNew[iTo-1]--;
          303  +            }
   299    304             }
   300    305             if( isNum>0 ){
   301    306               sqlite3ExprDelete(db, pPrefix);
   302    307               sqlite3ValueFree(pVal);
   303    308               return 0;
   304    309             }
   305    310           }

Changes to test/like3.test.

   202    202   # Ticket https://www.sqlite.org/src/info/ce8717f0885af975
   203    203   do_execsql_test like3-5.410 {
   204    204     DROP TABLE IF EXISTS t0;
   205    205     CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
   206    206     INSERT INTO t0(c0) VALUES ('.1%');
   207    207     SELECT * FROM t0 WHERE t0.c0 LIKE '.1%';
   208    208   } {.1%}
          209  +
          210  +# 2019-09-03
          211  +# Ticket https://www.sqlite.org/src/info/0f0428096f
          212  +do_execsql_test like3-5.420 {
          213  +  DROP TABLE IF EXISTS t0;
          214  +  CREATE TABLE t0(c0 UNIQUE);
          215  +  INSERT INTO t0(c0) VALUES(-1);
          216  +  SELECT * FROM t0 WHERE t0.c0 GLOB '-*';
          217  +} {-1}
          218  +do_execsql_test like3-5.421 {
          219  +  SELECT t0.c0 GLOB '-*' FROM t0;
          220  +} {1}
          221  +
   209    222   
   210    223   
   211    224   # 2019-02-27
   212    225   # Verify that the LIKE optimization works with an ESCAPE clause when
   213    226   # using PRAGMA case_sensitive_like=ON.
   214    227   #
   215    228   ifcapable !icu {