storage/mysql: add option for interpreting zero datetime values

[chuck-alt-delete]

Feature request

Unfortunately, MySQL has a date 0000-00-00. This affects the Date type as well as the timestamp type. This 0 date is absurd because it never actually happened. It seems to me users are really trying to use NULL. Maybe this allows them to avoid some COALESCE statement down the line in some query, I don’t know. But the fact remains people use this absurd value for timestamps, and it is impractical to update production tables with hundreds of millions of rows to fix it on the MySQL side.

The current best approach is to ingest these as text columns and do casting in a CASE WHEN, something like this (using the currently hypothetical try_parse_mysql_timestamp)

case
  when my_timestamp like '0000-00-00%'
    then null
  else
    try_parse_mysql_timestamp(my_timestamp)

Timestamps are very important in materialize. They are used for temporal filters with mz_now(), as well as for predicate pushdown. It’s a little bit of a rough user experience to require this kind of parsing for all MySQL users who use timestamps (aka all MySQL users) and I suspect this rake will be stepped on frequently.

I would prefer an option in the MySQL source that allows users to configure how to interpret the 0 date/timestamp. By default, I think it should be interpreted as NULL, but optionally users can specify some valid date.

[benesch]

Recording my suggestion on syntax from Slack.

Could we add an option like MAP ZERO DATE VALUES = {NULL | }, where users can explicitly specify a value to use for any zero dates?

I think the name needs some workshopping, but that's the gist.