SQLite

View Ticket
Login
Ticket Hash: 587791f92620090ea9bffd3b1a448a9b0fe2bf33
Title: Row is not fetched when using WHERE clause with INSTR()
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-09-17 13:30:42
Version Found In:
User Comments:
mrigger added on 2019-09-16 19:25:31:

Consider the following statements:

CREATE TABLE t0(c0 PRIMARY KEY, c1);
INSERT INTO t0(c0) VALUES (x'bb'), (0);
SELECT COUNT(*) FROM t0 WHERE INSTR(x'aabb', t0.c0) ORDER BY t0.c0, t0.c1; -- 1
SELECT * FROM t0 WHERE INSTR(x'aabb', t0.c0) ORDER BY t0.c0, t0.c1; -- no row is fetched

The two SELECT statements disagree, since the WHERE clause in the first query seems to evaluate to TRUE for one row, and for the second query the WHERE clause seems to evaluate to false for both rows.