MariaDB [test]> explain extended
|
-> SELECT
|
-> t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
|
-> t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
|
-> FROM
|
-> t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
|
-> LEFT JOIN
|
-> v1 as t2b
|
-> ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
|
-> (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
|
-> OR (t2b.K2 IS NULL)
|
-> WHERE
|
-> t1a.K1 = 1 AND
|
-> t2b.K2 IS NULL;
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
|
| 1 | SIMPLE | t1a | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
|
| 1 | SIMPLE | t2a | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
|
| 1 | SIMPLE | t1i | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
|
| 1 | SIMPLE | t2i | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Not exists |
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
|
MariaDB [test]> show warnings;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select '1' AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(((`test`.`t1i`.`K1` = 1) and (`test`.`t2i`.`K1r` = 1)))) on(((((`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`) and ((`test`.`t2a`.`K1r` = 1) and (`test`.`t2i`.`K1r` = 1))) or ((`test`.`t2i`.`K2` > `test`.`t2a`.`K2`) and (`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`)) or isnull(`test`.`t2i`.`K2`)) and (`test`.`t1i`.`K1` = 1))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`)) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
MariaDB [test]> explain extended
|
-> 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;
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
|
| 1 | SIMPLE | t1a | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
|
| 1 | SIMPLE | t2a | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
|
| 1 | SIMPLE | t1i | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
|
| 1 | SIMPLE | t2i | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Not exists |
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
|
MariaDB [test]> show warnings;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select '1' AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(((`test`.`t1i`.`K1` = 1) and (`test`.`t2i`.`K1r` = 1)))) on(((((`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`) and ((`test`.`t2a`.`K1r` = 1) and (`test`.`t2i`.`K1r` = 1))) or ((`test`.`t2i`.`K2` > `test`.`t2a`.`K2`) and (`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`)) or isnull(`test`.`t2i`.`K2`)) and (`test`.`t1i`.`K1` = 1))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`)) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
I've added
MDEV-12099"LEFT JOIN merged from view with null rejected WHERE is not converted into INNER JOIN" to fix the above problemYet without the transformation of the embedding LEFT JOIN into an INNER JOIN the server must return the
right result set.
To factor out this problem I've changed the testcase removed the predicate (t2i.K2 IS NOT NULL) from the WHERE condition of the view v1 and ORed the predicate (t2b.K2 IS NULL) to the ON condition of the query
CREATE VIEW v1 AS
SELECT t2i.*
FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
WHERE t1i.K1 = 1 ;
SELECT
t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
FROM
t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
LEFT JOIN
v1 as t2b
ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
OR (t2b.K2 IS NULL)
WHERE
t1a.K1 = 1 AND
t2b.K2 IS NULL;
The result of the manual merge of v1 into the main query now looks like this:
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;
Now the optimizer does not convert the embedded OUTER JOIN into INNER JOIN for
both queries:
MariaDB [test]> explain extended
-> SELECT
-> t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
-> t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
-> FROM
-> t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
-> LEFT JOIN
-> v1 as t2b
-> ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
-> (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
-> OR (t2b.K2 IS NULL)
-> WHERE
-> t1a.K1 = 1 AND
-> t2b.K2 IS NULL;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
| 1 | SIMPLE | t1a | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
| 1 | SIMPLE | t2a | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 1 | SIMPLE | t1i | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | t2i | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Not exists |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
MariaDB [test]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select '1' AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(((`test`.`t1i`.`K1` = 1) and (`test`.`t2i`.`K1r` = 1)))) on(((((`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`) and ((`test`.`t2a`.`K1r` = 1) and (`test`.`t2i`.`K1r` = 1))) or ((`test`.`t2i`.`K2` > `test`.`t2a`.`K2`) and (`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`)) or isnull(`test`.`t2i`.`K2`)) and (`test`.`t1i`.`K1` = 1))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
MariaDB [test]> explain extended
-> 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;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
| 1 | SIMPLE | t1a | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
| 1 | SIMPLE | t2a | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 1 | SIMPLE | t1i | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | t2i | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Not exists |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
MariaDB [test]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select '1' AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(((`test`.`t1i`.`K1` = 1) and (`test`.`t2i`.`K1r` = 1)))) on(((((`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`) and ((`test`.`t2a`.`K1r` = 1) and (`test`.`t2i`.`K1r` = 1))) or ((`test`.`t2i`.`K2` > `test`.`t2a`.`K2`) and (`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`)) or isnull(`test`.`t2i`.`K2`)) and (`test`.`t1i`.`K1` = 1))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
And both queries return wrong result sets:
MariaDB [test]> SELECT
-> t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
-> t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
-> FROM
-> t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
-> LEFT JOIN
-> v1 as t2b
-> ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
-> (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
-> OR (t2b.K2 IS NULL)
-> WHERE
-> t1a.K1 = 1 AND
-> t2b.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 |
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
MariaDB [test]>
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 | 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 |
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
(All results in these comments I've got on maridb-5.2. For all other version we get similar outputs).