MySQL XOR produces incorrect results

Posted on

Question :

Why does the expression conv('9000084e703720f9', 16, 10) ^ conv('801A93A5A18A773F', 16, 10) produce wrong result?

In a step by step explanation:

SELECT conv('9000084e703720f9', 16, 10);

Result: 10376302674444755193

SELECT conv('801A93A5A18A773F', 16, 10);

Result: 9230852725838346047

SELECT 10376302674444755193 ^ 9230852725838346047;

Result: 1160411291139790790

But by this query:

SELECT conv('9000084e703720f9', 16, 10) ^ conv('801A93A5A18A773F', 16, 10) 

Result: 0

I don’t understand why does this return zero?

Answer :

The problem is that some long string failed to fit into a BIGINT UNSIGNED.

On the other hand, consider avoiding going through decimal:

mysql> SELECT HEX(0x9000084e703720f9 ^ 0x801A93A5A18A773F)
+----------------------------------------------+
| HEX(0x9000084e703720f9 ^ 0x801A93A5A18A773F) |
+----------------------------------------------+
| 101A9BEBD1BD57C6                             |
+----------------------------------------------+

mysql> SELECT (0x9000084e703720f9 ^ 0x801A93A5A18A773F);
+-------------------------------------------+
| (0x9000084e703720f9 ^ 0x801A93A5A18A773F) |
+-------------------------------------------+
|                       1160411291139790790 |
+-------------------------------------------+

8.0 can handle bit operations longer than 64 bits, but perhaps not in this context.

Leave a Reply

Your email address will not be published. Required fields are marked *