Bug #83031 bin return inconsistent results with params of datetype bit
Submitted: 18 Sep 2016 8:09 Modified: 16 Nov 2016 1:20
Reporter: 帅 Bang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7, 5.6.33, 5.7.15, 5.5.52 OS:Linux
Assigned to: CPU Architecture:Any

[18 Sep 2016 8:09] 帅 Bang
Description:
mysql> create table sb(a bit(8));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into sb values(0b11111111);
Query OK, 1 row affected (0.01 sec)

mysql> select bin(a) from sb;

+----------+
| bin(a)   |
+----------+
| 11111111 |
+----------+
1 row in set (0.00 sec)

mysql> select bin(0b11111111);

+-----------------+
| bin(0b11111111) |
+-----------------+
| 0               |
+-----------------+
1 row in set (0.00 sec)

How to repeat:
 create table sb(a bit(8));
 insert into sb values(0b11111111);
 select bin(a) from sb;
 select bin(0b11111111);

Suggested fix:
' select bin(0b11111111) ' returns 11111111 as  'select bin(a) from sb' does
[19 Sep 2016 6:55] MySQL Verification Team
Hello Bang,

Thank you for the report.

Thanks,
Umesh
[23 Sep 2016 10:13] Tor Didriksen
Both bin() and oct() have the same problem.

mysql> select bin(0b11111111), oct(0b11111111), hex(0b11111111);
+-----------------+-----------------+-----------------+
| bin(0b11111111) | oct(0b11111111) | hex(0b11111111) |
+-----------------+-----------------+-----------------+
| 0               | 0               | FF              |
+-----------------+-----------------+-----------------+
1 row in set (0,00 sec)

Same problem for hexadecimal literals:

mysql> select bin(0xFF), oct(0xFF), hex(0xFF);
+-----------+-----------+-----------+
| bin(0xFF) | oct(0xFF) | hex(0xFF) |
+-----------+-----------+-----------+
| 0         | 0         | FF        |
+-----------+-----------+-----------+
1 row in set (0,00 sec)

Workaround

mysql> select bin(0b11111111+0), oct(0b11111111+0), hex(0b11111111+0);
+-------------------+-------------------+-------------------+
| bin(0b11111111+0) | oct(0b11111111+0) | hex(0b11111111+0) |
+-------------------+-------------------+-------------------+
| 11111111          | 377               | FF                |
+-------------------+-------------------+-------------------+
1 row in set (0,00 sec)

mysql> select bin(0xFF+0), oct(0xFF+0), hex(0xFF+0);
+-------------+-------------+-------------+
| bin(0xFF+0) | oct(0xFF+0) | hex(0xFF+0) |
+-------------+-------------+-------------+
| 11111111    | 377         | FF          |
+-------------+-------------+-------------+
1 row in set (0,01 sec)
[16 Nov 2016 1:20] Paul DuBois
Posted by developer:
 
Noted in 8.0.1 changelog.

The BIN(), OCT(), and HEX() functions could mishandle values of the
BIT data type.