[ https://issues.apache.org/jira/browse/DRILL-3689?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Deneche A. Hakim updated DRILL-3689: ------------------------------------ Fix Version/s: 1.2.0 > incorrect results : aggregate AVG returns wrong results over results returned > by LEAD function. > ----------------------------------------------------------------------------------------------- > > Key: DRILL-3689 > URL: https://issues.apache.org/jira/browse/DRILL-3689 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Relational Operators > Affects Versions: 1.2.0 > Environment: private-branch > https://github.com/adeneche/incubator-drill/tree/new-window-funcs > Reporter: Khurram Faraaz > Assignee: Deneche A. Hakim > Priority: Critical > Labels: window_function > Fix For: 1.2.0 > > > Aggregate AVG returns wrong results over results returned by LEAD function. > results returned by Drill > {code} > 0: jdbc:drill:schema=dfs.tmp> SELECT avg(lead_col1) FROM (SELECT LEAD(col1) > OVER(PARTITION BY col7 ORDER BY col1) lead_col1 , col7 FROM FEWRWSPQQ_101) > sub_query; > +-------------------------+ > | EXPR$0 | > +-------------------------+ > | 2.35195986941647008E17 | > +-------------------------+ > 1 row selected (0.264 seconds) > {code} > Explain plan for above query from Drill > {code} > 0: jdbc:drill:schema=dfs.tmp> explain plan for SELECT avg(lead_col1) FROM > (SELECT LEAD(col1) OVER(PARTITION BY col7 ORDER BY col1) lead_col1 , col7 > FROM FEWRWSPQQ_101) sub_query; > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(EXPR$0=[$0]) > 00-02 Project(EXPR$0=[CAST(/(CastHigh(CASE(=($1, 0), null, $0)), > $1)):ANY NOT NULL]) > 00-03 StreamAgg(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)]) > 00-04 Project(w0$o0=[$3]) > 00-05 Window(window#0=[window(partition {2} order by [1] range > between UNBOUNDED PRECEDING and CURRENT ROW aggs [LEAD($1)])]) > 00-06 SelectionVectorRemover > 00-07 Sort(sort0=[$2], sort1=[$1], dir0=[ASC], dir1=[ASC]) > 00-08 Project(T36¦¦*=[$0], col1=[$1], col7=[$2]) > 00-09 Scan(groupscan=[ParquetGroupScan > [entries=[ReadEntryWithPath [path=maprfs:///tmp/FEWRWSPQQ_101]], > selectionRoot=maprfs:/tmp/FEWRWSPQQ_101, numFiles=1, columns=[`*`]]]) > {code} > results returned by Postgres > {code} > postgres=# SELECT avg(lead_col1) FROM (SELECT LEAD(col1) OVER(PARTITION BY > col7 ORDER BY col1) lead_col1 , col7 FROM FEWRWSPQQ_101) sub_query; > avg > --------------------- > 1157533190627124568 > (1 row) > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)