[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17777750#comment-17777750
]
Ran Tao edited comment on CALCITE-6063 at 10/23/23 9:55 AM:
------------------------------------------------------------
Frankly speaking, before calcite-1.35, array/multiset/map sub-query all only
support limited query such as {*}select ... from ...{*}, or with optional
{*}where{*}.
the array allows *orderby* was intrdoced by CALCITE-5150
however there is no test case to cover order by or limit. This ticket try to
refine this if we decide to support it.
btw, the sql-standard-2003, 6.36 allows array query with {*}orderby{*}:
{code:java}
<array value constructor by query> ::=
ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>
{code}
the postgresql also support orderby and limit such as:
{code:java}
select array(select x from unnest(array[1,2,3]) as t(x) order by x desc limit
2); {code}
was (Author: lemonjing):
Frankly speaking, before calcite-1.35, array/multiset/map sub-query all only
support limited query such as {*}select ... from ...{*}, or with optional
{*}where{*}.
the array allows *orderby* was intrdoced by this
[https://github.com/apache/calcite/pull/3141] PR by mistake (The processing
here is just to receive all args to support this 'array()' function form rather
than support *orderby* or {*}limit{*}). It accept all exprs, so allow orderby
either.
and there is no test case to cover order by or limit. This ticket try to refine
this if we decide to support it.
btw, the sql-standard-2003, 6.36 allows array query with {*}orderby{*}:
{code:java}
<array value constructor by query> ::=
ARRAY <left paren> <query expression> [ <order by clause> ] <right paren>
{code}
the postgresql also support orderby and limit such as:
{code:java}
select array(select x from unnest(array[1,2,3]) as t(x) order by x desc limit
2); {code}
> ARRAY sub-query with OrderBy gives unexpected results
> -----------------------------------------------------
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.35.0
> Reporter: Ran Tao
> Priority: Major
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +-----------+
> | EXPR$0 |
> +-----------+
> | [1, 2, 3] |
> +-----------+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
