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

["Sergey Nuyanzin (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16588950#comment-16588950 ] 

Sergey Nuyanzin commented on CALCITE-2482:
------------------------------------------

Thank you for your catch! Will look at it

> 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
>            Priority: Major
>
> 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)