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: (text/x-fossil-wiki)
In the following test case, unexpectedly, four rows are fetched, also containing two rows with duplicate v0.c0=1 values:

<pre>
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
</pre>

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

<pre>
SELECT COUNT(*) FROM v0 WHERE v0.c0; -- 1
</pre>

drh added on 2019-11-08 20:28:42: (text/x-fossil-wiki)
Problem introduced by check-in [171138122690faaf] (2014-04-25)
which was an attempt
to fix ticket [388d01d4bb8f9a8b].  That fix was incorrect.