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 (

CodePudding user response：

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`