A zero date is treated like NULL in mysql and returned for an is null check.

CREATE TABLE t1 (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enddate` datetime NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO t1(`enddate`) VALUES ('0000-00-00 00:00:00');

In mysql 5.7 both these queries return the row. In mysql 8.4 only the first query returns the row. This seems to be a failure in basic boolean logic of the OR operator. Am i missing something?

SELECT * FROM t1 WHERE enddate IS NULL;
SELECT * FROM t1 WHERE enddate IS NULL OR enddate > '2025-01-01 00:00:00';

A zero date is treated like NULL in mysql and returned for an is null check.

CREATE TABLE t1 (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enddate` datetime NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO t1(`enddate`) VALUES ('0000-00-00 00:00:00');

In mysql 5.7 both these queries return the row. In mysql 8.4 only the first query returns the row. This seems to be a failure in basic boolean logic of the OR operator. Am i missing something?

SELECT * FROM t1 WHERE enddate IS NULL;
SELECT * FROM t1 WHERE enddate IS NULL OR enddate > '2025-01-01 00:00:00';

• mysql

• I get an "Incorrect datetime value" error trying to do the INSERT. – Barmar Commented Feb 16 at 22:53
• Why do you expect it to return the row in either case? 0000-00-00 is not NULL. And the NOT NULL option in the column means it can never be null. – Barmar Commented Feb 16 at 22:54
• 1 This looks like a bug in the old version that was fixed in 8. – Barmar Commented Feb 16 at 22:55
• 1 There is an open bug report on this with interesting discussion between the reporter and the mysql team: bugs.mysql/bug.php?id=114988 The bug's status is can't repeat, which indicates mysql is not going to fix it. I do think this is either a bug in v8.0 or mysql needs to update documentation to make this change clear. – Shadow Commented Feb 16 at 23:34
• 1 I was testing at db-fiddle, I don't know what SQL_MODE they have set, probably the default for the version. – Barmar Commented Feb 16 at 23:57

1 Answer 1

To be able to reproduce your issue sql_mode NO_ZERO_DATE should be disabled. This allows inserting invalid dates.

In that case for the date and datetime datatype 0000-00-00 or 0000-00-00 00:00:00 are considered both NULL and NOT NULL from MySQL. Not for timestamp datatype. though.

There is a good discussion on Is an invalid date considered the same as a NULL value? on this matter.

This seems to be a failure in basic boolean logic of the OR operator. Am i missing something?

SELECT * FROM t1 WHERE enddate IS NULL OR enddate > '2025-01-01 00:00:00';

enddate IS NULL evaluates to TRUE

enddate > '2025-01-01 00:00:00' evaluates to UNKNOWN or FALSE (My guess unknown)

Starting from MySQL 8+ TRUE OR UNKNOWN evaluates to NULL OR FALSE, which means the row is excluded from the result set.

See example