You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Aman Sinha (JIRA)" <ji...@apache.org> on 2015/01/23 20:27:34 UTC
[jira] [Created] (DRILL-2063) Wrong result for query with aggregate
expression
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)