[DRILL-4805] COUNT(*) over window and group by partitioning column results in validation error

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.8.0
• Component/s: Query Planning & Optimization
• Labels: window_function

Environment

4 node cluster

• Assignee: Unassigned
• Reporter: Khurram Faraaz
• Created: 24/Jul/16 10:47
• Updated: 27/Jul/16 03:51

Description

COUNT over window and group by partitioning column results in validation error.
MapR Drill 1.8.0 commit ID : 34ca63ba

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)

Postgres returns the COUNT for the same query and same data.

postgres=# SELECT COUNT(*) OVER ( PARTITION BY c2 ORDER BY c2 ) FROM t222 group by c2;
 count 
-------
     1
     1
     1
     1
     1
     1
(6 rows)

Interestingly, when we do a nested COUNT(COUNT) over the window, Drill does return the count.

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)

Also without the GROUP BY c2, and with column c2 in the project Drill returns results.

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)