SQLite
View Ticket
Not logged in
Ticket UUID: 05f43be8fdda9fbd948d374319b99b054140bc36
Title: Incorrect use of index with LIKE operators when the LHS is a blob
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2015-03-07 13:57:01
Version Found In: 3.8.8.3
User Comments:
drh added on 2015-03-06 13:14:40: (text/x-fossil-wiki)
In the following SQL, the first query returns no rows whereas the second
query returns one row.  Both queries should operate as the second.

<blockquote><verbatim>
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%';
</verbatim></blockquote>

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.