SQLite

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

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 '^';