Common Table Expressions don't accept cte_decl without params but sqlite3 does

[mingodad]

Example:

WITH RECURSIVE

-- adjacency list
input(id, parent_id, level) AS (VALUES
    (1, 0, 'level 1'),
    (2, 1, 'level 2'),
    (3, 2, 'level 3'),
    (4, 3, 'level 4')
),

hierarchy AS (
    SELECT id, parent_id, level
    FROM input
    WHERE id = 3 -- start point
    UNION ALL
    -- stop when join result is empty set (0 rows returned)
    SELECT i.id, i.parent_id, i.level
    FROM hierarchy h -- previous result set stored in temporary table
        JOIN input i
            ON i.id = h.parent_id
)

SELECT * FROM hierarchy;

SQLite3 output:

id|parent_id|level
3|2|level 3
2|1|level 2
1|0|level 1

CQL output:

cql --in recursive-cte.sql
recursive-cte.sql:11:1: error: syntax error, unexpected AS, expecting '('
Parse errors found, no further passes will run.

[ricomariani]

It really can't do its thing without column names. I could use inferred column names. This would essentially rewrite the above as

hierarchy(*) AS (
    SELECT id, parent_id, level
    FROM input
    WHERE id = 3 -- start point
    UNION ALL
    -- stop when join result is empty set (0 rows returned)
    SELECT i.id, i.parent_id, i.level
    FROM hierarchy h -- previous result set stored in temporary table
        JOIN input i
            ON i.id = h.parent_id
)

SELECT * FROM hierarchy;

Which means "use the column names of the inner select"

[mingodad]

I'm only pointing out that it differs from sqlite3/postgresql here. I did an experiment in the grammar to accept it by adding star behind the curtains:

cte_decl:
  name '(' name_list ')'  { $cte_decl = new_ast_cte_decl(CS, $name, $name_list); }
  | name '(' '*' ')'  { $cte_decl = new_ast_cte_decl(CS, $name, new_ast_star(CS)); }
  | name  { $cte_decl = new_ast_cte_decl(CS, $name, new_ast_star(CS)); }
  ;

[ricomariani]

I just did exactly the same:

https://github.com/ricomariani/CG-SQL-author/commit/d91c2315b90d2587c6957ce6d3f411352eddbf46

That's actually a pretty good approach for CQL.