CODEWITHSUNDEEP
phpmysqlbit-manipulationbitwise-operators
TIW DEV
TIW DEV

Reputation: 107

MySQL returning incorrect records to bitwise SELECT

Can anyone offer any insight into what is wrong here? Im using a BIGINT(20) field in a table to store a bitwise value (there are other fields in here too, but for simplicity i've ignored them for SO) when a SELECT statement is done on that field, it is returning a selection of records for some values, the selection is spot on, for others its wrong.

For example:

TABLE A (there is a created_at timestamp field that isn't shown below)
id   flag        approved
1    0           Y
2    1           Y
3    10          N
4    100         Y
5    1000        Y
6    10000       Y
7    100000      Y
8    1000000     Y
9    10000000    Y
10   100000000   Y
11   1000000000  Y

Now if i do:

SELECT * from tableA where approved = 'Y' AND flag & 4 ORDER BY created_at DESC

It correctly returns just the record with ID 4. All good, no problems. However, if i do:

SELECT * from tableA where approved = 'Y' AND flag & 256 ORDER BY created_at DESC

Then i get records 5, 6 and 10 - when I only expected record 10! Its not my code, because if i run the same query in phpMyAdmin I get the same responses, but why? It must be something I'm doing wrong, but I cannot see what?

My understanding of the second SQL Statement is to say, select all from table A where approved is set to Y and bit 256 in the flag field is set.

Can anyone offer any thoughts? Its not just with flag & 256 that its causing the issue, there are other values.

Upvotes: 1

Views: 258

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53830

You can perform bitwise operations on integer columns but you still need to store them as decimal values.

To store decimal 2 in an integer column store 2, not 10. You might want to use the bit column type instead.

Either way, you can use b'value' notation to write the values if it's easier for you.

INSERT INTO tablea (id, flag, approved)
VALUES (1, b'0', 'Y'), (2, b'1', 'Y'), (3, b'10', 'N')

Upvotes: 1

Bart Haalstra
Bart Haalstra

Reputation: 1062

The result is correct, For example 1000 is 1111101000 in binairy, if you compare it bitwice with 256 (100000000) you get 256 (100000000), which is true. The same for 10000 (10011100010000) and 100000000 (101111101011110000100000000).

The flag field should probaly have the value 0, 1, 2, 4, 8, 16, 32, etc.

Upvotes: 0

Related Questions