[
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)
