You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by "Matt P (Jira)" <ji...@apache.org> on 2022/06/06 14:54:00 UTC

[jira] [Created] (CALCITE-5182) STDDEV returning NaN values

Matt P created CALCITE-5182:
-------------------------------

             Summary: STDDEV returning NaN values
                 Key: CALCITE-5182
                 URL: https://issues.apache.org/jira/browse/CALCITE-5182
             Project: Calcite
          Issue Type: Bug
            Reporter: Matt P


Example w/ Calcite 1.30:
{code:java}
0: jdbc:calcite:model=src/test/resources/mode> SELECT STDDEV(x) FROM (VALUES 1+power(2,-52), 1+power(2,-51)) x;
+--------+
| EXPR$0 |
+--------+
| NaN    |
+--------+
1 row selected (0.577 seconds){code}
{{{}{}}}This is due to the numerical issues in the formula that is being used:
{code:java}
variance = avg(x*x) - avg(x)*avg(x){code}
Though mathematically this should always be non-negative, due to numerical errors it can be negative and thus when the square root is taken to get the STDDEV it results in NaN.



--
This message was sent by Atlassian Jira
(v8.20.7#820007)