mihaibudiu commented on code in PR #3733: URL: https://github.com/apache/calcite/pull/3733#discussion_r1576704016
########## babel/src/test/resources/sql/redshift.iq: ########## @@ -523,78 +523,78 @@ select deptno, ratio_to_report(sal) over (partition by deptno) from emp; !} # STDDEV_POP -select empno, stddev_pop(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1; +select empno, stddev_pop(CAST(comm AS DECIMAL(12, 4))) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1; Review Comment: Calcite does not specify anywhere the type of the results produced by an aggregate function. However, the type inference code for aggregates implies that the result type is the same as the data type. If this is correct, these results were wrong, because the scale of the results is not the same as the scale of the input data. As you see, many of these problems stem from the fact that there is no written spec for what Calcite should be doing. I don't have access to an Oracle database to check. My knowledge of Oracle for aggregations comes from their published documentation, e.g.: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/STDDEV.html Postgres returns double if inputs are integers, or DECIMAL otherwise, a completely different behavior from Oracle or Calcite. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
