Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not attempt the LIKE optimization on a column with numeric affinity if the rhs of the operator begins with whitespace. Fix for ticket [fd76310a5e]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
94b58ab059cba9771e75f16d1460f313 |
| User & Date: | dan 2019-06-10 13:46:42 |
References
|
2019-06-10
| ||
| 13:52 | • Fixed ticket [b1d8c793]: LIKE malfunctions for INT PRIMARY KEY COLLATE NOCASE column plus 6 other changes (artifact: 974e3f5c user: dan) | |
Context
|
2019-06-10
| ||
| 15:34 | Handle renaming a column or table when the schema contains a (meaningless) index on the constant expression ('text' IN ()) or ('text' NOT IN()). Fix for [fd76310a]. (check-in: 567b1309 user: dan tags: trunk) | |
| 13:46 | Do not attempt the LIKE optimization on a column with numeric affinity if the rhs of the operator begins with whitespace. Fix for ticket [fd76310a5e]. (check-in: 94b58ab0 user: dan tags: trunk) | |
|
2019-06-07
| ||
| 22:51 | Remove code in the round() SQL function that became unreachable due to the optimization of check-in [e95138f5f4febde5] (check-in: b141bae3 user: drh tags: trunk) | |
Changes
Changes to src/whereexpr.c.
| ︙ | ︙ | |||
260 261 262 263 264 265 266 |
for(iFrom=iTo=0; iFrom<cnt; iFrom++){
if( zNew[iFrom]==wc[3] ) iFrom++;
zNew[iTo++] = zNew[iFrom];
}
zNew[iTo] = 0;
assert( iTo>0 );
| | | | | | | > | 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 |
for(iFrom=iTo=0; iFrom<cnt; iFrom++){
if( zNew[iFrom]==wc[3] ) iFrom++;
zNew[iTo++] = zNew[iFrom];
}
zNew[iTo] = 0;
assert( iTo>0 );
/* If the RHS begins with a digit, a +/- sign or whitespace, then the
** LHS must be an ordinary column (not a virtual table column) with
** TEXT affinity. Otherwise the LHS might be numeric and "lhs >= rhs"
** would be false even though "lhs LIKE rhs" is true. But if the RHS
** does not start with a digit or +/-, then "lhs LIKE rhs" will always
** be false if the LHS is numeric and so the optimization still works.
**
** 2018-09-10 ticket c94369cae9b561b1f996d0054bfab11389f9d033
** The RHS pattern must not be '/%' because the termination condition
** will then become "x<'0'" and if the affinity is numeric, will then
** be converted into "x<0", which is incorrect.
*/
if( sqlite3Isdigit(zNew[0])
|| sqlite3Isspace(zNew[0])
|| zNew[0]=='-'
|| zNew[0]=='+'
|| zNew[iTo-1]=='0'-1
){
if( pLeft->op!=TK_COLUMN
|| sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT
|| IsVirtual(pLeft->y.pTab) /* Value might be numeric */
|
| ︙ | ︙ |
Changes to test/like.test.
| ︙ | ︙ | |||
13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
# in particular the optimizations that occur to help those operators
# run faster.
#
# $Id: like.test,v 1.13 2009/06/07 23:45:11 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Create some sample data to work with.
#
do_test like-1.0 {
execsql {
CREATE TABLE t1(x TEXT);
}
| > | 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# in particular the optimizations that occur to help those operators
# run faster.
#
# $Id: like.test,v 1.13 2009/06/07 23:45:11 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix like
# Create some sample data to work with.
#
do_test like-1.0 {
execsql {
CREATE TABLE t1(x TEXT);
}
|
| ︙ | ︙ | |||
1090 1091 1092 1093 1094 1095 1096 1097 1098 |
SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
} {22}
do_execsql_test like-15.121 {
EXPLAIN QUERY PLAN
SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
} {/SEARCH/}
}
finish_test
| > > > > > > > > > > > > > > > > > > | 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 |
SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
} {22}
do_execsql_test like-15.121 {
EXPLAIN QUERY PLAN
SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
} {/SEARCH/}
}
#-------------------------------------------------------------------------
# Tests for ticket [b1d8c79314].
#
reset_db
do_execsql_test 16.0 {
CREATE TABLE t1(a INTEGER COLLATE NOCASE);
CREATE INDEX i1 ON t1(a);
INSERT INTO t1 VALUES(' 1x');
INSERT INTO t1 VALUES(' 1-');
}
do_execsql_test 16.1 {
SELECT * FROM t1 WHERE a LIKE ' 1%';
} {{ 1x} { 1-}}
do_execsql_test 16.2 {
SELECT * FROM t1 WHERE a LIKE ' 1-';
} {{ 1-}}
finish_test
|