MySQL is clinically insane

It’s not like I needed much persuasion, but I’ve come to the conclusion that MySQL is a complete pain to work with — especially if you were expecting the kind of sane behaviour that you get from almost any other relational database.

I’ve just spent the last twenty minutes trying to figure out why my query (apparently) wasn’t working. Here’s something to demonstrate what I was seeing:

CREATE TABLE insanity
(
  value VARCHAR(20),
  description VARCHAR(50)
);

INSERT INTO insanity VALUES
  ('Hello', 'a string'),
  ('', 'an empty string'),
  (' ', 'a space'),
  (NULL, 'a NULL');

Okay, four rows into a simple table. Only one has a NULL value. Except that:

SELECT * FROM insanity;

+-------+-----------------+
| value | description     |
+-------+-----------------+
| Hello | a string        |
| NULL  | an empty string |
|       | a space         |
| NULL  | a NULL          |
+-------+-----------------+

Oh, so it looks like MySQL treats blank strings as equivalent to NULL? That’s annoying, but I can live with it. So that means:

SELECT * FROM insanity WHERE value IS NOT NULL;

+-------+-----------------+
| value | description     |
+-------+-----------------+
| Hello | a string        |
| NULL  | an empty string |
|       | a space         |
+-------+-----------------+

Eh? So empty strings aren’t equal to NULL? Why does it say ‘NULL’, then? I wonder what happens if I check for an empty string explicitly…

SELECT * FROM insanity WHERE value = '';

+-------+-----------------+
| value | description     |
+-------+-----------------+
| NULL  | an empty string |
|       | a space         |
+-------+-----------------+

No, it really is an empty string (Oh, and strings are also compared without regard to trailing spaces, which is pretty normal, but good to know). The problem here seems to be that an empty string is displayed as NULL by the mysql client. This appears to be a known bug, but it’s also supposed to be fixed in 5.0.22, which is what I appear to be using. Update: The problem is definitely fixed in the 5.0.41 client. I couldn’t figure out exactly which bug was the cause, though, so I can’t tell which version the fix appeared in.

Happily, there seems to be a whole list of gotchas after that one is fixed, so I won’t run out of fun!