Skip to content

[PG Dialect] SELECT ARRAY(SELECT 1) fails to parse #400

Closed

Description

@e-dard

Whilst working on a project that uses sqlparser to parse some real SQL queries from a common PG client (PGAdmin 6.1) I found that the following query emitted by PGAdmin at startup fails to parse correctly:

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

I poked around a bit and managed to reduce the failing query to:

SELECT ARRAY(SELECT 1))

which against PG 13 produces the following results:

edd=# SELECT ARRAY(SELECT 1);
 array 
-------
 {1}
(1 row)

However, the following program panics:

use sqlparser;

let q = "SELECT ARRAY(SELECT 1)";
let dialect = sqlparser::dialect::PostgreSqlDialect {};
let ast = sqlparser::parser::Parser::parse_sql(&dialect, &q).unwrap();    

with the following panic (due to an error being returned)

thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: ParserError("Expected ), found: 1")

Finally, if I change the query to something that is invalid in PG:

SELECT ARRAY(1);

then whilst this query fails against PG 13 it actually gets parsed by sqlparser:

[2022-01-06T18:38:38Z DEBUG sqlparser::parser] Parsing sql 'SELECT ARRAY(1)'...
[2022-01-06T18:38:38Z DEBUG sqlparser::parser] parsing expr
[2022-01-06T18:38:38Z DEBUG sqlparser::parser] parsing expr
[2022-01-06T18:38:38Z DEBUG sqlparser::parser] prefix: Value(Number("1", false))
[2022-01-06T18:38:38Z DEBUG sqlparser::parser] get_next_precedence() RParen
[2022-01-06T18:38:38Z DEBUG sqlparser::parser] next precedence: 0
[2022-01-06T18:38:38Z DEBUG sqlparser::parser] prefix: Function(Function { name: ObjectName([Ident { value: "ARRAY", quote_style: None }]), args: [Unnamed(Value(Number("1", false)))], over: None, distinct: false })
[2022-01-06T18:38:38Z DEBUG sqlparser::parser] get_next_precedence() EOF
[2022-01-06T18:38:38Z DEBUG sqlparser::parser] next precedence: 0

Activity

  1. LucaCappelletti94 commented on Sep 23, 2026

    @LucaCappelletti94
    Contributor

    Completed by #566.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions