[DRILL-3689] incorrect results : aggregate AVG returns wrong results over results returned by LEAD function.

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Not A Problem
• Affects Version/s: 1.2.0
• Fix Version/s: 1.2.0
• Component/s: Execution - Relational Operators
• Labels: window_function

Environment

private-branch https://github.com/adeneche/incubator-drill/tree/new-window-funcs

• Assignee: Khurram Faraaz
• Reporter: Khurram Faraaz
• Created: 22/Aug/15 00:59
• Updated: 16/Dec/15 07:05
• Resolved: 17/Sep/15 17:54

Description

Aggregate AVG returns wrong results over results returned by LEAD function.

results returned by Drill

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)

Explain plan for above query from Drill

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=[`*`]]])

results returned by Postgres

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)