frontend: support array constructing subquery array(select ...)

[lmatz]

Describe the bug

2023-07-25T16:18:17.011348+08:00 ERROR pgwire::pg_protocol: failed to parse sql:

SELECT
    roles.oid as id, roles.rolname as name,
    roles.rolsuper as is_superuser,
    CASE WHEN roles.rolsuper THEN true ELSE roles.rolcreaterole END as
    can_create_role,
    CASE WHEN roles.rolsuper THEN true
    ELSE roles.rolcreatedb END as can_create_db,
    CASE WHEN 'pg_signal_backend'=ANY(ARRAY(WITH RECURSIVE cte AS (
    SELECT pg_roles.oid,pg_roles.rolname FROM pg_roles
        WHERE pg_roles.oid = roles.oid
    UNION ALL
    SELECT m.roleid,pgr.rolname FROM cte cte_1
        JOIN pg_auth_members m ON m.member = cte_1.oid
        JOIN pg_roles pgr ON pgr.oid = m.roleid)
    SELECT rolname  FROM cte)) THEN True
    ELSE False END as can_signal_backend
FROM
    pg_catalog.pg_roles as roles
WHERE
    rolname = current_user:

sql parser error: Expected [, found: ( at line:9, column:51
Near " CASE WHEN 'pg_signal_backend'=ANY(ARRAY"
2023-07-25T16:18:17.011387+08:00 ERROR pgwire::pg_protocol: error when process message error=QueryError: sql parser error: Expected [, found: ( at line:9, column:51
Near " CASE WHEN 'pg_signal_backend'=ANY(ARRAY"

Error message/log

No response

To Reproduce

use pgAdmin 4 to connect to Risingwave

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

The tricky thing is recursive cte...
But the good side is that it is a batch query and it is fetching some catalog info, so no need to be super efficient.

And probably returning an empty result set for the CTE is good enough.

[fuyufjh]

This is exactly #7199 🥲

WITH RECURSIVE CTE is difficult. Is there any way to go around this?

[fuyufjh]

CASE
    WHEN 'pg_signal_backend' = ANY(
        ARRAY(
            WITH RECURSIVE cte AS (
                SELECT
                    pg_roles.oid,
                    pg_roles.rolname
                FROM
                    pg_roles
                WHERE
                    pg_roles.oid = roles.oid
                UNION
                ALL
                SELECT
                    m.roleid,
                    pgr.rolname
                FROM
                    cte cte_1
                    JOIN pg_auth_members m ON m.member = cte_1.oid
                    JOIN pg_roles pgr ON pgr.oid = m.roleid
            )
            SELECT
                rolname
            FROM
                cte
        )
    ) THEN True
    ELSE False
END as can_signal_backend

So this complex expression with CTE is aimed at getting can_signal_backend, which means whether this role is capable to "signal another backend to cancel a query or terminate its session" (reference)

Shall we simply put a true here? I think we may match this pattern in some quick and dirty way, for example, on the presence of both this specific alias can_signal_backend and from pg_catalog.pg_roles

[lmatz]

Don't know if we have ever discussed batch's recursive cte before. 🤔

Just purely for the sake of discussing the possibilities of implementing one in the future:

from this SQL only(Comprehensive support according to the standard may be overkill and extremely difficult), a basic implementation seems doable, i.e. not caring about performance or resource usage at all.

It seems the part we need to change is that:
the task executors are no long scheduled all at once in advance, but gradually scheduled at runtime.
(we don't really have a cyclic execution graph to deal with)

[github-actions]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.

[xiangjinwu]

• Array constructing subquery has been supported.
• Recursive CTE is WIP.