SQLite

View Ticket
Login
Plaintext
2018-09-27
13:10
Disallow the use of window functions in the recursive part of a recursive CTE. Fix for ticket [e8275b415a2f03bee]. (check-in: b2849570 user: drh tags: branch-3.25)
13:02 • Fixed ticket [e8275b41]: Assertion fault from window function inside of recursive CTE plus 5 other changes (artifact: 8e72ee7c user: drh)
12:14
Disallow the use of window functions in the recursive part of a recursive CTE. Fix for ticket [e8275b415a2f03bee]. (check-in: 7fc29944 user: dan tags: trunk)
11:03 • New ticket [e8275b41] Assertion fault from window function inside of recursive CTE. (artifact: 0d057e12 user: drh)
Ticket Hash: e8275b415a2f03beeae6dedbb9691e5b614d4d23
Title: Assertion fault from window function inside of recursive CTE
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-09-27 13:02:41
Version Found In: 3.25.2
User Comments:
drh added on 2018-09-27 11:03:57:

The following SQL results in an assertion fault: 

WITH RECURSIVE
  t(id, parent) AS (VALUES(1,2)),
  q(id, parent, rn) AS (
    VALUES(1,2,3)
    UNION ALL
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
      FROM q JOIN t ON t.parent = q.id
  )
SELECT * FROM q;

As the SQL makes use of window functions, this defect only affects SQLite version 3.25 and its patches. The problem was reported on the sqlite-users mailing list by Yaroslav Schekin.