[
https://issues.apache.org/jira/browse/CALCITE-4924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17454284#comment-17454284
]
Julian Hyde commented on CALCITE-4924:
--------------------------------------
I'm a bit surprised that the standard encourages approximate types (e.g.
DOUBLE) for aggregate functions rather than exact types (e.g. DECIMAL). People
process currency in SQL. You cannot use approximate types for currency.
> 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)
