[CALCITE-1306] Allow GROUP BY and HAVING to reference SELECT expressions by ordinal and alias - ASF JIRA

XML

Word

Printable

JSON

Allow GROUP BY and HAVING to reference SELECT expressions by ordinal and alias. It is not standard SQL, but MySQL and PostgreSQL allow it.

It would be enabled only by new methods isGroupByOrdinal and isGroupByAlias in SqlConformance.

We might allow alias in the HAVING clause (as described in HIVE-10557) but ordinal does not make sense.

Expressions that are not available before grouping would be illegal; for instance:

select count(*) as c
from t
group by c

We'd also need rules to resolve ambiguous expressions. For instance, in

select e.empno as deptno
from emp as e join dept as d
where e.deptno = d.deptno
group by deptno

does deptno refer to e.deptno, d.deptno, or e.empno?

is depended upon by

PHOENIX-3712 Column alias can't be resolved in GROUP BY clause

is related to

HIVE-10557 CBO : Support reference to alias in queries

relates to

CALCITE-1781 Allow expression in CUBE and ROLLUP