[jira] [Updated] (DRILL-1445) Incorrect casting of aggregate

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

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

Aman Sinha updated DRILL-1445:
------------------------------
    Attachment: 0001-DRILL-1455-Add-return-type-inference-strategy-for-ar.patch

Uploaded the fix for this (in Optiq code base).  Added a return type inference strategy that checks the operands of an operator binding and if any of the operands is ANY type, it returns ANY.  This inference strategy is chained at the beginning of the precedence chain for the following return type inference rules:
 NULLABLE_SUM  (this is used for plus and minus)
 PRODUCT_NULLABLE (for multiply)
 QUOTIENT_NULLABLE (for division)

I have done manual testing of the above operations. 
[~mehant] Could you please review ?  thanks. 

Here's the explain plan changes:  (for the top Project note the cast to INTEGER in the first case and cast  to ANY in the second case): 

{panel}
Without the fix:

: jdbc:drill:zk=local> explain plan for select avg(n_nationkey + 1) from cp.`tpch/nation.parquet`;
+------------+------------+
|    text    |    json    |
+------------+------------+
| 00-00    Screen
00-01      Project(EXPR$0=[CAST(/(CastHigh(CASE(=($1, 0), null, $0)), $1)):INTEGER])
00-02        StreamAgg(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
00-03          Project($f0=[+($0, 1)])
00-04            Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=/tpch/nation.parquet]], selectionRoot=/tpch/nation.parquet, numFiles=1, columns=[SchemaPath [`n_nationkey`]]]])
'

With the fix:

: jdbc:drill:zk=local> explain plan for select avg(n_nationkey + 1) from cp.`tpch/nation.parquet`;
+------------+------------+
|    text    |    json    |
+------------+------------+
| 00-00    Screen
00-01      Project(EXPR$0=[CAST(/(CastHigh(CASE(=($1, 0), null, $0)), $1)):ANY])
00-02        StreamAgg(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
00-03          Project($f0=[+($0, 1)])
00-04            Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=/tpch/nation.parquet]], selectionRoot=/tpch/nation.parquet, numFiles=1, columns=[SchemaPath [`n_nationkey`]]]])
{panel}

> Incorrect casting of aggregate
> ------------------------------
>
>                 Key: DRILL-1445
>                 URL: https://issues.apache.org/jira/browse/DRILL-1445
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 0.5.0
>            Reporter: Steven Phillips
>            Assignee: Aman Sinha
>             Fix For: 0.7.0
>
>         Attachments: 0001-DRILL-1455-Add-return-type-inference-strategy-for-ar.patch
>
>
> The following query returns an int when it should be a float:
> 0: jdbc:drill:>  select avg(nationkey - 1) from nat2;
> +------------+
> |   EXPR$0   |
> +------------+
> | 11         |
> +------------+
> 1 row selected (0.27 seconds)
> without the literal, it is correct:
> 0: jdbc:drill:>  select avg(nationkey) from nat2;
> +------------+
> |   EXPR$0   |
> +------------+
> | 12.0       |
> +------------+
> 1 row selected (0.462 seconds)
> Here is the logical plan for the incorrect query:
> 0: jdbc:drill:> explain plan without implementation for select avg(nationkey - 1) from nat2;
> +------------+------------+
> |    text    |    json    |
> +------------+------------+
> | DrillScreenRel
>   DrillProjectRel(EXPR$0=[CAST(/(CastHigh(CASE(=($1, 0), null, $0)), $1)):INTEGER])
>     DrillAggregateRel(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
>       DrillProjectRel($f0=[-($0, 1)])
>         DrillScanRel(table=[[dfs, drill, nat2]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/drill/nat2]], selectionRoot=/drill/nat2, columns=[SchemaPath [`nationkey`]]]])
>  



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