Bug #111624 It's storing -0 (Minus Zero) value

• Submitted: 29 Jun 2023 15:50
• Modified: 30 Jun 2023 12:53
• Reporter: Vivek Lad
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Any
• CPU Architecture: Any
• Tags: #minuszeroindouble, #mysql, #procedure

[29 Jun 2023 15:50] Vivek Lad

Description:
Be careful when you work with MySql database. I found one scenario in which “-0” value is stored in column having (Double or Float) data type field. Ideally there is no value "-0".

Here actually it's two bug if you look in details of below example.

IF I do rounding of 2 with any minus value like(-0.00005) should return zero, It should not be the "-0".

Also while inserting data into the table Data type should handle it to avoid such cases.

In my experience it's possible from Procedure.

How to repeat:
Example is mentioned below

CREATE TABLE MYCHECK
(
FIELD1 DOUBLE
)
GO
SELECT * FROM MYCHECK
GO
DROP PROCEDURE PROC_TEST
GO
CREATE PROCEDURE PROC_TEST()
BEGIN
DECLARE VAR DOUBLE;

SET VAR = 0.00005 * -1 ;
INSERT INTO MYCHECK VALUES(ROUND(VAR,2));
END;
GO
CALL PROC_TEST()
GO
SELECT * FROM MYCHECK
GO
Result:
==========
FIELD1
---------
-0

Suggested fix:
If data insert from procedure or normal SQL, behavior should be similar.
I think it should check and insert correct value from the Double and Float data type.

[30 Jun 2023 12:53] MySQL Verification Team

Hi Mr. Lad,

Thank you for your bug report.

MySQL fully follows IEEE standards on the floating point values, just as any other big company.

According to that standard, -0.0 is absolutely valid floating point number, in any of the three available formats. 

You should not get -0 with integers, where that value would be truly a bug.

Not a bug.