[ https://issues.apache.org/jira/browse/CALCITE-6324?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17826852#comment-17826852 ]
Mihai Budiu commented on CALCITE-6324: -------------------------------------- Indeed, it is not clear what the precision and scale of the result should be for some of these functions. The precision required to fit the result depends not only on the value type, but also on the table size. If we leave the current type inference rules for these functions a program like the the following: {code:sql} select var_samp(sal) from emp; {code} which appears in redshift.iq, will fail with a runtime error because the result ~1398313.87 cannot be represented in the type DECIMAL(7, 2), the type of EMP.SAL. SQL server uses a higher precision even for short integer types for AVG: https://learn.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql?view=sql-server-ver16#return-types SQL server also returns FLOAT for the statistical aggregates. Postgres seems to use an "arbitrary precision" decimal for these values (Postgres DECIMALS can have up to billions of digits), based on testing. Oracle uses for the result the same type as the source data type: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/STDDEV.html In my PR I have used DECIMAL(2*precision, 2*scale) for the result for this class of functions, where DECIMAL(precision, scale) is the precision of the source data. I think that a larger result type is more user-friendly, since fewer programs will surprisingly fail at runtime if people forget to cast the data. If we keep the current type for the results we will have to adjust the accumulator types and change quite a few test programs to insert explicit casts before the aggregation. (I will have to check, we may need to change the accumulator types anyway, didn't look to see exactly how the evaluation is done.) Happy for a suggestion to solve this. > 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)