[
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:28 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}
For example:
regr_sxx(dependent variable expression, independent variable expression)
was (Author: nobigo):
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}
> 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)
