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

["Khurram Faraaz (JIRA)" <ji...@apache.org>]

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)