[
https://issues.apache.org/jira/browse/CALCITE-5181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17550317#comment-17550317
]
xiong duan commented on CALCITE-5181:
-------------------------------------
[~libenchao] Thanks for your quick reply.
For the #1: According to SQL 2016 10.9<aggregate function> :
{noformat}
If SUM or AVG is specified, then:
i) DT shall be a numeric type or an interval type.
ii) If SUM is specified and DT is exact numeric with scale S, then the
declared type of the result is an implementation-defined exact numeric type
with scale S.
iii) If AVG is specified and DT is exact numeric, then the declared type of
the result is an implementation-defined exact numeric type with precision not
less than the precision of DT and scale not less than the scale of DT.
iv) If DT is approximate numeric, then the declared type of the result is
an implementation-defined approximate numeric with precision not less than the
precision of DT.
v) If DT is an interval, then the declared type of the result is an
interval with the same precision as DT.{noformat}
For the #2: I +1 for throwing an exception.
I think maybe we should use the BIGINT as the return data type when the
parameter is TINYINT、SMALLINT、INTEGER、BIGINT.
And for the other numeric function, I start a discussion about how to infer the
more accurate data type by
[email|https://lists.apache.org/thread/420r3g5ynd6c8cqkbo791wnhmfx59fn5].
Welcome to reply.
> SUM aggregate function return wong result
> -----------------------------------------
>
> Key: CALCITE-5181
> URL: https://issues.apache.org/jira/browse/CALCITE-5181
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.30.0
> Reporter: xiong duan
> Priority: Major
>
> To reproduce(dummy.iq):
> {code:java}
> !use scott
> !set outputformat mysql
> SELECT sum(empno) from "scott".emp; {code}
> Should return 108172, but return -22900.
> This is because RelDataTypeSystemImpl deriveSumType return a wrong datatype.
> We presume when the parameter is smllInt, the return datatype stay same as
> the parameter. So the result out of range.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
