Bug #109842 Problems when mod , division function and extremum function are used together
Submitted: 30 Jan 2023 13:51 Modified: 31 Jan 2023 5:49
Reporter: Chenglin Tian Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.31, 8.0.32, 5.7.41 OS:MacOS
Assigned to: CPU Architecture:x86

[30 Jan 2023 13:51] Chenglin Tian
This is related to the inconsistency of the results of the mod function in handling string types that we reported earlier. We found that problems also occur when we use the mod function, the division function and two extremal functions together

How to repeat:
The following is the test statement that we found the problem´╝Ü
select least('0'/-4,0/-4); -- -0
select greatest('0'/-4,0/-4); -- 0
select least(mod('-12',-4),mod(-12,-4)); -- -0
select Greatest(mod('-12',-4),mod(-12,-4)); -- 0

We tested the following two use cases
select mod('-12',-4) = mod(-12,-4); -- 1
select '0'/-4 = 0/4; -- 1

Suggested fix:
select mod('-12',-4) = mod(-12,-4); -- 1
select '0'/-4 = 0/4; -- 1

These two use cases prove that the two calculation results are equal. We hope that the extreme value function can give the same result, because the operation process of these two extreme value functions should also be the ultimate result of the comparison operator.
Although the result of the internal mod function or division function is incorrect, we think that the extreme value function should eliminate the influence of the negative number representation and give the correct answer
[31 Jan 2023 5:49] MySQL Verification Team
Hello Chenglin Tian,

Thank you for the report.