I cam across a weird bug this afternoon. A piece of code in a Rails app that looked like

Splines.where.not(reticulated_on: nil)

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 nil, which, as unexpected 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.

As 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 nil.

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 1000-01-01 to 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.