[MDEV-3799] Wrong result (NULLs instead of real values) with RIGHT JOIN in a FROM subquery and derived_merge=on Created: 2012-10-05  Updated: 2012-10-11  Resolved: 2012-10-11

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.27, 5.3.9
Fix Version/s: 5.5.28, 5.3.10

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates

 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



 Comments   
Comment by Oleksandr Byelkin [ 2012-10-09 ]

Probably incorrectly detected ref_table in case of RIGHT JOIN.

Comment by Oleksandr Byelkin [ 2012-10-10 ]

TABLE_LIST::get_real_join_table should take table corresponding to the field (not just first in join)...

Comment by Oleksandr Byelkin [ 2012-10-10 ]

fix committed.

Comment by Oleksandr Byelkin [ 2012-10-11 ]

pushed into 5.3

Generated at Thu Feb 08 06:51:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.