ClickHouse / ClickHouse/ClickHouse
Decimal overflow while multiplicating 2 decimals
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe the unexpected behaviour**
While multiplying 2 Decimals (Decimal(18, 2) and Decimal(18, 9)) Iget the result Decimal(18, 11), but there are 19 digits in the result. If I slightly increase the second decimal, I get Decimal overflow exception.
**How to reproduce**
The first multiplication
```
SELECT
toDecimal64(13142889.11, 2) as a,
toTypeName(a) as type_a,
toDecimal64(7.017766002, 9) as multiplier,
toTypeName(multiplier) as type_mp,
a*multiplier as res_1,
toTypeName(res_1) as type_res_1
```
And the result is
```
┌───────────a─┬─type_a─────────┬──multiplier─┬─type_mp────────┬────────────────res_1─┬─type_res_1──────┐
│ 13142889.11 │ Decimal(18, 2) │ 7.017766002 │ Decimal(18, 9) │ 92233720.36421403822 │ Decimal(18, 11) │
└─────────────┴────────────────┴─────────────┴────────────────┴──────────────────────┴─────────────────┘
```
The second multiplication
```
SELECT
toDecimal64(13142889.11, 2) as a,
toTypeName(a) as type_a,
toDecimal64(7.017766003, 9) as multiplier,
toTypeName(multiplier) as type_mp,
a*multiplier as res_1,
toTypeName(res_1) as type_res_1
```
And I get exception ```Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Decimal math overflow: While processing toDecimal64(13142889.11, 2) AS a, toTypeName(a) AS type_a, toDecimal64(7.017766003, 9) AS multiplier, toTypeName(multiplier) AS type_mp, a * multiplier AS res_1, toTypeName(res_1) AS type_res_1. (DECIMAL_OVERFLOW)```
**Expected behavior**
I expect the first multiplication to fail, since the ceiling for the Decimal(18, 11) is 9 999 999.99999999999
**Additional context**
I was told on bugcrowd that this might occur since Decimal64 is treated as Int64, which means that the ceiling is 9223372036854775807. This number has 19 digits and is higher than the first result if we put comma after 11 digits from the right. If I increase the multiplier by 0.000000001 I get greater number which causes exception.
From documentation this behavior is unexpected.
I've checked this issue on 21.2.9.41, 22.8.4.7 and 22.9.3.18
Contributor guide
Assessment
This issue has not been assessed yet.