MDEV-30812: Improve output cardinality estimates for hash join

Introduces @@optimizer_switch flag: hash_join_cardinality

When this option is on, use EITS statistics to produce tighter bounds
for hash join output cardinality.

This patch is an extension / replacement to a similar patch in 10.6

New features:
- optimizer_switch hash_join_cardinality is on by default
- records_out is set to fanout when HASH is used
- Fixed bug in is_eits_usable: The function did not work with views

23 files changed, 494 insertions, 168 deletions

diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result
index efe6c03f2ec..0e1ea88404b 100644
--- a/mysql-test/main/analyze_format_json.result
+++ b/mysql-test/main/analyze_format_json.result
@@ -1236,7 +1236,7 @@ ANALYZE
             "cost": "REPLACED",
             "r_table_time_ms": "REPLACED",
             "r_other_time_ms": "REPLACED",
-            "filtered": 70,
+            "filtered": 0.209580004,
             "r_filtered": 70,
             "attached_condition": "t10.a < 700"
           },
diff --git a/mysql-test/main/analyze_format_json_timings.result b/mysql-test/main/analyze_format_json_timings.result
index af801095b81..4686359186e 100644
--- a/mysql-test/main/analyze_format_json_timings.result
+++ b/mysql-test/main/analyze_format_json_timings.result
@@ -78,7 +78,7 @@ X
             "cost": "REPLACED",
             "r_table_time_ms": "REPLACED",
             "r_other_time_ms": "REPLACED",
-            "filtered": 100,
+            "filtered": 10,
             "r_filtered": 20,
             "attached_condition": "t2.a < 100"
           },
@@ -174,7 +174,7 @@ X
             "cost": "REPLACED",
             "r_table_time_ms": "REPLACED",
             "r_other_time_ms": "REPLACED",
-            "filtered": 100,
+            "filtered": 10,
             "r_filtered": 100
           },
           "buffer_type": "flat",
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index c02ac192dde..d6a530a9f77 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -5977,10 +5977,9 @@ LEFT JOIN t5  ON t4.e1 = t5.e1
 LEFT JOIN  (SELECT e1  FROM t2 ) AS d ON  t4.e1 = d.e1) a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	128	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	128	
-2	MATERIALIZED	t5	hash_ALL	NULL	#hash#$hj	5	test.t4.e1	128	Using where; Using join buffer (flat, BNLH join)
-2	MATERIALIZED	t2	hash_ALL	NULL	#hash#$hj	5	test.t4.e1	128	Using where; Using join buffer (incremental, BNLH join)
+1	PRIMARY	t4	hash_ALL	NULL	#hash#$hj	5	test.t1.i1	128	Using where; Start temporary; Using join buffer (flat, BNLH join)
+1	PRIMARY	t5	hash_ALL	NULL	#hash#$hj	5	test.t4.e1	128	Using where; Using join buffer (incremental, BNLH join)
+1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	5	test.t4.e1	128	Using where; End temporary; Using join buffer (incremental, BNLH join)
 SELECT * FROM t1
 WHERE
 i1 < 10 AND
@@ -6035,12 +6034,11 @@ f1	f2
 EXPLAIN EXTENDED SELECT * FROM temp
 WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	temp	ALL	NULL	NULL	NULL	NULL	7	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
-2	MATERIALIZED	t2	hash_index	v1	#hash#v1:v1	4:9	test.t1.v1	10	33.33	Using index; Using join buffer (flat, BNLH join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Start temporary
+1	PRIMARY	temp	hash_ALL	NULL	#hash#$hj	9	test.t1.i1,test.t1.v1	7	10.00	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t2	hash_index	v1	#hash#v1:v1	4:9	test.t1.v1	10	10.00	Using index; End temporary; Using join buffer (incremental, BNLH join)
 Warnings:
-Note	1003	select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where `test`.`t2`.`v1` = `test`.`t1`.`v1`
+Note	1003	select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where `test`.`temp`.`f1` = `test`.`t1`.`i1` and `test`.`temp`.`f2` = `test`.`t1`.`v1` and `test`.`t2`.`v1` = `test`.`t1`.`v1`
 DROP TABLE t1,t2,temp;
 set join_cache_level=@save_join_cache_level;
 #
@@ -6163,9 +6161,9 @@ b	c	d	e
 EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t4	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t4.e	2	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t3	hash_index	d	#hash#d:d	5:5	test.t2.c	2	Using where; Using index; Using join buffer (incremental, BNLH join)
-1	SIMPLE	t4	hash_index	PRIMARY	#hash#PRIMARY:PRIMARY	4:4	test.t2.b	2	Using index; Using join buffer (incremental, BNLH join)
 SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
 a	b	c	d	e
 1	1	2	2	1
@@ -6176,9 +6174,9 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b
 WHERE e IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t4	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index; Not exists; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t4.e	2	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t3	hash_index	d	#hash#d:d	5:5	test.t2.c	2	Using where; Using index; Using join buffer (incremental, BNLH join)
-1	SIMPLE	t4	hash_index	PRIMARY	#hash#PRIMARY:PRIMARY	4:4	test.t2.b	2	Using where; Using index; Not exists; Using join buffer (incremental, BNLH join)
 SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
 WHERE e IS NULL;
 a	b	c	d	e
