MySQL Bugs: #62838: Bit wise operations return incorrect result

Bug #62838	Bit wise operations return incorrect result
Submitted:	20 Oct 2011 4:32	Modified:	20 Oct 2011 11:24
Reporter:	Vel R	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any
Tags:	& operator, bit, Bitwise

Description:
Hi,

I am trying to do bit operation on integer field to get values using following similar query:

select * from PO where status & (4 | 1);

But the result returned has status with bits enabled for '4' or '1' whereas
what I need was the status with bits '4' and '1' that is '5'

How to repeat:
You can try this in any table with a file of integer column.

I think you have a background in SQL Server.
What you are trying to do is this: http://msdn.microsoft.com/en-us/library/ms176122.aspx

MySQL does not support this syntax.  MySQL statement syntax is listed here:
http://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html

Peter
(not a MySQL person)

The & operation should return the records with field values which satisfies all the conditions. In this case (4 | 1) = 5. But the results are returned with records 4 as well as 1

Please, read the manual, http://dev.mysql.com/doc/refman/5.5/en/bit-functions.html. Then check, noting that 5 is 101 in binary form (note bits for 4 and 1 are set, bit for 2 is not set). So:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1 | 4;
+-------+
| 1 | 4 |
+-------+
|     5 |
+-------+
1 row in set (0.03 sec)

mysql> select 5 & (1 | 4);
+-------------+
| 5 & (1 | 4) |
+-------------+
|           5 |
+-------------+
1 row in set (0.01 sec)

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

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

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

mysql> select 3 & (1 | 4);
+-------------+
| 3 & (1 | 4) |
+-------------+
|           1 |
+-------------+
1 row in set (0.01 sec)

This is not a bug, but expected and documented behavior of bitwise operations.

Valeriy,

I agree the results are exactly as you mentioned. But the expression in parenthesis should be executed first before appliying to the the other part of the expresion. In this case "5 & (4|1)" the parenthesis part (4|1) should be executed and continue the remaining part.

I have a status field which I want to get value only if both the status (4 as well as 1) is present. The reason for '|' operation is to combine all the statuses and check the field with the '&' operation. 

Try this by having a field in a table.

Vel R,

& is a bit mask operation.

To check that *every* bit is set, instead of *any* bit set,
the statement to use should probably look like:
where (status & mask) = mask

select * from PO where (status & 5) = 5;