Index: src/whereexpr.c ================================================================== --- src/whereexpr.c +++ src/whereexpr.c @@ -262,32 +262,35 @@ 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. + /* 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. ** - ** 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. + ** 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( 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 */ ){ - 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; } } Index: test/like3.test ================================================================== --- test/like3.test +++ test/like3.test @@ -195,10 +195,19 @@ 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. Index: test/tkt-78e04e52ea.test ================================================================== --- test/tkt-78e04e52ea.test +++ test/tkt-78e04e52ea.test @@ -39,11 +39,11 @@ execsql { CREATE INDEX i1 ON ""("" COLLATE nocase); } } {} do_test tkt-78e04-1.4 { - db eval {EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE '1abc%';} + 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; Index: test/vtab1.test ================================================================== --- test/vtab1.test +++ test/vtab1.test @@ -1309,27 +1309,33 @@ 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 like ?} 8J%} + {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 like ?} 8j%} + {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 '8J%'" {3 4} - {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} 8J%} + 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 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 } Index: test/vtabH.test ================================================================== --- test/vtabH.test +++ test/vtabH.test @@ -30,17 +30,31 @@ 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 like ?} - xFilter {SELECT rowid, a, b FROM 't6' 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 ?} 8abc + xFilter {SELECT rowid, a, b FROM 't6' WHERE b glob ?} 8e/ } } { do_test 1.$tn { set echo_module {} execsql $sql