[
https://issues.apache.org/jira/browse/CALCITE-5768?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-5768.
----------------------------------
Fix Version/s: 1.35.0
Resolution: Fixed
Fixed in
[cf7f71bc|https://github.com/apache/calcite/commit/cf7f71bcde345cdb814f01adb8eb7d0f11e8e83f];
thanks for the PR, [~wnoble]!
> Rel-to-sql conversion fails to insert a subquery for some sort-by-ordinal
> queries
> ---------------------------------------------------------------------------------
>
> Key: CALCITE-5768
> URL: https://issues.apache.org/jira/browse/CALCITE-5768
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Will Noble
> Assignee: Will Noble
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Currently, when given a rel expression like this:
> {code}
> LogicalProject(JOB=[$0])
> LogicalSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
> LogicalAggregate(group=[{2}], agg#0=[COUNT($1)])
> LogicalTableScan(table=[[scott, EMP]])
> {code}
> Calcite will convert to this invalid ({{ORDER BY 2}} despite only one
> {{SELECT}} expression) SQL:
> {code:sql}
> SELECT "JOB"
> FROM "scott"."EMP"
> GROUP BY "JOB"
> ORDER BY "JOB", 2
> {code}
> When it should convert to this SQL with a sub-query:
> {code:sql}
> SELECT "JOB"
> FROM (SELECT "JOB", COUNT("ENAME") AS "$f1"
> FROM "scott"."EMP"
> GROUP BY "JOB"
> ORDER BY "JOB", 2) AS "t0"
> {code}
> The problem is that the {{hasSortByOrdinal}} method sees a non-ordinal "JOB"
> in the {{ORDER BY}} clause and incorrectly says that there are no ordinals,
> due to an [improper early
> return|https://github.com/apache/calcite/blob/c4042a34ef054b89cec1c47fefcbc8689bad55be/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1902].
> It should only return early if the result is {{true}}. Otherwise, it should
> continue searching for ordinals.
> When the method is fixed, a subquery is created.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