diff --git a/mysql-test/main/join_cache_cardinality.result b/mysql-test/main/join_cache_cardinality.result
new file mode 100644
index 00000000000..de6b99f4b3a
--- /dev/null
+++ b/mysql-test/main/join_cache_cardinality.result
@@ -0,0 +1,105 @@
+create table t1 (a int, b int, c int);
+insert into t1 select seq,seq/2, seq/4 from seq_1_to_100;
+create table t2 (a int, b int, c int);
+insert into t2 select seq, seq/2, seq/4 from seq_1_to_200;
+analyze table t1,t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+set optimizer_trace=1;
+set join_cache_level=6;
+set optimizer_switch='hash_join_cardinality=on';
+explain select *
+from t1, t2
+where t1.a=t2.a and t1.a=t2.b and t1.c=t2.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	15	test.t1.a,test.t1.a,test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "a",
+                "avg_frequency": 1
+            },
+            {
+                "field": "b",
+                "avg_frequency": 2
+            },
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 1
+    }
+]
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+ROWS_FOR_PLAN
+[100]
+explain select *
+from t1, t2 where t1.c=t2.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 3.9216
+    }
+]
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+ROWS_FOR_PLAN
+[392.16]
+explain select *
+from t1 straight_join t2 where t1.c=t2.c and t2.a<30;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.c	200	Using where; Using join buffer (flat, BNLH join)
+set @json= (select trace from information_schema.optimizer_trace);
+# Note that rows is the same:
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+JS
+[
+    {
+        "hash_join_columns": 
+        [
+            {
+                "field": "c",
+                "avg_frequency": 3.9216
+            }
+        ],
+        "rows": 0.568632
+    }
+]
+# Despite available selectivity:
+select json_detailed(json_extract(@json, '$**.selectivity_for_columns')) as JS;
+JS
+[
+    [
+        {
+            "column_name": "a",
+            "ranges": 
+            ["NULL < a < 30"],
+            "selectivity_from_histogram": 0.145
+        }
+    ]
+]
+drop table t1,t2;
diff --git a/mysql-test/main/join_cache_cardinality.test b/mysql-test/main/join_cache_cardinality.test
new file mode 100644
index 00000000000..9036f1fecd2
--- /dev/null
+++ b/mysql-test/main/join_cache_cardinality.test
@@ -0,0 +1,40 @@
+# Disabled embedded as it does not support optimizer_trace
+--source include/not_embedded.inc
+--source include/have_sequence.inc
+
+create table t1 (a int, b int, c int);
+insert into t1 select seq,seq/2, seq/4 from seq_1_to_100;
+
+create table t2 (a int, b int, c int);
+insert into t2 select seq, seq/2, seq/4 from seq_1_to_200;
+
+analyze table t1,t2 persistent for all;
+
+set optimizer_trace=1;
+set join_cache_level=6;
+set optimizer_switch='hash_join_cardinality=on';
+explain select *
+from t1, t2
+where t1.a=t2.a and t1.a=t2.b and t1.c=t2.c;
+
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+
+explain select *
+from t1, t2 where t1.c=t2.c;
+set @json= (select trace from information_schema.optimizer_trace);
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+select json_detailed(json_extract(@json, '$**.rest_of_plan[*].rows_for_plan'))
+as ROWS_FOR_PLAN;
+
+explain select *
+from t1 straight_join t2 where t1.c=t2.c and t2.a<30;
+set @json= (select trace from information_schema.optimizer_trace);
+--echo # Note that rows is the same:
+select json_detailed(json_extract(@json, '$**.hash_join_cardinality')) as JS;
+
+--echo # Despite available selectivity:
+select json_detailed(json_extract(@json, '$**.selectivity_for_columns')) as JS;
+drop table t1,t2;
diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result
index d5c46d48e68..3c292484c9e 100644
--- a/mysql-test/main/join_nested_jcl6.result
+++ b/mysql-test/main/join_nested_jcl6.result
@@ -61,8 +61,8 @@ LEFT JOIN
 ON t2.b=t4.b;
 a	b	a	b	a	b
 4	2	1	2	3	2
-4	2	2	2	3	2
 4	2	1	2	4	2
+4	2	2	2	3	2
 4	2	2	2	4	2
 3	3	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL
@@ -85,8 +85,8 @@ ON t2.b=t4.b
 WHERE t3.a=1 OR t3.c IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	10.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where `test`.`t3`.`a` = 1 or `test`.`t3`.`c` is null
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
@@ -139,16 +139,16 @@ LEFT JOIN
 ON t2.b=t4.b;
 a	b	a	b	a	b	a	b
 4	2	1	2	3	2	3	1
-4	2	2	2	3	2	3	1
 4	2	1	2	4	2	3	1
+4	2	2	2	3	2	3	1
 4	2	2	2	4	2	3	1
 4	2	1	2	3	2	2	2
-4	2	2	2	3	2	2	2
 4	2	1	2	4	2	2	2
+4	2	2	2	3	2	2	2
 4	2	2	2	4	2	2	2
 4	2	1	2	3	2	3	3
-4	2	2	2	3	2	3	3
 4	2	1	2	4	2	3	3
+4	2	2	2	3	2	3	3
 4	2	2	2	4	2	3	3
 3	3	NULL	NULL	NULL	NULL	NULL	NULL
 5	3	NULL	NULL	NULL	NULL	NULL	NULL
@@ -161,8 +161,8 @@ ON t2.b=t4.b
 WHERE t3.a>1 OR t3.c IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	10.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(`test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where `test`.`t3`.`a` > 1 or `test`.`t3`.`c` is null
@@ -191,8 +191,8 @@ WHERE (t3.a>1 OR t3.c IS NULL) AND
 (t5.a<3 OR t5.c IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	10.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(`test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where (`test`.`t3`.`a` > 1 or `test`.`t3`.`c` is null) and (`test`.`t5`.`a` < 3 or `test`.`t5`.`c` is null)
@@ -242,7 +242,7 @@ ON t7.b=t8.b AND t6.b < 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
-1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t7.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t7.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
 Note	1003	select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t7`.`b` and `test`.`t6`.`b` < 10 and `test`.`t7`.`b` is not null) where 1
 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
@@ -556,14 +556,14 @@ t0.b=t1.b AND
 (t2.a >= 4 OR t2.c IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	10.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
 Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null)
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
@@ -651,17 +651,17 @@ t0.b=t1.b AND
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	10.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t9`.`b` = `test`.`t8`.`b` or `test`.`t8`.`c` is null)
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t3`.`b` = `test`.`t4`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t9`.`b` = `test`.`t8`.`b` or `test`.`t8`.`c` is null)
 SELECT t9.a,t9.b
 FROM t9;
 a	b
