0

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' ;
5
  • Replace string-typed literal with correct date literal: WHERE a.jma = '2020-05-18' simply. – Akina May 18 '20 at 7:29
  • Why does it work with no left join and it doesn't with the left join ? – Cyril Bre May 18 '20 at 7:59
  • I don't know how does the implicit type convertion is performed in each case... despite the fact that the convertion rules are described in the User manual. – Akina May 18 '20 at 8:15
  • Come back after you have switched from MyISAM to InnoDB. (Unless you have a specific need for MyISAM, in which case, explain.) – Rick James May 20 '20 at 23:25
  • Like @Akina says, use a date format for your date. Why it works with the inner join is a mystery, but MySQL has very - let's say - generous casting rules. It may magically be able to guess what the date is supposed to be in the case of the inner join, but not in the left join. – Lennart May 21 '20 at 7:31
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.