[DRILL-3580] wrong plan for window function queries containing function(col1 + colb)

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.1.0
• Fix Version/s: 1.2.0
• Component/s: Query Planning & Optimization
• Labels: window_function
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Abdel Hakim Deneche
• Created: 30/Jul/15 19:23
• Updated: 15/Sep/15 19:22
• Resolved: 11/Sep/15 05:41

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