/ View Ticket
Login
Ticket UUID: 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.