SQLite

View Ticket
Login
Plaintext
2020-09-29
11:52 • Ticket [c51489c3] Incorrect result from WITH RECURSIVE using DISTINCT status still Fixed with 3 other changes (artifact: 465c627583 user: drh)
2020-09-17
00:47 • Fixed ticket [c51489c3]. (artifact: bc9cfc49fb user: drh)
00:46
DISTINCT may not be ignored inside a UNION ALL common table expression. Fix for ticket [c51489c3b8f919c5] (check-in: 7d2b590d3a user: drh tags: trunk)
00:00 • New ticket [c51489c3] Incorrect result from WITH RECURSIVE using DEFAULT. (artifact: bc4760677c user: drh)
Ticket Hash: c51489c3b8f919c5143919ecbcdc40d4336aafaf
Title: Incorrect result from WITH RECURSIVE using DISTINCT
Status: Fixed Type: Code_Defect
Severity: Important Priority: Medium
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2020-09-29 11:52:53
Version Found In:
User Comments:
drh added on 2020-09-17 00:00:16:

Test case:

    CREATE TABLE t (label VARCHAR(10), step INTEGER);
    INSERT INTO T VALUES('a', 1);
    INSERT INTO T VALUES('a', 1);
    INSERT INTO T VALUES('b', 1);
    WITH RECURSIVE cte(label, step) AS (
        SELECT DISTINCT * FROM t 
      UNION ALL 
        SELECT label, step + 1 FROM cte WHERE step < 3
    )
    SELECT * FROM cte;

The above should return 6 rows, but is returning 9 rows with all recent versions of SQLite. That 6 rows are correct is confirmed on sqlfiddle.com using PostgreSQL and SQL Server.

Bisect shows the problem first appeared in check-in 45d8cc678d128f1d on 2014-03-21 (6.5 years ago!) and was first released with version 3.8.5. The problem has not been reported from the field in all that time, which tells us that it is not a high priority to fix.

Problem reported on the Forum via post 1d3b0519e2.