Wrong result for nested left join using not_exists optimization

CREATE TABLE t1(

K1 INT PRIMARY KEY,

Name VARCHAR(15)

);

INSERT INTO t1 VALUES

(1,'T1Row1'), (2,'T1Row2');

CREATE TABLE t2(

K2 INT PRIMARY KEY,

K1r INT,

rowTimestamp DATETIME,

Event VARCHAR(15)

);

INSERT INTO t2 VALUES

(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'),

(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'),

(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3');

SELECT t1a.*, t2a.*,

t2i.K2 AS K2B, t2i.K1r AS K1rB,

t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB

FROM

t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1

LEFT JOIN

( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1)

ON (t1i.K1 = 1) AND

(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR

(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))

OR (t2i.K2 IS NULL))

WHERE

t2a.K1r = 1 AND t2i.K2 IS NULL;

MariaDB [test]> SELECT t1a.*, t2a.*, t2i.K2 AS K2B, t2i.K1r AS K1rB, t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB FROM t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 LEFT JOIN ( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) ON (t1i.K1 = 1) AND (((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) OR (t2i.K2 IS NULL)) WHERE t2a.K1r = 1 AND t2i.K2 IS NULL;

+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+

| K1 | Name   | K2 | K1r  | rowTimestamp        | Event        | K2B  | K1rB | rowTimestampB | EventB |

+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+

|  1 | T1Row1 |  3 |    1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL          | NULL   |

+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+

1 row in set (0.000 sec)

mysql> SELECT t1a.*, t2a.*,

    -> t2i.K2 AS K2B, t2i.K1r AS K1rB,

    -> t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB

    -> FROM

    -> t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1

    -> LEFT JOIN

    -> ( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1)

    -> ON (t1i.K1 = 1) AND

    -> (((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR

    -> (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))

    -> OR (t2i.K2 IS NULL))

    -> WHERE

    -> t2a.K1r = 1 AND t2i.K2 IS NULL;

+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+

| K1 | Name   | K2 | K1r  | rowTimestamp        | Event        | K2B  | K1rB | rowTimestampB | EventB |

+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+

|  1 | T1Row1 |  1 |    1 | 2015-04-13 10:42:11 | T1Row1Event1 | NULL | NULL | NULL          | NULL   |

|  1 | T1Row1 |  2 |    1 | 2015-04-13 10:42:12 | T1Row1Event2 | NULL | NULL | NULL          | NULL   |

|  1 | T1Row1 |  3 |    1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL          | NULL   |

+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+

3 rows in set (0.01 sec)