zero_date is considered as NULL, depending on optimizer_switch

DROP TABLE IF EXISTS  t1;

SELECT  CAST('0000-00-00 00:00:00' as datetime) is null;

CREATE TABLE t1 (d1 datetime NOT NULL);

INSERT INTO t1 VALUES ('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');

SET SESSION optimizer_switch='derived_merge=off';

SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;  ## result 2, expected result 2

SET SESSION optimizer_switch='derived_merge=on';

SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;  ## result 0, expected result 2

DROP TABLE t1;

MariaDB [test]> SELECT  CAST('0000-00-00 00:00:00' as datetime) is null;

+-------------------------------------------------+

| CAST('0000-00-00 00:00:00' as datetime) is null |

+-------------------------------------------------+

|                                               0 |

+-------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> CREATE TABLE t1 (d1 datetime NOT NULL);

Query OK, 0 rows affected (0.13 sec)

MariaDB [test]> INSERT INTO t1 VALUES ('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');

Query OK, 3 rows affected (0.03 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> SET SESSION optimizer_switch='derived_merge=off';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;

+----------+

| count(*) |

+----------+

|        2 |

+----------+

1 row in set (0.00 sec)

MariaDB [test]> SET SESSION optimizer_switch='derived_merge=on';  

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)