[DRILL-3298] Wrong result with SUM window function and order by without partition by in the OVER clause

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.0.0
• Fix Version/s: 1.1.0
• Component/s: Execution - Flow
• Labels: window_function
• Assignee: Aman Sinha
• Reporter: Victoria Markman
• Created: 16/Jun/15 00:17
• Updated: 29/Jun/15 16:49
• Resolved: 19/Jun/15 15:25

Description

This query returns incorrect result when planner.slice_target = 1

select
        j1.c_integer,
        sum(j1.c_integer) over w
from j1
window  w as (order by c_integer desc)
order by
        1, 2;

Query plan with planner.slice_target = 1

00-01      Project(c_integer=[$0], EXPR$1=[$1])
00-02        SingleMergeExchange(sort0=[0 ASC], sort1=[1 ASC])
01-01          SelectionVectorRemover
01-02            Sort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
01-03              Project(c_integer=[$0], EXPR$1=[$1])
01-04                HashToRandomExchange(dist0=[[$0]], dist1=[[$1]])
02-01                  UnorderedMuxExchange
03-01                    Project(c_integer=[$0], EXPR$1=[$1], E_X_P_R_H_A_S_H_F_I_E_L_D=[castInt(hash64AsDouble($1, hash64AsDouble($0)))])
03-02                      Project(c_integer=[$0], EXPR$1=[CASE(>($1, 0), CAST($2):ANY, null)])
03-03                        Window(window#0=[window(partition {} order by [0 DESC] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [COUNT($0), $SUM0($0)])])
03-04                          SelectionVectorRemover
03-05                            Sort(sort0=[$0], dir0=[DESC])
03-06                              Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///drill/testdata/subqueries/j1]], selectionRoot=/drill/testdata/subqueries/j1, numFiles=1, columns=[`c_integer`]]])

Query plan with planner.slice_target = 100000;

00-01      Project(c_integer=[$0], EXPR$1=[$1])
00-02        SelectionVectorRemover
00-03          Sort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
00-04            Project(c_integer=[$0], EXPR$1=[CASE(>($1, 0), CAST($2):ANY, null)])
00-05              Window(window#0=[window(partition {} order by [0 DESC] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [COUNT($0), $SUM0($0)])])
00-06                SelectionVectorRemover
00-07                  Sort(sort0=[$0], dir0=[DESC])
00-08                    Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///drill/testdata/subqueries/j1]], selectionRoot=/drill/testdata/subqueries/j1, numFiles=1, columns=[`c_integer`]]])

Attached:

• table j1
• test.res - result generated with postgres