I think the reason that you saw such SqlValidatorException is that Calcite does not allow ordinal reference in GROUP BY. Ordinal reference is only allowed in ORDER BY clause.

Some systems, e.g Postgre, do allow ordinal reference in GROUP BY. The following query works in poster 9.3.

select distinct dept_name from dept group by 1;
 dept_name
-----------
 abc

Seems it's desirable to open this restriction in Calcite, and allow ordinal reference in GROUP BY clause. Julian Hyde, what's your opinion on this ?

In the original query, ordinal reference is in order by actually.

The exception is thrown here: AggChecker.java (line 117).

And it seems Calcite does not allow aggregation function to be in ORDER BY clause if there is SELECT DISTINCT.

However, as, in this query, this aggregation function is simply an entity in the select clause,
calcite might be less restrictive?

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.

A patch to address this issue

Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/5e285a16.

Resolved in release 1.2.0-incubating (2015-04-16)