[jira] [Comment Edited] (CALCITE-6063) ARRAY sub-query with OrderBy gives unexpected results

["Ran Tao (Jira)" <ji...@apache.org>]

    [ 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/21/23 2:43 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 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}


was (Author: lemonjing):
FYI, 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)