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: |
16bd54783a3f5531c55564ddefdada65 |
| 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
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
|