Zhen Chen created CALCITE-6971:
----------------------------------
Summary: 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
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)
