[jira] [Created] (DRILL-2191) Aggregation on columns with case statements returns wrong results

["Abhishek Girish (JIRA)" <ji...@apache.org>]

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)