MySQL Bugs: #27627: Bit-Field Values and User Variables

Bug #27627	Bit-Field Values and User Variables
Submitted:	3 Apr 2007 22:02	Modified:	13 Jul 2007 13:36
Reporter:	Hakan Ensari	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.27, 5.1, 5.2	OS:	Linux (GNU/Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I've run into some quirks using bit functions and bit-field values in stored procedures and triggers. I've provided an example with the bitwise AND (&) below. You may be able to replicate the behavior with similar functions.

I would very much appreciate any insight into what is going on here -- in case this is not a bug, it may be helpful to expand the documentation on bit functions.

How to repeat:
To give an example of what I mean by quirk:

mysql> set @bitter = b'111';
Query OK, 0 rows affected (0.00 sec)

mysql> select (@bitter + 0);
+---------------+
| (@bitter + 0) |
+---------------+
|             0 | 
+---------------+
1 row in set (0.00 sec)

mysql> select @bitter & b'111';
+------------------+
| @bitter & b'111' |
+------------------+
|                0 | 
+------------------+
1 row in set (0.00 sec)

Apparently, the b'value' notation is of no use when assigning values to user-defined variables in this particular context.

So, say we attempted to use the BIN function to convert a decimal number into a binary value:

mysql> set @bitter = BIN(7);
Query OK, 0 rows affected (0.00 sec)

mysql> select @bitter & b'111';
+------------------+
| @bitter & b'111' |
+------------------+
|                7 | 
+------------------+
1 row in set (0.00 sec)

Apparently, this works as expected! However:

mysql> set @bitter = BIN(15);
Query OK, 0 rows affected (0.00 sec)

mysql> select @bitter & b'1111';
+-------------------+
| @bitter & b'1111' |
+-------------------+
|                 7 | 
+-------------------+
1 row in set (0.00 sec)

Quite odd, to say the least! The left-most 1 is not included in whatever calculation is taking place in the background.

To make a long story short, the only reliable way to go about using the bitwise AND in this particular context seems to be:

mysql> set @bitter = 15;
Query OK, 0 rows affected (0.00 sec)

mysql> select @bitter & b'1111';
+-------------------+
| @bitter & b'1111' |
+-------------------+
|                15 | 
+-------------------+
1 row in set (0.00 sec)

My comment concerning the reliability of the above is not based on any exhaustive observation, so I would not be surprised if a similar issue cropped up with this set-up as well!

Thank you for the report.

Verified as described. All versions are affected.

Tim, suggest to assign to Ramil (the implementor of bit type)

After discussion with Ramil we agree this is not a server bug.

Output of BIN(15) is treating as binary string and one should explicitly cast values if she wants to use it as number.

Though this should be documented as done for Hexadecimal Values at http://dev.mysql.com/doc/refman/5.0/en/hexadecimal-values.html

As a clarification, the cast must be done when selecting the value, as:

set @bitter = cast(b'111' as unsigned);

Once stored as a binary string, the values can't be cast to numbers in a simple way (the ill-named ASCII() function, which works just fine for values > 127, might help).

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added discussion/example showing how to assign bit values as numbers to user variables:

http://dev.mysql.com/doc/refman/5.0/en/bit-field-values.html
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html