[
https://issues.apache.org/jira/browse/CALCITE-6080?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17782259#comment-17782259
]
Stamatis Zampetakis commented on CALCITE-6080:
----------------------------------------------
STDDEV_POP is defined based on other primitive functions so from my perspective
the expanded and not expanded alternative should both return the same result no
matter which runtime is used. If the results are not the same then it looks
more like a runtime problem rather than a rule problem.
> The simplified form after applying AggregateReduceFunctionsRule is giving
> wrong results for STDDEV, Covariance with double and decimal types.
> ---------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6080
> URL: https://issues.apache.org/jira/browse/CALCITE-6080
> Project: Calcite
> Issue Type: Bug
> Reporter: Dayakar M
> Assignee: Dayakar M
> Priority: Major
>
> The simplified form after applying AggregateReduceFunctionsRule is giving
> wrong results for STDDEV, Covariance with double and decimal types.
> For example, after applying AggregateReduceFunctionsRule
> {noformat}
> STDDEV_POP(x) -> SQRT((SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / COUNT(x))
> {noformat}
> for x as double/decimal, it is giving wrong result which can be easily
> reproducible with below simple java code
>
> {code:java}
> double input1 = 23.79d;
> double o1 = input1 * input1;
> System.out.println("ip*ip=" + o1);
> double sum = o1 + o1 + o1;
> System.out.println("Sum(ip*ip)="+sum); double sum1 = input1 + input1 +
> input1;
> System.out.println("Sum(ip)="+sum1);
> double sum2 = sum1 * sum1;
> System.out.println("Sum(ip)*Sum(ip)="+ sum2);
> double fin = sum2/3d;
> System.out.println("Sum(ip)*Sum(ip)/3="+fin);
> double fin1 = sum - fin;
> System.out.println("Sum(ip*ip)-Sum(ip)*Sum(ip)/3=" + fin1);
> System.out.println("SQRT((Sum(ip*ip)-Sum(ip)*Sum(ip)/3)/3)=" +
> Math.sqrt(fin1/3));{code}
> The output is
> {code:java}
> ip*ip=565.9640999999999
> Sum(ip*ip)=1697.8922999999998
> Sum(ip)=71.37
> Sum(ip)*Sum(ip)=5093.6769
> Sum(ip)*Sum(ip)/3=1697.8923000000002
> Sum(ip*ip)-Sum(ip)*Sum(ip)/3=-4.547473508864641E-13
> SQRT((Sum(ip*ip)-Sum(ip)*Sum(ip)/3)/3)=NaN {code}
> The final output should be *0.0* but here it is coming as {*}NaN{*}.
> So for double and decimal type data we should not simplify STDDEV, Covariance
> functions as it leads to wrong results.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
