MariaDB / MySQL CTE Raw SQL ambiguous error

Question

I have this RAWSQL query

with 
    group as (
     select * from groups where id = ?
    ),
    attributes as (
     select JSON_ARRAYAGG(
     JSON_OBJECT('id', a.id,'name', a.name ))
     from attributes a
     join groups g on g.id = ?
     join attribute_group ag on ag.group_id = g.id
     and ag.attribute_id = a.id
    ),
    templates as (
     select JSON_ARRAYAGG(
     JSON_OBJECT('id', t.id,'name', t.name))
     from templates t
     join groups g on g.id = ?
     join group_template gt on gt.group_id = g.id
     and gt.template_id = t.id
    )

    select *, 
    (select cast(count(*) as char) from attribute_group where group_id = ? ) groups_count,
    (select * from groups) groups,
    (select cast(count(*) as char) from group_template where group_id = ? ) templates_count,
    from group

I have this error

Query 1 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'group as (

Answer 1

The documentation advises to not use reserved words like group and groups. They are reserved characters and should only be used for things like the GROUP BY statement:

GROUP (R)

GROUPING (R); added in 8.0.1 (reserved)

GROUPS (R); added in 8.0.2 (reserved)

Outside of that, I noticed a few syntax errors:

1. If you have a table named GROUP, which it looks like you do, you can escape the reserved word by using back ticks `` or double quotes "" (I.e. `group`, "group").
2. Remove the comma , after templates_count,.
3. Replace ? with a valid id column value (I used 1 below).

Like so:

with 
    `group` as (
     select * from `groups` where id = 1
    ),
    attributes as (
     select JSON_ARRAYAGG(
     JSON_OBJECT('id', a.id,'name', a.name ))
     from attributes a
     join `groups` g on g.id = 1
     join attribute_group ag on ag.group_id = g.id
     and ag.attribute_id = a.id
    ),
    templates as (
     select JSON_ARRAYAGG(
     JSON_OBJECT('id', t.id,'name', t.name))
     from templates t
     join `groups` g on g.id = 1
     join group_template gt on gt.group_id = g.id
     and gt.template_id = t.id
    )

    select *, 
    (select cast(count(*) as char) from attribute_group where group_id = 1 ) groups_count,
    (select * from `groups`)  `groups`,
    (select cast(count(*) as char) from group_template where group_id = 1 ) templates_count
    from `group`