[
https://issues.apache.org/jira/browse/CALCITE-6971?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17945880#comment-17945880
]
Mihai Budiu commented on CALCITE-6971:
--------------------------------------
I don't think this has anything to do with optimization rules.
This is about type checking.
The default type for an aggregate is the type of the input values. If your
values are integers, the result will be an integer. You can do two things:
* cast the values to another type, like DECIMAL
* change the default type system. There are functions to control the
aggregation result type. But I don't know if there are enumerable-based
implementations for all these possibilities
> Calculation accuracy loss after using PROJECT_OVER_SUM_TO_SUM0_RULE
> -------------------------------------------------------------------
>
> Key: CALCITE-6971
> URL: https://issues.apache.org/jira/browse/CALCITE-6971
> Project: Calcite
> Issue Type: Bug
> Reporter: Zhen Chen
> Priority: Major
>
> As testWindowedSum0BecomesCoalesce the sql
>
> {code:java}
> select
> AVG("net_weight") OVER (order by "product_id" rows 3 preceding)
> from "product"; {code}
> can be convert to
>
>
> {code:java}
> SELECT CASE
> WHEN COUNT("net_weight") OVER (ORDER BY "product_id" ROWS BETWEEN 3
> PRECEDING AND CURRENT ROW) > 0 THEN COALESCE(SUM("net_weight") OVER (ORDER BY
> "product_id" ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 0)
> ELSE NULL
> END / COUNT("net_weight") OVER (ORDER BY "product_id" ROWS BETWEEN 3
> PRECEDING AND CURRENT ROW)
> FROM "product"; {code}
> But the execution results are different in pgsql.
> {code:java}
> create table product (product_id int, net_weight int);
> insert into product values (1,1),(1,2),(2,1),(2,2),(null,1),(1,null),(null,
> null); {code}
> Result is
> {code:java}
> -- first sql
> avg
> ------------------------
> 1.00000000000000000000
> 1.00000000000000000000
> 1.5000000000000000
> 1.6666666666666667
> 1.6666666666666667
> 1.5000000000000000
> 1.3333333333333333
> (7 rows)
> -- second sql
> ?column?
> ----------
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> (7 rows){code}
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
