MySQL Bugs: #109149: The representation of 0 in MOD function in computing string

Bug #109149	The representation of 0 in MOD function in computing string
Submitted:	20 Nov 2022 5:01	Modified:	5 Dec 2023 4:04
Reporter:	Chenglin Tian	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.31, 5.7	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
When we use the mod function to calculate, we find that if there is a negative number type string, the result will be inconsistent with the expectation

How to repeat:
The following is the test statement that we found the problem：
SELECT mod(-12,-4); -- expected: 0, actual: 0 
SELECT mod('-12',-4); -- expected: 0, actual: -0   

Suggested fix:
This is a problem that we have detected that is inconsistent with the expected result. We believe that - 12 and '- 12' can be regarded as the same during type conversion, but we get two results: 0 and - 0. I don't know whether this is an exception

Hello Chenglin Tian,

Thank you for the report.

regards,
Umesh

Workaround: SELECT MOD(CAST('-12' AS SIGNED), -4);

Hello, so in this example, if no forced type conversion is performed, what data type will the string '-12' be implicitly converted to? Could you please tell me? In addition, we would like to locate this problem in the source code. , where exactly does this problem appear in the source code?

In all arithmetic expressions, a string expression will be interpreted as a double precision value, thus the result of the MOD is also double precision.

Notice also that floating point negative zero is identical in value to regular
zero, so this is mostly a display issue and not a matter of wrong value.

The reason why the workaround with CAST have to work is that there is no such thing as an integer negative zero (as long as we deal with two's complement values).

If the actual string value cannot be interpreted as an integer, the workaround is no good, of course.

thank you for your reply, I have understood