SQLite

View Ticket
Login
Ticket Hash: ce8717f0885af975dbdce9cedb342d5822901a12
Title: LIKE malfunctions for UNIQUE COLLATE NOCASE column
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-06-14 12:29:07
Version Found In:
User Comments:
mrigger added on 2019-06-14 10:01:47:

Consider this example:

CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
INSERT INTO t0(c0) VALUES ('.1%');
SELECT * FROM t0 WHERE t0.c0 LIKE '.1%'; -- expected: '.1%', actual: no row is fetched

Although the LIKE expression should yield 1 (see below), no row is fetched:

SELECT t0.c0 LIKE '.1%' FROM t0; -- 1

It seems that this problem only applies to COLLATE NOCASE as well as to numeric columns (INT, REAL, and NUMERIC).