/ View Ticket
Login
Ticket UUID: c4130c33be081b2e75e8eb12b623ff6b67752ed0
Title: DISTINCT malfunctions for VIEW
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Not_A_Bug
Last Modified: 2019-09-10 19:46:38
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-09-10 19:07:26:

Consider the following test case:

CREATE TABLE t0(c0);
INSERT INTO t0(c0) VALUES (0.0), (0);
CREATE VIEW v0(c0) AS SELECT DISTINCT c0 FROM t0;
SELECT * FROM v0; -- expected: 0.0, 0, actual: 0.0

Unexpectedly, only one of the zero values is fetched. This bug can indirectly be observed by the different behavior of two related queries:

SELECT COUNT(*) FROM v0 WHERE v0.c0 || 0.1; -- 1
SELECT (v0.c0 || 0.2) IS TRUE FROM v0; -- 0


drh added on 2019-09-10 19:46:38:

0 is not distinct from 0.0 since (0 IS 0.0) IS TRUE. So the query

SELECT DISTINCT * FROM (VALUES(0),(0.0));

Is free to return either 0 or 0.0. The choice is arbitrary. It is analogous to a SELECT with a "LIMIT 1" and without an ORDER BY clause in that the query is free to return any single row that it wants.

If the query decides to return 0, then 0||0.1 is a string '00.1' which evaluates to TRUE. If the query decides to return 0.0, then 0.0||0.1 is a string '0.00.1' which evaluates to false. So the other queries in the original problem statement are free to return either result depending on which value comes out of the ambiguous view.