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().

5 files changed, 48 insertions, 3 deletions

diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index ae224fe0220..a1796d7fa3b 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1460,7 +1460,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	D	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	DSAR	system	NULL	NULL	NULL	NULL	1	
 1	SIMPLE	DT	range	t_id	t_id	2	NULL	2	Using where
-1	SIMPLE	DSA	ref	PRIMARY	PRIMARY	8	const,test.DT.t_id,test.D.birthday	1	Using index
+1	SIMPLE	DSA	ref	PRIMARY	PRIMARY	8	const,test.DT.t_id,func	1	Using index
 SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR
 WHERE DU.dog_id=D.dog_id AND D.dog_id=DT.dog_id AND D.birthday=DT.birthday AND
 DT.t_id=DSA.t_id AND DT.birthday=DSA.birthday AND DSA.dog_id=DSAR.dog_id;
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;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 3fc91b452a5..2d229db9ec5 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2485,7 +2485,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ref	a	a	5	const	1	Using index
-1	PRIMARY	t2	ref	a	a	5	test.t3.b	1	Using index
+1	PRIMARY	t2	ref	a	a	5	func	1	Using index
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	0	
 SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index f18de3c4d0b..a0ebbb3305d 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2499,7 +2499,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ref	a	a	5	const	1	Using index
-1	PRIMARY	t2	ref	a	a	5	test.t3.b	1	Using index
+1	PRIMARY	t2	ref	a	a	5	func	1	Using index
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	0	
 SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test
index 68894ad18cb..e286c92285d 100644
--- a/mysql-test/t/subselect2.test
+++ b/mysql-test/t/subselect2.test
@@ -315,6 +315,26 @@ set optimizer_switch=@tmp_mdev614;
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-4420: non-expensive single-value subquery used as
+--echo #            used as an access key to join a table
+--echo #
+
+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 );
+
+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 );
+
+drop table t1,t2,t3;
 
 set optimizer_switch=@subselect2_test_tmp;