[jira] [Commented] (DRILL-7539) Aggregate expression is illegal in GROUP BY clause

["benj (Jira)" <ji...@apache.org>]

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

benj commented on DRILL-7539:
-----------------------------

Please note that is also possible to bypass the problem with fully prefixing columns used in GROUP BY

Example (on the same way as before):
{code:sql}
/* OK because the GROUP BY is on x.b (not only b) */
apache drill 1.17> SELECT a, any_value(b) AS b FROM (SELECT 'a' a, 1 b) x GROUP BY a, x.b;
+---+---+
| a | b |
+---+---+
| a | 1 |
+---+---+
{code}


> Aggregate expression is illegal in GROUP BY clause
> --------------------------------------------------
>
>                 Key: DRILL-7539
>                 URL: https://issues.apache.org/jira/browse/DRILL-7539
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: SQL Parser
>    Affects Versions: 1.17.0
>            Reporter: benj
>            Priority: Major
>
> When using GROUPED field in aggregate function it works unless the field is aliased with the original name of the field.
> Example (minimalist example with no real sense but based on structure actually used (with more complex GROUP BY part)):
> {code:sql}
> /* OK because aggregate is on b that is not a grouped field */
> apache drill 1.17> SELECT a, any_value(b) AS b FROM (SELECT 'a' a, 1 b) x GROUP BY a;
> +---+---+
> | a | b |
> +---+---+
> | a | 1 |
> +---+---+
> /* NOK because the aggregate on grouped field b is aliased to b (name used on the group by) */
> apache drill 1.17> SELECT a, any_value(b) AS b FROM (SELECT 'a' a, 1 b) x GROUP BY a, b;
> Error: VALIDATION ERROR: From line 1, column 11 to line 1, column 16: Aggregate expression is illegal in GROUP BY clause
> /* OK as aggregate on grouped_field b is aliased to c */
> apache drill 1.17> SELECT a, any_value(b) AS c FROM (SELECT 'a' a, 1 b) x GROUP BY a, b;
> +---+---+
> | a | c |
> +---+---+
> | a | 1 |
> +---+---+
> {code}
> This is a problem that is easy to work around but it's easy to get caught. And the bypass will sometimes requires an additional level of SELECT, which is rarely desired.
> Tested to compare VS postgres that doesn't have this problem.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)