Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Thu, 30 Mar 2017 06:57:41 +0000 (UTC)
From: "Khurram Faraaz (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.12992052.1469302238000.162199.1490857061741@Atlassian.JIRA>
In-Reply-To: <JIRA.12992052.1469302238000@Atlassian.JIRA>
References: <JIRA.12992052.1469302238000@Atlassian.JIRA> <JIRA.12992052.1469302238888@jira-lw-us.apache.org>
Subject: [jira] [Commented] (DRILL-4802) NULLS are not first when NULLS
 FIRST is used with ORDER BY in window definition
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Thu, 30 Mar 2017 06:57:47 -0000


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

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

[~amansinha100] Confirmed that we do not see nulls first in the result (when NULLS FIRST) is specified in window definition, even for regular window functions like, ROW_NUMBER(), COUNT etc

> 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.15#6346)