A few months ago, when I was doing some detailed database backup and restore testing, I discovered there were, out of millions of records which had user-entered dates, a handful that had null dates in a database column. I scratched my head for a while and couldn’t work out how this had happened, as the field is validated in Rails for empty/null.
Just today, I got an exception report from a different part of the system which does a query based on the user entered date, and it revealed the source of this extremely rare problem! So.. drum roll..
Accidentally, a user had entered a date with the year 20223.
This is valid in Ruby/Rails but too big to be stored in the mysql Date column, so had ended up (silently) being stored as null!
Easily fixed by limiting the date range a bit!