/ Check-in [6ae4b8c5]
Login

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

Overview
Comment:Enable the LIKE optimization when the ESCAPE keyword is present and the PRAGMA case_sensitive_like pragma is ON.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 6ae4b8c525f446ddb0e0bdd5e4e01e06e2ab975e3effa42fce6a1102b3a39830
User & Date: drh 2019-02-27 23:05:14
Context
2019-02-28
13:21
Add an "|| CORRUPT_DB" term to an assert() statement in the btree defragmenter. check-in: 6bfd8655 user: drh tags: trunk
2019-02-27
23:05
Enable the LIKE optimization when the ESCAPE keyword is present and the PRAGMA case_sensitive_like pragma is ON. check-in: 6ae4b8c5 user: drh tags: trunk
19:59
Fix the readfile() UDF so that it returns an empty BLOB, not an OOM error, when reading an empty file. check-in: 0edad533 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/func.c.

  1801   1801   ** Set the LIKEOPT flag on the 2-argument function with the given name.
  1802   1802   */
  1803   1803   static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
  1804   1804     FuncDef *pDef;
  1805   1805     pDef = sqlite3FindFunction(db, zName, 2, SQLITE_UTF8, 0);
  1806   1806     if( ALWAYS(pDef) ){
  1807   1807       pDef->funcFlags |= flagVal;
         1808  +  }
         1809  +  pDef = sqlite3FindFunction(db, zName, 3, SQLITE_UTF8, 0);
         1810  +  if( pDef ){
         1811  +    pDef->funcFlags |= flagVal;
  1808   1812     }
  1809   1813   }
  1810   1814   
  1811   1815   /*
  1812   1816   ** Register the built-in LIKE and GLOB functions.  The caseSensitive
  1813   1817   ** parameter determines whether or not the LIKE operator is case
  1814   1818   ** sensitive.  GLOB is always case sensitive.

Changes to test/like3.test.

   173    173   } {/abc}
   174    174   do_eqp_test like3-5.211 {
   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  +
          181  +# 2019-02-27
          182  +# Verify that the LIKE optimization works with an ESCAPE clause when
          183  +# using PRAGMA case_sensitive_like=ON.
          184  +#
          185  +do_execsql_test like3-6.100 {
          186  +  DROP TABLE IF EXISTS t1;
          187  +  CREATE TABLE t1(path TEXT COLLATE nocase PRIMARY KEY,a,b,c) WITHOUT ROWID;
          188  +}
          189  +do_eqp_test like3-6.110 {
          190  +  SELECT * FROM t1 WHERE path LIKE 'a%';
          191  +} {
          192  +  QUERY PLAN
          193  +  `--SEARCH TABLE t1 USING PRIMARY KEY (path>? AND path<?)
          194  +}
          195  +do_eqp_test like3-6.120 {
          196  +  SELECT * FROM t1 WHERE path LIKE 'a%' ESCAPE '_';
          197  +} {
          198  +  QUERY PLAN
          199  +  `--SEARCH TABLE t1 USING PRIMARY KEY (path>? AND path<?)
          200  +}
          201  +do_execsql_test like3-6.200 {
          202  +  DROP TABLE IF EXISTS t2;
          203  +  CREATE TABLE t2(path TEXT,x,y,z);
          204  +  CREATE INDEX t2path ON t2(path COLLATE nocase);
          205  +  CREATE INDEX t2path2 ON t2(path);
          206  +}
          207  +do_eqp_test like3-6.210 {
          208  +  SELECT * FROM t2 WHERE path LIKE 'a%';
          209  +} {
          210  +  QUERY PLAN
          211  +  `--SEARCH TABLE t2 USING INDEX t2path (path>? AND path<?)
          212  +}
          213  +do_eqp_test like3-6.220 {
          214  +  SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '_';
          215  +} {
          216  +  QUERY PLAN
          217  +  `--SEARCH TABLE t2 USING INDEX t2path (path>? AND path<?)
          218  +}
          219  +db eval {PRAGMA case_sensitive_like=ON}
          220  +do_eqp_test like3-6.230 {
          221  +  SELECT * FROM t2 WHERE path LIKE 'a%';
          222  +} {
          223  +  QUERY PLAN
          224  +  `--SEARCH TABLE t2 USING INDEX t2path2 (path>? AND path<?)
          225  +}
          226  +do_eqp_test like3-6.240 {
          227  +  SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '_';
          228  +} {
          229  +  QUERY PLAN
          230  +  `--SEARCH TABLE t2 USING INDEX t2path2 (path>? AND path<?)
          231  +}
   180    232   
   181    233   finish_test