drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-4802) NULLS are not first when NULLS FIRST is used with ORDER BY in window definition
Date Mon, 25 Jul 2016 07:21:20 GMT

    [ https://issues.apache.org/jira/browse/DRILL-4802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15391456#comment-15391456
] 

Khurram Faraaz commented on DRILL-4802:
---------------------------------------

We have several tests in our functional suite (resources/Functional/window_functions) that
cover NULLS FIRST, and they are there for sometime now and running clean. So this issue seems
to be related to nested aggregates.
Here are some existing tests for NULLS FIRST with regular window functions.

{noformat}
aggregates/winFnQry_62.q:select c1, c2, max ( c1 ) over ( partition by c2 order by c1 nulls
first ) w_max from `tblWnulls.parquet`;
aggregates/winFnQry_63.q:select c1, c2, sum ( c1 ) over ( partition by c2 order by c1 desc
nulls first ) w_sum from `tblWnulls.parquet`;
aggregates/winFnQry_82.q:select c1, c2, w_avg from ( select c1, c2, avg ( c1 ) over ( partition
by c2 order by c1 asc nulls first ) w_avg from `tblWnulls.parquet` ) sub_query where w_avg
is not null;

{noformat}

> NULLS are not first when NULLS FIRST is used with ORDER BY in window definition
> -------------------------------------------------------------------------------
>
>                 Key: DRILL-4802
>                 URL: https://issues.apache.org/jira/browse/DRILL-4802
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Flow
>    Affects Versions: 1.8.0
>         Environment: 4 node CentOS cluster
>            Reporter: Khurram Faraaz
>
> NULLS FIRST is not honored when used with ORDER BY inside window definition. This in
a wrong results issue.
> MapR Drill 1.8.0 commit ID : 34ca63ba
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> select c2, AVG(SUM(c1)) OVER(partition by c2 order by
c2 nulls first) FROM `tblWnulls.parquet` group by c2;
> +-------+----------------+
> |  c2   |     EXPR$1     |
> +-------+----------------+
> | a     | 11152.0        |
> | b     | 41.0           |
> | c     | 56.0           |
> | d     | 4.294967315E9  |
> | e     | 14.0           |
> | null  | 1065555.0      |
> +-------+----------------+
> 6 rows selected (0.227 seconds)
> {noformat}
> {noformat}
> postgres=# select c2, AVG(SUM(c1)) OVER(partition by c2 order by c2 nulls first) FROM
t222 group by c2;
>  c2 |          avg           
> ----+------------------------
>     |   1065555.000000000000
>  a  | 11152.0000000000000000
>  b  |    41.0000000000000000
>  c  |    56.0000000000000000
>  d  |    4294967315.00000000
>  e  |    14.0000000000000000
> (6 rows)
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message