[
https://issues.apache.org/jira/browse/CALCITE-634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14371889#comment-14371889
]
Julian Hyde commented on CALCITE-634:
-------------------------------------
Two issues here.
1. Yes, this is a bug. The original query "select distinct count(empno) from
emp group by empno order by 1" is valid SQL and Calcite should give the correct
answer.
(I can't think of a case where you would actually want to write this query
"count(distinct empno)" is much more common than "distinct count(empno)".)
2. Whether to allow ordinals in GROUP BY. I am not particularly inclined to.
First of all, it is not standard SQL. Second, there are semantic problems
because SELECT is evaluated after GROUP BY. Third, although it is onerous to
type long expressions in both the SELECT and GROUP BY clause, there is a
workaround, namely to create a sub-query in the FROM clause or use a WITH
clause. I would consider accepting it as an optional feature, disabled by
default, if it was supported by tests that dealt with all of the semantic
issues. If you want to pursue this, please open a new jira case.
> Failure to order by an aggregate function, which exists in select-clause if
> distinct is present in select-clause
> ----------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-634
> URL: https://issues.apache.org/jira/browse/CALCITE-634
> Project: Calcite
> Issue Type: Bug
> Reporter: Sean Hsuan-Yi Chu
> Assignee: Julian Hyde
>
> For example, the query
> {code:sql}
> select distinct count(empno) from emp
> group by empno
> order by 1
> {code}
> gave:
> org.apache.calcite.sql.validate.SqlValidatorException <init>
> SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Expression
> 'COUNT(`EMP`.`EMPNO`)' is not in the select clause
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
