[ https://issues.apache.org/jira/browse/DRILL-4805?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15395012#comment-15395012 ]
Khurram Faraaz commented on DRILL-4805: --------------------------------------- Yes, we should have uncovered this earlier. This issue is not a result of changes made for DRILL-2330. > COUNT(*) over window and group by partitioning column results in validation > error > --------------------------------------------------------------------------------- > > Key: DRILL-4805 > URL: https://issues.apache.org/jira/browse/DRILL-4805 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 1.8.0 > Environment: 4 node cluster > Reporter: Khurram Faraaz > Labels: window_function > > COUNT(*) over window and group by partitioning column results in validation > error. > MapR Drill 1.8.0 commit ID : 34ca63ba > {noformat} > 0: jdbc:drill:schema=dfs.tmp> SELECT COUNT(*) OVER ( PARTITION BY c2 ORDER BY > c2 ) FROM `tblWnulls.parquet` group by c2; > Error: VALIDATION ERROR: At line 1, column 14: Expression '*' is not being > grouped > SQL Query null > [Error Id: 44e03ee7-4c86-4809-90e7-5eaeb634691d on centos-01.qa.lab:31010] > (state=,code=0) > {noformat} > Postgres returns the COUNT for the same query and same data. > {noformat} > postgres=# SELECT COUNT(*) OVER ( PARTITION BY c2 ORDER BY c2 ) FROM t222 > group by c2; > count > ------- > 1 > 1 > 1 > 1 > 1 > 1 > (6 rows) > {noformat} > Interestingly, when we do a nested COUNT(COUNT(*)) over the window, Drill > does return the count. > {noformat} > 0: jdbc:drill:schema=dfs.tmp> SELECT COUNT(COUNT(*)) OVER ( PARTITION BY c2 > ORDER BY c2 ) FROM `tblWnulls.parquet` group by c2; > +---------+ > | EXPR$0 | > +---------+ > | 1 | > | 1 | > | 1 | > | 1 | > | 1 | > | 1 | > +---------+ > 6 rows selected (0.22 seconds) > {noformat} > Also without the GROUP BY c2, and with column c2 in the project Drill returns > results. > {noformat} > 0: jdbc:drill:schema=dfs.tmp> SELECT COUNT(*) OVER ( PARTITION BY c2 ORDER BY > c2 ), c2 FROM `tblWnulls.parquet`; > +---------+-------+ > | EXPR$0 | c2 | > +---------+-------+ > | 7 | a | > | 7 | a | > | 7 | a | > | 7 | a | > | 7 | a | > | 7 | a | > | 7 | a | > | 4 | b | > | 4 | b | > | 4 | b | > | 4 | b | > | 7 | c | > | 7 | c | > | 7 | c | > | 7 | c | > | 7 | c | > | 7 | c | > | 7 | c | > | 6 | d | > | 6 | d | > | 6 | d | > | 6 | d | > | 6 | d | > | 6 | d | > | 2 | e | > | 2 | e | > | 4 | null | > | 4 | null | > | 4 | null | > | 4 | null | > +---------+-------+ > 30 rows selected (0.172 seconds) > {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)