Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.3.20, 10.4.10, 10.5.1
-
None
Description
CREATE TABLE t1 (a INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1),(2); |
|
|
CREATE TABLE t2 (b INT, c INT, KEY(c)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (1,NULL),(2,10); |
|
|
CREATE TABLE t3 (d INT) ENGINE=MyISAM; |
INSERT INTO t3 VALUES (11),(12); |
|
|
CREATE TABLE t4 (e INT, f INT, KEY(f)) ENGINE=MyISAM; |
|
|
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
SET optimizer_switch='mrr=on,not_null_range_scan=on'; |
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
|
10.5 9fd30949 |
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
a b c d e f
|
1 NULL NULL NULL NULL NULL |
2 NULL NULL NULL NULL NULL |
SET optimizer_switch='mrr=on,not_null_range_scan=on'; |
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
a b c d e f
|
1 1 NULL NULL NULL NULL |
2 1 NULL NULL NULL NULL |
1 2 10 NULL NULL NULL |
2 2 10 NULL NULL NULL |
PostgreSQL, MySQL 5.7 and previous versions of MariaDB return the same result as the first query (two rows, all NULLs except for column a).
Execution plans:
EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 |
1 SIMPLE t2 ALL c NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) |
1 SIMPLE t4 ALL f NULL NULL NULL 0 0.00 Using where; Using join buffer (incremental, BNL join) |
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) |
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`e` AS `e`,`test`.`t4`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t4`.`e` = `test`.`t2`.`b` and `test`.`t3`.`d` = `test`.`t4`.`f`)) on(`test`.`t2`.`c` = `test`.`t1`.`a`) where 1 |
SET optimizer_switch='mrr=on,not_null_range_scan=on'; |
EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t4 const f NULL NULL NULL 1 100.00 Impossible ON condition |
1 SIMPLE t3 const NULL NULL NULL NULL 1 100.00 Impossible ON condition |
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 |
1 SIMPLE t2 ALL c NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) |
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,NULL AS `d`,NULL AS `e`,NULL AS `f` from `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(multiple equal(`test`.`t2`.`b`, NULL) and multiple equal(NULL, NULL))) on(`test`.`t2`.`c` = `test`.`t1`.`a`) where 1 |
If table t2 is changed from MyISAM to Aria, the result is incorrect in a different way – the second query returns an empty result set:
CREATE TABLE t1 (a INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1),(2); |
|
|
CREATE TABLE t2 (b INT, c INT, KEY(c)) ENGINE=Aria; |
INSERT INTO t2 VALUES (1,NULL),(2,10); |
|
|
CREATE TABLE t3 (d INT) ENGINE=MyISAM; |
INSERT INTO t3 VALUES (11),(12); |
|
|
CREATE TABLE t4 (e INT, f INT, KEY(f)) ENGINE=MyISAM; |
|
|
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
SET optimizer_switch='mrr=on,not_null_range_scan=on'; |
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
CREATE TABLE t1 (a INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1),(2); |
CREATE TABLE t2 (b INT, c INT, KEY(c)) ENGINE=Aria; |
INSERT INTO t2 VALUES (1,NULL),(2,10); |
CREATE TABLE t3 (d INT) ENGINE=MyISAM; |
INSERT INTO t3 VALUES (11),(12); |
CREATE TABLE t4 (e INT, f INT, KEY(f)) ENGINE=MyISAM; |
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
a b c d e f
|
1 NULL NULL NULL NULL NULL |
2 NULL NULL NULL NULL NULL |
SET optimizer_switch='mrr=on,not_null_range_scan=on'; |
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
a b c d e f
|
Execution plans for test case with Aria for t2:
EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 |
1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 Using where |
1 SIMPLE t4 ALL f NULL NULL NULL 0 0.00 Using where |
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where |
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t4`.`e` AS `e`,`test`.`t4`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t4`.`e` = `test`.`t2`.`b` and `test`.`t3`.`d` = `test`.`t4`.`f`)) on(`test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where 1 |
SET optimizer_switch='mrr=on,not_null_range_scan=on'; |
EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.d = t4.f ON t2.b = t4.e ON t1.a = t2.c; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t4 const f NULL NULL NULL 1 100.00 Impossible ON condition |
1 SIMPLE t3 const NULL NULL NULL NULL 1 100.00 Impossible ON condition |
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 |
1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,NULL AS `d`,NULL AS `e`,NULL AS `f` from `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(multiple equal(`test`.`t2`.`b`, NULL) and multiple equal(NULL, NULL))) on(`test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where 1 |