bit(n) data type gets confused when used with IF(), IFNULL()...

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. Consider this:

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;

The SQL below returns a 6-column result set. All 6 columns SHOULD contain the same NUMERIC data. However, column 2 returns a string of bits (1's and 0's) corresponding to the ASCII encoding of the result interpreted as a string! That is, if the correct result is 25, it actually returns either 11001000110101 (in phpMyAdmin) or 12853 (the decimal equivalent of that binary number, when using mysqli from PHP directly))!

Note that:

ASCII('2') = 50 = 110010 binary,
ASCII('5') = 53 = 00110101 binary

and 12853 = 50*256+53

...which is where those values come from.

Needless to say, this is completely fubar!

Also, using mysqli to retrieve the data yeilds a "Malformed packet" error after the first row containing a double-digit number. Also completely fubar!

If you're writing SQL, a simple workaround is just to add a "+0" or a CAST to coerce the result into numeric form.

However, if you've (for example) just converted a bool column to a bit(1) and expect things to work correctly as before, this bug may bite you!

MySQL (tested v5.6 and v5.7) suffers a very similar (though possibly not quite identical) problem; see MySQL bug #83923.