[
https://issues.apache.org/jira/browse/CALCITE-6324?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825877#comment-17825877
]
Julian Hyde commented on CALCITE-6324:
--------------------------------------
You can't say the type is 'incorrect' when the standard leaves it
implementation-defined, and other implementations are inconsistent.
The reasoning is similar to AVG. By default, AVG applied to an INTEGER column
yields an INTEGER result. That is probably too few decimal digits in most
cases. But at least the rules are simple: the result type equals the argument
type. So it you want 3 digits to the right of the decimal from AVG, you can
simply apply a cast to its argument.
Your point that STDDEV's internal accumulator overflows seems to be somewhat
separate from the complaints about the return type. Because STDDEV's formula
sums the squares its arguments and then takes the square root, it makes sense
that the accumulator would have twice as many digits of precision as the result.
> Type inferred for result of STDDEV, VAR_SAMP, etc. is incorrect
> ---------------------------------------------------------------
>
> Key: CALCITE-6324
> URL: https://issues.apache.org/jira/browse/CALCITE-6324
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.36.0
> Reporter: Mihai Budiu
> Assignee: Mihai Budiu
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> These functions are all use the same type inference algorithm, essentially
> the algorithm used by AVG.
> But if the values processed are decimal, STDDEV (and others) need much higher
> precision to represent the result. (I am not sure that the inference is right
> for integer types either, btw.)
> This surfaced during the implementation of a fix for [CALCITE-6322]: if we
> use the type inferred for these functions, the result overflows and causes a
> runtime exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
