|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)|
|01:41||The "LIKE Optimization" (which converts a LIKE into a BETWEEN operator that is able to use an index) does not work and cannot be used if the collating sequence of the column is not TEXT and if the pattern starts with a "+" sign. This is another case of ticket [c94369cae9b561b1f996d005]. The new test case was discovered by Manuel Rigger. (check-in: b043a54c user: drh tags: trunk)|
|12:41||• Fixed ticket [c94369ca]: Wrong answer due to the LIKE optimization plus 3 other changes (artifact: 0606ed8e user: drh)|
|12:40||Fix the LIKE optimization so that it is disabled when the LHS of the expression does not have TEXT affinity and the RHS is the pattern '/%' or the RHS begins with the escape character. Fix for ticket [c94369cae9b561b1f996d0054b]. (check-in: f191431d user: drh tags: trunk)|
|10:16||• Ticket [c94369ca] Wrong answer due to the LIKE optimization status still Open with 6 other changes (artifact: 9aeb839f user: drh)|
|10:46||• New ticket [c94369ca]. (artifact: ffd4cb43 user: drh)|
|Title:||Wrong answer due to the LIKE optimization|
|Last Modified:||2018-09-10 12:41:21|
|Version Found In:|
drh added on 2018-09-09 10:46:11:
The following query returns no rows where it should return one row:
CREATE TABLE t1(x INT COLLATE NOCASE UNIQUE); INSERT INTO t1 VALUES('/abc'); SELECT x FROM t1 WHERE x LIKE '/%';
The problem is that a numeric column is used with a LIKE operator and the LIKE pattern begins with the character '/'. The '/' character is one less than '0' (in ASCII) and so the LIKE optimization converts the expression into:
... x>='/' AND x<'0'
But because column x is numeric, the x<'0' is convert into x<0 and there are not values that are both greater than '/' and less then zero.
Of course, a reasonable schema should not do a LIKE string comparison on a numeric column. This problem was reported (via private) email against a schema that uses the type "STRING" for the column x, which is actually a numeric type in SQLite. (The fact that STRING is a numeric type is a separate issue that cannot be fixed without breaking backwards compatibility.)
drh added on 2018-09-10 10:16:53:
A similar problem can arise if the first character of the pattern is the ESCAPE character. The following SQL returns an empty set but it should return one row.
CREATE TABLE t1(x INT COLLATE NOCASE UNIQUE); INSERT INTO t1 VALUES('123'); SELECT x FROM t1 WHERE x LIKE '^1%' ESCAPE '^';