[
https://issues.apache.org/jira/browse/CALCITE-5150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17537380#comment-17537380
]
Dmitry Sysolyatin commented on CALCITE-5150:
--------------------------------------------
[~Chunwei Lei] By sql standard array value constructor by query should support
"order by". subquery inside array is usual subquery without any restriction.
{code}
<array value constructor by query> ::=
ARRAY <table subquery>
<table subquery> ::=
<subquery>
<subquery> ::=
<left paren> <query expression> <right paren>
<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
{code}
At least PG supports it. I faced with this issue trying to handle the following
query that psql sends on '\dt+':
{code}
SELECT pol.polname, pol.polpermissive,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE
pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where
oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '1' ORDER BY 1
{code}
> Parser should parse subquery with order by inside array constructor
> -------------------------------------------------------------------
>
> Key: CALCITE-5150
> URL: https://issues.apache.org/jira/browse/CALCITE-5150
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Dmitry Sysolyatin
> Assignee: Dmitry Sysolyatin
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Parser can't parse query that looks like:
> {code}
> SELECT array(select x from (VALUES(1)) x ORDER BY x)
> {code}
> {code}
> Caused by: org.apache.calcite.sql.parser.babel.ParseException: Encountered
> "ORDER" at line 1, column 42.
> Was expecting one of:
> ")" ...
> "NATURAL" ...
> {code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
