Yaoxuan Wu created FLINK-40946:
----------------------------------
Summary: SUM / AVG on DECIMAL(38, s) over a join are rounded to
scale 6
Key: FLINK-40946
URL: https://issues.apache.org/jira/browse/FLINK-40946
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.3.0, 2.4.0
Reporter: Yaoxuan Wu
{code:java}
CREATE TEMPORARY VIEW a AS SELECT * FROM (VALUES (CAST(0.1234567891 AS
DECIMAL(38,10))),
(CAST(0.0000000001 AS
DECIMAL(38,10)))) AS a(x);
CREATE TEMPORARY VIEW b AS SELECT * FROM (VALUES (1), (2)) AS b(y);SELECT
SUM(a.x) FROM a, b; -- Flink: 0.2469140000 expected:
0.2469135784
SELECT SUM(CAST(a.x AS DECIMAL(20,10))) FROM a, b; -- 0.2469135784 (correct)
{code}
The result type is DECIMAL(38, 10), but the value is rounded to 6 decimal
places. The aggregate is pushed below the join and multiplied by the row count
of the other side:
{code:java}
Calc(select=[CAST((s * $f0) AS DECIMAL(38, 10)) AS $f2]) +-
NestedLoopJoin(joinType=[InnerJoin], where=[true], select=[s, $f0],
build=[left], singleRowJoin=[true]) :- HashAggregate(isMerge=[true],
select=[Final_SUM(sum$0) AS s]) +- HashAggregate(isMerge=[true],
select=[Final_COUNT(count1$0) AS $f0]) {code}
{{DECIMAL(38, 10) * BIGINT}} is typed {{{}DECIMAL(38, 6){}}}, so the product is
rounded before it is cast back to {{{}DECIMAL(38, 10){}}}. With {{DECIMAL(20,
10)}} the product fits and the result is exact. AVG is affected the same way
(e.g. one row -690.3023534438 joined with 4 rows gives AVG -690.3023535000).
Also reproduces on current master (2.4-SNAPSHOT, a47e4bf).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
