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> |