MySQL Bugs: #40247: NULL-Safe Comparison of DATE

Bug #40247	NULL-Safe Comparison of DATE
Submitted:	22 Oct 2008 13:02	Modified:	26 Oct 2008 10:33
Reporter:	Craig Thomson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Linux (RHEL4 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	<=>, comparison, date, null-safe

Description:
When comparing a field which is NULL with one which is a valid date on tables joined with a left join the NULL-Safe comparison Operator <=> returns 1 when I would expect it to return 0 (since NULL does not equal a date).  The simple example below shows this clearly.  If VARCHAR(45) fields are used to store the date as text then the NULL-Safe comparison works as I would expect (returning 0).

This has also been confirmed on 5.0.67 on RHEL4 i686 and 5.0.67 on Windows XP Pro 32 Bit.

How to repeat:
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `datevalue` DATE NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `test2`;
CREATE TABLE  `test2` (
  `id` int(11) NOT NULL,
  `datevalue` DATE NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;
INSERT INTO test1 VALUES('1', '2008-01-01'),('2', '2008-01-02');
INSERT INTO test2 VALUES('1', '2008-01-01');

SELECT    test1.id AS id,
          test1.datevalue AS a,
          test2.datevalue AS b,
          test1.datevalue <=> test2.datevalue AS a_eq_b
FROM      test1
LEFT JOIN test2
ON        test1.id = test2.id

Outputs:

id	a	        b	        a_eq_b
1	01/01/2008	01/01/2008	1
2	02/01/2008	NULL	        1

Suggested fix:
As a workaround you can explicitly check if each field is NULL but this would require either a large IF statement for each use or a user defined function for comparing two fields.

Thank you for the bug report. Could you please test with latest release (your version is quite older. I couldn't repeat with source server:

mysql 5.0 > SELECT    test1.id AS id,
    ->           test1.datevalue AS a,
    ->           test2.datevalue AS b,
    ->           test1.datevalue <=> test2.datevalue AS a_eq_b
    -> FROM      test1
    -> LEFT JOIN test2
    -> ON        test1.id = test2.id
    -> ;
+----+------------+------------+--------+
| id | a          | b          | a_eq_b |
+----+------------+------------+--------+
|  1 | 2008-01-01 | 2008-01-01 |      1 |
|  2 | 2008-01-02 | NULL       |      0 |
+----+------------+------------+--------+
2 rows in set (0.06 sec)

mysql 5.0 >

Thanks for the response,

In addition to 5.0.51a I recieve the same error on both Windows and Linux 5.0.67 which I thought was the most recent version.  Could you please confirm which version you tested this on.

Thank you for the feedback.

I could repeat the problem with version 5.0.67, but bug is not repeatable with current development sources. So I close the report as "Can't repeat". Please wait next release.

This appears to be a duplicate of bug #36100  which indicates this was indeed not fixed in 5.0.67 but was fixed in 5.0.81 (and maybe fixed somewhere in between those).