drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aman Sinha (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-2063) Wrong result for query with aggregate expression
Date Fri, 23 Jan 2015 19:27:34 GMT
Aman Sinha created DRILL-2063:
---------------------------------

             Summary: Wrong result for query with aggregate expression
                 Key: DRILL-2063
                 URL: https://issues.apache.org/jira/browse/DRILL-2063
             Project: Apache Drill
          Issue Type: Bug
          Components: Query Planning & Optimization
            Reporter: Aman Sinha
            Assignee: Aman Sinha
            Priority: Critical


The following query gives wrong result for avg_price: 
{code}
0: jdbc:drill:zk=local> select l_suppkey, sum(l_extendedprice)/sum(l_quantity) as avg_price
from cp.`tpch/lineitem.parquet` where l_orderkey in (select o_orderkey from cp.`tpch/orders.parquet`
where o_custkey = 2) and l_suppkey = 4 group by l_suppkey;
+------------+------------+
| l_suppkey  | avg_price  |
+------------+------------+
| 4          | 0.1111111111111111 |
+------------+------------+
{code}

If I include the aggregate functions explicitly outside of the expression, I get the right
result: 
{code}
0: jdbc:drill:zk=local> select l_suppkey, sum(l_extendedprice) as total_price, sum(l_quantity)
as total_qty, sum(l_extendedprice)/sum(l_quantity) as avg_price from cp.`tpch/lineitem.parquet`
where l_orderkey in (select o_orderkey from cp.`tpch/orders.parquet` where o_custkey = 2)
and l_suppkey = 4 group by l_suppkey;
+------------+-------------+------------+------------+
| l_suppkey  | total_price | total_qty  | avg_price  |
+------------+-------------+------------+------------+
| 4          | 49480.92    | 36.0       | 1374.47    |
+------------+-------------+------------+------------+
{code}

Note that the wrong result  in the first query is because of using the wrong column for the
numerator of the division.  It is actually doing l_suppkey/total_qty  (4/36 = 0.11111).  
Since this is an egregious error, I am marking this critical. 



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

Mime
View raw message