/ Ticket Change Details
Login
Overview

Artifact ID: 3f8b5e74f1f3681e4a939b7873182055abde769d061e67c33415fe3c78320fb0
Ticket: 7e59041f9c4e5102b483592c781be2d2497791fd
DISTINCT malfunctions for VIEW with virtual table
User & Date: mrigger 2019-11-07 21:58:13
Changes

  1. foundin changed to: "3.30.0"
  2. icomment:
    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>
    
  3. login: "mrigger"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Critical"
  6. status changed to: "Open"
  7. title changed to: "DISTINCT malfunctions for VIEW with virtual table"
  8. type changed to: "Code_Defect"