[jira] [Comment Edited] (CALCITE-4924) The COVAR aggregate function returns a wrong data type

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

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

duan xiong edited comment on CALCITE-4924 at 12/6/21, 1:19 PM:
---------------------------------------------------------------

I do some research about this type of aggregate function. According to the ISO-SQL-2016

{*}10.9 <aggregate function>{*}:
{noformat}
DTDVE: be the declared type of dependent variable expression
DTIVE: be the declared type of independent variable 
expression

the declared type of the result is an implementation-defined approximate numerictype.
If DTDVE is an approximate numeric type, then the precision of the result is not less than the precision of DTDVE. 
If DTIVE is an approximate numeric type, then the precision of the result is not less than the precision of DTIVE.{noformat}


was (Author: nobigo):
I do some research about this type of aggregate function. According to the ISO-SQL-2016:
{noformat}
DTDVE: be the declared type of dependent variable expression
DTIVE: be the declared type of independent variable 
expression

the declared type of the result is an implementation-defined approximate numerictype.
If DTDVE is an approximate numeric type, then the precision of the result is not less than the precision of DTDVE. 
If DTIVE is an approximate numeric type, then the precision of the result is not less than the precision of DTIVE.{noformat}

> The COVAR aggregate function returns a 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: duan xiong
>            Priority: Major
>
> According agg.iq unit test:
> {code:java}
> # [CALCITE-1776, CALCITE-2402] REGR_SXX, REGR_SXY, REGR_SYY
> SELECT
>   regr_sxx(COMM, SAL) as "REGR_SXX(COMM, SAL)",
>   regr_syy(COMM, SAL) as "REGR_SYY(COMM, SAL)",
>   regr_sxx(SAL, COMM) as "REGR_SXX(SAL, COMM)",
>   regr_syy(SAL, COMM) as "REGR_SYY(SAL, COMM)"
> from "scott".emp; {code}
> This SQL top physical is:
> {code:java}
> EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t0):DECIMAL(7, 2)], expr#7=[0], expr#8=[=($t2, $t7)], expr#9=[null:INTEGER], expr#10=[*($t1, $t1)], expr#11=[/($t10, $t2)], expr#12=[CASE($t8, $t9, $t11)], expr#13=[CAST($t12):DECIMAL(7, 2)], expr#14=[-($t6, $t13)], expr#15=[CAST($t14):DECIMAL(7, 2)], expr#16=[CAST($t3):DECIMAL(7, 2)], expr#17=[=($t5, $t7)], expr#18=[*($t4, $t4)], expr#19=[/($t18, $t5)], expr#20=[CASE($t17, $t9, $t19)], expr#21=[CAST($t20):DECIMAL(7, 2)], expr#22=[-($t16, $t21)], expr#23=[CAST($t22):DECIMAL(7, 2)], REGR_SXX(COMM, SAL)=[$t15], EXPR$1=[$t23], REGR_SXX(SAL, COMM)=[$t23], REGR_SYY(SAL, COMM)=[$t15]) {code}
> The result data type should be DECIMAL(7, 2). But the asserted result is:
> {noformat}
> +---------------------+---------------------+---------------------+---------------------+
> | REGR_SXX(COMM, SAL) | REGR_SYY(COMM, SAL) | REGR_SXX(SAL, COMM) | REGR_SYY(SAL, COMM) |
> +---------------------+---------------------+---------------------+---------------------+
> |          95000.0000 |        1090000.0000 |        1090000.0000 |          95000.0000 |
> +---------------------+---------------------+---------------------+---------------------+
> (1 row){noformat}
> Actually, this result value can't fit in DECIMAL(7, 2). 
> The Postgresql about this aggregation functions in [agg-functions|https://www.yiibai.com/manual/postgresql/functions-aggregate.html]. And I have tested the result is right.
>  



--
This message was sent by Atlassian Jira
(v8.20.1#820001)