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