drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Abhishek Girish (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-2191) Aggregation on columns with case statements returns wrong results
Date Mon, 09 Feb 2015 19:10:38 GMT
Abhishek Girish created DRILL-2191:
--------------------------------------

             Summary: Aggregation on columns with case statements returns wrong results
                 Key: DRILL-2191
                 URL: https://issues.apache.org/jira/browse/DRILL-2191
             Project: Apache Drill
          Issue Type: Bug
          Components: Execution - Flow
            Reporter: Abhishek Girish
            Assignee: Chris Westin


Dataset: TPCDS SF 1

The following queries returns wrong results:

*Query 1*
SELECT
    ss_quantity ,
    SUM(wholesale) AS sum_wholesale
FROM
    (
        SELECT
            ss_quantity ,
      sum(case when s.ss_quantity between 1 AND 2 then s.ss_wholesale_cost else 0 end) as
wholesale
        FROM
            store_sales s
        WHERE
           s.ss_quantity between 1 AND 2
        GROUP BY
                ss_quantity,
                        ss_store_sk
    ) dat
GROUP BY        ss_quantity
ORDER BY        ss_quantity;

*Query 2:*
SELECT
        c.c_birth_country,
        sum(case when s.ss_quantity between 1 AND 2 then s.ss_wholesale_cost else 0 end) as
wholesale
   FROM
        store_sales s
   JOIN
        customer c
     ON
        s.ss_customer_sk = c.c_customer_sk
GROUP BY
        c.c_birth_country
Order by c.c_birth_country;

The returned results have issues with decimal placement. Example:

Rows returned from Drill:
	3	2227134.0
	3	2128570.0

Rows returned from Postgres:
	3	22271.34
	3	21285.7

The results are correct when case statements are removed. 



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

Mime
View raw message