I cam across a weird bug this afternoon. A piece of code in a Rails app that looked like
which generated an SQL query of
SELECT `splines`.* FROM `splines` WHERE `splines`.`reticulated_on` IS NOT NULL
was nevertheless returning an item with a
reticulated_on set to
nils tend to, broke everything.
But how? I specifically asked for no
nils. On closer inspection, the database
record had the date set to an impossible
0000-00-00. Gee, thanks, MySQL.
0000-00-00 isn’t a representable date, it can’t be cast to one by
ActiveRecord, which reports it as a
nil. That’s about as reasonable as one
can ask for in the circumstances, but the impedance mismatch does mean that
you can ask for not
nil and nevertheless get
This zero date is sometimes allowed in MySQL, depending on configuration. I don’t know why you’d ever want that, but MySQL often makes me ask myself that. This database must have been configured to allow zero dates at some point in the past, probably via an unhelpful default, but is now configured to enforce more sensible data integrity constraints.
The fix is to change such rogue values in the database to
NULL. But it’s hard
to write a data migration because you can’t use
0000-00-00 as a
date in the
WHERE clause without changing the database configuration. If you
try, it says
Incorrect date value: '0000-00-00' for column 'reticulated_on' at row 1
To slay this gorgon, instead of looking at it directly, we have to reflect it
in the back of our shield. We can’t test for equality, but we can do a
comparison with a valid date. There’s no data older than the early 2010s in this
application, so any reasonable date before that within MySQL’s supported
DATE range of
9999-12-31 will work. For religiocultural reasons, I went for
the Unix epoch:
UPDATE splines SET reticulated_on = NULL WHERE reticulated_on IS NOT NULL AND reticulated_on < '1970-01-01'
That did the trick.
I miss PostgreSQL.