Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Refactor the LIKE optimization decision logic so that it uses sqlite3AtoF() on both boundary keys to determine if the optimization can be used when the LHS is something that might not have TEXT affinity. Ticket [ce8717f0885af975]. See also [c94369cae9b561b1], [b043a54c3de54b28], [fd76310a5e843e07], and [158290c0abafde67]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
b4a9e09e60213ccff925d09f0b6e549e |
User & Date: | drh 2019-06-14 12:28:21 |
Context
2019-06-14
| ||
13:24 | Fix the implementation of the built-in RTRIM collating sequence so that it works for control characters at the end of the string. Ticket [f1580ba1b574e9e9] (check-in: 86fa0087 user: drh tags: trunk) | |
12:28 | Refactor the LIKE optimization decision logic so that it uses sqlite3AtoF() on both boundary keys to determine if the optimization can be used when the LHS is something that might not have TEXT affinity. Ticket [ce8717f0885af975]. See also [c94369cae9b561b1], [b043a54c3de54b28], [fd76310a5e843e07], and [158290c0abafde67]. (check-in: b4a9e09e user: drh tags: trunk) | |
2019-06-13
| ||
16:14 | Add a new requirement mark on the ctime.c code. (check-in: c4b40568 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 288 289 290 291 292 293 294 295 296 297 298 | 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 LHS is not an ordinary column with TEXT affinity, then the ** pattern prefix boundaries (both the start and end boundaries) must ** not look like a number. Otherwise the pattern might be treated as ** a number, which will invalidate the LIKE optimization. ** ** Getting this right has been a persistent source of bugs in the ** LIKE optimization. See, for example: ** 2018-09-10 https://sqlite.org/src/info/c94369cae9b561b1 ** 2019-05-02 https://sqlite.org/src/info/b043a54c3de54b28 ** 2019-06-10 https://sqlite.org/src/info/fd76310a5e843e07 ** 2019-06-14 https://sqlite.org/src/info/ce8717f0885af975 */ if( pLeft->op!=TK_COLUMN || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT || IsVirtual(pLeft->y.pTab) /* Value might be numeric */ ){ int isNum; double rDummy; isNum = sqlite3AtoF(zNew, &rDummy, iTo, SQLITE_UTF8); if( isNum<=0 ){ zNew[iTo-1]++; isNum = sqlite3AtoF(zNew, &rDummy, iTo, SQLITE_UTF8); zNew[iTo-1]--; } if( isNum>0 ){ sqlite3ExprDelete(db, pPrefix); sqlite3ValueFree(pVal); return 0; } } } *ppPrefix = pPrefix; |
︙ | ︙ |
Changes to test/like3.test.
︙ | ︙ | |||
193 194 195 196 197 198 199 200 201 202 203 204 205 206 | # do_execsql_test like3-5.400 { DROP TABLE IF EXISTS t0; CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE); INSERT INTO t0(c0) VALUES ('./'); SELECT * FROM t0 WHERE t0.c0 LIKE './'; } {./} # 2019-02-27 # Verify that the LIKE optimization works with an ESCAPE clause when # using PRAGMA case_sensitive_like=ON. # ifcapable !icu { | > > > > > > > > > | 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 | # do_execsql_test like3-5.400 { DROP TABLE IF EXISTS t0; CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE); INSERT INTO t0(c0) VALUES ('./'); SELECT * FROM t0 WHERE t0.c0 LIKE './'; } {./} # 2019-06-14 # Ticket https://www.sqlite.org/src/info/ce8717f0885af975 do_execsql_test like3-5.410 { DROP TABLE IF EXISTS t0; CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE); INSERT INTO t0(c0) VALUES ('.1%'); SELECT * FROM t0 WHERE t0.c0 LIKE '.1%'; } {.1%} # 2019-02-27 # Verify that the LIKE optimization works with an ESCAPE clause when # using PRAGMA case_sensitive_like=ON. # ifcapable !icu { |
︙ | ︙ |
Changes to test/tkt-78e04e52ea.test.
︙ | ︙ | |||
37 38 39 40 41 42 43 | } {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0} do_test tkt-78e04-1.3 { execsql { CREATE INDEX i1 ON ""("" COLLATE nocase); } } {} do_test tkt-78e04-1.4 { | | | 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | } {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0} do_test tkt-78e04-1.3 { execsql { CREATE INDEX i1 ON ""("" COLLATE nocase); } } {} do_test tkt-78e04-1.4 { db eval {EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE '1e5%';} } {/*SCAN TABLE USING COVERING INDEX i1*/} do_test tkt-78e04-1.5 { execsql { DROP TABLE ""; SELECT name FROM sqlite_master; } } {t2} |
︙ | ︙ |
Changes to test/vtab1.test.
︙ | ︙ | |||
1307 1308 1309 1310 1311 1312 1313 | 1.1 "SELECT a FROM e6 WHERE b>'8James'" {4 2 6 1 5} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b > ?} 8James} 1.2 "SELECT a FROM e6 WHERE b>='8' AND b<'9'" {3 4} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ?} 8 9} 1.3 "SELECT a FROM e6 WHERE b LIKE '8J%'" {3 4} | | | > > > | | | > > > | 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 | 1.1 "SELECT a FROM e6 WHERE b>'8James'" {4 2 6 1 5} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b > ?} 8James} 1.2 "SELECT a FROM e6 WHERE b>='8' AND b<'9'" {3 4} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ?} 8 9} 1.3 "SELECT a FROM e6 WHERE b LIKE '8J%'" {3 4} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8J 8k 8J%} 1.4 "SELECT a FROM e6 WHERE b LIKE '8j%'" {3 4} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8J 8k 8j%} 1.5 "SELECT a FROM e6 WHERE b LIKE '8%'" {3 4} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8%} } { set echo_module {} do_execsql_test 18.$tn.1 $sql $res do_test 18.$tn.2 { lrange $::echo_module 2 end } $filter } do_execsql_test 18.2.0 { PRAGMA case_sensitive_like = ON } foreach {tn sql res filter} { 2.1 "SELECT a FROM e6 WHERE b LIKE '8%'" {3 4} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8%} 2.2 "SELECT a FROM e6 WHERE b LIKE '8j%'" {} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8j 8k 8j%} 2.3 "SELECT a FROM e6 WHERE b LIKE '8J%'" {3 4} {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8J 8K 8J%} } { set echo_module {} do_execsql_test 18.$tn.1 $sql $res do_test 18.$tn.2 { lrange $::echo_module 2 end } $filter } do_execsql_test 18.2.x { PRAGMA case_sensitive_like = OFF } |
︙ | ︙ |
Changes to test/vtabH.test.
︙ | ︙ | |||
28 29 30 31 32 33 34 | CREATE TABLE t6(a, b TEXT); CREATE INDEX i6 ON t6(b, a); CREATE VIRTUAL TABLE e6 USING echo(t6); } foreach {tn sql expect} { 1 "SELECT * FROM e6 WHERE b LIKE '8abc'" { | > | > | > > > > > > > > > > > > | | 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | CREATE TABLE t6(a, b TEXT); CREATE INDEX i6 ON t6(b, a); CREATE VIRTUAL TABLE e6 USING echo(t6); } foreach {tn sql expect} { 1 "SELECT * FROM e6 WHERE b LIKE '8abc'" { xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b like ?} 8ABC 8abd 8abc } 2 "SELECT * FROM e6 WHERE b GLOB '8abc'" { xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b glob ?} xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ? AND b glob ?} 8abc 8abd 8abc } 3 "SELECT * FROM e6 WHERE b LIKE '8e/'" { xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b like ?} xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8e/ } 4 "SELECT * FROM e6 WHERE b GLOB '8e/'" { xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b glob ?} xFilter {SELECT rowid, a, b FROM 't6' WHERE b glob ?} 8e/ } } { do_test 1.$tn { set echo_module {} execsql $sql set ::echo_module } [list {*}$expect] |
︙ | ︙ |