[
https://issues.apache.org/jira/browse/CALCITE-6971?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17945919#comment-17945919
]
Zhen Chen commented on CALCITE-6971:
------------------------------------
[~mbudiu] Thanks for your reply. I realize I didn't identify the issue
precisely enough. The problem occurs after converting AVG(...) OVER (...) to
CASE WHEN expressions, where we failed to CAST the SUM function's return type
to match AVG's return type. Typically in databases, these two aggregate
functions don't have consistent return types. I've updated the JIRA ticket
title accordingly.
> Calculation accuracy loss after agg over to case when
> -----------------------------------------------------
>
> 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)
