Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-3298

Wrong result with SUM window function and order by without partition by in the OVER clause

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    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

      Attachments

        1. j1.tar
          1.04 MB
          Victoria Markman
        2. test.res
          232 kB
          Victoria Markman
        3. DRILL-3298.1.patch.txt
          1 kB
          Abdel Hakim Deneche

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            amansinha100 Aman Sinha
            vicky Victoria Markman
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment