/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
  ){
    /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must
    ** be the name of an indexed column with TEXT affinity. */
    return 0;
  }
  assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */

  pRight = pList->a[0].pExpr;
  op = pRight->op;
  if( op==TK_VARIABLE ){
    Vdbe *pReprepare = pParse->pReprepare;
    int iCol = pRight->iColumn;
    pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_NONE);
    if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
      z = (char *)sqlite3_value_text(pVal);







|







663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
  ){
    /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must
    ** be the name of an indexed column with TEXT affinity. */
    return 0;
  }
  assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */

  pRight = sqlite3ExprSkipCollate(pList->a[0].pExpr);
  op = pRight->op;
  if( op==TK_VARIABLE ){
    Vdbe *pReprepare = pParse->pReprepare;
    int iCol = pRight->iColumn;
    pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_NONE);
    if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
      z = (char *)sqlite3_value_text(pVal);

Changes to test/like.test.

888
889
890
891
892
893
894
895


896





















































897
  }
} {abc abcd ABC ABCD sort {} t11cnc}
do_test like-11.10 {
  queryplan {
    SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a;
  }
} {abc abcd sort {} t11cb}

























































finish_test








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
  }
} {abc abcd ABC ABCD sort {} t11cnc}
do_test like-11.10 {
  queryplan {
    SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a;
  }
} {abc abcd sort {} t11cb}

# A COLLATE clause on the pattern does not change the result of a
# LIKE operator.
#
do_execsql_test like-12.1 {
  CREATE TABLE t12nc(id INTEGER, x TEXT UNIQUE COLLATE nocase);
  INSERT INTO t12nc VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF');
  CREATE TABLE t12b(id INTEGER, x TEXT UNIQUE COLLATE binary);
  INSERT INTO t12b VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF');
  SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;
} {1 3}
do_execsql_test like-12.2 {
  SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;
} {1 3}
do_execsql_test like-12.3 {
  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
} {1 3}
do_execsql_test like-12.4 {
  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
} {1 3}
do_execsql_test like-12.5 {
  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
} {1 3}
do_execsql_test like-12.6 {
  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
} {1 3}

# Adding a COLLATE clause to the pattern of a LIKE operator does nothing
# to change the suitability of using an index to satisfy that LIKE
# operator.
#
do_execsql_test like-12.11 {
  EXPLAIN QUERY PLAN
  SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;
} {/SEARCH/}
do_execsql_test like-12.12 {
  EXPLAIN QUERY PLAN
  SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;
} {/SCAN/}
do_execsql_test like-12.13 {
  EXPLAIN QUERY PLAN
  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
} {/SEARCH/}
do_execsql_test like-12.14 {
  EXPLAIN QUERY PLAN
  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
} {/SCAN/}
do_execsql_test like-12.15 {
  EXPLAIN QUERY PLAN
  SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
} {/SEARCH/}
do_execsql_test like-12.16 {
  EXPLAIN QUERY PLAN
  SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
} {/SCAN/}


finish_test