[ https://issues.apache.org/jira/browse/CALCITE-6020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17767768#comment-17767768 ]
Julian Hyde edited comment on CALCITE-6020 at 9/22/23 1:12 AM: --------------------------------------------------------------- {{SUM0}} stores less information than {{SUM}}. Therefore there are situations where {{SUM0}} is easier to compute and still provides the needed data. Summary tables are one example. If you need {{SUM}} you can compute it from {{SUM0}} and {{COUNT}}: {{SUM\(x) === CASE COUNT\(x) WHEN 0 THEN NULL ELSE SUM0\(x)}} I can't make the claim that SUM0 is definitely needed anywhere. However I do claim that it is a better primitive than SUM, and we should be embracing the best primitives we can find. was (Author: julianhyde): {{SUM0}} stores less information than {{SUM}}. Therefore there are situations where {{SUM0}} is easier to compute and still provides the needed data. Summary tables are one example. If you need {{SUM}} you can compute it from {{SUM0}} and {{COUNT}}: {{SUM(x) === CASE COUNT(x) WHEN 0 THEN NULL ELSE SUM0(x)}} I can't make the claim that SUM0 is definitely needed anywhere. However I do claim that it is a better primitive than SUM, and we should be embracing the best primitives we can find. > SqlToRelConverter should not replace windowed SUM with equivalent expression > using SUM0 > --------------------------------------------------------------------------------------- > > Key: CALCITE-6020 > URL: https://issues.apache.org/jira/browse/CALCITE-6020 > Project: Calcite > Issue Type: Improvement > Reporter: Zoltan Haindrich > Assignee: Zoltan Haindrich > Priority: Major > Labels: pull-request-available > > {{SqlToRelConverter}} replaces {{SUM}} with {{SUM0}} around > [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L5885] > This might have been needed at some point in the past - but I think it will > be better to leave it as {{SUM}} - as in case there is no {{SUM0}} in the > system that will be replaced with a {{COALESCE(SUM(...) , 0 )}} to provide it > - as see > [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1288] -- This message was sent by Atlassian Jira (v8.20.10#820010)