We're updating the issue view to help you get more done. 

Wrong result (NULLs instead of real values) with INNER and RIGHT JOIN in a FROM subquery, derived_merge=on

Description

The following test case

1 2 3 4 5 6 7 8 9 10 11 12 SET optimizer_switch = 'derived_merge=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (3),(4); CREATE TABLE t3 (c INT) ENGINE=MyISAM; INSERT INTO t3 VALUES (5),(6); SELECT * FROM ( SELECT c FROM ( t1 INNER JOIN t2 ) RIGHT JOIN t3 ON a = c ) AS alias;

returns NULLs:

1 2 3 4 c ------ NULL NULL

If the subquery is executed separately, it returns not null values, which is the correct result:

1 2 3 4 c ------ 5 6

The problem was either introduced or made visible with the revision http://bazaar.launchpad.net/~maria-captains/maria/5.3/revision/3574:

1 2 3 4 5 6 7 8 9 10 revno: 3574 committer: sanja@montyprogram.com branch nick: work-maria-5.3-MDEV-486 timestamp: Wed 2012-09-05 23:23:58 +0300 message: MDEV-486 LP BUG#1010116 fix. Link view/derived table fields to a real table to check turning the table record to null row. Item_direct_view_ref wrapper now checks if table is turned to null row.

Reproducible on current maria/5.3, maria/5.5, maria/10.0.
MySQL 5.6 and MariaDB 5.2 return the correct result.

Minimal optimizer_switch: derived_merge=on.
Also reproducible with the default optimizer_switch:

1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN with the default optimizer_switch (also wrong result):

1 2 3 4 5 6 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 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) 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t3`.`c` AS `c` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on((`test`.`t1`.`a` = `test`.`t3`.`c`)) where 1

EXPLAIN with the minimal optimizer_switch (also wrong result):

1 2 3 4 5 6 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`t3`.`c` AS `c` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on((`test`.`t1`.`a` = `test`.`t3`.`c`)) where 1

Environment

None

Status

Assignee

Oleksandr Byelkin

Reporter

Elena Stepanova

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Affects versions

10.0.0
5.5.28
5.3.10

Priority

Major