MySQL Bugs: #115731: Wrong result for double type

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

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)

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.