Description:
I used the same table schema, except for adding and removing the PRIMARY KEY constraint on the column of type DATETIME.
The results of the same query were inconsistent.
mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE IF NOT EXISTS t2(
-> c1 DATETIME PRIMARY KEY
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT TRUE FROM t2 WHERE (t2.c1) IS NULL;
+------+
| TRUE |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE IF NOT EXISTS t2(
-> c1 DATETIME
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT TRUE FROM t2 WHERE (t2.c1) IS NULL;
Empty set (0.00 sec)
How to repeat:
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- Schema with PRIMARY KEY
DROP TABLE IF EXISTS t2;
CREATE TABLE IF NOT EXISTS t2(
c1 DATETIME PRIMARY KEY
);
INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
SELECT TRUE FROM t2 WHERE (t2.c1) IS NULL;
-- Schema without PRIMARY KEY
DROP TABLE IF EXISTS t2;
CREATE TABLE IF NOT EXISTS t2(
c1 DATETIME
);
INSERT INTO t2 VALUES ('0000-00-00 00:00:00');
SELECT TRUE FROM t2 WHERE (t2.c1) IS NULL;
Description: I used the same table schema, except for adding and removing the PRIMARY KEY constraint on the column of type DATETIME. The results of the same query were inconsistent. mysql> DROP TABLE IF EXISTS t2; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE IF NOT EXISTS t2( -> c1 DATETIME PRIMARY KEY -> ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t2 VALUES ('0000-00-00 00:00:00'); Query OK, 1 row affected (0.01 sec) mysql> SELECT TRUE FROM t2 WHERE (t2.c1) IS NULL; +------+ | TRUE | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS t2; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE IF NOT EXISTS t2( -> c1 DATETIME -> ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t2 VALUES ('0000-00-00 00:00:00'); Query OK, 1 row affected (0.00 sec) mysql> SELECT TRUE FROM t2 WHERE (t2.c1) IS NULL; Empty set (0.00 sec) How to repeat: set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- Schema with PRIMARY KEY DROP TABLE IF EXISTS t2; CREATE TABLE IF NOT EXISTS t2( c1 DATETIME PRIMARY KEY ); INSERT INTO t2 VALUES ('0000-00-00 00:00:00'); SELECT TRUE FROM t2 WHERE (t2.c1) IS NULL; -- Schema without PRIMARY KEY DROP TABLE IF EXISTS t2; CREATE TABLE IF NOT EXISTS t2( c1 DATETIME ); INSERT INTO t2 VALUES ('0000-00-00 00:00:00'); SELECT TRUE FROM t2 WHERE (t2.c1) IS NULL;