Fixed bug mdev-4420.

The code of JOIN::optimize that performed substitutions for the best equal
field in all ref items did not take into account that a multiple equality
could contain the result of the single-value subquery if the subquery is
inexpensive. This code was corrected.
Also made necessary corresponding corrections in the code of make_join_select().

1 files changed, 25 insertions, 0 deletions

diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 4fd303dfd44..e06d799845e 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -295,4 +295,29 @@ node_uid	date	mirror_date	result
 2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
 set optimizer_switch=@tmp_mdev614;
 DROP TABLE t1;
+#
+# MDEV-4420: non-expensive single-value subquery used as
+#            used as an access key to join a table
+#
+create table t1 (a varchar(3));
+insert into t1 values ('USA'), ('FRA');
+create table t2 select * from t1;
+insert into t2 values ('RUS');
+create table t3 select * from t2;
+create index idx on t3(a);
+explain extended
+select * from t1, t2 left join t3 on ( t2.a = t3.a )
+where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ref	idx	idx	6	func	2	100.00	Using where; Using index
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where ((`test`.`t1`.`a` = (select min(`test`.`t1`.`a`) from `test`.`t1`)) and (`test`.`t2`.`a` = (select min(`test`.`t1`.`a`) from `test`.`t1`)))
+select * from t1, t2 left join t3 on ( t2.a = t3.a )
+where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
+a	a	a
+FRA	FRA	FRA
+drop table t1,t2,t3;
 set optimizer_switch=@subselect2_test_tmp;