[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17778540#comment-17778540
]
Ran Tao edited comment on CALCITE-6063 at 10/23/23 10:55 AM:
-------------------------------------------------------------
In reference.md we don't have any docs about ARRAY/MAP/MULTISET with sub-query.
we just have a value constructor:
[https://calcite.apache.org/docs/reference.html#value-constructors]
> why array subquery with ORDER BY doesn’t work in Calcite?
The CALCITE-5150 supported ORDER BY clause. But there is only one sql-parser
test case, and there is no unit test and integration test. Therefore, some
cases were not verified.
> what I think we need to do now:
1. Fix this case to get the correct result when use ORDER BY
2. Add more unit tests and iq integration tests to cover ORDER BY and LIMIT
3. Add the usage doc of array subquery in reference.md.
btw, In fact, the result of using ORDER BY and LIMIT together is correct, so
this is actually just a small bug for using ORDER BY alone.
was (Author: lemonjing):
In reference.md we don't have any docs about ARRAY/MAP/MULTISET with sub-query.
we just have a value constructor:
[https://calcite.apache.org/docs/reference.html#value-constructors]
> why array subquery with ORDER BY doesn’t work in Calcite?
The CALCITE-5150 support ORDER BY clause. But there is only one sql-parser test
case, and there is no unit test and integration test. Therefore, some cases
were not verified.
> what I think we need to do now:
1. Fix this case to get the correct result when use ORDER BY
2. Add more unit tests and iq integration tests to cover ORDER BY and LIMIT
3. Add the usage doc of array subquery in reference.md.
btw, In fact, the result of using ORDER BY and LIMIT together is correct, so
this is actually just a small bug for using ORDER BY alone.
> 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)
