Wrong result (missing rows) on LEFT JOINs and <non-nullable datetime field> IS NULL

CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (i2 INT) ENGINE=MyISAM;

INSERT INTO t2 VALUES (10),(20);

CREATE TABLE t3 (i3 INT, d3 DATETIME NOT NULL) ENGINE=MyISAM;

INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12');

SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;

EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;

SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;

i1	i2	i3	d3

1	NULL	NULL	NULL

2	NULL	NULL	NULL

SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;

i1	i2	i3	d3

revno: 3654

committer: Igor Babaev <igor@askmonty.org>

branch nick: maria-5.3-bugs

timestamp: Fri 2013-05-03 18:45:20 -0700

message:

  Made consistent handling of the predicates of the form

  <non-nullable datatime field> IS NULL in outer joins with

  that in inner joins.

  Previously such condition was transformed into the condition

  <non-nullable datatime field> = 0 unless the field belonged

  to an inner table of an outer join. In this case the predicate

  was interpreted as for any other field.

  Now if the field in the predicate <non-nullable datatime field> IS NULL

  belongs to an inner table of an outer join the predicate is

  transformed into the disjunction

  <non-nullable datatime field> = 0 OR <non-nullable datatime field> IS NULL.

  This is fully compatible with the semantics of such predicates in 5.5.