MDEV-17382 Hash join algorithm should not be used to join materialized

           derived table / view by equality

Now rows of a materialized derived table are always put into a
temporary table before join operation. If BNLH is used to join this
table with the result of a partial join then both operands of the
join are actually put into main memory. In most cases this is not
efficient.
We could avoid this by sending the rows of the derived table directly
to the join operation. However this kind of data flow is not supported
yet.
Fixed by not allowing usage of hash join algorithm to join a materialized
derived table if it's joined by an equality predicate of the form
f=e where f is a field of the derived table.

Change for the test case in 10.3: splitting must be turned off to preserve
the explain.

1 files changed, 1 insertions, 1 deletions

diff --git a/mysql-test/main/innodb_mrr_cpk.result b/mysql-test/main/innodb_mrr_cpk.result
index 28d7dd51df8..a2e43d7d127 100644
--- a/mysql-test/main/innodb_mrr_cpk.result
+++ b/mysql-test/main/innodb_mrr_cpk.result
@@ -226,7 +226,7 @@ set join_cache_level=3;
 explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	
-1	PRIMARY	<derived2>	hash_ALL	key0	#hash#key0	25	test.t1.id	#	Using join buffer (flat, BNLH join)
+1	PRIMARY	<derived2>	ref	key0	key0	25	test.t1.id	#	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	#	
 set join_cache_level= @tmp_mdev5037;
 drop table t0,t1,t2;