Khurram Faraaz created DRILL-4494: ------------------------------------- Summary: Window sum over integer column returns incorrect results. Key: DRILL-4494 URL: https://issues.apache.org/jira/browse/DRILL-4494 Project: Apache Drill Issue Type: Bug Components: Execution - Flow Affects Versions: 1.6.0 Environment: 4 node cluster CentOS Reporter: Khurram Faraaz Priority: Critical
Window sum over integer column returns incorrect results. Drill 1.6.0, git commit ID : git.commit.id=64ab0a8e {noformat} 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) {noformat} Postgres 9.3 {noformat} 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. {noformat} 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) {noformat} {noformat} 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) {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)