[
https://issues.apache.org/jira/browse/CALCITE-6080?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17781349#comment-17781349
]
Stamatis Zampetakis commented on CALCITE-6080:
----------------------------------------------
The following equivalence is the definition of STDEV_POX(x) present in SQL
standard.
{noformat}
STDDEV_POP(x) -> SQRT((SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / COUNT(x))
{noformat}
We cannot really say that the simplification is wrong because this is the
actual definition of the function.
The Java code in the description implies that there is some kind of problem in
the runtime (not in the rule) but the way it is right now it is hard to follow.
To showcase the problem it would be better to populate a table with columns of
different data types and and then show the result of the following queries in
various DBMS:
{code:sql}
SELECT STDDEV_POC(x) FROM ...
SELECT SQRT((SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / COUNT(x)) FROM ...
{code}
I would expect that the results would vary from system to system since all this
strongly depend on how arithmetic operations and types are handled.
The double data type by definition is an approximate numeric so I cannot
really say if the result should be 0.0 or NaN or something else.
Moreover, the SQL standard defines that the result of VAR_POP is an
implementation defined approximate numeric so in turn SQRT(VAR_POP(X)) is as
well an approximate numeric. A specific database vendor can define what is
going to be the result in every case.
> 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)
