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)
|