Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.27, 5.3.9
-
None
-
None
Description
Assigning it to Sanja because it's a regression introduced with a bugfix for MDEV-486 (revno 3574 on maria/5.3 and revno 3526 on maria/5.5).
The following test case
SET optimizer_switch = 'derived_merge=on'; |
|
|
CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (4),(6); |
|
|
CREATE TABLE t2 (f2 INT) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (7),(8); |
|
|
SELECT * FROM ( |
SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2 |
) AS alias; |
|
|
returns
f1 f2
|
NULL NULL
|
NULL NULL
|
on the affected versions, and
f1 f2
|
NULL 7
|
NULL 8
|
on unaffected versions. The latter is correct.
Reproducible with MyISAM, Aria, InnoDB.
Reproducible starting from revno 3574 on maria/5.3 from revno 3526 on maria/5.5, and up to current ones (3584 and 3544, correspondingly).
Not reproducible if I replace the subquery with a view.
Not reproducible if I replace RIGHT JOIN with LEFT JOIN.
The subquery itself, executed separately, produces the expected result.
Reproducible with the default optimizer switch as well as with optimizer_switch='derived_merge=on' only.
EXPLAIN (with the default optimizer_switch):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`f1` = `test`.`t2`.`f2`)) where 1
|