[jira] [Created] (CALCITE-2482) Fix result for COVAR_POP, COVAR_SAMP, REGR_SXX, REGR_SYY

["Caizhi Weng (JIRA)" <ji...@apache.org>]

Caizhi Weng created CALCITE-2482:
------------------------------------

             Summary: Fix result for COVAR_POP, COVAR_SAMP, REGR_SXX, REGR_SYY
                 Key: CALCITE-2482
                 URL: https://issues.apache.org/jira/browse/CALCITE-2482
             Project: Calcite
          Issue Type: Bug
          Components: core
            Reporter: Caizhi Weng
            Assignee: Julian Hyde


There are two situations where REGR_SXX, REGR_SYY, COVAR_POP and COVAR_SAMP may produce undesired results using the current implementation provided from the pull request of [CALCITE 2402].

*1. When a filter is applied along with REGR_SXX, REGR_SYY, etc., the filter will be ignored due to the bug when reducing these aggregate functions.*

Consider the following query:
{code:sql}
SELECT
  regr_sxx(a, b) filter (where a <= 0) as "REGR_SXX(A, B)"
from (values (1, 1), (2, 2), (3, 3)) as t(a, b);
{code}

It's easy to discover that the answer should be null, as no row matches the filter condition. But the current implementation gives 2 as the answer.

*2. If the user implements the {{deriveCovarType}} of the {{RelDataTypeSystem}}, the result of REGR_SXX, REGR_SYY, etc. might not be the desired result.*

Consider the following query:
{code:sql}
SELECT
  regr_sxx(a, b) as "REGR_SXX(A, B)"
from (values (1, 1), (2, 2)) as t(a, b);
{code}

Current implementation in Calcite provides 1 as the answer (precisely speaking, it's 0.5 rounds up to 1).

This result is OK for the default implementation of {{deriveCovarType}}. But if the user implements his own {{deriveCovarType}} (for example, he explicitly sets the result type of the function to {{double}}), 1 is not the desired result.

What we need to do is to fix the filter problem, and make sure that the operands of the division operation in REGR_SXX are of the desired type.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)