Julian Hyde Thanks for review. Committed the changes handling review comments to pull request at https://github.com/apache/calcite/pull/413.
The query SELECT a, COUNT AS c FROM t GROUP BY a, c is cyclic and must fail in validation.
It fails with aggregate functions not in group by in both calcite and Postgres. Added test as well.
Are large literals interpreted as column references (and therefore fail because out of range) or are they treated as integers? E.g. SELECT deptno FROM emp GROUP BY deptno, 100. See what Postgres does and make sure we do the same.
Postgres fails with ordinal position not in range same in calcite after my changes. Added test as well.
Are literals inside expressions interpreted as column references? E.g. SELECT COUNT FROM (SELECT 1 FROM emp GROUP BY substring(name FROM 2 FOR 3)). See what Postgres does and make sure we do the same.
This is valid in Postgres and same in calcite now. 2 and 3 are considered as liternals not an ordinal. Added test as well.
Check that if the conformance disables them, we get the old behavior for aliases and ordinals.
Added the tests which fail without conformance. Added test as well.
Should not be able to use alias in an aggregate function in the HAVING clause. E.g. SELECT deptno AS x FROM emp HAVING min < 20 is an error.
Not allowing alias in having aggregate expression now with my changes. Added test as well.
If an alias matches a column, the alias wins. E.g. SELECT COUNT FROM (SELECT gender AS deptno FROM emp GROUP BY deptno) should return 2. Please see what Postgres does. Also test a HAVING query.
Giving priority to alias than column when there is conflict. But Postgres gives priority to column name and fail with ambiguous column. Added test as well.
If an alias matches two columns, the alias wins, and the query is not ambiguous. E.g. SELECT COUNT FROM (SELECT gender AS deptno FROM emp, dept GROUP BY deptno). Also test a HAVING query.
Giving priority to alias than column name. Added test as well.
Matching is according to the case of the session. Thus SELECT x + y AS "z" FROM t GROUP BY "Z" is valid if case sensitivity is off.
It works if sensitivity is off added test as well.
Expressions involving aliases, e.g. SELECT a + b AS c, a + b + d, COUNT FROM t GROUP BY c, d should be valid, because even though a + b + d is not grouped, it matches (a + b) + d, which combines two grouped expressions. Check what Postgres does.
This works as well and added test cases for the same. Postgres allows the same.
Referencing aliases in the SELECT clause is not valid, SELECT a + b AS c, c + d FROM t GROUP BY c, d. Again, see what Postgres does.
This is not valid in calcite and Postgres.
We need to test sql-to-rel conversion for some of the cases that do not fail but have different behavior, e.g. large literals, alias same name as columns.
Added some sql-to-rel tests for corner cases.
Changed to switch case.
Please review the changes.