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:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.31, 5.7 OS:Ubuntu
Assigned to: CPU Architecture:x86

[20 Nov 2022 5:01] Chenglin Tian
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
[21 Nov 2022 5:17] MySQL Verification Team
Hello Chenglin Tian,

Thank you for the report.

[21 Nov 2022 8:37] Roy Lyseng
Workaround: SELECT MOD(CAST('-12' AS SIGNED), -4);
[3 Dec 2023 6:46] Chenglin Tian
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?
[3 Dec 2023 12:16] Roy Lyseng
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.
[5 Dec 2023 4:04] Chenglin Tian
thank you for your reply, I have understood