Bug #109147 Errors in the representation of bit operators and negative results

• Submitted: 20 Nov 2022 4:40
• Modified: 21 Nov 2022 8:56
• Reporter: Chenglin Tian
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.31, 5.7.40
• OS: Ubuntu
• CPU Architecture: Any

[20 Nov 2022 4:40] Chenglin Tian

Description:
When the result of bit operator calculation is negative, there will be problems in the representation of the result. When we use the result of bit operator and logical operator together, there will be errors

How to repeat:
The following test cases are the ones that we found inconsistent with the expected results. There will be negative exceptions indicating results for both numeric and string types

SELECT -5 & -4; -- expected: -8, actual: 18446744073709551608
SELECT '-5' | '-4'; -- expected: -3, actual: 18446744073709551615
SELECT '5' ^ '-4'; -- expected: -7, actual: 18446744073709551609
SELECT ~5; -- expected: -6, actual: 18446744073709551610

We suspect that this is just a representation problem, but when we use logical operators to calculate the results, we find the following problems

SELECT -3 > ('5' ^ '-4'); -- expected: 1, actual: 0

Suggested fix:
In terms of bitwise calculation operators, we found many inconformities with the expected results. Maybe we need to correct some errors about these operators

[21 Nov 2022 5:23] MySQL Verification Team

Hello Chenglin Tian,

Thank you for the report.

regards,
Umesh

[21 Nov 2022 8:56] Roy Lyseng

Posted by developer:
 
This is not a bug.
According to documentation, negative numeric values in bit operations are converted to unsigned values before the operation.