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

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

benj created DRILL-7539:
---------------------------

             Summary: 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


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)