Binary comparison giving wrong result MYSQL

Question

Having this DB called Employees:

+--+----------------+
|ID|Ability_required|
+--+----------------+
|1 |   0000000111   |
|2 |   0000001111   |
|3 |   0000000111   |
|4 |   0000001101   |
|5 |   0000001111   |
+--+----------------+

And this SQL query:

SELECT ID FROM `Employees`
WHERE `Ability_required` & b'0000001111' = b'0000001111'

Why does my MYSQL return row number 1 and 3? What am I doing wrong here?

Check image for an example:

Answer 1

You are storing numbers as strings while bitwise and operates on integers. MySQL will perform an implicit conversion, however, it assumes that the string represents a decimal number.

Convert the string manually using the MySQL CONV function:

SELECT * FROM `Employees`
WHERE CONV(`Ability_required`, 2, 10) & b'0000001111' = b'0000001111';

ID Ability_required
2  0000001111
5  0000001111

I would suggest using an INT field. Or normalize the table if possible.