[DRILL-3292] SUM(constant) OVER(...) returns wrong results

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.0.0
• Fix Version/s: 1.2.0
• Component/s: Execution - Relational Operators, Query Planning & Optimization
• Labels: window_function
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Abdel Hakim Deneche
• Created: 15/Jun/15 16:13
• Updated: 04/Sep/15 00:17
• Resolved: 08/Jul/15 22:31

Description

The following query returns wrong results:

0: jdbc:drill:> select sum(1) over w sum1, sum(5) over w sum5 from cp.`employee.json` where position_id = 2 window w as (partition by position_id);
+-------+-------+
| sum1  | sum5  |
+-------+-------+
| 6     | 6     |
| 6     | 6     |
| 6     | 6     |
| 6     | 6     |
| 6     | 6     |
| 6     | 6     |
+-------+-------+

The second column should display 30 (5 x 6) instead of 6.

Here is the plan for the query:

00-00    Screen
00-01      Project(sum1=[$0], sum5=[$1])
00-02        Project(sum1=[$0], sum5=[$1])
00-03          Project($0=[$1], $1=[$2])
00-04            Window(window#0=[window(partition {0} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1), SUM($2)])])
00-05              SelectionVectorRemover
00-06                Sort(sort0=[$0], dir0=[ASC])
00-07                  Filter(condition=[=($0, 2)])
00-08                    Scan(groupscan=[EasyGroupScan [selectionRoot=/employee.json, numFiles=1, columns=[`position_id`], files=[classpath:/employee.json]]])