Bug #117720 Instant modification causes inconsistency in double data.

• Submitted: 17 Mar 2:39
• Modified: 17 Mar 10:14
• Reporter: linus luo
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 8030, 8.0.41, 8.4.4
• OS: Any
• CPU Architecture: Any
• Tags: double, instant, modify column

[17 Mar 2:39] linus luo

Description:
The original data type of the column was DOUBLE(12, 5). After inserting the value 0.00265, the result of SELECT * shows 0.0027. Then, using ALGORITHM=INSTANT, the data type was modified from DOUBLE(12, 5) to DOUBLE(12, 4). After inserting the same value 0.00265 again, the result of SELECT * now shows 0.0026. Even though the same data was inserted before and after the INSTANT MODIFY, the results from SELECT * are inconsistent.This will cause the checksum of the data between the source database and the replica database to be inconsistent during DTS migration.

How to repeat:
create table t2(id int primary key, value double(12, 5));
insert into t2 values(1, 0.00265);
-- 0.0027
select * from t2;
alter table t2 modify column value double(12, 4), algorithm=instant;
-- 0.0026
select * from t2;

Suggested fix:
After reviewing the code, I found that the logic for handling precision during insertion uses rint, which follows the banker's rounding rule. This causes values like n.5 to round to the nearest even number,The code path is:
```
Sql_cmd_insert_values::execute_inner
  fill_record_n_invoke_before_triggers
    fill_record
      Item::save_in_field
        Item_decimal::save_in_field_inner
          Field_real::store_decimal
  	    Field_double::store
              // use rint
  	      Field_real::truncate
```
The logic for handling precision during SELECT uses dtoa, which employs rounding,The code path is:
```
floating_point_to_text
  my_fcvt
    my_fcvt_internal
      dtoa
```

[17 Mar 2:43] linus luo

Fix How to repeat:
create table t2(id int primary key, value double(12, 5));
insert into t2 values(1, 0.00265);
-- 0.00265
select * from t2;
alter table t2 modify column value double(12, 4), algorithm=instant;
-- 0.0027
select * from t2;
insert into t2 values(2, 0.00265);
-- 0.0026
select * from t2 where id = 2;

[17 Mar 2:43] linus luo

Fix How to repeat:
create table t2(id int primary key, value double(12, 5));
insert into t2 values(1, 0.00265);
-- 0.00265
select * from t2;
alter table t2 modify column value double(12, 4), algorithm=instant;
-- 0.0027
select * from t2;
insert into t2 values(2, 0.00265);
-- 0.0026
select * from t2 where id = 2;

[17 Mar 6:39] MySQL Verification Team

Hello linus luo,

Thank you for the report and feedback.

regards.
Umesh

[17 Mar 10:12] linus luo

Is there a fixed version available later?

[17 Mar 10:14] linus luo

how to fix it concretely, or we need to regard it as a feature of mysql.