[
https://issues.apache.org/jira/browse/CALCITE-6516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17878205#comment-17878205
]
Mihai Budiu commented on CALCITE-6516:
--------------------------------------
The result type is always supposed to be (in Calcite) the same as the source
data type. This is about the intermediate result types, which can be different,
as long as you insert a cast converting the final result to the expected type.
This only makes a difference for DECIMAL types, since it's the only type where
the source and result types of arithmetic operations can be different.
But I think that [~lchistov1987]'s proposal is actually sound, since
aggregation in SQL does not specify the order of processing or the type of
intermediate results. So the consequence is that more programs will execute
successfully with this change.
> AVG to SUM+COUNT transformation inserts invalid CAST call
> ---------------------------------------------------------
>
> Key: CALCITE-6516
> URL: https://issues.apache.org/jira/browse/CALCITE-6516
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.37.0
> Reporter: Leonid Chistov
> Priority: Major
> Labels: pull-request-available
>
> As described in https://issues.apache.org/jira/browse/CALCITE-4911.
> In Calcite:
> {code:java}
> select avg(comm) as a from "scott".emp{code}
> This SQL physical plan is:
> {noformat}
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)],
> expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)],
> expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)],
> expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
> EnumerableTableScan(table=[[scott, EMP]]){noformat}
> Problem here is that cast of SUM result to DECIMAL(7, 2) is not correct and
> may cause overflow error at runtime or result in not expected data truncation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
