Wrong result when cast float to decimal

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1;
create table t1(c1 float);
alter table t1 set tiflash replica 1;
set tidb_isolation_read_engines='tiflash';
set @@tidb_enforce_mpp = 1; 
insert into t1 values(3.40282e+37);
select cast(c1 as decimal(50, 2)) from t1;

2. What did you expect to see? (Required)

mysql> select cast(c1 as decimal(50, 2)) from t1;
+-------------------------------------------+
| cast(c1 as decimal(50, 2))                |
+-------------------------------------------+
| 34028199169636080000000000000000000000.00 |
+-------------------------------------------+

3. What did you see instead (Required)

mysql> select cast(c1 as decimal(50, 2)) from t1;
+-----------------------------------------------------+
| cast(c1 as decimal(50, 2))                          |
+-----------------------------------------------------+
| 999999999999999999999999999999999999999999999999.99 |
+-----------------------------------------------------+
1 row in set (0.12 sec)

4. What is your TiFlash version? (Required)

master

[guo-shaoge]

We got overflow when casting float to decimal. Overflow happens here. value is Float32, actually we should use Float64.

[guo-shaoge]

Decimal to decimal also has this problem:

drop table if exists t1;
create table t1(c1 decimal(50, 0));
insert into t1 values(12345678901234567890123456789012345678901234567890);
alter table t1 set tiflash replica 1;
set tidb_isolation_read_engines='tiflash';
set @@tidb_enforce_mpp = 1;
select cast(c1 as decimal(35, 30)) from t1;

TiFlash:

mysql> select cast(c1 as decimal(35, 30)) from t1;
ERROR 1105 (HY000): other error for mpp stream: Unable to allocate sufficient storage for the value of the result: value overflows the maximum allowable magnitude.

MySQL and TiDB:

mysql> select cast(c1 as decimal(35, 30)) from t1;
+--------------------------------------+
| cast(c1 as decimal(35, 30))          |
+--------------------------------------+
| 99999.999999999999999999999999999999 |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

[guo-shaoge]

Decimal to decimal also has this problem:

drop table if exists t1;
create table t1(c1 decimal(50, 0));
insert into t1 values(12345678901234567890123456789012345678901234567890);
alter table t1 set tiflash replica 1;
set tidb_isolation_read_engines='tiflash';
set @@tidb_enforce_mpp = 1;
select cast(c1 as decimal(35, 30)) from t1;

TiFlash:

mysql> select cast(c1 as decimal(35, 30)) from t1;
ERROR 1105 (HY000): other error for mpp stream: Unable to allocate sufficient storage for the value of the result: value overflows the maximum allowable magnitude.

MySQL and TiDB:

mysql> select cast(c1 as decimal(35, 30)) from t1;
+--------------------------------------+
| cast(c1 as decimal(35, 30))          |
+--------------------------------------+
| 99999.999999999999999999999999999999 |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

This problem can be fixed by #3912.