Bug #85192 "derived_merge=on" will cause incorrect resultset
Submitted: 27 Feb 2017 7:31 Modified: 25 Sep 2018 22:59
Reporter: Simon Ko Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.16, 5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[27 Feb 2017 7:31] Simon Ko 
Description:
"derived_merge=on" will cause incorrect resultset when using derived column in where clause with left join.

How to repeat:
create table t1 (fldt11 varchar(10));
create table t2 (fldt21 varchar(10));
insert into t1 values ('100');
insert into t1 values ('200');
insert into t1 values ('300');
insert into t1 values ('400');
insert into t2 values ('100');
insert into t2 values ('200');
insert into t2 values ('300');
SET optimizer_switch = 'derived_merge=on';

select * from (
	select * from (
	(select fldt11 from t1) tier11 left join
	(select 'PEN' as cat, fldt21  from t2) tier12 
		on tier11.fldt11 = tier12.fldt21 )
	) tier2   
where cat ='PEN';

--- Result ---
fldt11	cat	fldt21
100	PEN	100
200	PEN	200
300	PEN	300
400	NULL	NULL

SET optimizer_switch = 'derived_merge=off'; 

select * from (
	select * from (
	(select fldt11 from t1) tier11 left join
	(select 'PEN' as cat, fldt21  from t2) tier12 
		on tier11.fldt11 = tier12.fldt21 )
	) tier2   
where cat ='PEN';
--- Result
fldt11	cat	fldt21
200	PEN	200
300	PEN	300
100	PEN	100
[27 Feb 2017 7:40] MySQL Verification Team 
Hello Simon Ko,

Thank you for the report and test case.
Observed this with 5.7.17 build.

Thanks,
Umesh
[7 Sep 2018 12:39] Sveta Smirnova 
Not repeatable with versions 5.7.21 and 8.0.12:

mysql> select * from (select * from ((select fldt11 from t1) tier11 left join(select 'PEN' as cat, fldt21  from t2) tier12 on tier11.fldt11 = tier12.fldt21 )) tier2 where cat ='PEN';
+--------+------+--------+
| fldt11 | cat  | fldt21 |
+--------+------+--------+
| 100    | PEN  | 100    |
| 200    | PEN  | 200    |
| 300    | PEN  | 300    |
+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off
1 row in set (0.00 sec)

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.12-debug |
+--------------+
1 row in set (0.00 sec)
[25 Sep 2018 22:58] Roy Lyseng 
This is probably a duplicate of bug#26627181 which was fixed in 5.7.21 and 8.0.4.