[DRILL-4494] Window sum over integer column returns incorrect results.

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Duplicate
• Affects Version/s: 1.6.0
• Fix Version/s: 1.7.0
• Component/s: Execution - Relational Operators
• Labels: window_function

Environment

4 node cluster CentOS

• Assignee: Abdel Hakim Deneche
• Reporter: Khurram Faraaz
• Created: 10/Mar/16 06:12
• Updated: 10/Mar/16 06:50
• Resolved: 10/Mar/16 06:50

Description

Window sum over integer column returns incorrect results.

Drill 1.6.0, git commit ID : git.commit.id=64ab0a8e

Note that Drill returns the same value for SUM(c2) in the below query.

0: jdbc:drill:schema=dfs.tmp> select SUM(c2) OVER w as w_sum from ( SELECT * FROM t_alltype ORDER BY c1,c2 DESC NULLS LAST ) subquery WINDOW w AS (PARTITION BY c8 ORDER BY c2 DESC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
+---------------+
|     w_sum     |
+---------------+
| -16323034011  |
| -16323034011  |
| -16323034011  |
...
| -16323034011  |
| -16323034011  |
| -16323034011  |
| -16323034011  |
| -16323034011  |
+---------------+
145 rows selected (0.683 seconds)

Postgres 9.3

postgres=#   select SUM(c2) OVER w as w_sum from ( SELECT * FROM t_alltype ORDER BY c1,c2 DESC NULLS LAST ) subquery WINDOW w AS (PARTITION BY c8 ORDER BY c2 DESC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
    w_sum
-------------
 -8995559793
 -8995559793
 -8995559793
 -8995559793
 -8995559793
 -8995559793
 -8995559793
 ...
 -9169127412
 -9169127412
 -9169127412
 -9169127412
 -9169127412
 -9169127412
 -9169127412
 -9169127412
 ...
  1841653194
  1841653194
  1841653194
  1841653194
  1841653194
(145 rows)

Both in Drill and on Postgres there are 142 distinct rows and three nulls of the 145 total rows, in column c2.

0: jdbc:drill:schema=dfs.tmp> select count(distinct c2) from t_alltype;
+---------+
| EXPR$0  |
+---------+
| 142     |
+---------+
1 row selected (0.682 seconds)
0: jdbc:drill:schema=dfs.tmp> select c2 from t_alltype where c2 is null;
+-------+
|  c2   |
+-------+
| null  |
| null  |
| null  |
+-------+
3 rows selected (0.389 seconds)

postgres=#  select count(distinct c2) from t_alltype;
 count
-------
   142
(1 row)

postgres=# select c2 from t_alltype where c2 is null;
 c2
----



(3 rows)

Issue Links

• duplicates: DRILL-4469 SUM window query returns incorrect results over integer data