/ Check-in [74020110]
Login

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

Overview
Comment:Fix another pattern for which the LIKE optimization does not work for a non-TEXT affinity. Case found by Manuel Rigger.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 740201107ae802c12b678e388ea524db01ad0eb70601c78490ad63eae0fe6cf1
User & Date: drh 2019-05-08 19:55:24
Context
2019-05-08
21:14
Add another test case to cover a previously uncovered branch in the RBU module. check-in: 30392985 user: dan tags: trunk
19:55
Fix another pattern for which the LIKE optimization does not work for a non-TEXT affinity. Case found by Manuel Rigger. check-in: 74020110 user: drh tags: trunk
19:32
Simplification to the logic underlying PRAGMA case_sensitive_like. check-in: ef0015fd user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

   274    274           ** The RHS pattern must not be '/%' because the termination condition
   275    275           ** will then become "x<'0'" and if the affinity is numeric, will then
   276    276           ** be converted into "x<0", which is incorrect.
   277    277           */
   278    278           if( sqlite3Isdigit(zNew[0])
   279    279            || zNew[0]=='-'
   280    280            || zNew[0]=='+'
   281         -         || (zNew[0]+1=='0' && iTo==1)
          281  +         || (iTo>0 && zNew[iTo-1]=='0'-1)
   282    282           ){
   283    283             if( pLeft->op!=TK_COLUMN 
   284    284              || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
   285    285              || IsVirtual(pLeft->y.pTab)  /* Value might be numeric */
   286    286             ){
   287    287               sqlite3ExprDelete(db, pPrefix);
   288    288               sqlite3ValueFree(pVal);

Changes to test/like3.test.

   175    175     SELECT x FROM t5b WHERE x GLOB '/a*';
   176    176   } {
   177    177     QUERY PLAN
   178    178     `--SEARCH TABLE t5b USING COVERING INDEX sqlite_autoindex_t5b_1 (x>? AND x<?)
   179    179   }
   180    180   
   181    181   # 2019-05-01
   182         -# another case of the above reported on the mailing list by Manual Rigger.
          182  +# another case of the above reported on the mailing list by Manuel Rigger.
   183    183   #
   184    184   do_execsql_test like3-5.300 {
   185    185     CREATE TABLE t5c (c0 REAL);
   186    186     CREATE INDEX t5c_0 ON t5c(c0 COLLATE NOCASE);
   187    187     INSERT INTO t5c(rowid, c0) VALUES (99,'+/');
   188    188     SELECT * FROM t5c WHERE (c0 LIKE '+/');
   189    189   } {+/}
          190  +
          191  +# 2019-05-08
          192  +# Yet another case for the above from Manuel Rigger.
          193  +#
          194  +do_execsql_test like3-5.400 {
          195  +  DROP TABLE IF EXISTS t0;
          196  +  CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
          197  +  INSERT INTO t0(c0) VALUES ('./');
          198  +  SELECT * FROM t0 WHERE t0.c0 LIKE './';
          199  +} {./}
          200  +
   190    201   
   191    202   # 2019-02-27
   192    203   # Verify that the LIKE optimization works with an ESCAPE clause when
   193    204   # using PRAGMA case_sensitive_like=ON.
   194    205   #
   195    206   ifcapable !icu {
   196    207   do_execsql_test like3-6.100 {