Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
Description
Allow GROUP BY and HAVING to reference SELECT expressions by ordinal and alias. It is not standard SQL, but MySQL and PostgreSQL allow it.
See Stack Overflow: SQL - using alias in Group By.
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?
Attachments
Issue Links
- is depended upon by
-
PHOENIX-3712 Column alias can't be resolved in GROUP BY clause
- Resolved
- is related to
-
HIVE-10557 CBO : Support reference to alias in queries
- Open
- relates to
-
CALCITE-1781 Allow expression in CUBE and ROLLUP
- Closed