LantaoJin commented on PR #4193: URL: https://github.com/apache/calcite/pull/4193#issuecomment-2658723825
> DOUBLE is almost never the right type in SQL, since computations on double values are in general non-deterministic. If any type is right, a variant of DECIMAL should be used for AVG. Can you propose an algorithm to choose the precision and scale for the result? The `CUME_DIST` and `PERCENT_RANK` return DOUBLE type in Calcite too. Any specific reason to use DECIMAL for AVG? And in Calcite, the default precision is 15 for DOUBLE, 17 for DECIMAL. Will using the default DOUBLE precision in `AVG` result in non-deterministic? In Postgres, the DOUBLE precision for `AVG` calculated based on the input values as follows (not quite sure for now): > precision = max(precision(input_values) + ceil(log10(count(input_values))), scale(input_values) + decimal_places) Not sure what precision algorithm would be the best, but definitely not return integer for `AVG` by default. -- 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]
