[
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 7:25 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?
because before 1.35.0 our parser(in parser.jj ArrayConstructor) not support
ORDER BY clause, it will cause exception. however the CALCITE-5624 in order to
support Spark's array function, the logic of this part of the parser has been
modified, causing the parser to no longer throw exceptions but to allow ORDER
BY expressions, but this is obviously not the original meaning of the PR. So
actually the subquery of array with ORDER BY does not have any testing or
documentation. We have never supported it.
> If we want to support ORDER BY like Postgres, what I think we need to do now
> is:
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 (maybe also add map and
multiset together)
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.
> If we choose not to support it for the time being:
IMHO, in order to avoid ambiguity for users, we should repair the parse logic
of the array constructor so that it continues to maintain the same logic as
before 1.35.0, and does not support ORDER BY and LIMIT clauses. (In the
meantime, this change needs to ensure that it is also compatible with the spark
array function)
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?
because before 1.35.0 our parser(in parser.jj ArrayConstructor) not support
ORDER BY clause, it will cause exception. however the CALCITE-5624 in order to
support Spark's array function, the logic of this part of the parser has been
modified, causing the parser to no longer throw exceptions but to allow ORDER
BY expressions, but this is obviously not the original meaning of the PR. So
actually the subquery of array with ORDER BY does not have any testing or
documentation. We have never supported it.
> If we want to support ORDER BY like Postgres, what I think we need to do now
> is:
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 (maybe also add map and
multiset together)
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.
> If we choose not to support it for the time being:
IMHO, in order to avoid ambiguity for users, we should repair the parse logic
of the array constructor so that it continues to maintain the same logic as
before 1.35.0, and does not support ORDER BY and LIMIT clauses. (In the
meantime, we are compatible with CALCITE-5624 for spark array changes)
> 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)
