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

wrong plan for window function queries containing function(col1 + colb)

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      The following query has a wrong plan:

      explain plan for select position_id, salary, sum(salary) over (partition by position_id), sum(position_id + salary) over (partition by position_id) from cp.`employee.json` limit 20;
      +------+------+
      | text | json |
      +------+------+
      | 00-00    Screen
      00-01      ProjectAllowDup(position_id=[$0], salary=[$1], EXPR$2=[$2], EXPR$3=[$3])
      00-02        SelectionVectorRemover
      00-03          Limit(fetch=[20])
      00-04            Project(position_id=[$0], salary=[$1], w0$o0=[$2], w0$o00=[$4])
      00-05              Window(window#0=[window(partition {0} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($3)])])
      00-06                Project(position_id=[$1], salary=[$2], w0$o0=[$3], $3=[+($1, $2)])
      00-07                  Window(window#0=[window(partition {1} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($2)])])
      00-08                    SelectionVectorRemover
      00-09                      Sort(sort0=[$1], dir0=[ASC])
      00-10                        Project(T13¦¦*=[$0], position_id=[$1], salary=[$2])
      00-11                          Scan(groupscan=[EasyGroupScan [selectionRoot=classpath:/employee.json, numFiles=1, columns=[`*`], files=[classpath:/employee.json]]])
      

      The plan contains 2 window operators which shouldn't be possible according to DRILL-3196.

      The results are also incorrect.

      Depending on which aggregation or window function used we get wrong results or an IndexOutOfBounds exception

      Attachments

        Activity

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

          People

            seanhychu Sean Hsuan-Yi Chu
            adeneche Abdel Hakim Deneche
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment