[
https://issues.apache.org/jira/browse/CALCITE-2552?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16769670#comment-16769670
]
Julian Hyde commented on CALCITE-2552:
--------------------------------------
Can you please improve the summary? "Dialect Specific Order By Field Identifier
Resolution" is noun soup.
The problem isn't really dialect-specific. We generate the wrong SQL in the
ORDER BY clause when there is an alias. And the problem is to find a simple
robust solution; your solution isn't particularly robust, because (I surmise)
after your fix, the problem still exists in MySQL.
Quite a bit of this behavior is already covered by SqlConformance - see methods
isSortByAliasObscures(), isSortByAlias(). Is there any way we can tie into
that? In my experience, just about all dialects allow alias in the ORDER BY
clause, so I'd hate to have to re-discover that, for each dialect, one bug at a
time. Best thing would be to avoid adding a method.
> Dialect Specific Order By Field Identifier Resolution
> -----------------------------------------------------
>
> Key: CALCITE-2552
> URL: https://issues.apache.org/jira/browse/CALCITE-2552
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: KrishnaKant Agrawal
> Assignee: Julian Hyde
> Priority: Major
> Labels: pull-request-available
>
> The Order By Keys when resolved during RelToSql Conversion is hardcoded to
> fallback to Original Expression instead of an alias(if present)
>
> This should be based on the Dialect set in RelToSqlConverter.
>
> If the Order By Key is a Projection which was Aliased, the Output query
> contains the original column name instead of the Alias, which is not allowed
> in HIVE as valid Order By keys are to be selected from Select List
> Identifiers only.
>
> Generated SQL(Failing in hive!),
> {code:java}
> Select first_name as n1 from emp order by first_name;{code}
> Expected SQL,
> {code:java}
> Select first_name as n1 from emp order by n1;{code}
> The Code Excerpt Responsible for this particular use case:-
> (SqlImplementor.java:952)
> {code:java}
> public SqlNode field(int ordinal) {
> final SqlNode selectItem = selectList.get(ordinal);
> switch (selectItem.getKind()) {
> case AS:
> return ((SqlCall) selectItem).operand(0);
> }
> return selectItem;
> }{code}
>
> I create the TableScan, Projection(with the alias) & Sort in the mentioned
> order.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
