SQLite

View Ticket
Login
Ticket Hash: 7e59041f9c4e5102b483592c781be2d2497791fd
Title: DISTINCT malfunctions for VIEW with virtual table
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-11-08 20:28:42
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-11-07 21:58:13:

In the following test case, unexpectedly, four rows are fetched, also containing two rows with duplicate v0.c0=1 values:

CREATE TABLE t0(c0);
CREATE VIRTUAL TABLE vt0 USING fts5(c0);
INSERT INTO t0(c0) VALUES (1), (0);
INSERT INTO vt0(c0) VALUES (0), (0);
CREATE VIEW v0 AS SELECT DISTINCT t0.c0 FROM vt0, t0 ORDER BY vt0.rowid;
SELECT * FROM v0; -- unexpected: 4 rows are fetched

The DISTINCT seems to work correctly when used together with a COUNT:

SELECT COUNT(*) FROM v0 WHERE v0.c0; -- 1


drh added on 2019-11-08 20:28:42:

Problem introduced by check-in [171138122690faaf] (2014-04-25) which was an attempt to fix ticket [388d01d4bb8f9a8b]. That fix was incorrect.