/ View Ticket
Login
Ticket UUID: c94369cae9b561b1f996d0054bfab11389f9d033
Title: Wrong answer due to the LIKE optimization
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-09-10 12:41:21
Version Found In:
User Comments:
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 '^';