This code fails at runtime, when run against the db:
(sql
(with #:recursive
([(cte id name parent)
(inner-join
(union
(select h.id h.name h.parent #:from (as heap h) #:where (and (= name ?) (is-null parent)))
(select h.id h.name h.parent #:from (as heap h))
#:all)
cte
#:on (= cte.id h.parent))])
(select name #:from (select id name #:from cte #:order-by id #:desc))))
This results in the query:
(sql-statement
"WITH RECURSIVE cte(id, name, parent) AS ((SELECT h.id, h.name, h.parent FROM heap AS h WHERE ((name = ?) AND (parent IS NULL)) UNION ALL SELECT h.id, h.name, h.parent FROM heap AS h) INNER JOIN cte ON (cte.id = h.parent)) SELECT name FROM (SELECT id, name FROM cte ORDER BY id DESC)")
The problem is that according to SQLite docs, the 1st AS( must be directly followed by a SELECT statement, whereas here the statement is enclosed in an additional pair of parenthesis produced by the inner-join, and this is unfortunately not accepted by the SQLite parser.
I'm attaching a self-contained test file that can be run as-is in the SQLite3 CLI, where you'll find that the 2nd WITH RECURSIVE statement fails, while the 1st one that has the parentheses commented out runs fine.
test.txt
Hi,
This code fails at runtime, when run against the db:
This results in the query:
The problem is that according to SQLite docs, the 1st
AS(must be directly followed by aSELECTstatement, whereas here the statement is enclosed in an additional pair of parenthesis produced by theinner-join, and this is unfortunately not accepted by the SQLite parser.I'm attaching a self-contained test file that can be run as-is in the SQLite3 CLI, where you'll find that the 2nd
WITH RECURSIVEstatement fails, while the 1st one that has the parentheses commented out runs fine. test.txt