MySQL LEFT JOIN with date filter

MySQL

Could you please help me with this subject.
We are using date filter since many time now and discovering a behaviour.

I made an exemple here : http://sqlfiddle.com/#!9/8f01f4/3

As you can see the join query returns 'a' and 'b' data.
When just adding a left join I loose 'b' data.

Could you please explain why this behaviour ?

If I change the where like that : a.jma = '2020-05-18' OR a.jma = 20200518
it works. But I would like to understand to not reproduce this "bug".

Thanks

The codes copied from the fiddle:

CREATE TABLE `a` (
  `clef` INT(11) NOT NULL AUTO_INCREMENT,
  `jma` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`clef`),
  UNIQUE KEY `jma` (`jma`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1
;

INSERT INTO a VALUES (1,20200518);

CREATE TABLE `b` (
  `clef` INT(11) NOT NULL AUTO_INCREMENT,
  `jma` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`clef`),
  UNIQUE KEY `jma` (`jma`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 ;

INSERT INTO b VALUES (1,20200518);

SELECT a.clef , b.clef
FROM a AS a
JOIN b AS b ON a.jma = b.jma
WHERE a.jma = '20200518';

SELECT a.clef , b.clef
FROM a AS a
LEFT JOIN b AS b ON a.jma = b.jma
WHERE a.jma = '20200518' ;

Best Answer

I checked around. The bug seems to be in both MyISAM and InnoDB, MySQL 5.5 and 5.6. It seems to be fixed in 5.7 and 8.0, plus MariaDB 10.

I did not find the bug report in order to identify what, exactly, was the cause, but here are some recommendations:

  • Don't use DEFAULT '0000-00-00'; "zero dates" are, by default disallowed in later versions. (There is a switch to allow them.)
  • Don't use dates looking like 20200518, use instead '2020-05-18'
  • It may be that 20200518 works, but '20200518' fails (in 5.5 and 5.6). That is, the quotes caused trouble.
  • Upgrade the version.
  • Reconsider the need for an AUTO_INCREMENT since you have a "natural" PRIMARY KEY (jma).
Related Question