[
https://issues.apache.org/jira/browse/HIVE-28925?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17947053#comment-17947053
]
Stamatis Zampetakis commented on HIVE-28925:
--------------------------------------------
The extra cast to DOUBLE around COALESCE is added when the
HiveAggregateReduceFunctionsRule is executed to reduce SUM0 to SUM with
COALESCE. The CAST is added for nullability purposes since the return type of
COALESCE is a NOT NULLABLE DOUBLE. Before the upgrade the return type was
NULLABLE DOUBLE and the CAST was eventually removed by the simplifier.
The difference is caused by the way RexBuilder creates literals (in this case
the zero literal) that changed with the commit
[56a86a032ac05ed522846910eea6f884b31820e3|https://github.com/apache/calcite/commit/56a86a032ac05ed522846910eea6f884b31820e3].
Refactor: Change return type of RelBuilder.literal from RexNode to RexLiteral
> Redundant CAST around COALESCE in aggregate query with SUM0
> -----------------------------------------------------------
>
> Key: HIVE-28925
> URL: https://issues.apache.org/jira/browse/HIVE-28925
> Project: Hive
> Issue Type: Improvement
> Components: CBO
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
>
> After the Calcite upgrade to 1.33.0 (HIVE-27102) a redundant CAST appears on
> aggregate queries with SUM0.
> {code:sql}
> CREATE TABLE test (c_numeric STRING);
> EXPLAIN CBO SELECT `$SUM0`(c_numeric) FROM test;
> {code}
> {noformat}
> HiveProject(_o__c0=[CAST(COALESCE($0, 0E0:DOUBLE)):DOUBLE])
> HiveAggregate(group=[{}], agg#0=[sum($0)])
> HiveTableScan(table=[[default, test]], table:alias=[test])
> {noformat}
> The CAST to DOUBLE is redundant since the result of COALESCE is always a
> DOUBLE.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
