[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 7:11 AM:
-----------------------------------------------------------
thanks for review. About MULTISET/MAP constructor by query:
1. multiset constructor by query doesn't support order by and limit.
{noformat}
<array value constructor by query> ::=
ARRAY <left paren> <query expression> [ <order by clause> ] <right
paren>
<multiset value constructor by query> ::=
MULTISET <left paren> <query expression> <right paren>
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
was (Author: lemonjing):
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
<array value constructor by query> ::=
ARRAY <left paren> <query expression> [ <order by clause> ] <right
paren>
<multiset value constructor by query> ::=
MULTISET <left paren> <query expression> <right paren>
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> ------------------------------------------------------------------
>
> 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
> Assignee: Ran Tao
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> 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 issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
