Bug #115731 Wrong result for double type

Submitted: 31 Jul 2024 12:33 Modified: 31 Jul 2024 13:02
Reporter: Huicong Xu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[31 Jul 2024 12:33] Huicong Xu
Description:
Hello,

First, I created a table with a column of double type and inserted two records. Then, I executed an update statement to modify these two records. However, after the update, I encountered the following erroneous data. The data is not in double format as expected. I believe this update statement should have reported an error, but it didn't! I look forward to your response and thank you in advance.
mysql> SELECT * FROM tNm4JO07;
+------------------------+
| c0                     |
+------------------------+
| 00000000000000000000-0 |
| 00000000000000000000-0 |
+------------------------+
2 rows in set (0.00 sec)

How to repeat:
CREATE TABLE tNm4JO07 (c0 DOUBLE UNSIGNED ZEROFILL);
INSERT INTO tNm4JO07 VALUES (96531.68722361008);
INSERT INTO tNm4JO07 VALUES (98863.18790849278);
UPDATE tNm4JO07 SET c0="-0.0";
SELECT * FROM tNm4JO07;

mysql> UPDATE tNm4JO07 SET c0="-0.0";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM tNm4JO07;
+------------------------+
| c0                     |
+------------------------+
| 00000000000000000000-0 |
| 00000000000000000000-0 |
+------------------------+
2 rows in set (0.00 sec)
[31 Jul 2024 13:02] MySQL Verification Team
HI Mr. Xu,

Thank you for your bug report.

However, this is not a bug.

You are simply using deprecated features, that are not supported any more:

mysql> CREATE TABLE tNm4JO07 (c0 DOUBLE UNSIGNED ZEROFILL);
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column. |
| Warning | 1681 | UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.                                                  |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Not a bug.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.