MySQL Bugs: #36100: NULL-safe comparison does not work with TIMESTAMP, DATETIME

Bug #36100	NULL-safe comparison does not work with TIMESTAMP, DATETIME
Submitted:	15 Apr 2008 20:01	Modified:	6 Aug 2009 20:10
Reporter:	Anthony Bush	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.45,5.0.54, 5.0.58, 5.0.67	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	datetime, null-safe, timestamp

Description:
Running a NULL-safe comparison between two TIMESTAMP or two DATETIME fields does not work when one of the fields is NULL.

How to repeat:
Setup SQL:
----------

	CREATE TABLE `null_safe` (
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	`timestamp1` TIMESTAMP NULL ,
	`timestamp2` TIMESTAMP NULL ,
	`datetime1` DATETIME NULL ,
	`datetime2` DATETIME NULL
	) ENGINE = INNODB;

	INSERT INTO null_safe VALUES (NULL, CURRENT_TIMESTAMP, NULL, NOW(), NULL);

What I did:
-----------

	SELECT * FROM null_safe WHERE timestamp1 <=> timestamp2;
	SELECT * FROM null_safe WHERE datetime1 <=> datetime2;

What I wanted to happen:
------------------------

I wanted both queries to return zero results.

What actually happened:
-----------------------

Both queries returned one result.

Work around:
------------

Explictly check NULL in addition to using the normal comparison operator.

For example, to check if two datetime fields are different use:
	
	SELECT
		*
	FROM
		null_safe
	WHERE
		datetime1 IS NULL AND datetime2 IS NOT NULL
		OR datetime1 IS NOT NULL AND datetime2 IS NULL
		OR datetime1 != datetime2;

Or, to check if two datetime fields are the same (like <=> is supposed to do):

	SELECT
		*
	FROM
		null_safe
	WHERE
		datetime1 IS NULL AND datetime2 IS NULL
		OR datetime1 IS NOT NULL AND datetime2 IS NOT NULL
		OR datetime1 = datetime2;

The very last work around example (check if two datetime fields are the same) has faulty logic.  Instead try:

	SELECT
		*
	FROM
		null_safe
	WHERE
		datetime1 IS NULL AND datetime2 IS NULL
		OR (datetime1 IS NOT NULL AND datetime2 IS NOT NULL AND datetime1 = datetime2);

Thank you for a bug report. Verified with 5.0.58 also:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.58-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `null_safe` (
    ->  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    ->  `timestamp1` TIMESTAMP NULL ,
    ->  `timestamp2` TIMESTAMP NULL ,
    ->  `datetime1` DATETIME NULL ,
    ->  `datetime2` DATETIME NULL
    ->  ) ENGINE = INNODB;
Query OK, 0 rows affected (0.27 sec)

mysql>  INSERT INTO null_safe VALUES (NULL, CURRENT_TIMESTAMP, NULL, NOW(), NULL
);
Query OK, 1 row affected (0.09 sec)

mysql> select * from null_safe;
+----+---------------------+------------+---------------------+-----------+
| id | timestamp1          | timestamp2 | datetime1           | datetime2 |
+----+---------------------+------------+---------------------+-----------+
|  1 | 2008-04-16 06:38:12 |       NULL | 2008-04-16 06:38:12 | NULL      |
+----+---------------------+------------+---------------------+-----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM null_safe WHERE timestamp1 <=> timestamp2;
+----+---------------------+------------+---------------------+-----------+
| id | timestamp1          | timestamp2 | datetime1           | datetime2 |
+----+---------------------+------------+---------------------+-----------+
|  1 | 2008-04-16 06:38:12 |       NULL | 2008-04-16 06:38:12 | NULL      |
+----+---------------------+------------+---------------------+-----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM null_safe WHERE datetime1 <=> datetime2;
+----+---------------------+------------+---------------------+-----------+
| id | timestamp1          | timestamp2 | datetime1           | datetime2 |
+----+---------------------+------------+---------------------+-----------+
|  1 | 2008-04-16 06:38:12 |       NULL | 2008-04-16 06:38:12 | NULL      |
+----+---------------------+------------+---------------------+-----------+
1 row in set (0.00 sec)

With INT column, for example, <=> works as expected:

mysql> alter table null_safe add i1 int;
Query OK, 1 row affected (0.39 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table null_safe add i2 int;
Query OK, 1 row affected (0.24 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update null_safe set i1=1, i2=NULL;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM null_safe WHERE i1 <=> i2;
Empty set (0.02 sec)

I also found this bug hile trying to create a trigger based audit trail for a table. I would just like to point out that this bug only happens if the right hand side of the comparrison is NULL. If the left hand side of the comparison is NULL, everything is fine. You can use this simple query to show the difference:

SELECT CAST(NULL AS DATETIME) <=> CAST('2008/01/01' AS DATETIME) AS LEFT_NULL,
       CAST('2008/01/01' AS DATETIME) <=> CAST(NULL AS DATETIME) AS RIGHT_NULL;

Result:

+-----------+------------+
| LEFT_NULL | RIGHT_NULL |
+-----------+------------+
|         0 |          1 |
+-----------+------------+

It works when using CHAR:

SELECT CAST(NULL AS CHAR(15)) <=> CAST('2008/01/01' AS CHAR(15)) AS LEFT_NULL,
       CAST('2008/01/01' AS CHAR(15)) <=> CAST(NULL AS CHAR(15)) AS RIGHT_NULL;

+-----------+------------+
| LEFT_NULL | RIGHT_NULL |
+-----------+------------+
|         0 |          0 |
+-----------+------------+

Hmm...
I can't repeat this via 5.1.32

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `null_safe` (
    -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> `timestamp1` TIMESTAMP NULL ,
    -> `timestamp2` TIMESTAMP NULL ,
    -> `datetime1` DATETIME NULL ,
    -> `datetime2` DATETIME NULL
    -> ) ENGINE = INNODB;
        INSERT INTO null_safe VALUES (NULL, CURRENT_TIMESTAMP, NULL, NOW(), NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO null_safe VALUES (NULL, CURRENT_TIMESTAMP, NULL, NOW(), NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM null_safe WHERE timestamp1 <=> timestamp2;
Empty set (0.00 sec)

mysql> SELECT * FROM null_safe WHERE datetime1 <=> datetime2;
Empty set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.32-log | 
+------------+
1 row in set (0.00 sec)

Tested this again in 5.0.67 (still broke) and 5.0.81 (appears to be fixed):

mysql> use test;
Database changed
mysql> CREATE TABLE `null_safe` (
    -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> `timestamp1` TIMESTAMP NULL ,
    -> `timestamp2` TIMESTAMP NULL ,
    -> `datetime1` DATETIME NULL ,
    -> `datetime2` DATETIME NULL
    -> ) ENGINE = INNODB;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO null_safe VALUES (NULL, CURRENT_TIMESTAMP, NULL, NOW(), NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM null_safe WHERE timestamp1 <=> timestamp2;
Empty set (0.00 sec)

mysql> SELECT * FROM null_safe WHERE datetime1 <=> datetime2;
Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.81    | 
+-----------+
1 row in set (0.00 sec)

Marking closed since this is fixed in 5.0.81 and 5.1.32 and hopefully the latest versions in each 5.0.x and 5.1.x series.