[
https://issues.apache.org/jira/browse/FLINK-34376?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17815091#comment-17815091
]
xuyang commented on FLINK-34376:
--------------------------------
This bug is introduced by https://issues.apache.org/jira/browse/FLINK-22586 .
We can simplify the sql to re-produce this bug:
{code:java}
select cast(9.000000000000000011 AS DECIMAL(38,18)) * 10
+----+------------------------------------------+
| op | EXPR$0 |
+----+------------------------------------------+
| +I | 90.0000000 |
+----+------------------------------------------+ {code}
This is a designed behavior(but there seems to be some problems). For the
multiplication of Decimal types, the following formula is currently used.
{code:java}
// ========================= Decimal Precision Deriving
==========================
// Adopted from
"https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-
// scale-and-length-transact-sql"
//
// Operation Result Precision Result Scale
// e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
// e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
// e1 * e2 p1 + p2 + 1 s1 + s2
// e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
// e1 % e2 min(p1-s1, p2-s2) + max(s1, s2) max(s1, s2)
//
// Also, if the precision / scale are out of the range, the scale may be
sacrificed
// in order to prevent the truncation of the integer part of the decimals.
{code}
For Integer type, the default precision is 10 and the scale is 0. So the result
precision and scale is (49, 18). However, the precision exceeds the max
precision 38, then it chooses to adjust scale from 18 to 7:
{code:java}
integer part: 49 - 18 = 31
adjusted scale: 38 - 31 = 7{code}
IMO, the original design that choose to keep the integer part of the completion
makes sense. But in this case, the result is wrong and we should fix it (by
verifying mysql the result is `90.000000000000000110`).
> FLINK SQL SUM() causes a precision error
> ----------------------------------------
>
> Key: FLINK-34376
> URL: https://issues.apache.org/jira/browse/FLINK-34376
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Runtime
> Affects Versions: 1.14.3, 1.18.1
> Reporter: Fangliang Liu
> Priority: Major
> Attachments: image-2024-02-06-11-15-02-669.png,
> image-2024-02-06-11-17-03-399.png
>
>
> {code:java}
> select cast(sum(CAST(9.000000000000000011 AS DECIMAL(38,18)) *10 ) as STRING)
> {code}
> The precision is wrong in the Flink 1.14.3 and master branch
> !image-2024-02-06-11-15-02-669.png!
>
> The accuracy is correct in the Flink 1.13.2
> !image-2024-02-06-11-17-03-399.png!
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
