Khurram Faraaz created DRILL-4805:
-------------------------------------
Summary: 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
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)
