Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Tue, 8 Sep 2015 17:36:47 +0000 (UTC)
From: "Jinfeng Ni (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.12857661.1440108237000.281164.1441733807155@Atlassian.JIRA>
In-Reply-To: <JIRA.12857661.1440108237000@Atlassian.JIRA>
References: <JIRA.12857661.1440108237000@Atlassian.JIRA>
 <JIRA.12857661.1440108237998@arcas>
Subject: [jira] [Commented] (DRILL-3680) window function query returns
 Incorrect results
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Jinfeng Ni commented on DRILL-3680:
-----------------------------------

Turns out that there is field name conflicts in Window RelNode.  The conflicts do not cause any problem for Calcite, since it uses index-based execution. However, this will cause problem for Drill, which uses name-based execution, a similar problem when we handle the naming conflicts in a Join operator.

The fix is to resolve the naming conflicts at Prel node, before we convert to Drill Physical Operator (POP).

 
> window function query returns Incorrect results 
> ------------------------------------------------
>
>                 Key: DRILL-3680
>                 URL: https://issues.apache.org/jira/browse/DRILL-3680
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Flow
>    Affects Versions: 1.2.0
>         Environment: private-branch https://github.com/adeneche/incubator-drill/tree/new-window-funcs
>            Reporter: Khurram Faraaz
>            Assignee: Jinfeng Ni
>            Priority: Critical
>              Labels: window_function
>             Fix For: 1.2.0
>
>         Attachments: tblWnulls.parquet
>
>
> Query plan from Drill for the query that returns wrong results
> {code}
> 0: jdbc:drill:schema=dfs.tmp> explain plan for select c1 , c2 , lead(c2) OVER ( PARTITION BY c2 ORDER BY c1) lead_c2 FROM (SELECT c1 , c2, ntile(3) over(PARTITION BY c2 ORDER BY c1) FROM `tblWnulls.parquet`);
> +------+------+
> | text | json |
> +------+------+
> | 00-00    Screen
> 00-01      Project(c1=[$0], c2=[$1], lead_c2=[$2])
> 00-02        Project(c1=[$0], c2=[$1], lead_c2=[$2])
> 00-03          Project(c1=[$0], c2=[$1], $2=[$3])
> 00-04            Window(window#0=[window(partition {1} order by [0] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [LEAD($1)])])
> 00-05              Window(window#0=[window(partition {1} order by [0] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [NTILE($2)])])
> 00-06                SelectionVectorRemover
> 00-07                  Sort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
> 00-08                    Project(c1=[$1], c2=[$0])
> 00-09                      Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///tmp/tblWnulls.parquet]], selectionRoot=maprfs:/tmp/tblWnulls.parquet, numFiles=1, columns=[`c1`, `c2`]]])
> {code}
> Results returned by Drill.
> {code}
> 0: jdbc:drill:schema=dfs.tmp> select c1 , c2 , lead(c2) OVER ( PARTITION BY c2 ORDER BY c1) lead_c2 FROM (SELECT c1 , c2, ntile(3) over(PARTITION BY c2 ORDER BY c1) FROM `tblWnulls.parquet`);
> +-------------+-------+----------+
> |     c1      |  c2   | lead_c2  |
> +-------------+-------+----------+
> | 0           | a     | null     |
> | 1           | a     | null     |
> | 5           | a     | null     |
> | 10          | a     | null     |
> | 11          | a     | null     |
> | 14          | a     | null     |
> | 11111       | a     | null     |
> | 2           | b     | null     |
> | 9           | b     | null     |
> | 13          | b     | null     |
> | 17          | b     | null     |
> | 4           | c     | null     |
> | 6           | c     | null     |
> | 8           | c     | null     |
> | 12          | c     | null     |
> | 13          | c     | null     |
> | 13          | c     | null     |
> | null        | c     | null     |
> | 10          | d     | null     |
> | 11          | d     | null     |
> | 2147483647  | d     | null     |
> | 2147483647  | d     | null     |
> | null        | d     | null     |
> | null        | d     | null     |
> | -1          | e     | null     |
> | 15          | e     | null     |
> | 19          | null  | null     |
> | 65536       | null  | null     |
> | 1000000     | null  | null     |
> | null        | null  | null     |
> +-------------+-------+----------+
> 30 rows selected (0.339 seconds)
> {code}
> Results returned by Postgres
> {code}
> postgres=# select c1 , c2 , lead(c2) OVER ( PARTITION BY c2 ORDER BY c1) lead_c2 FROM (SELECT c1 , c2, ntile(3) over(PARTITION BY c2 ORDER BY c1) FROM t222) sub_query;
>      c1     | c2 | lead_c2 
> ------------+----+---------
>           0 | a  | a
>           1 | a  | a
>           5 | a  | a
>          10 | a  | a
>          11 | a  | a
>          14 | a  | a
>       11111 | a  | 
>           2 | b  | b
>           9 | b  | b
>          13 | b  | b
>          17 | b  | 
>           4 | c  | c
>           6 | c  | c
>           8 | c  | c
>          12 | c  | c
>          13 | c  | c
>          13 | c  | c
>             | c  | 
>          10 | d  | d
>          11 | d  | d
>  2147483647 | d  | d
>  2147483647 | d  | d
>             | d  | d
>             | d  | 
>          -1 | e  | e
>          15 | e  | 
>          19 |    | 
>       65536 |    | 
>     1000000 |    | 
>             |    | 
> (30 rows)
> {code}


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