MySQL Bugs: #114962: insert into -0 become 0 (Fields of type double)

Bug #114962	insert into -0 become 0 (Fields of type double)
Submitted:	11 May 2024 2:46	Modified:	13 May 2024 11:32
Reporter:	Guisen Yang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	zero data

Description:

after importing data using LOAD DATA in the master-database and using mysqldump to build master-slave, it was found that the master-slave data was inconsistent.

in slave:
double filed:    -0   ---->   0

How to repeat:
shell> cat /home/mysql/aa.txt
ABCDEFGH,-0
ABCDEFGH,-0.0
ABCDEFGH,0.1
ABCDEFGH,-1.1

shell> mysql --local-infile
mysql> set global local_infile=ON;
mysql> create table db1.t20240511 (name char(10), id double);

mysql> load data local infile '/home/mysql/aa.txt' ignore into table db1.t20240511
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, id);

use mysqldump to build master-slave

MASTER:
mysql> delete from db1.t20240511;

and you will receive the following error message in slave 

Could not execute Delete_rows event on table db1.t20240511; Can't find record in 't20240511', Error_code: 1032; handler error HA_ERR_END_OF_FILE

That's because insert into -0 has changed to 0     -_-

select * from db1.t20240511;
MASTER:
+----------+------+
| name     | id   |
+----------+------+
| ABCDEFGH |   -0 |
| ABCDEFGH |   -0 |
| ABCDEFGH |  0.1 |
| ABCDEFGH | -1.1 |
+----------+------+
SLAVE:
+----------+------+
| name     | id   |
+----------+------+
| ABCDEFGH |    0 |
| ABCDEFGH |    0 |
| ABCDEFGH |  0.1 |
| ABCDEFGH | -1.1 |
+----------+------+

Suggested fix:
LOAD DATA  -0 to 0 , just like load DML

HI Mr. lain,

Thank you very much for your bug report.

However, this is not a bug.

DOUBLE is a floating data type that is defined in IEEE standard.

According to that standard 0.0 and -0.0 are identical values.

Hence, this is not a bug.

Hi,

We are also unable to repeat your test case:

+----------+------+
| name     | id   |
+----------+------+
| ABCDEFGH |   -0 |
| ABCDEFGH |   -0 |
| ABCDEFGH |  0.1 |
| ABCDEFGH | -1.1 |
+----------+------+

But, more important is the fact that you are using floating point type. If you want to preserve precision, use DECIMAL type.

Thank you for your reply.

For SELECT, -0 and 0 are the same. However, the data imported with -0 using Load DATA and -0 using Insert are different in the MYSQL database.

anther example (Ver: 8.0.28):
SHELL> cat /tmp/aa.txt
-0.0
0.0

MYSQL> create table db1.t20240513(aa double);
MYSQL> load data local infile '/tmp/aa.txt' ignore into table db1.t20240513;
MYSQL> checksum table db1.t20240513;
+---------------+------------+
| Table         | Checksum   |
+---------------+------------+
| db1.t20240513 | 3988317094 |
+---------------+------------+
MYSQL> select * from db1.t20240513;
+------+
| aa   |
+------+
|   -0 |
|    0 |
+------+

SHELL> mysqldump --databases db1 --table t20240513 --set-gtid-purged=off | mysql -D db1
MYSQL> checksum table db1.t20240513;
+---------------+----------+
| Table         | Checksum |
+---------------+----------+
| db1.t20240513 | 56647878 |
+---------------+----------+
MYSQL> select * from db1.t20240513;
+------+
| aa   |
+------+
|    0 |
|    0 |
+------+

Hi Mr. Yang,

Yes, this is correct.

This are typical characteristics of the IEEE floating point numbers.

Expected behaviour.