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

    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. test.res
          232 kB
          Victoria Markman
        2. j1.tar
          1.04 MB
          Victoria Markman
        3. DRILL-3298.1.patch.txt
          1 kB
          Abdel Hakim Deneche

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: