|Title:||Incorrect use of index with LIKE operators when the LHS is a blob|
|Last Modified:||2015-03-07 13:57:01|
|Version Found In:||18.104.22.168|
drh added on 2015-03-06 13:14:40:
In the following SQL, the first query returns no rows whereas the second query returns one row. Both queries should operate as the second.
CREATE TABLE t1(x TEXT UNIQUE COLLATE nocase); INSERT INTO t1(x) VALUES(x'616263'); SELECT 'query-1', x FROM t1 WHERE x LIKE 'a%'; SELECT 'query-2', x FROM t1 WHERE +x LIKE 'a%';
This problem appears to have been introduced on 2005-08-28 (9.5 years ago) by check-in [ef84ff795c85e9d2]. And yet the problem has never been observed in the wild. The problem was discovered by the SQLite developers while reasoning about the validity of the LIKE optimization.