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)
