|
Thanks for the report and the test case.
The workaround would be to set optimizer_switch='derived_merge=off'.
|
Test case (exactly the same as in the attachment, just cleaned up)
|
CREATE TABLE T1(
|
K1 INT PRIMARY KEY,
|
Name VARCHAR(15)
|
);
|
|
INSERT INTO T1 VALUES (1,'T1Row1');
|
|
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.*,
|
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 (
|
SELECT
|
t2i.*
|
FROM
|
T1 as t1i
|
LEFT JOIN T2 as t2i
|
ON t2i.K1r = t1i.K1
|
WHERE
|
t1i.K1 = 1
|
and t2i.K2 IS NOT NULL
|
) as t2b
|
ON t2b.K1r = t1a.K1
|
AND t2b.rowTimestamp > t2a.rowTimestamp
|
OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)
|
WHERE
|
t1a.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 (
|
SELECT
|
t2i.*
|
FROM
|
T1 as t1i
|
LEFT JOIN T2 as t2i
|
ON t2i.K1r = t1i.K1
|
WHERE
|
t1i.K1 = 1
|
and t2i.K2 IS NOT NULL
|
) as t2b
|
ON t2b.K1r = t1a.K1
|
AND t2b.rowTimestamp > t2a.rowTimestamp
|
OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)
|
WHERE
|
t1a.K1 = 1
|
and t2b.K2 IS NULL
|
;
|
|
DROP TABLE T1, T2;
|
|
|
Thank you for your prompt response.
The workaround solved the problem and also a related performance issue. Query time (on the application query from which the simplified test case I sent was derived) went from 7 minutes to 1.5 seconds, the same time as the MySql 5.5 we are looking to migrate from.
Your help has put the migration back on the front burner for us. Thanks again.
|
|
pp00p0ppp0pppp00jq
|
|
00ⁿ6)):/@lvo€
dv
|
|
Despite the same EXPLAIN EXTENDED it looks like optimization problem because fixing condition like this solves the problem (t2b.K2 IS NULL or t2b.K2 = 222)
|
|
Conditions looks like the same (in terms of optimization) except 2 last:
T@5 : | | | | | | info: Query: SELECT
WHERE:(WHERE in setup_conds) 0x7f6b6c023390 ((`t1i`.`K1` = 1) and (`t2i`.`K2` is not null))
WHERE:(WHERE in setup_conds) 0x7f6b6c025c90 (`t1a`.`K1` = 1)
WHERE:(original) 0x7f6b6c02fa98 ((`test`.`t1a`.`K1` = 1) and (`test`.`t2a`.`K1r` = `test`.`t1a`.`K1`))
WHERE:(after equal_items) 0x7f6b6c02fa98 (multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`))
WHERE:(after const change) 0x7f6b6c02fa98 (multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`))
WHERE:(after remove) 0x7f6b6c02fd98 multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`)
WHERE:(after substitute_best_equal) 0x7f6b6c032760 (`test`.`t2a`.`K1r` = 1)
WHERE:(constants) (nil)
WHERE:(t2a) 0x7f6b6c032760 (`test`.`t2a`.`K1r` = 1)
WHERE:(t2a) 0x7f6b6c032760 (`test`.`t2a`.`K1r` = 1)
WHERE:(t1i) (nil)
WHERE:(t1i) (nil)
WHERE:(t2i) (nil)
WHERE:(t2i) (nil)
T@5 : | | | | | | info: Query: SELECT
WHERE:(WHERE in setup_conds) 0x7f6b6c0233a8 ((`t1i`.`K1` = 1) and (`t2i`.`K2` is not null))
WHERE:(WHERE in setup_conds) 0x7f6b6c02ae68 ((`t1a`.`K1` = 1) and (isnull(`t2b`.`K2`) or (`t2b`.`K2` = 222)))
WHERE:(original) 0x7f6b6c0304c0 ((`test`.`t1a`.`K1` = 1) and (isnull(`test`.`t2i`.`K2`) or (`test`.`t2i`.`K2` = 222)) and (`test`.`t2a`.`K1r` = `test`.`t1a`.`K1`))
WHERE:(after equal_items) 0x7f6b6c0304c0 ((isnull(`test`.`t2i`.`K2`) or multiple equal(222, `test`.`t2i`.`K2`)) and multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`))
WHERE:(after const change) 0x7f6b6c0304c0 ((isnull(`test`.`t2i`.`K2`) or multiple equal(222, `test`.`t2i`.`K2`)) and multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`))
WHERE:(after remove) 0x7f6b6c0304c0 ((isnull(`test`.`t2i`.`K2`) or multiple equal(222, `test`.`t2i`.`K2`)) and multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`))
WHERE:(after substitute_best_equal) 0x7f6b6c0304c0 ((`test`.`t2a`.`K1r` = 1) and (isnull(`test`.`t2i`.`K2`) or (`test`.`t2i`.`K2` = 222)))
WHERE:(constants) (nil)
WHERE:(t2a) 0x7f6b6c037320 (`test`.`t2a`.`K1r` = 1)
WHERE:(t2a) 0x7f6b6c037320 (`test`.`t2a`.`K1r` = 1)
WHERE:(t1i) (nil)
WHERE:(t1i) (nil)
WHERE:(t2i) 0x7f6b6c038bc0 (isnull(`test`.`t2i`.`K2`) or (`test`.`t2i`.`K2` = 222))
WHERE:(t2i) 0x7f6b6c038f98 trigcond(trigcond((isnull(`test`.`t2i`.`K2`) or (`test`.`t2i`.`K2` = 222))))
T@5 : | | | | | | info: Query: SELECT
WHERE:(WHERE in setup_conds) 0x7f6b6c023390 ((`t1i`.`K1` = 1) and (`t2i`.`K2` is not null))
WHERE:(WHERE in setup_conds) 0x7f6b6c026018 ((`t1a`.`K1` = 1) and isnull(`t2b`.`K2`))
WHERE:(original) 0x7f6b6c02ff18 ((`test`.`t1a`.`K1` = 1) and isnull(`test`.`t2i`.`K2`) and (`test`.`t2a`.`K1r` = `test`.`t1a`.`K1`))
WHERE:(after equal_items) 0x7f6b6c02ff18 (isnull(`test`.`t2i`.`K2`) and multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`))
WHERE:(after const change) 0x7f6b6c02ff18 (isnull(`test`.`t2i`.`K2`) and multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`))
WHERE:(after remove) 0x7f6b6c02ff18 (isnull(`test`.`t2i`.`K2`) and multiple equal(1, `test`.`t1a`.`K1`, `test`.`t2a`.`K1r`))
WHERE:(after substitute_best_equal) 0x7f6b6c02ff18 ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
WHERE:(constants) (nil)
WHERE:(t2a) 0x7f6b6c035e58 (`test`.`t2a`.`K1r` = 1)
WHERE:(t2a) 0x7f6b6c035e58 (`test`.`t2a`.`K1r` = 1)
WHERE:(t1i) (nil)
WHERE:(t1i) (nil)
WHERE:(t2i) 0x7f6b6c025f50 isnull(`test`.`t2i`.`K2`)
WHERE:(t2i) 0x7f6b6c038138 trigcond(trigcond(isnull(`test`.`t2i`.`K2`)))
|
|
Interesting results of calling is_null() methods (so bug is not in optimization):
T@5 : | | | | | | info: Query: SELECT –
T@5 : | | | | | | info: Query: SELECT – (t2b.K2 IS NULL or t2b.K2 = 222)
T@5 : | | | | | | | | | | | | | | | | XXX: Item_direct_view_ref: 2 is NOT NULL
T@5 : | | | | | | | | | | | | | | | XXX: Item_func_isnull: result 0
T@5 : | | | | | | | | | | | | | | | | XXX: Item_direct_view_ref: 3 is NOT NULL
T@5 : | | | | | | | | | | | | | | | XXX: Item_func_isnull: result 0
T@5 : | | | | | | | | | | | | | | | | XXX: Item_direct_view_ref: 3 is NOT NULL
T@5 : | | | | | | | | | | | | | | | XXX: Item_func_isnull: result 0
T@5 : | | | | | | | | | | | | | XXX: Item_direct_view_ref: ref null
T@5 : | | | | | | | | | | | | XXX: Item_func_isnull: result 1
T@5 : | | | | | | info: Query: SELECT – t2b.K2 IS NULL
T@5 : | | | | | | | | | | | | | XXX: Item_direct_view_ref: ref null
T@5 : | | | | | | | | | | | | XXX: Item_func_isnull: result 1
T@5 : | | | | | | | | | | | | | XXX: Item_direct_view_ref: ref null
T@5 : | | | | | | | | | | | | XXX: Item_func_isnull: result 1
T@5 : | | | | | | | | | | | | | XXX: Item_direct_view_ref: ref null
T@5 : | | | | | | | | | | | | XXX: Item_func_isnull: result 1
T@5 : | | | | | | | | | | | | | XXX: Item_direct_view_ref: ref null
T@5 : | | | | | | | | | | | | XXX: Item_func_isnull: result 1
T@5 : | | | | | | | | | | | | | XXX: Item_direct_view_ref: ref null
T@5 : | | | | | | | | | | | | XXX: Item_func_isnull: result 1
T@5 : | | | | | | | | | | | | | XXX: Item_direct_view_ref: ref null
T@5 : | | | | | | | | | | | | XXX: Item_func_isnull: result 1
|
|
It is not a MDEV-7445-7565-7846 duplicate.
|
|
just diff to see what is going on in is_null().
|
|
It is probably incorrect choice of reference table for NULL detection (t1i).
|
|
Adding any expression "saves" execution, so it is problem of "second pointer":
SELECT – t2b.K2 IS NULL
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 (
SELECT
t2i.*
FROM
T1 as t1i
LEFT JOIN T2 as t2i
ON t2i.K1r = t1i.K1
WHERE
t1i.K1 = 1
and t2i.K2 IS NOT NULL
) as t2b
ON t2b.K1r = t1a.K1
AND t2b.rowTimestamp > t2a.rowTimestamp
OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)
WHERE
t1a.K1 = 1
and (t2b.K2 + 1) 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
|
|
the reference null table is the same in all cases of Item_direct_view_ref calls
|
|
Execution path (via sub_select()) and expression except obvious OR change looks also the same.
the question is why null_row set...
|
|
outer trigger is switched off so IS_NULL looks like TRUE
Item_func_trig_cond::val_int (this=0x7fffe8033398) at /home/bell/maria/git/server/sql/item_cmpfunc.h:486
(gdb) p *trig_var
$2 = false
(gdb) p dbug_print_item(args[0])
$3 = 0x1518cc0 <dbug_item_print_buf> "trigcond(isnull(`test`.`t2i`.`K2`))"
(gdb)
|
|
Ok, the situation is really complicated.
First of all I've slightly changed the test case:
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.*, 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
|
( SELECT t2i.*
|
FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
|
WHERE t1i.K1 = 1 and t2i.K2 IS NOT NULL ) as t2b
|
ON t2b.K1r = t1a.K1
|
AND t2b.rowTimestamp > t2a.rowTimestamp
|
OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)
|
WHERE
|
t1a.K1 = 1 AND
|
t2b.K2 IS NULL;
|
I've also added the result of the manual merge of the derived table t2b into
the main query:
|
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.K2 IS NOT NULL) AND
|
((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
|
(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
|
WHERE
|
t2a.K1r = 1 AND t2i.K2 IS NULL;
|
These two queries return different results and only the first one returns an incorrect result set.
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
|
-> ( SELECT t2i.*
|
-> FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
|
-> WHERE t1i.K1 = 1 and t2i.K2 IS NOT NULL ) as t2b
|
-> ON t2b.K1r = t1a.K1
|
-> AND t2b.rowTimestamp > t2a.rowTimestamp
|
-> OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)
|
-> 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]> 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.K2 IS NOT NULL) AND
|
-> ((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
|
-> (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
|
-> 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 |
|
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
|
I've checked what execution plans were used and the plans are different:
the first query uses 'not exists' optimization for the table t2i, while the second doesn't.
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
|
-> ( SELECT t2i.*
|
-> FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
|
-> WHERE t1i.K1 = 1 and t2i.K2 IS NOT NULL ) as t2b
|
-> ON t2b.K1r = t1a.K1
|
-> AND t2b.rowTimestamp > t2a.rowTimestamp
|
-> OR (t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)
|
-> 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`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `test`.`t2a`.`K2`))) and (`test`.`t2i`.`K2` is not null))) 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.K2 IS NOT NULL) AND
|
-> ((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
|
-> (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
|
-> 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 | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | Using where |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
|
4 rows in set, 1 warning (8.90 sec)
|
|
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` join `test`.`t2` `t2i`) on(((`test`.`t1i`.`K1` = 1) and (`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`K2` is not null) and (((`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `test`.`t2a`.`K2`))))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`)) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
Notice that for the second query the embedded left join is transformed into an inner join.
For the first query it's not transformed into an inner join. This is not good by itself.
Let's check how it is in maridb-5.2.
There are no mergeable derived tables in 5.2, but we can use a view instead:
CREATE VIEW v1 AS
|
SELECT t2i.*
|
FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
|
WHERE t1i.K1 = 1 and t2i.K2 IS NOT NULL ;
|
|
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)
|
WHERE
|
t1a.K1 = 1 AND
|
t2b.K2 IS NULL;
|
We have the same problem with the lack of transformation of embedded outer join:
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)
|
-> 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`))) and (`test`.`t2i`.`K2` is not null) and (`test`.`t1i`.`K1` = 1))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`)) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Moreover, we have a wrong result here as well:
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)
|
-> 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 |
|
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
|
|
|
I've added MDEV-12099 "LEFT JOIN merged from view with null rejected WHERE is not converted into INNER JOIN" to fix the above problem
Yet 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).
|
|
Now we can focus exclusively on the query:
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;
|
and find out why it returns a wrong result set.
Most probably it's because the optimizer tries to use 'Not exists' optimization shortcut for the last table.
Not exists optimization was originally introduced for simple, not nested outer joins. When nested
outer joins were supported no special measures were taken to support this optimization for them.
So the following code of get_best_combination() tried to block usage of this optimization for nested
outer joins:
if (!*j->on_expr_ref)
|
form->reginfo.not_exists_optimize=0; // Only with LEFT JOIN
|
This code erroneously allowed to use this optimization for simple nested outer joins.
The proper code should block usage of this optimization for any nested outer joins:
if (!*j->on_expr_ref || j->table->pos_in_table_list->embedding)
|
form->reginfo.not_exists_optimize=0; // Only with LEFT JOIN
|
With this code applied to 5.2 the query returns the right result set:
ariaDB [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 |
|
+----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
|
|
|
If I apply the change
if (!*j->on_expr_ref || j->table->pos_in_table_list->embedding)
|
form->reginfo.not_exists_optimize=0; // Only with LEFT JOIN
|
to 5.5 the last query, of course, returns the correct result set, but EXPLAIN show that 'Not exists" optimization is not applied to the query anymore. Also some a test from join_nested fails because EXPLAIN does not show
'Not exists' anymore.
To prohibit applying 'Not exists' optimization to nested outer joins is, probably, not a bright idea.
Let's try to support this optimization for any nested outer join.
To figure out why the current code does not support it properly, let's first revert the patch merged from the mysql code line that tried to fix bug #58490 that concerns exacly 'Not exists' optimization applied to nested outer joins. (I'll explain later why this patch does not fix the problem in general.)
The mariadb-5.3 still has the old variant of the code where this optimization is applied
for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
|
{
|
/* Check all predicates that has just been activated. */
|
/*
|
Actually all predicates non-guarded by first_unmatched->found
|
will be re-evaluated again. It could be fixed, but, probably,
|
it's not worth doing now.
|
*/
|
if (tab->select_cond && !tab->select_cond->val_int())
|
{
|
/* The condition attached to table tab is false */
|
if (tab == join_tab)
|
{
|
found= 0;
|
if (tab->table->reginfo.not_exists_optimize)
|
DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
|
}
|
else
|
{
|
/*
|
Set a return point if rejected predicate is attached
|
not to the last table of the current nest level.
|
*/
|
join->return_tab= tab;
|
if (tab->table->reginfo.not_exists_optimize)
|
DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
|
else
|
DBUG_RETURN(NESTED_LOOP_OK);
|
}
|
}
|
}
|
Let's figure out what's wrong with this code.
The problem with the above code is that it actually does not check that the IS NULL predicate is
actually active at the moment when we decide to skip records. This check should be done like this:
bool not_exists_opt_is_applicable=
|
tab->table->reginfo.not_exists_optimize;
|
for (JOIN_TAB *first_upper= first_unmatched->first_upper;
|
not_exists_opt_is_applicable && first_upper;
|
first_upper= first_upper->first_upper)
|
{
|
if (!first_upper->found)
|
not_exists_opt_is_applicable= false;
|
}
|
The fix for bug #58490 missed this point as well.
|
|
OK to push
|
|
The fix for this bug was pushed into the 5.5 tree.
It should applied upstream as it is.
|
BugReport.txt
diff