[jira] [Comment Edited] (CALCITE-1306) Allow GROUP BY and HAVING to reference SELECT expressions by ordinal and alias

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15949308#comment-15949308 ] 

Julian Hyde edited comment on CALCITE-1306 at 3/30/17 3:59 PM:
---------------------------------------------------------------

Here are a few tricky cases that I didn't see tested:
* The query {{SELECT a, COUNT(\*) AS c FROM t GROUP BY a, c}} is cyclic and must fail in validation.
*  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.
* 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.
* Check that if the conformance disables them, we get the old behavior for aliases and ordinals.
* 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\(x) < 20}} is an error.
* 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.
* 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.
* 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.
* 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.
* 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.

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.

Matching on error message is dodgy: {code}e.getCause().getMessage().equals(RESOURCE.columnNotFound(id.names.get(0)).str()){code}

When comparing enum values use == not equals, or better, switch. See {code}if (root.getKind().equals(SqlKind.CUBE){code}.


was (Author: julianhyde):
Here are a few tricky cases that I didn't see tested:
* The query {{SELECT a, COUNT(*) AS c FROM t GROUP BY a, c}} is cyclic and must fail in validation.
*  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.
* 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.
* Check that if the conformance disables them, we get the old behavior for aliases and ordinals.
* 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(x) < 20}} is an error.
* 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.
* 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.
* 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.
* 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.
* 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.

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.

Matching on error message is dodgy: {code}e.getCause().getMessage().equals(RESOURCE.columnNotFound(id.names.get(0)).str()){code}

When comparing enum values use == not equals, or better, switch. See {code}if (root.getKind().equals(SqlKind.CUBE){code}.

> Allow GROUP BY and HAVING to reference SELECT expressions by ordinal and alias
> ------------------------------------------------------------------------------
>
>                 Key: CALCITE-1306
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1306
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Assignee: Rajeshbabu Chintaguntla
>              Labels: dialect, phoenix
>
> 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|http://stackoverflow.com/questions/3841295/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:
> {code}
> select count(*) as c
> from t
> group by c
> {code}
> We'd also need rules to resolve ambiguous expressions. For instance, in
> {code}
> select e.empno as deptno
> from emp as e join dept as d
> where e.deptno = d.deptno
> group by deptno
> {code}
> does {{deptno}} refer to {{e.deptno}}, {{d.deptno}}, or {{e.empno}}?



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)