bug: Incorrect decimal division result

[yufan022]

Search before asking

• I had searched in the issues and found no similar issues.

Version

main

What's Wrong?

SELECT 404.754480000000000000000000 / 563.653044520000000000000000;
+---------------------------------------------------------------+
| (404.754480000000000000000000 / 563.653044520000000000000000) |
+---------------------------------------------------------------+
|                                    0.000000000000018945439744 |
+---------------------------------------------------------------+
1 row in set (0.14 sec)

SELECT cast(2684.987000000000000000000000 as decimal(76,37)) /  cast(1622.577931230000000000000000 as decimal(76,37));
+-------------------------------------------------------------------------------------------------------------------+
| (cast(2684.987000000000000000000000 as decimal(76, 37)) / cast(1622.577931230000000000000000 as decimal(76, 37))) |
+-------------------------------------------------------------------------------------------------------------------+
|                                                                           0.2275053839625715045037111710786532777 |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.14 sec)

create table decimal_test2(a decimal(28,8), b decimal(24,16));
insert into decimal_test2 values(300.07878791,5325.0000000000000000),(2.00000000,10491.0000000000000000);
select sum(a * b) / sum(a * b) from decimal_test2;
+-------------------------------+
| (sum((a * b)) / sum((a * b))) |
+-------------------------------+
|    0.000000000000000097402409 |
+-------------------------------+

create table decimal_test(a decimal(76,37), b decimal(76,37));
insert into decimal_test values(2684.987000000000000000000000,1622.577931230000000000000000);
select a/b from decimal_test;
+-----------------------------------------+
| (a / b)                                 |
+-----------------------------------------+
| 0.2275053839625715045037111710786532777 |
+-----------------------------------------+

SELECT cast(2684.987000000000000000000000 as decimal(60,30)) /  cast(1622.577931230000000000000000 as decimal(60,30));
+-------------------------------------------------------------------------------------------------------------------+
| (cast(2684.987000000000000000000000 as decimal(60, 30)) / cast(1622.577931230000000000000000 as decimal(60, 30))) |
+-------------------------------------------------------------------------------------------------------------------+
|                                                                                  0.000000000000000000000000004649 |
+-------------------------------------------------------------------------------------------------------------------+

How to Reproduce?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[yufan022]

SELECT cast(2684.987000000000000000000000 as decimal(76,37)) /  cast(1622.577931230000000000000000 as decimal(76,37));
databend:0.2275053839625715045037111710786532777
tidb: ERROR 1426 (42000): Too big precision 76 specified for column '2684.987000000000000000000000'. Maximum is 65.
mysql: [2024-06-20 21:43:31] [42000][1426] Too-big precision 76 specified for '2684.987000000000000000000000'. Maximum is 65.

create table decimal_test(a decimal(76,37), b decimal(76,37));
insert into decimal_test values(2684.987000000000000000000000,1622.577931230000000000000000);
select a/b from decimal_test;
databend :0.2275053839625715045037111710786532777
tidb: 创建表报错ERROR 1425 (42000): Too big scale 37 specified for column 'a'. Maximum is 30.
mysql: 创建表报错[42000][1425] Too big scale 37 specified for column 'a'. Maximum is 30.

# 以上两条 都是属于精度太大了问题，但是下面的结果不应该错吧。

create table decimal_test2(a decimal(28,8), b decimal(24,16));
insert into decimal_test2 values(300.07878791,5325.0000000000000000),(2.00000000,10491.0000000000000000);
select sum(a * b) / sum(a * b) from decimal_test2;
databend:0.000000000000000097402409
tidb:1.0000000000000000000000000000
mysql:1.0000000000000000000000000000

SELECT 404.754480000000000000000000 / 563.653044520000000000000000;
databend: 0.000000000000018945439744
tidb: 0.7180915350944017996839045975
mysql: 0.7180915350944017996839045975

create table decimal_test3(a decimal(60,30), b decimal(60,30));
insert into decimal_test3 values(2684.987000000000000000000000,1622.577931230000000000000000);
select a/b from decimal_test3;
databend:1.654766127605740121859625965770
tidb:1.654766127605740121859625965770
mysql:1.654766127605740121859625965770

SELECT cast(2684.987000000000000000000000 as decimal(60,30)) /  cast(1622.577931230000000000000000 as decimal(60,30));
databend:0.000000000000000000000000004649
tidb:1.654766127605740121859625965770
mysql:1.654766127605740121859625965770

[yufan022]

SELECT 404.754480000000000000000000 / 563.653044520000000000000000;
databend: 0.000000000000018945439744
tidb: 0.7180915350944017996839045975
mysql: 0.7180915350944017996839045975
clickhouse: 0.7180915350944018
snowflake: 0.71809153509

drop table decimal_test2;
create table decimal_test2(a decimal(28,8), b decimal(24,16));
insert into decimal_test2 values(300.07878791,5325.0000000000000000),(2.00000000,10491.0000000000000000);
select sum(a * b) / sum(a * b) from decimal_test2;
databend:0.000000000000000097402409
tidb:1.0000000000000000000000000000
mysql:1.0000000000000000000000000000
clickhouse:{ "meta": [ ], "data": [ ], "rows": 0, "exception": "Code: 407. DB::Exception: Overflow during decimal division: While processing sum(a * b) \/ sum(a * b). (DECIMAL_OVERFLOW) (version 24.2.2.16370 (official build))" }
snowflake:1.0000000000000000

drop table decimal_test4 all;
drop table decimal_test5 all;
create table decimal_test4(a decimal(28,8), b decimal(24,16), symbol varchar(20));
create table decimal_test5(a decimal(28,8), b decimal(24,16), symbol varchar(20));
insert into decimal_test4 values(300.07878791,5325.0000000000000000,'u'),(2.00000000,10491.0000000000000000,'u');
insert into decimal_test5 values(200.00000000,0.1456000000000000,'u'),(62.53987147,0.9966000000000000,'u');
select a.s/b.s from (select symbol,sum(a*b) as s from decimal_test4 group by symbol) as a left join (select symbol,sum(a*b) as s from decimal_test5 group by symbol) as b on a.symbol=b.symbol;
databend:0.000000000001724326707827
tidb:17703.1216915851334322891316139509
mysql:17703.1216915851334322891316139509
clickhouse:{ "meta": [ ], "data": [ ], "rows": 0, "exception": "Code: 407. DB::Exception: Overflow during decimal division: While processing s \/ b.s. (DECIMAL_OVERFLOW) (version 24.2.2.16370 (official build))" }
snowflake:17703.1216915851334323