[DRILL-7539] Aggregate expression is illegal in GROUP BY clause

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.17.0
• Component/s: SQL Parser
• Assignee: Unassigned
• Reporter: benj
• Created: 20/Jan/20 08:53
• Updated: 20/Jan/20 09:00

Description

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

/* 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 |
+---+---+

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.