Is an invalid date considered the same as a NULL value?

Question

I am using MySQL 5.7 and I have notice something which I cannot explain to myself with my current knowledge so here it goes.

Is an "invalid" date 0000-00-00 considered same as NULL for a date column type with no possibilities for nullables? See the following example:

create table if not exists example
(
    id int auto_increment primary key,
    doskip date default '0000-00-00' not null
);

INSERT INTO example VALUES(1, '0000-00-00'), (2, '0000-00-00'), (3, '0000-00-00');

Having the above and running the following queries gave me the exact same count: 3 and I wonder why, is there any explanation for this?

SELECT COUNT(*) FROM example j WHERE j.doskip = '0000-00-00';
SELECT COUNT(*) FROM example j WHERE j.doskip IS NULL;

Here is a Fiddle showing this behavior, is it because of the mode?

Answer 1

Is an invalid date considered the same as a NULL value?

From my understanding, 0000-00-00 on a date datatype column it is considered as a NULL.

From Problems Using DATE Columns

MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values. If a date to be stored in a DATE column cannot be converted to any reasonable value, MySQL stores '0000-00-00'. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

In above statement isn't specifically mentioned that 0000-00-00 is treated as NULL, but I guess somewhere in the code it has to.

Another statement from the docs

The special “zero” date '0000-00-00' can be stored and retrieved as '0000-00-00'. When a '0000-00-00' date is used through Connector/ODBC, it is automatically converted to NULL because ODBC cannot handle that kind of date

Note, this applies to date and datetime datatype, not timestamp

---

My suggestion

Fix the table design by making doskip date default null since doskip date default '0000-00-00' not null doesn't make any sense anyway. Enable NO_ZERO_DATE with strict mode , replace all values from '0000-00-00' to NULL.

NULL is considered an unknow value and it can only be evaluated by using is null/is not null. 0000-00-00 is considered an unknown date (not an unknown value , thats why is not null count gives 3 rows ) and that's why developers might have applied same null rules.

Answer 2

'0000-00-00' is a magic marker that is both "null" and "not null" simultaneously. You can extend your fiddle with:

SELECT COUNT(*) FROM example j WHERE j.doskip IS NOT NULL;

or even:

SELECT COUNT(*) FROM example j WHERE j.doskip IS NULL AND j.doskip IS NOT NULL;

The latter is a contradiction, yet all rows qualify. You can bring some sanity into older versions of MySQL by setting sql_mode to NO_ZERO_DATE. While you are at it, also add ONLY_FULL_GROUP_BY

Another indication that it does not behave like null is that

j.doskip = j.doskip

evaluates to TRUE, whereas:

j.doskip <> j.doskip

evaluates to FALSE. You can try with:

select id, j.doskip=j.doskip, j.doskip<>j.doskip from example j;

MySQL 8 has improved a lot compared to the older versions. You may consider upgrading if you want to avoid these kinds of quirks