Bug #100233 bitwise and & produces wrong result
Submitted: 16 Jul 2020 9:20 Modified: 16 Jul 2020 14:02
Reporter: Yushan ZHANG Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: and, BINARY

[16 Jul 2020 9:20] Yushan ZHANG
Description:

mysql> select CONV(-2456,10,2);
+------------------------------------------------------------------+
| CONV(-2456,10,2)                                                 |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111011001101000 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

-- incorrect 5.7.31 & 8.0.21
mysql> select CONV(-2456,10,2) & 1;
+----------------------+
| CONV(-2456,10,2) & 1 |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

-- correct
mysql> select (-2456) & 1;
+-------------+
| (-2456) & 1 |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

It is a bitwise operation, then the second query should give the same
result as the last one, however, it returns 1.

Added comment to https://bugs.mysql.com/bug.php?id=100223 (which is a question), but there is no reply so opened a new one.

How to repeat:
select CONV(-2456,10,2);

-- incorrect 5.7.31 & 8.0.21
select CONV(-2456,10,2) & 1;
select (-2456) & 1;
[16 Jul 2020 14:02] MySQL Verification Team
Hi Mr. ZHANG,

Thank you for your bug report.

However, this is not a bug.

Our Reference Manual nicely documents that CONV() does not return a numeric value, hence result of any bitwise operator might not be what you expect.

Not a bug.