Bug #83923 bit(n) data type confused when used with IF(), IFNULL()...

• Submitted: 23 Nov 2016 0:00
• Modified: 27 Jan 2017 1:36
• Reporter: Dean Trower
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.6, 5.7, 5.6.34, 5.7.16
• OS: Any
• CPU Architecture: Any
• Tags: bit ifnull

[23 Nov 2016 0:00] Dean Trower

Description:
Columns of bit(n) type should be treated consistently as numeric data.

However, VERY strange results can be obtained if you use this datatype in an IFNULL(a,b) function or similar.

The SQL below returns a 6-column result set.  All 6 columns *SHOULD* contain the same NUMERIC data.  However, columns 5 and 6 return type "BLOB" containing the data as a string... and even worse, column 2 returns the numeric ASCII code of the first character of the result, interpreted as a string!  (That is, if the correct result is 25, it actually returns ASCII('2') instead)!

How to repeat:
CREATE TABLE t (a bit(5));
INSERT INTO t VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(19),(20),(21),(29),(30),(31);

SELECT a,IFNULL(a,a),IFNULL(a,a)+0,IFNULL(a+0,a+0),IFNULL(a+0,a),IFNULL(a,a+0) FROM t;

DROP TABLE t;

Suggested fix:
Doing a cast or adding a "+0" to the IF() is a simple workaround if you're writing SQL code, but this is potentially a very serious bug, and needs to be fixed properly!

[23 Nov 2016 0:14] Dean Trower

I've confirmed this on v5.6 and 5.7, and MariaDB 10.1.19 has a similar problem...

[23 Nov 2016 6:16] MySQL Verification Team

Hello Dean Trower,

Thank you for the report and test case.
Observed this with 5.6.34, 5.7.16.

Thanks,
Umesh

[26 Jan 2017 19:13] Erlend Dahl

Duplicate of

Bug#83148 IF and friends gives wrong type for signed and unsigned integer

[27 Jan 2017 1:36] Dean Trower

With respect Erlend, although the bugs appear related, I don't think they're duplicates.  The problems afflicting the bit(n) type are a lot worse than simply mixing up signed and unsigned representations.