[
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)
