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)
