[jira] [Commented] (CALCITE-4924) REGR_SXX and similar aggregate functions return the wrong data type

["xiong duan (Jira)" <ji...@apache.org>]

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

xiong duan commented on CALCITE-4924:
-------------------------------------

Maybe we can't expect one return type for these functions. So I prepare to calculate the return type[same as AggregateReduceFunctionsRule]. For example, the AVG return type can be calculated by SUM/COUNT. And I prepare a [draft PR|https://github.com/apache/calcite/pull/2826] to implement this. And the dummy.iq has a test for this demo. Any feedback from you will be very appreciated.

> REGR_SXX and similar aggregate functions return the wrong data type
> -------------------------------------------------------------------
>
>                 Key: CALCITE-4924
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4924
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.28.0
>            Reporter: xiong duan
>            Assignee: xiong duan
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 20m
>  Remaining Estimate: 0h
>
> The SQL:
> {code:java}
> SELECT regr_sxx(SAL, COMM) as "REGR_SXX(SAL, COMM)" from "scott".emp; {code}
> This SQL top physical plan is:
> {code:java}
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):DECIMAL(7, 2)], expr#4=[0], expr#5=[=($t2, $t4)], expr#6=[null:INTEGER], expr#7=[*($t1, $t1)], expr#8=[/($t7, $t2)], expr#9=[CASE($t5, $t6, $t8)], expr#10=[CAST($t9):DECIMAL(7, 2)], expr#11=[-($t3, $t10)], expr#12=[CAST($t11):DECIMAL(7, 2)], REGR_SXX(SAL, COMM)=[$t12]){code}
> According to the physical plan, The data type of result should be DECIMAL(7, 2). But the asserted result is:
> {noformat}
> +---------------------+
> |REGR_SXX(SAL, COMM) |
> +---------------------+
> |       1090000.0000 | 
> +---------------------+
> (1 row){noformat}
> Actually, this result value can't fit in DECIMAL(7, 2). 
> The Postgresql description about this aggregation functions in [agg-functions|https://www.yiibai.com/manual/postgresql/functions-aggregate.html]. 
> The similar aggregations include COVAR_POP,COVAR_SAMP,REGR_SXX,REGR_SYY.



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