A little bit about BIT again.

The MySQL implementation of the BIT type has had severe criticism over time.

I think the famous Blog by Baron Schwartz here will be known to a lot of people. It both complains about the implementation as such and deals with bugs in the early 5.0 implementation. Most (if not all) of the regular bugs have been fixed. However the complaints about the implementation are still valid. I use to post this link to those of our users having difficulty with BIT and advise to use  a TINYINT instead (at least instead of a BIT(1)).

It is also a part of the story that many users do not understand that a BIT is a numerical type and instead use it as a BOOL/BOOLEAN (what it is not). A BIT(n) may of course be used as an ‘n-array of booleans’ – but it requires tedious and careful coding in the application and with other clients (command line, GUI clients) the result is often confusing – unless the client has some client-side ‘smart handling of BIT’ added (what SQLyog among others has).

But what I did not realize clearly before was however that a BIT only applies to storage on disk. There is no ‘memory-type’ corresponding the ‘storage-type’ BIT in MySQL.

It is actually indirectly clear from the MySQL documentation on CAST()/CONVERT(). You may cast to BINARY[(N)], CHAR[(N)], DATE , DATETIME, DECIMAL[(M[,D])], SIGNED [INTEGER], TIME, UNSIGNED [INTEGER]. You cannot cast to a BIT because in memory there is no BIT at all. In memory a BIT is represented by a BIGINT UNSIGNED. I don’t think this is documented properly and I also am  not sure if this was properly considered at the time of implementation. It looks to me like BIT was ‘rushed through’ 6 years back without proper considerations.

It also means that the mostly used BIT variant – BIT(1) – is memory-inefficient as compared to TINYINT.

I posted a few related bug reports recently:

.. and the most simple test case illustrating what I am writing here is probably this:

CREATE TABLE bittest(b BIT(2));
INSERT INTO bittest VALUES (b’11’);
SET @bit = (SELECT b FROM bittest LIMIT 1);
SELECT @bit;
returns “3” as an integer

Also you may actually do things like  SELECT LOG(SELECT BIT-value FROM sometable);
— using a logaritmic function

In every respect a BIT behaves as BIGINT UNSIGNED unless it is fetched directly from storage to a client.

For me the considerations actually started with this bug (that I did not post myself, but I realized at once I saw it, that the basic problem was a server problem and not a client problem):  http://bugs.mysql.com/bug.php?id=63457

It is not the point that Workbench does weird things here (then I would have many points! 🙂  ). Though I am not able to understand how it reaches the result it does, it does not really matter much in the context.   The point is that even a MAX|MIN aggregate on a BIT returns an INTEGER. And that happens along with inconsistent metadata in this particular case.  This is how you confuse clients!

MySQL should rather have implemented a true BOOLEAN than than the BIT in my opinion. Because people use a BIT as a BOOLEAN mostly – and they get confused frequently. Clients are blamed for the server behaviour and implementation.