/ Check-in [16bd5478]
Login

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

Overview
Comment:Always use available indices to optimize LIKE operators even if the pattern of the LIKE operator has a COLLATE modifier. This fixes an ineffiency that was introduced into 3.7.15 by check-in [8542e6180d4] on 2012-12-08.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 16bd54783a3f5531c55564ddefdada657c078eb0
User & Date: drh 2014-01-16 15:31:41
Context
2014-01-17
15:15
Add support for common table expressions (WITH clauses). check-in: 0171e3bb user: dan tags: trunk
2014-01-16
15:31
Always use available indices to optimize LIKE operators even if the pattern of the LIKE operator has a COLLATE modifier. This fixes an ineffiency that was introduced into 3.7.15 by check-in [8542e6180d4] on 2012-12-08. check-in: 16bd5478 user: drh tags: trunk
2014-01-14
10:17
Fix harmless compiler warning in LEMON. check-in: f61a7058 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   663    663     ){
   664    664       /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must
   665    665       ** be the name of an indexed column with TEXT affinity. */
   666    666       return 0;
   667    667     }
   668    668     assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */
   669    669   
   670         -  pRight = pList->a[0].pExpr;
          670  +  pRight = sqlite3ExprSkipCollate(pList->a[0].pExpr);
   671    671     op = pRight->op;
   672    672     if( op==TK_VARIABLE ){
   673    673       Vdbe *pReprepare = pParse->pReprepare;
   674    674       int iCol = pRight->iColumn;
   675    675       pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_NONE);
   676    676       if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
   677    677         z = (char *)sqlite3_value_text(pVal);

Changes to test/like.test.

   888    888     }
   889    889   } {abc abcd ABC ABCD sort {} t11cnc}
   890    890   do_test like-11.10 {
   891    891     queryplan {
   892    892       SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a;
   893    893     }
   894    894   } {abc abcd sort {} t11cb}
          895  +
          896  +# A COLLATE clause on the pattern does not change the result of a
          897  +# LIKE operator.
          898  +#
          899  +do_execsql_test like-12.1 {
          900  +  CREATE TABLE t12nc(id INTEGER, x TEXT UNIQUE COLLATE nocase);
          901  +  INSERT INTO t12nc VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF');
          902  +  CREATE TABLE t12b(id INTEGER, x TEXT UNIQUE COLLATE binary);
          903  +  INSERT INTO t12b VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF');
          904  +  SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;
          905  +} {1 3}
          906  +do_execsql_test like-12.2 {
          907  +  SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;
          908  +} {1 3}
          909  +do_execsql_test like-12.3 {
          910  +  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
          911  +} {1 3}
          912  +do_execsql_test like-12.4 {
          913  +  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
          914  +} {1 3}
          915  +do_execsql_test like-12.5 {
          916  +  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
          917  +} {1 3}
          918  +do_execsql_test like-12.6 {
          919  +  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
          920  +} {1 3}
          921  +
          922  +# Adding a COLLATE clause to the pattern of a LIKE operator does nothing
          923  +# to change the suitability of using an index to satisfy that LIKE
          924  +# operator.
          925  +#
          926  +do_execsql_test like-12.11 {
          927  +  EXPLAIN QUERY PLAN
          928  +  SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;
          929  +} {/SEARCH/}
          930  +do_execsql_test like-12.12 {
          931  +  EXPLAIN QUERY PLAN
          932  +  SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;
          933  +} {/SCAN/}
          934  +do_execsql_test like-12.13 {
          935  +  EXPLAIN QUERY PLAN
          936  +  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
          937  +} {/SEARCH/}
          938  +do_execsql_test like-12.14 {
          939  +  EXPLAIN QUERY PLAN
          940  +  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
          941  +} {/SCAN/}
          942  +do_execsql_test like-12.15 {
          943  +  EXPLAIN QUERY PLAN
          944  +  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
          945  +} {/SEARCH/}
          946  +do_execsql_test like-12.16 {
          947  +  EXPLAIN QUERY PLAN
          948  +  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
          949  +} {/SCAN/}
   895    950   
   896    951   
   897    952   finish_test