search

CurtTilmes / perl6-dbmysql

DB::MySQL
2 stars 3 forks source link

0000-00-00 00:00:00 legacy timestamp not supported #6

Open bbkr opened 5 years ago

bbkr commented 5 years ago

MySQL (up to 5.6) supports 0000-00-00 00:00:00 to express "empty date". In MySQL 5.7+ this can be controlled via NO_ZERO_DATE flags enabled by default.

Currently legacy timestamps are not supported:

create table foo (bar timestamp null default null) engine = innodb;
insert into foo values ('0000-00-00 00:00:00');
$db.execute("select bar from foo").value.say
Month out of range. Is: 0, should be in 1..12

I'm not sure how to handle those. Assume that 0000-00-00 00:00:00 is in fact NULL? But then MySQL has another case - NO_ZERO_IN_DATE - which accepts 2019-00-00 00:00:00 as "valid" date when disabled. So stepping into DateTime exception does not mean we always have NULL.

Maybe Failure should be returned and original string value should be passed to user as part of Failure message? Currently user gets OutOfRange exception:

X::OutOfRange.new(what => "Month", got => 0, range => "1..12", comment => Any)

Or maybe this should not be supported at all, so user is forced to convert to valid value on SELECT level and document this behavior?

bbkr commented 5 years ago

We were his by this issue in quite a few tables (over 7500) in our databases. And it was good excuse to finally clean up legacy "empty date" and convert those to proper NULLs.

So my vote here is to NOT support legacy dates in any form (forcing users to be more strict is always a good thing) but I think it should be at least mentioned as special case in exceptions section.