[
https://issues.apache.org/jira/browse/DRILL-1445?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14197209#comment-14197209
]
Aman Sinha commented on DRILL-1445:
-----------------------------------
I did additional tests by creating a view on nullable column in TpcDs as well
as on the base table's nullable column (previous tests were with non-nullable
column).
{code:sql}
0: jdbc:drill:zk=local> create view vv1 as select cast(wr_net_loss as double)
as wr_net_loss from dfs.TpcDs.web_returns;
+------------+------------+
| ok | summary |
+------------+------------+
| true | View 'vv1' created successfully in 'dfs.tmp' schema |
+------------+------------+
: jdbc:drill:zk=local> explain plan for select avg(wr_net_loss+2) from vv1 ;
+------------+------------+
| text | json |
+------------+------------+
| 00-00 Screen
00-01 Project(EXPR$0=[CAST(/(CastHigh(CASE(=($1, 0), null, $0)),
$1)):DOUBLE])
00-02 StreamAgg(group=[{}], agg#0=[$SUM0($0)], agg#1=[COUNT()])
00-03 Project($f0=[+(CAST(CAST($0):DOUBLE):DOUBLE NOT NULL, 2)])
00-04 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/Users/asinha/data/tpcds/web_returns]],
selectionRoot=/Users/asinha/data/tpcds/web_returns, numFiles=1,
columns=[SchemaPath [`wr_net_loss`]]]])
0: jdbc:drill:zk=local> select avg(wr_net_loss+2) as avg_loss from vv1 ;
+------------+
| avg_loss |
+------------+
| 731.1760976616293 |
+------------+
{code}
Note that the top Project in this case does cast the output to a Double which
is what we expect.
> 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)
