drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-4469) SUM window query returns incorrect results over integer data
Date Thu, 03 Mar 2016 10:23:18 GMT
Khurram Faraaz created DRILL-4469:
-------------------------------------

             Summary: SUM window query returns incorrect results over integer data
                 Key: DRILL-4469
                 URL: https://issues.apache.org/jira/browse/DRILL-4469
             Project: Apache Drill
          Issue Type: Bug
          Components: Execution - Flow
    Affects Versions: 1.6.0
         Environment: 4 node CentOS cluster
            Reporter: Khurram Faraaz
            Priority: Critical


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

{noformat}
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)
{noformat}

results from Postgres 9.3

{noformat}
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)
{noformat}

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

Results from Drill 1.6.0

{noformat}
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)
{noformat}

Results from Postgres

{noformat}
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)
{noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message