joins conflict with WITH RECURSIVE forms

[Rscho314]

Hi,

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