@@ -849,8 +849,8 @@ WHERE t1.a <= 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
+1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`b` is not null) where `test`.`t1`.`a` <= 2
 INSERT INTO t2 VALUES  (-1,9,0), (-3,10,0), (-2,8,0), (-4,11,0), (-5,15,0);
@@ -919,17 +919,17 @@ t0.b=t1.b AND
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	10.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t4`.`b` = `test`.`t3`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t9`.`b` = `test`.`t8`.`b` or `test`.`t8`.`c` is null)
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(`test`.`t3`.`a` = 1 and `test`.`t4`.`b` = `test`.`t2`.`b` and `test`.`t2`.`a` > 0 and `test`.`t2`.`b` is not null) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(`test`.`t8`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` < 10 and `test`.`t5`.`b` is not null)) on(`test`.`t7`.`b` = `test`.`t5`.`b` and `test`.`t6`.`b` >= 2 and `test`.`t5`.`b` is not null)) on((`test`.`t3`.`b` = 2 or `test`.`t3`.`c` is null) and (`test`.`t6`.`b` = 2 or `test`.`t6`.`c` is null) and (`test`.`t5`.`b` = `test`.`t0`.`b` or `test`.`t3`.`c` is null or `test`.`t6`.`c` is null or `test`.`t8`.`c` is null) and `test`.`t1`.`a` <> 2) join `test`.`t9` where `test`.`t0`.`a` = 1 and `test`.`t1`.`b` = `test`.`t0`.`b` and `test`.`t9`.`a` = 1 and (`test`.`t2`.`a` >= 4 or `test`.`t2`.`c` is null) and (`test`.`t3`.`a` < 5 or `test`.`t3`.`c` is null) and (`test`.`t3`.`b` = `test`.`t4`.`b` or `test`.`t3`.`c` is null or `test`.`t4`.`c` is null) and (`test`.`t5`.`a` >= 2 or `test`.`t5`.`c` is null) and (`test`.`t6`.`a` >= 4 or `test`.`t6`.`c` is null) and (`test`.`t7`.`a` <= 2 or `test`.`t7`.`c` is null) and (`test`.`t8`.`a` < 1 or `test`.`t8`.`c` is null) and (`test`.`t9`.`b` = `test`.`t8`.`b` or `test`.`t8`.`c` is null)
 INSERT INTO t4 VALUES (-3,12,0), (-4,13,0), (-1,11,0), (-3,11,0), (-5,15,0);
 INSERT INTO t5 VALUES (-3,11,0), (-2,12,0), (-3,13,0), (-4,12,0);
 CREATE INDEX idx_b ON t4(b);
@@ -971,12 +971,12 @@ t0.b=t1.b AND
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	10.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	7	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	1	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
@@ -1021,10 +1021,10 @@ t0.b=t1.b AND
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	10.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	7	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	1	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (incremental, BNL join)
@@ -1075,7 +1075,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ref	idx_b	idx_b	5	test.t0.b	1	100.00	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	7	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	1	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (incremental, BNL join)
@@ -1848,8 +1848,8 @@ ON t1.a=t2.a
 WHERE t3.a IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	4	test.t1.a	1	100.00	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	t3	hash_ALL	NULL	#hash#$hj	5	test.t1.a	1	100.00	Using where; Not exists; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	4	test.t1.a	1	10.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	hash_ALL	NULL	#hash#$hj	5	test.t1.a	1	10.00	Using where; Not exists; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t3.a	0	0.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`b` AS `b` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on(`test`.`t3`.`b` = `test`.`t1`.`a`) left join `test`.`t4` on(`test`.`t4`.`b` = `test`.`t3`.`a` and `test`.`t3`.`a` is not null)) on(`test`.`t2`.`a` = `test`.`t1`.`a`) where `test`.`t3`.`a` is null
diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result
index ff5e76b78ad..a34bf96a0ef 100644
--- a/mysql-test/main/join_outer_jcl6.result
+++ b/mysql-test/main/join_outer_jcl6.result
@@ -2230,8 +2230,8 @@ SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
 WHERE d3 IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.i1	2	100.00	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	t3	hash_ALL	NULL	#hash#$hj	5	test.t1.i1	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.i1	2	10.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	hash_ALL	NULL	#hash#$hj	5	test.t1.i1	2	10.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`i2` = `test`.`t1`.`i1` and `test`.`t3`.`i3` = `test`.`t1`.`i1` and `test`.`t1`.`i1` is not null and `test`.`t1`.`i1` is not null) where `test`.`t3`.`d3` = 0 or `test`.`t3`.`d3` is null
 DROP TABLE t1,t2,t3;
@@ -2251,7 +2251,7 @@ WHERE b IN (1,2,3) OR b = d;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-1	SIMPLE	t3	hash_ALL	NULL	#hash#$hj	5	const	2	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	hash_ALL	NULL	#hash#$hj	5	const	2	10.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
 Note	1003	select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`d` = 10 and 10 is not null) where `test`.`t2`.`c` = 8 and `test`.`t3`.`d` = 8
 SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a
@@ -2368,7 +2368,7 @@ ON t1.x = t2.x
 WHERE IFNULL(t2.x,0)=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.x	2	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.x	2	10.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t1`.`x` is not null) where ifnull(`test`.`t2`.`x`,0) = 0
 SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
