[
https://issues.apache.org/jira/browse/CALCITE-6516?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17871110#comment-17871110
]
Mihai Budiu commented on CALCITE-6516:
--------------------------------------
The SQL spec does not say what the result type of an aggregation function
should be.
Calcite currently implements the rule that is used in Oracle, where the
aggregation result type is the same as the data type that is being aggregated.
This seems like a reasonable rule, because it gives the user control over the
result type, by allowing users to cast the source data type to the desired
precision.
> 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
>
> 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)
