SQLite

View Ticket
Login
Ticket Hash: 1cda8f4b5496d7f93fd51bf364d75120328ad077
Title: UNLIKELY in query causes row to not be fetched
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Not_A_Bug
Last Modified: 2019-11-05 13:04:18
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-11-04 21:30:10:

Consider the following query:

CREATE TABLE t0(c0, c1, c2, PRIMARY KEY(c2)) WITHOUT ROWID;
CREATE INDEX i0 ON t0(CAST(c1 AS INT));
CREATE VIEW v0 AS SELECT 0, c0 FROM t0 GROUP BY 1 HAVING c2;
INSERT INTO t0(c2) VALUES('');
INSERT INTO t0(c1, c2) VALUES(1, 1);
SELECT * FROM v0 WHERE UNLIKELY(1); -- unexpected: no row is fetched

Unexpectedly, no row is fetched, although the condition for the row in the view should evaluate to TRUE:

SELECT UNLIKELY(1) FROM v0; -- 1


drh added on 2019-11-05 13:04:18:

The query is ambiguous. Both results are valid.

The table has two rows:

null,null,''
null,1,1

Because the GROUP BY clause refers to a constant, both rows are in the same group. Thus C2 value used by the HAVING clause can legally be either '' (which evaluates to FALSE) or 1 (which evaluates to TRUE). SQLite has the freedom to choose either value for C2. Sometimes it picks the '', and other times it picks the 1.

Other RDBMSes would throw and error here, due to the ambiguity. And you can argue that SQLite ought to do the same. But we cannot really change that now, since the existing behavior is widely exploited in practice.