Description:
Running bitwise and's and or's on binary numbers greater than 20-bits long results in an overflow. However, one can run longer queries as long as the result is only 20-bits. Is this documented anywhere? Is this limit intentional when binary fields can hold 255-bits?
Thank you,
Clay
How to repeat:
To repeat showing issue with two 21-bit strings:
mysql> select binary(1111111111111111111)&binary(1111111111111111111);
+---------------------------------------------------------+
| binary(1111111111111111111)&binary(1111111111111111111) |
+---------------------------------------------------------+
| 1111111111111111111 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select binary(10000000000000000000)&binary(10000000000000000000);
+-----------------------------------------------------------+
| binary(10000000000000000000)&binary(10000000000000000000) |
+-----------------------------------------------------------+
| 9223372036854775807 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
To repeat showing no-results longer than 20-bits:
mysql> select binary(1000000000000000000)&binary(100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| binary(1000000000000000000)&binary(100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| 1000000000000000000 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select binary(1000000000000000000)|binary(100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| binary(1000000000000000000)|binary(100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| 9223372036854775807 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
Ensure intermediate variables are large enough to hold required result. Similarly, if allowed in the SQL-standard return an overflow error, not a bad result.
