You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "duan xiong (Jira)" <ji...@apache.org> on 2021/12/04 12:33:00 UTC
[jira] [Updated] (CALCITE-4924) The COVAR aggregate function returns a wrong data type
[ https://issues.apache.org/jira/browse/CALCITE-4924?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
duan xiong updated CALCITE-4924:
--------------------------------
Description:
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.
was:
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.
> 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)