[
https://issues.apache.org/jira/browse/FLINK-22182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17331446#comment-17331446
]
Shuo Cheng edited comment on FLINK-22182 at 4/25/21, 8:12 AM:
--------------------------------------------------------------
Hi [~maver1ck], I think the result is not wrong, which involve the precision
derivation of decimal data. (You can see details here
[https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql).]
Briefly,
* (38, 4) / (38, 4) -> (38, 6)
* (38, 4) / (10, 0) -> (38, 6)
`(CAST(28604 as decimal(38,4)))/(cast(451 as decimal(38,4))) - 1121` =
`-1057.576497`
then
`-1057.576497 / 10` = -105.757650
(You can see the same result in other system, e.g, Spark).
Actually, the result for decimal data after multiple `CAST`s may lose some
precision. So if you want more 'accurate' result, you can preserve more scale.
was (Author: icshuo):
Hi [~maver1ck], I think the result is not wrong, which involve the precision
derivation of decimal data. (You can see details here
[https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql).]
Briefly,
-> (38, 4) / (38, 4) -> (38, 6)
-> (38, 4) / (10, 0) -> (38, 6)
`(CAST(28604 as decimal(38,4)))/(cast(451 as decimal(38,4))) - 1121` =
`-1057.576497`
then
`-1057.576497 / 10` = -105.757650
(You can see the same result in other system, e.g, Spark).
Actually, the result for decimal data after multiple `CAST`s may lose some
precision. So if you want more 'accurate' result, you can preserve more scale.
> Incorrect round when dividing decimals
> --------------------------------------
>
> Key: FLINK-22182
> URL: https://issues.apache.org/jira/browse/FLINK-22182
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Runtime
> Affects Versions: 1.12.2
> Reporter: Maciej Bryński
> Priority: Major
>
> Hi,
> I have following problem:
> This query:
> {code:java}
> select ((CAST(28604 as decimal(38,4)))/(cast(451 as decimal(38,4))) -
> 1121)/10 ;
> {code}
> Gives following answer:
> {code:java}
> -105.757650000000000000
> {code}
> Correct answer should be:
> {code:java}
> -105,75764966740576496674057649667
> {code}
> Why the result is rounded to 5th decimal place ?
> Saving both values back to Decimal(38,4) column will give different results:
> -105.7577 vs -105.7576 (where second one is correct)
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
