[jira] [Updated] (DRILL-2063) Wrong result for query with aggregate expression

["Aman Sinha (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DRILL-2063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Aman Sinha updated DRILL-2063:
------------------------------
    Attachment: 0001-DRILL-2063-Make-sure-internal-fields-in-the-row-type.patch
                0001-DRILL-2063-Fix-is-in-Calcite-ensure-uniqueness-of-fi.patch

Uploaded calcite patch and added test in Drill.  [~jni] could you pls review ?  Note that once Drill is rebased on master branch of calcite, the changes in this patch would likely be subsumed by the changes for deriving row type of Aggregate rels.

> 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
>         Attachments: 0001-DRILL-2063-Fix-is-in-Calcite-ensure-uniqueness-of-fi.patch, 0001-DRILL-2063-Make-sure-internal-fields-in-the-row-type.patch
>
>
> 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)