@@ -2384,7 +2384,7 @@ ON t1.x = t2.x
 WHERE f(t2.x,0)=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.x	2	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.x	2	10.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t1`.`x` is not null) where `f`(`test`.`t2`.`x`,0) = 0
 drop function f;
@@ -2424,7 +2424,7 @@ FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
 WHERE IFNULL(t2.col3,0) = 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	17	test.t1.col1	2	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	17	test.t1.col1	2	10.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1` and `test`.`t1`.`col1` is not null) where ifnull(`test`.`t2`.`col3`,0) = 0
 SELECT t1.col1, t2.col1, t2.col3
@@ -2438,7 +2438,7 @@ FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
 WHERE f1(t2.col3,0) = 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	17	test.t1.col1	2	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	17	test.t1.col1	2	10.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1` and `test`.`t1`.`col1` is not null) where `f1`(`test`.`t2`.`col3`,0) = 0
 DROP FUNCTION f1;
@@ -2542,7 +2542,7 @@ a	b
 explain extended select * from t1 left join t2 on a=b where (b > 3) is not true;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.a	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.a	3	10.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where `test`.`t2`.`b` > 3 is not true
 select * from t1 left join t2 on a=b where (b > 3) is not false;
@@ -2552,7 +2552,7 @@ a	b
 explain extended select * from t1 left join t2 on a=b where (b > 3) is not false;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.a	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.a	3	10.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where `test`.`t2`.`b` > 3 is not false
 drop table t1,t2;
@@ -2783,8 +2783,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t3	hash_range	a	#hash#$hj:a	5:5	test.t1.a	5	Using where; Rowid-ordered scan; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t3	hash_range	a	#hash#$hj:a	5:5	test.t1.a	5	Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
 #
 # .. part 2: make sure condition selectivity can use the condition too.
 #
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index 69cd7b5cb35..dbb4fbc262d 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -799,7 +799,8 @@ The following specify which files/extra groups are read (specified before remain
  extended_keys, exists_to_in, orderby_uses_equalities, 
  condition_pushdown_for_derived, split_materialized, 
  condition_pushdown_for_subquery, rowid_filter, 
- condition_pushdown_from_having, not_null_range_scan
+ condition_pushdown_from_having, not_null_range_scan, 
+ hash_join_cardinality
  --optimizer-trace=name 
  Controls tracing of the Optimizer:
  optimizer_trace=option=val[,option=val...], where option
@@ -1758,7 +1759,7 @@ optimizer-rowid-copy-cost 0.002653
 optimizer-scan-setup-cost 10
 optimizer-search-depth 62
 optimizer-selectivity-sampling-limit 100
-optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on
+optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
 optimizer-trace 
 optimizer-trace-max-mem-size 1048576
 optimizer-use-condition-selectivity 4
diff --git a/mysql-test/main/mysqltest_tracking_info.result b/mysql-test/main/mysqltest_tracking_info.result
index 61bb3f2d1e2..c4fedd6b59c 100644
--- a/mysql-test/main/mysqltest_tracking_info.result
+++ b/mysql-test/main/mysqltest_tracking_info.result
@@ -38,7 +38,7 @@ SET @@session.session_track_system_variables='optimizer_switch';
 set optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off';
 -- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
 -- optimizer_switch
--- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+-- index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=on,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
 
 Warnings:
 Warning	1681	'engine_condition_pushdown=on' is deprecated and will be removed in a future release
diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result
index acd1269d875..ca06d25db49 100644
--- a/mysql-test/main/subselect3_jcl6.result
+++ b/mysql-test/main/subselect3_jcl6.result
@@ -1170,11 +1170,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off';
 set @@max_heap_table_size= 16384;
 explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	D	hash_ALL	NULL	#hash#$hj	5	test.E.a	10	Using where; Using join buffer (incremental, BNLH join)
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (incremental, BNL join)
-1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (incremental, BNL join)
-1	PRIMARY	D	hash_ALL	NULL	#hash#$hj	5	test.E.a	10	Using where; End temporary; Using join buffer (incremental, BNLH join)
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (incremental, BNL join)
 flush status;
 select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 count(*)
diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result
index fa10a4aa066..e2e44fd8e80 100644
--- a/mysql-test/main/subselect_sj2.result
+++ b/mysql-test/main/subselect_sj2.result
@@ -152,7 +152,7 @@ insert into t1 values (2, 'duplicate ok', 'duplicate ok');
 insert into t1 values (18, 'duplicate ok', 'duplicate ok');
 insert into t2 values (3, 'duplicate ok', 'duplicate ok');
 insert into t2 values (19, 'duplicate ok', 'duplicate ok');
-explain select 
+explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/mysql-test/main/subselect_sj2.test b/mysql-test/main/subselect_sj2.test
index 4ccdcc50a38..67c70d449d7 100644
--- a/mysql-test/main/subselect_sj2.test
+++ b/mysql-test/main/subselect_sj2.test
@@ -124,7 +124,7 @@ insert into t1 values (18, 'duplicate ok', 'duplicate ok');
 insert into t2 values (3, 'duplicate ok', 'duplicate ok');
 insert into t2 values (19, 'duplicate ok', 'duplicate ok');
 
-explain select 
+explain select
  a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 select 
@@ -1248,6 +1248,7 @@ INSERT INTO t2 VALUES
 analyze table t1 persistent for all;
 analyze table t2 persistent for all;
 --replace_column 9 #
+
 EXPLAIN
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result
index 6ccec99ba5e..a5bcfbfa147 100644
--- a/mysql-test/main/subselect_sj2_jcl6.result
+++ b/mysql-test/main/subselect_sj2_jcl6.result
@@ -161,7 +161,7 @@ insert into t1 values (2, 'duplicate ok', 'duplicate ok');
 insert into t1 values (18, 'duplicate ok', 'duplicate ok');
 insert into t2 values (3, 'duplicate ok', 'duplicate ok');
 insert into t2 values (19, 'duplicate ok', 'duplicate ok');
-explain select 
+explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -912,11 +912,10 @@ EXPLAIN
 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	4	test.t3.a	1	Using where; Using join buffer (flat, BNLH join)
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t4	hash_ALL	NULL	#hash#$hj	4	test.t3.b	2	Using where; Start temporary; End temporary; Using join buffer (incremental, BNLH join)
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);
@@ -1151,10 +1150,10 @@ WHERE alias5.b = alias4.b
 AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
-1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	#	Using index
-1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using where; FirstMatch(alias3)
-1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias4	index	PRIMARY,c	c	4	NULL	#	Using where; Using index; Start temporary
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using join buffer (flat, BKA join); Key-ordered scan
+1	PRIMARY	alias3	hash_ALL	PRIMARY	#hash#$hj	4	test.alias4.c	#	Using where; End temporary; Using join buffer (incremental, BNLH join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (incremental, BNL join)
 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (incremental, BNL join)
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
@@ -1172,10 +1171,10 @@ WHERE alias5.b = alias4.b
 AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
-1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	#	Using index
-1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using where; FirstMatch(alias3)
-1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias4	index	PRIMARY,c	c	4	NULL	#	Using where; Using index; Start temporary
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using join buffer (flat, BKA join); Key-ordered scan
+1	PRIMARY	alias3	hash_ALL	PRIMARY	#hash#$hj	4	test.alias4.c	#	Using where; End temporary; Using join buffer (incremental, BNLH join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (incremental, BNL join)
 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (incremental, BNL join)
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result
index 5bc7751eeaa..acd600a734f 100644
--- a/mysql-test/main/subselect_sj2_mat.result
+++ b/mysql-test/main/subselect_sj2_mat.result
@@ -154,7 +154,7 @@ insert into t1 values (2, 'duplicate ok', 'duplicate ok');
 insert into t1 values (18, 'duplicate ok', 'duplicate ok');
 insert into t2 values (3, 'duplicate ok', 'duplicate ok');
 insert into t2 values (19, 'duplicate ok', 'duplicate ok');
-explain select 
+explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1661,7 +1661,7 @@ SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-2	DEPENDENT SUBQUERY	t3	hash_ALL	NULL	#hash#$hj	5	func	3	100.00	Using where; Using join buffer (flat, BNLH join)
+2	DEPENDENT SUBQUERY	t3	hash_ALL	NULL	#hash#$hj	5	func	3	10.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where <expr_cache><`test`.`t1`.`i1`>(<in_optimizer>(`test`.`t1`.`i1`,<exists>(/* select#2 */ select `test`.`t3`.`i3` from `test`.`t2` join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and <cache>(`test`.`t1`.`i1`) = `test`.`t3`.`i3`)))
 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
@@ -1671,12 +1671,11 @@ set optimizer_switch='materialization=on,semijoin=on';
 EXPLAIN EXTENDED 
 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-2	MATERIALIZED	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start temporary
+1	PRIMARY	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	3	10.00	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	5	10.00	Using where; End temporary; Using join buffer (flat, BNLH join)
 Warnings:
-Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2`
+Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2`
 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
 i1
 7
@@ -1684,12 +1683,11 @@ EXPLAIN EXTENDED
 SELECT * FROM t1 
 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-2	MATERIALIZED	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	3	100.00	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start temporary
+1	PRIMARY	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	3	10.00	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	5	10.00	Using where; End temporary; Using join buffer (flat, BNLH join)
 Warnings:
-Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0
+Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0
 SELECT * FROM t1 
 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2);
 i1
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index 5971fa30e89..6e397ddd754 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -3575,8 +3575,8 @@ SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
 GROUP BY a HAVING a != 'z';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	range	idx_a	idx_a	4	NULL	3	Using where; Using index
-1	PRIMARY	t1	ref	idx_a	idx_a	4	test.t.a	1	Using index
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	idx_a	idx_a	4	test.t.a	1	Using index; FirstMatch(t)
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
 GROUP BY a HAVING a != 'z';
 a
diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
index 80bd2d7af5f..66399f61535 100644
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
@@ -1,60 +1,60 @@
 set @@global.optimizer_switch=@@optimizer_switch;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on
 set global optimizer_switch=4101;
 set session optimizer_switch=2058;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set global optimizer_switch="index_merge_sort_union=on";
 set session optimizer_switch="index_merge=off";
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set session optimizer_switch="default";
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set optimizer_switch = replace(@@optimizer_switch, '=off', '=on');
 Warnings:
 Warning	1681	'engine_condition_pushdown=on' is deprecated and will be removed in a future release
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,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=on,mrr_cost_based=on,mrr_sort_keys=on,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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,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=on,mrr_cost_based=on,mrr_sort_keys=on,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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=on,hash_join_cardinality=on
 set global optimizer_switch=1.1;
 ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
 set global optimizer_switch=1e1;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index cc63673635f..bbf6c22b70f 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -2489,7 +2489,7 @@ VARIABLE_COMMENT	Fine-tune the optimizer behavior
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,default
+ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,hash_join_cardinality,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_TRACE
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 9ade9511434..545a1ad7a6b 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2659,7 +2659,7 @@ VARIABLE_COMMENT	Fine-tune the optimizer behavior
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,default
+ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,hash_join_cardinality,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_TRACE
diff --git a/mysql-test/suite/sysschema/r/optimizer_switch.result b/mysql-test/suite/sysschema/r/optimizer_switch.result
index 017276fc4b8..4193bf0739e 100644
--- a/mysql-test/suite/sysschema/r/optimizer_switch.result
+++ b/mysql-test/suite/sysschema/r/optimizer_switch.result
@@ -8,6 +8,7 @@ derived_with_keys	on
 exists_to_in	on
 extended_keys	on
 firstmatch	on
+hash_join_cardinality	on
 index_condition_pushdown	on
 index_merge	on
 index_merge_intersection	on
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index 6f4eff4880c..5f2074851e1 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -238,38 +238,39 @@
 #define OPTIMIZER_SWITCH_USE_ROWID_FILTER          (1ULL << 33)
 #define OPTIMIZER_SWITCH_COND_PUSHDOWN_FROM_HAVING (1ULL << 34)
 #define OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN       (1ULL << 35)
-
-#define OPTIMIZER_SWITCH_DEFAULT   (OPTIMIZER_SWITCH_INDEX_MERGE | \
-                                    OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
-                                    OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION | \
-                                    OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT | \
-                                    OPTIMIZER_SWITCH_INDEX_COND_PUSHDOWN | \
-                                    OPTIMIZER_SWITCH_DERIVED_MERGE | \
-                                    OPTIMIZER_SWITCH_DERIVED_WITH_KEYS | \
-                                    OPTIMIZER_SWITCH_TABLE_ELIMINATION | \
-                                    OPTIMIZER_SWITCH_EXTENDED_KEYS | \
-                                    OPTIMIZER_SWITCH_IN_TO_EXISTS | \
-                                    OPTIMIZER_SWITCH_MATERIALIZATION | \
-                                    OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\
-                                    OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\
-                                    OPTIMIZER_SWITCH_OUTER_JOIN_WITH_CACHE | \
-                                    OPTIMIZER_SWITCH_SEMIJOIN_WITH_CACHE | \
-                                    OPTIMIZER_SWITCH_JOIN_CACHE_INCREMENTAL | \
-                                    OPTIMIZER_SWITCH_JOIN_CACHE_HASHED | \
-                                    OPTIMIZER_SWITCH_JOIN_CACHE_BKA | \
-                                    OPTIMIZER_SWITCH_SUBQUERY_CACHE | \
-                                    OPTIMIZER_SWITCH_SEMIJOIN | \
-                                    OPTIMIZER_SWITCH_FIRSTMATCH | \
-                                    OPTIMIZER_SWITCH_LOOSE_SCAN | \
-                                    OPTIMIZER_SWITCH_EXISTS_TO_IN | \
-                                    OPTIMIZER_SWITCH_ORDERBY_EQ_PROP | \
-                                    OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED | \
-                                    OPTIMIZER_SWITCH_SPLIT_MATERIALIZED | \
-                                    OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_SUBQUERY | \
-                                    OPTIMIZER_SWITCH_USE_ROWID_FILTER | \
-                                    OPTIMIZER_SWITCH_COND_PUSHDOWN_FROM_HAVING | \
-                                    OPTIMIZER_SWITCH_OPTIMIZE_JOIN_BUFFER_SIZE)
-
+#define OPTIMIZER_SWITCH_HASH_JOIN_CARDINALITY     (1ULL << 36)
+
+#define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \
+                                  OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
+                                  OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION | \
+                                  OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT | \
+                                  OPTIMIZER_SWITCH_INDEX_COND_PUSHDOWN | \
+                                  OPTIMIZER_SWITCH_DERIVED_MERGE | \
+                                  OPTIMIZER_SWITCH_DERIVED_WITH_KEYS | \
+                                  OPTIMIZER_SWITCH_TABLE_ELIMINATION | \
+                                  OPTIMIZER_SWITCH_EXTENDED_KEYS | \
+                                  OPTIMIZER_SWITCH_IN_TO_EXISTS | \
+                                  OPTIMIZER_SWITCH_MATERIALIZATION | \
+                                  OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\
+                                  OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\
+                                  OPTIMIZER_SWITCH_OUTER_JOIN_WITH_CACHE | \
+                                  OPTIMIZER_SWITCH_SEMIJOIN_WITH_CACHE | \
+                                  OPTIMIZER_SWITCH_JOIN_CACHE_INCREMENTAL | \
+                                  OPTIMIZER_SWITCH_JOIN_CACHE_HASHED | \
+                                  OPTIMIZER_SWITCH_JOIN_CACHE_BKA | \
+                                  OPTIMIZER_SWITCH_SUBQUERY_CACHE | \
+                                  OPTIMIZER_SWITCH_SEMIJOIN | \
+                                  OPTIMIZER_SWITCH_FIRSTMATCH | \
+                                  OPTIMIZER_SWITCH_LOOSE_SCAN | \
+                                  OPTIMIZER_SWITCH_EXISTS_TO_IN | \
+                                  OPTIMIZER_SWITCH_ORDERBY_EQ_PROP | \
+                                  OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED | \
+                                  OPTIMIZER_SWITCH_SPLIT_MATERIALIZED | \
+                                  OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_SUBQUERY |\
+                                  OPTIMIZER_SWITCH_USE_ROWID_FILTER | \
+                                  OPTIMIZER_SWITCH_COND_PUSHDOWN_FROM_HAVING | \
+                                  OPTIMIZER_SWITCH_OPTIMIZE_JOIN_BUFFER_SIZE |\
+                                  OPTIMIZER_SWITCH_HASH_JOIN_CARDINALITY)
 /*
   Replication uses 8 bytes to store SQL_MODE in the binary log. The day you
   use strictly more than 64 bits by adding one more define above, you should
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index cdf9af40f7a..9c665bf5df0 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7872,7 +7872,7 @@ static double apply_selectivity_for_table(JOIN_TAB *s,
   this table in join order.
 */
 
-static double use_found_constraint(double records)
+inline double use_found_constraint(double records)
 {
   records-= records/4;
   return records ? MY_MAX(records, MIN_ROWS_AFTER_FILTERING) : 0.0;
@@ -8062,6 +8062,155 @@ apply_filter(THD *thd, TABLE *table, ALL_READ_COST *cost,
 }
 
 
+/*
+  @brief
+    Compute the fanout of hash join operation using EITS data
+
+  @param join             JOIN structure
+  @param tab              JOIN_TAB for the current table
+  @param remaining_tables Map of tables not yet accessable
+  @param rnd_records      Number of accepted rows in the table, after taking
+                          selectivity into account.
+  @param hj_start_key     Pointer to hash key
+  @param stats_found      Is set to 1 if we found any usable hash key part
+                          with statistics from analyze.
+*/
+
+double hash_join_fanout(JOIN *join, JOIN_TAB *tab, table_map remaining_tables,
+                        double rnd_records, KEYUSE *hj_start_key,
+                        bool *stats_found)
+{
+  THD *thd= join->thd;
+  /*
+    Before doing the hash join, we will scan the table and apply the local part
+    of the WHERE condition. This will produce rnd_records.
+
+    The EITS statistics describes the entire table. Calling
+
+      table->field[N]->get_avg_frequency()
+
+    produces average #rows in the table with some value.
+
+    What happens if we filter out rows so that rnd_records rows are left?
+    Something between the two outcomes:
+    A. filtering removes a fraction of rows for each value:
+      avg_frequency=avg_frequency * condition_selectivity
+
+    B. filtering removes entire groups of rows with the same value, but
+       the remaining groups remain of the same size.
+
+    We make pessimistic assumption and assume B.
+    We also handle an edge case: if rnd_records is less than avg_frequency,
+    assume we'll get rnd_records rows with the same value, and return
+    rnd_records as the fanout estimate.
+  */
+  double min_freq= (double) tab->table->stat_records();
+  bool found_not_usable_field= 0;
+  bool found_usable_field __attribute__((unused))= 0;
+  DBUG_ENTER("hash_join_cardinality");
+
+  Json_writer_object trace_obj(thd, "hash_join_cardinality");
+
+  /*
+    There can be multiple KEYUSE referring to same or different columns
+
+       KEYUSE(tbl.col1 = ...)
+       KEYUSE(tbl.col1 = ...)
+       KEYUSE(tbl.col2 = ...)
+
+    Hash join code can use multiple columns: (col1, col2) for joining.
+    We need n_distinct({col1, col2}).
+
+    EITS only has statistics on individual columns: n_distinct(col1),
+    n_distinct(col2).
+
+    Our current solution is to be very conservative and use selectivity
+    of one column with the lowest avg_frequency.
+
+    In the future, we should an approach that cautiosly takes into account
+    multiple KEYUSEs either multiply by number of equalities or by sqrt
+    of the second most selective equality.
+  */
+  Json_writer_array trace_arr(thd, "hash_join_columns");
+  for (KEYUSE *keyuse= hj_start_key;
+       keyuse->table == tab->table && is_hash_join_key_no(keyuse->key);
+       keyuse++)
+  {
+    if (!(remaining_tables & keyuse->used_tables) &&
+        (!keyuse->validity_ref || *keyuse->validity_ref) &&
+        tab->access_from_tables_is_allowed(keyuse->used_tables,
+                                         join->sjm_lookup_tables))
+    {
+      Field *field= tab->table->field[keyuse->keypart];
+      found_usable_field= 1;
+      if (is_eits_usable(field))
+      {
+        double freq= field->read_stats->get_avg_frequency();
+
+        Json_writer_object trace_field(thd);
+        trace_field.add("field",field->field_name.str).
+          add("avg_frequency", freq);
+        if (freq < min_freq)
+          min_freq= freq;
+        *stats_found= 1;
+        continue;
+      }
+    }
+    if (!keyuse->validity_ref || *keyuse->validity_ref)
+      found_not_usable_field= 1;
+  }
+  /* Ensure that some part of hash_key is usable */
+  DBUG_ASSERT(found_usable_field);
+
+  trace_arr.end();
+  if (found_not_usable_field)
+  {
+    /*
+      We did not't have data for all key fields. Assume that the hash
+      will at least limit the number of matched rows to HASH_FANOUT.
+      This makes the cost same as when 'hash_join_cardinality=off'
+      in the case when no analyze of the tables have been made.
+
+      However, it may cause problems when min_freq is higher than
+      HASH_FANOUT as the optimizer will then assume it is better to
+      put the table earlier in the plan when all key parts are not
+      usable.
+      Note that min_freq can become less than 1.0. This is intentional
+      as it matches what happens if OPTIMIZER_SWITCH_HASH_JOIN_CARDINALITY
+      is not used.
+    */
+    double max_expected_records= rnd_records * HASH_FANOUT;
+    set_if_smaller(min_freq, max_expected_records);
+    trace_obj.add("using_default_hash_fanout", HASH_FANOUT);
+  }
+  else
+  {
+    /*
+      Before joining the table with the contents of join buffer, we will
+      use the quick select and/or apply the table condition.
+
+      This will reduce the number of rows joined to rnd_records.
+      How will this affect n_distinct?
+      Depending on which rows are removed, this can either leave n_distinct as
+      is (for some value X, some rows are removed but some are left, leaving the
+      number of distinct values the same), or reduce n_distinct in proportion
+      with the fraction of rows removed (for some values of X, either all or
+      none of the rows with that value are removed).
+
+      We assume the latter: n_distinct is reduced in proportion the condition
+      and quick select's selectivity.
+      This is in effect same as applying apply_selectivity_for_table() on
+      min_freq as we have already done on rnd_records
+    */
+    min_freq*= rnd_records / tab->table->stat_records();
+    set_if_bigger(min_freq, HASH_FANOUT);
+  }
+
+  trace_obj.add("rows", min_freq);
+  DBUG_RETURN(min_freq);
+}
+
+
 /**
   Find the best access path for an extension of a partial execution
   plan and add this path to the plan.
@@ -8923,15 +9072,47 @@ best_access_path(JOIN      *join,
       (!(table->map & join->outer_join) ||
        join->allowed_outer_join_with_cache))    // (2)
   {
-    double refills, row_copy_cost, cmp_time, cur_cost, records_table_filter;
+    Json_writer_object trace_access_hash(thd);
+    double refills, row_copy_cost, copy_cost, cur_cost, where_cost;
+    double matching_combinations, fanout, join_sel;
     /* Estimate the cost of the hash join access to the table */
-    double rnd_records= apply_selectivity_for_table(s, use_cond_selectivity);
-    records_table_filter= ((found_constraint) ?
-                           use_found_constraint(rnd_records) :
-                           rnd_records);
+    double rnd_records;
+    bool stats_found= 0;
 
+    rnd_records= apply_selectivity_for_table(s, use_cond_selectivity);
     DBUG_ASSERT(rnd_records <= rows2double(s->found_records) + 0.5);
-    set_if_smaller(best.records_out, records_table_filter);
+    DBUG_ASSERT(hj_start_key);
+
+    fanout= rnd_records;
+    if (optimizer_flag(thd, OPTIMIZER_SWITCH_HASH_JOIN_CARDINALITY))
+    {
+      /*
+        Starting from this point, rnd_records should not be used anymore.
+        Use "fanout" for an estimate of # matching records.
+      */
+      fanout= hash_join_fanout(join, s, remaining_tables, rnd_records,
+                               hj_start_key, &stats_found);
+      set_if_smaller(best.records_out, fanout);
+      join_sel= 1.0;
+    }
+    if (!stats_found)
+    {
+      /*
+        No OPTIMIZER_SWITCH_HASH_JOIN_CARDINALITY or no field statistics
+        found.
+
+        Take into account if there is non constant constraints used with
+        earlier tables in the where expression.
+        If yes, this will set fanout to rnd_records/4.
+        We estimate that there will be HASH_FANOUT (10%)
+        hash matches / row.
+      */
+      fanout= ((found_constraint) ?
+               use_found_constraint(rnd_records) :
+               rnd_records);
+      set_if_smaller(best.records_out, fanout * HASH_FANOUT);
+      join_sel= HASH_FANOUT;
+    }
 
     /*
       The following cost calculation is identical to the cost calculation for
@@ -8958,36 +9139,36 @@ best_access_path(JOIN      *join,
       Cost of doing the hash lookup and check all matching rows with the
       WHERE clause.
       We assume here that, thanks to the hash, we don't have to compare all
-      row combinations, only a HASH_FANOUT (10%) rows in the cache.
+      row combinations, only a fanout or HASH_FANOUT (10%) rows in the cache.
     */
     row_copy_cost= (ROW_COPY_COST_THD(thd) *
                     JOIN_CACHE_ROW_COPY_COST_FACTOR(thd));
-    cmp_time= (record_count * row_copy_cost +
-               rnd_records * record_count * HASH_FANOUT *
-               ((idx - join->const_tables) * row_copy_cost +
-                WHERE_COST_THD(thd)));
-    cur_cost= COST_ADD(cur_cost, cmp_time);
+    matching_combinations= fanout * join_sel * record_count;
+    copy_cost= (record_count * row_copy_cost +
+                matching_combinations *
+                ((idx - join->const_tables) * row_copy_cost));
+    where_cost= matching_combinations * WHERE_COST_THD(thd);
+    cur_cost= COST_ADD(cur_cost, copy_cost + where_cost);
 
     best.cost= cur_cost;
     best.records_read= best.records_after_filter= rows2double(s->records);
-    best.records= rnd_records;
-#ifdef NOT_YET
-    set_if_smaller(best.records_out, rnd_records * HASH_FANOUT);
-#endif
+    best.records= rnd_records;        // Records after where (Legacy value)
     best.key= hj_start_key;
     best.ref_depends_map= 0;
     best.use_join_buffer= TRUE;
     best.filter= 0;
     best.type= JT_HASH;
     best.refills= (ulonglong) ceil(refills);
-    Json_writer_object trace_access_hash(thd);
     if (unlikely(trace_access_hash.trace_started()))
       trace_access_hash.
         add("type", "hash").
         add("index", "hj-key").
         add("rows", rnd_records).
+        add("rows_after_hash", fanout * join_sel).
         add("refills", refills).
-        add("cost", best.cost).
+        add("jbuf_use_cost", copy_cost).
+        add("extra_cond_check_cost", where_cost).
+        add("total_cost", best.cost).
         add("chosen", true);
   }
 
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index ecde847c8d4..b6f180f609f 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -4147,7 +4147,7 @@ bool is_eits_usable(Field *field)
   if (!col_stats)
     return false;
 
-  DBUG_ASSERT(field->table->stats_is_read);
+  DBUG_ASSERT(field->orig_table->stats_is_read);
 
   /*
     (1): checks if we have EITS statistics for a particular column
@@ -4160,8 +4160,8 @@ bool is_eits_usable(Field *field)
   return !col_stats->no_stat_values_provided() &&        //(1)
     field->type() != MYSQL_TYPE_GEOMETRY &&              //(2)
 #ifdef WITH_PARTITION_STORAGE_ENGINE
-    (!field->table->part_info ||
-     !field->table->part_info->field_in_partition_expr(field)) &&     //(3)
+    (!field->orig_table->part_info ||
+     !field->orig_table->part_info->field_in_partition_expr(field)) &&     //(3)
 #endif
     true;
 }
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 7c940c36723..a77841e4dda 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2891,6 +2891,7 @@ export const char *optimizer_switch_names[]=
   "rowid_filter",
   "condition_pushdown_from_having",
   "not_null_range_scan",
+  "hash_join_cardinality",
   "default", 
   NullS
 };