SQLite

View Ticket
Login
Ticket Hash: 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: (text/x-fossil-wiki)
The following query returns no rows where it should return one row:

<blockquote><verbatim>
CREATE TABLE t1(x INT COLLATE NOCASE UNIQUE);
INSERT INTO t1 VALUES('/abc');
SELECT x FROM t1 WHERE x LIKE '/%';
</verbatim></blockquote>

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: (text/x-fossil-wiki)
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.

<blockquote><verbatim>
CREATE TABLE t1(x INT COLLATE NOCASE UNIQUE);
INSERT INTO t1 VALUES('123');
SELECT x FROM t1 WHERE x LIKE '^1%' ESCAPE '^';
</verbatim></blockquote>