[
https://issues.apache.org/jira/browse/DRILL-2191?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14511919#comment-14511919
]
Abhishek Girish commented on DRILL-2191:
----------------------------------------
Testcase: Functional/Passing/tpcds/bugs/DRILL-2191-1.sql, Testcase:
Functional/Passing/tpcds/bugs/DRILL-2191-2.sql
> 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: Query Planning & Optimization
> Reporter: Abhishek Girish
> Assignee: Aman Sinha
> Priority: Critical
> Fix For: 0.8.0
>
> Attachments: explain_plan_q1_with_case.txt,
> explain_plan_q1_without_case.txt
>
>
> 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)
