[DRILL-4469] SUM window query returns incorrect results over integer data

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.6.0
• Fix Version/s: 1.13.0
• Component/s: Query Planning & Optimization
• Labels: window_function

Environment

4 node CentOS cluster

• Assignee: Vova Vysotskyi
• Reporter: Khurram Faraaz
• Created: 03/Mar/16 10:22
• Updated: 19/Jan/18 11:23
• Resolved: 19/Jan/18 09:35

Description

SUM window query returns incorrect results as compared to Postgres, with or without the frame clause in the window definition. Note that there is a sub query involved and data in column c1 is sorted integer data with no nulls.

Drill 1.6.0 commit ID: 6d5f4983

Results from Drill 1.6.0

0: jdbc:drill:schema=dfs.tmp> SELECT SUM(c1) OVER w FROM (select * from dfs.tmp.`t_alltype`) subQry WINDOW w AS (PARTITION BY c8 ORDER BY c1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
+---------+
| EXPR$0  |
+---------+
| 10585   |
| 10585   |
| 10585   |
| 10585   |
| 10585   |
| 10585   |
...
| 10585  |
| 10585  |
| 10585  |
+--------+
145 rows selected (0.257 seconds)

results from Postgres 9.3

postgres=# SELECT SUM(c1) OVER w FROM (select * from t_alltype) subQry WINDOW w AS (PARTITION BY c8 ORDER BY c1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
 sum
------
 4499
 4499
 4499
 4499
 4499
 4499
...
 5613
 5613
 5613
  473
  473
  473
  473
  473
(145 rows)

Removing the frame clause from window definition, still results in completely different results on Postgres vs Drill

Results from Drill 1.6.0

0: jdbc:drill:schema=dfs.tmp>    SELECT SUM(c1) OVER w FROM (select * from t_alltype) subQry WINDOW w AS (PARTITION BY c8 ORDER BY c1);
+---------+
| EXPR$0  |
+---------+
| 10585   |
| 10585   |
| 10585   |
| 10585   |
| 10585   |
| 10585   |
| 10585   |
| 10585   |
| 10585   |
...
| 10585  |
| 10585  |
| 10585  |
| 10585  |
| 10585  |
+--------+
145 rows selected (0.28 seconds)

Results from Postgres

postgres=# SELECT SUM(c1) OVER w FROM (select * from t_alltype) subQry WINDOW w AS (PARTITION BY c8 ORDER BY c1);
 sum
------
    5
   12
   21
   33
   47
   62
   78
   96
  115
  135
  158
  182
  207
  233
  260
  289
...
4914
 5051
 5189
 5328
 5470
 5613
    8
   70
  198
  332
  473
(145 rows)

Issue Links

• depends upon: DRILL-3993 Rebase Drill on Calcite master branch
• is duplicated by: DRILL-4494 Window sum over integer column returns incorrect results.
• is related to: CALCITE-1944 Window function applied to sub-query with dynamic star gives wrong plan