[
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)
