Fixed cost calculations for materialized tables

One effect of this change in the test suite is that tests with very few
rows changed to use sub queries instead of materialization. This is
correct and expected as for these the materialization overhead is too high.

A lot of tests where fixed to still use materialization by adding a
few rows to the tables (most tests has only 2-3 rows and are thus easily
affected when cost computations are changed).

Other things:
- Added more variables to TMPTABLE_COSTS for better cost calculation
- Added cost of copying rows to TMPTABLE_COSTS lookup and write
- Added THD::optimizer_cache_hit_ratio for easier cost calculations
- Added DISK_FAST_READ_SIZE to be used when calculating costs when
  reading big blocks from a disk

48 files changed, 463 insertions, 328 deletions

diff --git a/mysql-test/include/explain_non_select.inc b/mysql-test/include/explain_non_select.inc
index d22310c9813..9ce620a72de 100644
--- a/mysql-test/include/explain_non_select.inc
+++ b/mysql-test/include/explain_non_select.inc
@@ -73,11 +73,11 @@ INSERT INTO t2 VALUES (1), (2), (3);
 --source include/explain_utils.inc
 DROP TABLE t1, t2;
 
---echo #7
+--echo #7a
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1), (2), (3);
 CREATE TABLE t2 (b INT);
-INSERT INTO t2 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1), (2), (3), (1000);
 --let $query =  UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
 --let $select = SELECT * FROM t1, t2            WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
 --source include/explain_utils.inc
@@ -197,7 +197,7 @@ DROP TABLE t1, t2, t3;
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1), (2), (3);
 CREATE TABLE t2 (a INT);
-INSERT INTO t2 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1), (2), (3), (1000);
 --let $query =  UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2)
 --let $select = SELECT * FROM t1     WHERE a IN (SELECT a FROM t2)
 --source include/explain_utils.inc
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc
index 43f57375a4e..6cb029c2050 100644
--- a/mysql-test/include/index_merge1.inc
+++ b/mysql-test/include/index_merge1.inc
@@ -509,7 +509,7 @@ DROP TABLE t1;
 create table t1 (a int);
 insert into t1 values (1),(2);
 create table t2(a int, b int);
-insert into t2 values (1,1), (2, 1000);
+insert into t2 values (1,1), (2, 1000),(5000,5000);
 create table t3 (a int, b int, filler char(100), key(a), key(b));
 
 insert into t3 select 1000, 1000,'filler' from seq_1_to_1000;
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 164a1ee0e7b..f24ae2fcbac 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -17348,7 +17348,7 @@ create table t1 (id int, a int, index (a), index (id, a)) engine=myisam;
 insert into t1 values
 (17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2);
 create table t2 (a int) engine=myisam;
-insert into t2 values (1),(2),(3);
+insert into t2 values (1),(2),(3),(1000),(2000),(3000);
 create  table t3 (id int) engine=myisam;
 insert into t3 values (1),(2);
 analyze table t1,t2,t3;
@@ -17372,7 +17372,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	test.t3.id	1	
 1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	<derived2>	ref	key0	key0	5	test.t3.id	2	
-3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	
 2	DERIVED	cp2	index	NULL	a	5	NULL	7	Using index
 explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3
 where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2);
@@ -17415,7 +17415,7 @@ EXPLAIN
           "table": {
             "table_name": "t2",
             "access_type": "ALL",
-            "rows": 3,
+            "rows": 6,
             "filtered": 100
           }
         }
@@ -17462,7 +17462,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	test.t3.id	1	
 1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	<derived2>	ref	key0	key0	5	test.t3.id	2	
-3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	
 2	LATERAL DERIVED	cp2	ref	a	a	5	test.t1.a	1	Using where; Using index
 explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3
 where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2);
@@ -17505,7 +17505,7 @@ EXPLAIN
           "table": {
             "table_name": "t2",
             "access_type": "ALL",
-            "rows": 3,
+            "rows": 6,
             "filtered": 100
           }
         }
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index a09fd1b59e3..83e784c3450 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3601,7 +3601,7 @@ insert into t1 values
 (17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2);
 
 create table t2 (a int) engine=myisam;
-insert into t2 values (1),(2),(3);
+insert into t2 values (1),(2),(3),(1000),(2000),(3000);
 
 create  table t3 (id int) engine=myisam;
 insert into t3 values (1),(2);
diff --git a/mysql-test/main/explain_json.result b/mysql-test/main/explain_json.result
index bb9f0c4d3a2..f3ee13ee17f 100644
--- a/mysql-test/main/explain_json.result
+++ b/mysql-test/main/explain_json.result
@@ -902,17 +902,19 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 2,
       "filtered": 100,
-      "attached_condition": "!<in_optimizer>(t1.a,t1.a in (subquery#2))"
+      "attached_condition": "!<in_optimizer>(t1.a,<exists>(subquery#2))"
     },
     "subqueries": [
       {
         "query_block": {
           "select_id": 2,
+          "having_condition": "trigcond(t2.b is null)",
           "table": {
             "table_name": "t2",
             "access_type": "ALL",
             "rows": 2,
-            "filtered": 100
+            "filtered": 100,
+            "attached_condition": "trigcond(<cache>(t1.a) = t2.b or t2.b is null)"
           }
         }
       }
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result
index 6cd82d2a96a..2e489077a78 100644
--- a/mysql-test/main/index_merge_myisam.result
+++ b/mysql-test/main/index_merge_myisam.result
@@ -554,7 +554,7 @@ DROP TABLE t1;
 create table t1 (a int);
 insert into t1 values (1),(2);
 create table t2(a int, b int);
-insert into t2 values (1,1), (2, 1000);
+insert into t2 values (1,1), (2, 1000),(5000,5000);
 create table t3 (a int, b int, filler char(100), key(a), key(b));
 insert into t3 select 1000, 1000,'filler' from seq_1_to_1000;
 insert into t3 values (1,1,'data');
@@ -566,7 +566,7 @@ where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
 2	MATERIALIZED	t3	ALL	a,b	NULL	NULL	NULL	1002	Range checked for each record (index map: 0x3)
 select * from t1 
 where exists (select 1 from t2, t3 
diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result
index 2b1ac415265..98bb644fd5f 100644
--- a/mysql-test/main/myisam_explain_non_select_all.result
+++ b/mysql-test/main/myisam_explain_non_select_all.result
@@ -327,11 +327,11 @@ Handler_read_rnd_next	7
 Handler_update	2
 
 DROP TABLE t1, t2;
-#7
+#7a
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1), (2), (3);
 CREATE TABLE t2 (b INT);
-INSERT INTO t2 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1), (2), (3), (1000);
 #
 # query:  UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
 # select: SELECT * FROM t1, t2            WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
@@ -342,16 +342,16 @@ EXPLAIN UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -361,8 +361,8 @@ EXPLAIN EXTENDED SELECT * FROM t1, t2            WHERE a IN (SELECT b FROM t2 WH
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using join buffer (flat, BNL join)
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` where `test`.`t2`.`b` < 3
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
@@ -373,11 +373,11 @@ Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_key	7
-Handler_read_rnd_next	12
+Handler_read_rnd_next	14
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_key	7
-Handler_read_rnd_next	16
+Handler_read_rnd_next	19
 Handler_update	2
 
 DROP TABLE t1, t2;
@@ -978,7 +978,7 @@ DROP TABLE t1, t2, t3;
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1), (2), (3);
 CREATE TABLE t2 (a INT);
-INSERT INTO t2 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1), (2), (3), (1000);
 #
 # query:  UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2)
 # select: SELECT * FROM t1     WHERE a IN (SELECT a FROM t2)
@@ -988,13 +988,13 @@ Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be
 EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
 FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 # Status of EXPLAIN EXTENDED query
 Variable_name	Value
 Handler_read_key	4
@@ -1004,7 +1004,7 @@ EXPLAIN EXTENDED SELECT * FROM t1     WHERE a IN (SELECT a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where 1
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
@@ -1015,7 +1015,7 @@ Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_key	7
-Handler_read_rnd_next	8
+Handler_read_rnd_next	9
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_key	4
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 93020416551..f3bc5e40ea9 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -3248,7 +3248,7 @@ explain extended select * from t1 where a in (select pk from t10)	{
                       {
                         "strategy": "SJ-Materialization",
                         "records": 3,
-                        "cost": 6.914282227
+                        "cost": 8.664282227
                       },
                       {
                         "strategy": "DuplicateWeedout",
@@ -3314,7 +3314,7 @@ explain extended select * from t1 where a in (select pk from t10)	{
             "best_join_order": ["t1", "<subquery2>"],
             "best_access_method": {
               "rows": 3,
-              "cost": 6.914282227
+              "cost": 8.664282227
             }
           },
           {
@@ -5038,7 +5038,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
                           {
                             "strategy": "SJ-Materialization",
                             "records": 3,
-                            "cost": 9.10769043
+                            "cost": 10.75769043
                           },
                           {
                             "strategy": "DuplicateWeedout",
@@ -5171,7 +5171,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
             "best_join_order": ["t1", "<subquery2>"],
             "best_access_method": {
               "rows": 3,
-              "cost": 9.10769043
+              "cost": 10.75769043
             }
           },
           {
@@ -7565,7 +7565,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                           {
                             "strategy": "SJ-Materialization",
                             "records": 3,
-                            "cost": 16.01281738
+                            "cost": 19.46281738
                           },
                           {
                             "strategy": "DuplicateWeedout",
@@ -7607,7 +7607,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                               }
                             },
                             "rows_for_plan": 27,
-                            "cost_for_plan": 28.72050781,
+                            "cost_for_plan": 32.17050781,
                             "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
@@ -7642,7 +7642,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                   }
                                 },
                                 "rows_for_plan": 81,
-                                "cost_for_plan": 62.87307129,
+                                "cost_for_plan": 66.32307129,
                                 "semijoin_strategy_choice": [],
                                 "rest_of_plan": [
                                   {
@@ -7678,25 +7678,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                       }
                                     },
                                     "rows_for_plan": 729,
-                                    "cost_for_plan": 394.1807617,
+                                    "cost_for_plan": 397.6307617,
                                     "semijoin_strategy_choice": [
                                       {
                                         "strategy": "FirstMatch",
                                         "records": 27,
-                                        "cost": 339.9126465
+                                        "cost": 343.3626465
                                       },
                                       {
                                         "strategy": "SJ-Materialization",
                                         "records": 27,
-                                        "cost": 44.18076172
+                                        "cost": 57.08076172
                                       },
                                       {
                                         "strategy": "DuplicateWeedout",
                                         "records": 27,
-                                        "dups_cost": 394.1807617,
+                                        "dups_cost": 397.6307617,
                                         "write_cost": 5.05,
                                         "full_lookup_cost": 109.35,
-                                        "total_cost": 508.5807617
+                                        "total_cost": 512.0307617
                                       },
                                       {
                                         "chosen_strategy": "SJ-Materialization"
@@ -7737,7 +7737,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                   }
                                 },
                                 "rows_for_plan": 243,
-                                "cost_for_plan": 129.1781982,
+                                "cost_for_plan": 132.6281982,
                                 "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               }
@@ -7770,7 +7770,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                               }
                             },
                             "rows_for_plan": 9,
-                            "cost_for_plan": 20.91538086,
+                            "cost_for_plan": 24.36538086,
                             "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
@@ -7805,7 +7805,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                   }
                                 },
                                 "rows_for_plan": 81,
-                                "cost_for_plan": 56.57307129,
+                                "cost_for_plan": 60.02307129,
                                 "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               },
@@ -7841,7 +7841,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                   }
                                 },
                                 "rows_for_plan": 81,
-                                "cost_for_plan": 56.57307129,
+                                "cost_for_plan": 60.02307129,
                                 "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               }
@@ -7874,7 +7874,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                               }
                             },
                             "rows_for_plan": 27,
-                            "cost_for_plan": 28.72050781,
+                            "cost_for_plan": 32.17050781,
                             "semijoin_strategy_choice": [],
                             "pruned_by_heuristic": true
                           }
@@ -8387,7 +8387,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
             ],
             "best_access_method": {
               "rows": 27,
-              "cost": 44.18076172
+              "cost": 57.08076172
             }
           },
           {
@@ -10212,8 +10212,8 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting'))
                 "table": "t2",
                 "key": "idx_a",
                 "record_count": 4,
-                "cost": 3.651150919,
-                "unsplit_cost": 46.6794762
+                "cost": 4.157170953,
+                "unsplit_cost": 60.1794762
             }
         }
     ]
@@ -10226,8 +10226,8 @@ json_detailed(json_extract(trace, '$**.lateral_derived'))
 [
     
     {
-        "startup_cost": 14.60460368,
-        "splitting_cost": 3.651150919,
+        "startup_cost": 16.62868381,
+        "splitting_cost": 4.157170953,
         "records": 1
     }
 ]
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index aa1ede9cbd3..33eedf3f103 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -4356,7 +4356,7 @@ CREATE TABLE t1 (a INT, b int, primary key(a));
 CREATE TABLE t2 (a INT, b INT);
 INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6),
 (2354,7),(321421,3),(535,2),(4535,3);
-INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3);
+INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3),(1000,1000),(2000,2000);
 # Join order should have the SJM scan table as the first table for both
 # the queries with GROUP BY and ORDER BY clause.
 EXPLAIN SELECT t1.a
@@ -4364,9 +4364,9 @@ FROM t1
 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
 ORDER BY t1.a DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	Using filesort
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	Using filesort
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using index
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 EXPLAIN FORMAT=JSON SELECT t1.a
 FROM t1
 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
@@ -4382,7 +4382,7 @@ EXPLAIN
           "table_name": "<subquery2>",
           "access_type": "ALL",
           "possible_keys": ["distinct_key"],
-          "rows": 3,
+          "rows": 5,
           "filtered": 100,
           "materialized": {
             "unique": 1,
@@ -4391,7 +4391,7 @@ EXPLAIN
               "table": {
                 "table_name": "t2",
                 "access_type": "ALL",
-                "rows": 3,
+                "rows": 5,
                 "filtered": 100,
                 "attached_condition": "t2.b = 3 and t2.a is not null"
               }
@@ -4427,9 +4427,9 @@ FROM t1
 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
 GROUP BY t1.a DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	Using filesort
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	Using filesort
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 EXPLAIN FORMAT=JSON SELECT t1.a, group_concat(t1.b)
 FROM t1
 WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
@@ -4445,7 +4445,7 @@ EXPLAIN
           "table_name": "<subquery2>",
           "access_type": "ALL",
           "possible_keys": ["distinct_key"],
-          "rows": 3,
+          "rows": 5,
           "filtered": 100,
           "materialized": {
             "unique": 1,
@@ -4454,7 +4454,7 @@ EXPLAIN
               "table": {
                 "table_name": "t2",
                 "access_type": "ALL",
-                "rows": 3,
+                "rows": 5,
                 "filtered": 100,
                 "attached_condition": "t2.b = 3 and t2.a is not null"
               }
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index 815953f2c02..d40813cf223 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -2682,7 +2682,7 @@ CREATE TABLE t2 (a INT, b INT);
 
 INSERT INTO t1 (a,b) VALUES (58,1),(96,2),(273,3),(23,4),(231,5),(525,6),
                             (2354,7),(321421,3),(535,2),(4535,3);
-INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3);
+INSERT INTO t2 (a,b) VALUES (58,3),(96,3),(273,3),(1000,1000),(2000,2000);
 
 --echo # Join order should have the SJM scan table as the first table for both
 --echo # the queries with GROUP BY and ORDER BY clause.
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index ed0ded47c1c..6b1ba12cea1 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -895,6 +895,9 @@ select (select a+1) from t1;
 NULL
 4.5
 drop table t1;
+#
+# Null with keys
+#
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -4438,10 +4441,13 @@ out_a	MIN(b)
 1	2
 2	4
 DROP TABLE t1;
+#
+# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+#
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
 INSERT INTO t1 VALUES (1),(2),(3),(4);
-INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2),(1000);
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 2
 2
@@ -4451,7 +4457,7 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	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	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where 1
@@ -4460,8 +4466,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -5700,7 +5706,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 CREATE TABLE t1 (a int) ;
 INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
 CREATE TABLE t2 (a int, INDEX idx(a)) ;
-INSERT INTO t2 VALUES (NULL), (1), (NULL);
+INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000);
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
 a
@@ -5711,7 +5717,7 @@ WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 a
@@ -5720,9 +5726,8 @@ EXPLAIN
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	index	idx	idx	5	NULL	3	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	2	Using index; FirstMatch(t1)
 DROP TABLE t1,t2;
 #
 # BUG#752992: Wrong results for a subquery with 'semijoin=on'
diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test
index 2093b4ff2d7..e84b928f891 100644
--- a/mysql-test/main/subselect.test
+++ b/mysql-test/main/subselect.test
@@ -516,9 +516,9 @@ explain extended select (select a+1) from t1;
 select (select a+1) from t1;
 drop table t1;
 
-#
-# Null with keys
-#
+--echo #
+--echo # Null with keys
+--echo #
 
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
@@ -3321,15 +3321,15 @@ GROUP BY a;
 DROP TABLE t1;
 
 
-#
-# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
-#
+--echo #
+--echo # Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+--echo #
 
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
 
 INSERT INTO t1 VALUES (1),(2),(3),(4);
-INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2),(1000);
 
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 EXPLAIN EXTENDED
@@ -4794,7 +4794,7 @@ CREATE TABLE t1 (a int) ;
 INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
 
 CREATE TABLE t2 (a int, INDEX idx(a)) ;
-INSERT INTO t2 VALUES (NULL), (1), (NULL);
+INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000);
 
 SELECT * FROM t1
   WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
diff --git a/mysql-test/main/subselect3.inc b/mysql-test/main/subselect3.inc
index 8ed1c9ee210..c2491b0360e 100644
--- a/mysql-test/main/subselect3.inc
+++ b/mysql-test/main/subselect3.inc
@@ -1107,6 +1107,7 @@ insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 as select a as a, a as b, a as c from t0 where a < 3;
 create table t2 as select a as a, a as b from t0 where a < 3;
 insert into t2 select * from t2;
+select count(*) from t2;
 
 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
 
@@ -1152,7 +1153,7 @@ drop table t0, t1;
 create table t1 (
   idIndividual int primary key
 );
-insert into t1 values (1),(2);
+insert into t1 values (1),(2),(1000);
 
 create table t2 (
   idContact int primary key,
@@ -1167,7 +1168,7 @@ create table t3 (
   postalStripped varchar(100)
 );
 
-insert into t3 values (1,1, 'foo'), (2,2,'bar');
+insert into t3 values (1,1, 'foo'), (2,2,'T2H3B2');
 
 --echo The following must be converted to a semi-join:
 set @save_optimizer_switch=@@optimizer_switch;
@@ -1178,6 +1179,12 @@ WHERE a.idIndividual IN
 		INNER JOIN t2 c ON c.idContact=cona.idContact 
 		WHERE cona.postalStripped='T2H3B2'
 	);
+SELECT a.idIndividual FROM t1 a 
+WHERE a.idIndividual IN 
+	(	SELECT c.idObj FROM t3 cona 
+		INNER JOIN t2 c ON c.idContact=cona.idContact 
+		WHERE cona.postalStripped='T2H3B2'
+	);
 set @@optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 
diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result
index fe6ef646886..36063f3e594 100644
--- a/mysql-test/main/subselect3.result
+++ b/mysql-test/main/subselect3.result
@@ -1180,9 +1180,8 @@ create table t3 ( a int , filler char(100), key(a));
 insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	End temporary
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -1336,6 +1335,9 @@ insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 as select a as a, a as b, a as c from t0 where a < 3;
 create table t2 as select a as a, a as b from t0 where a < 3;
 insert into t2 select * from t2;
+select count(*) from t2;
+count(*)
+6
 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
@@ -1391,7 +1393,7 @@ drop table t0, t1;
 create table t1 (
 idIndividual int primary key
 );
-insert into t1 values (1),(2);
+insert into t1 values (1),(2),(1000);
 create table t2 (
 idContact int primary key,
 contactType int,
@@ -1403,7 +1405,7 @@ idAddress int primary key,
 idContact int,
 postalStripped varchar(100)
 );
-insert into t3 values (1,1, 'foo'), (2,2,'bar');
+insert into t3 values (1,1, 'foo'), (2,2,'T2H3B2');
 The following must be converted to a semi-join:
 set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch='materialization=off';
@@ -1419,6 +1421,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.c.idObj	1	100.00	Using index; End temporary
 Warnings:
 Note	1003	select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where `test`.`cona`.`postalStripped` = 'T2H3B2' and `test`.`a`.`idIndividual` = `test`.`c`.`idObj` and `test`.`c`.`idContact` = `test`.`cona`.`idContact`
+SELECT a.idIndividual FROM t1 a 
+WHERE a.idIndividual IN 
+(	SELECT c.idObj FROM t3 cona 
+INNER JOIN t2 c ON c.idContact=cona.idContact 
+WHERE cona.postalStripped='T2H3B2'
+	);
+idIndividual
+2
 set @@optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 #
diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result
index 25d76e6ee8c..41f280b5bd0 100644
--- a/mysql-test/main/subselect3_jcl6.result
+++ b/mysql-test/main/subselect3_jcl6.result
@@ -1183,9 +1183,8 @@ create table t3 ( a int , filler char(100), key(a));
 insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -1339,6 +1338,9 @@ insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 as select a as a, a as b, a as c from t0 where a < 3;
 create table t2 as select a as a, a as b from t0 where a < 3;
 insert into t2 select * from t2;
+select count(*) from t2;
+count(*)
+6
 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -1394,7 +1396,7 @@ drop table t0, t1;
 create table t1 (
 idIndividual int primary key
 );
-insert into t1 values (1),(2);
+insert into t1 values (1),(2),(1000);
 create table t2 (
 idContact int primary key,
 contactType int,
@@ -1406,7 +1408,7 @@ idAddress int primary key,
 idContact int,
 postalStripped varchar(100)
 );
-insert into t3 values (1,1, 'foo'), (2,2,'bar');
+insert into t3 values (1,1, 'foo'), (2,2,'T2H3B2');
 The following must be converted to a semi-join:
 set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch='materialization=off';
@@ -1422,6 +1424,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.c.idObj	1	100.00	Using index; End temporary
 Warnings:
 Note	1003	select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where `test`.`cona`.`postalStripped` = 'T2H3B2' and `test`.`a`.`idIndividual` = `test`.`c`.`idObj` and `test`.`c`.`idContact` = `test`.`cona`.`idContact`
+SELECT a.idIndividual FROM t1 a 
+WHERE a.idIndividual IN 
+(	SELECT c.idObj FROM t3 cona 
+INNER JOIN t2 c ON c.idContact=cona.idContact 
+WHERE cona.postalStripped='T2H3B2'
+	);
+idIndividual
+2
 set @@optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 #
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index 3535d35a6f3..7c12b2f1aa5 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -1247,7 +1247,7 @@ drop table t1, t2;
 #
 CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
 CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
-INSERT INTO t2 VALUES ('k'), ('d');
+INSERT INTO t2 VALUES ('k'), ('d'),('x');
 CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
 INSERT INTO t3 VALUES ('a'), ('b'), ('c');
 CREATE TABLE t4 (c1 varchar(1) primary key);
@@ -1260,16 +1260,16 @@ EXPLAIN
 SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
 SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 c1	c1
 EXPLAIN
 SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(t1)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
 SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 c1	c1
 SET optimizer_switch='materialization=on';
@@ -1277,10 +1277,10 @@ EXPLAIN
 SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	Const row not found
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
 c1	c1	c1
 EXPLAIN
@@ -1288,8 +1288,8 @@ SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t4	index	NULL	PRIMARY	3	NULL	2	Using index
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
 c1	c1
 SET optimizer_switch=@save_optimizer_switch;
@@ -2753,12 +2753,21 @@ INSERT INTO t4 VALUES
 ('w'),('w'),('x'),('x'), (NULL),(NULL);
 SET @save_join_cache_level=@@join_cache_level;
 SET join_cache_level=0;
+explain select 1
+from t2 join t1 on
+('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t1	index	NULL	v1	9	NULL	5	Using index
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	t1	ref_or_null	v1	v1	4	const	3	Using where; Using index
+2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	50	Using where
 select 1
 from t2 join t1 on
-('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 500;
+('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10;
 1
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 3020 rows, which exceeds LIMIT ROWS EXAMINED (500). The query result may be incomplete
+Warning	1931	Query execution was interrupted. The query examined at least 14 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete
 SET join_cache_level= @save_join_cache_level;
 DROP TABLE t1,t2,t3,t4;
 #
@@ -2788,9 +2797,8 @@ set names 'utf8';
 EXPLAIN
 SELECT * FROM t2 WHERE (t2.a,t2.b) IN (('abc',1), ('def', 2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	func,func	1	Using where
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	<derived3>	ref	key1	key1	4	test.t2.b	2	Using where; FirstMatch(t2)
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 set names default;
 set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold;
diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test
index a32d6ec69d5..8c075541114 100644
--- a/mysql-test/main/subselect4.test
+++ b/mysql-test/main/subselect4.test
@@ -943,7 +943,7 @@ drop table t1, t2;
 
 CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
 CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
-INSERT INTO t2 VALUES ('k'), ('d');
+INSERT INTO t2 VALUES ('k'), ('d'),('x');
 CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
 INSERT INTO t3 VALUES ('a'), ('b'), ('c');
 CREATE TABLE t4 (c1 varchar(1) primary key);
@@ -2281,9 +2281,13 @@ INSERT INTO t4 VALUES
 SET @save_join_cache_level=@@join_cache_level;
 SET join_cache_level=0;
 
+explain select 1
+from t2 join t1 on
+('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10;
+
 select 1
 from t2 join t1 on
-('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 500;
+('i','w') not in (select t1.v1,t4.v2 from t4,t1,t3 where t3.v2 = t1.v1) LIMIT ROWS EXAMINED 10;
 SET join_cache_level= @save_join_cache_level;
 
 DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result
index c01c28fc258..7b3966c3f93 100644
--- a/mysql-test/main/subselect_exists2in.result
+++ b/mysql-test/main/subselect_exists2in.result
@@ -297,23 +297,27 @@ d	a	b	e412	e412	h412
 d	b	a	i421	i421	l421
 d	b	b	m422	m422	o422
 drop table t1, t2, t3;
+#
+# LP BUG#901835 - incorrect semi-join conversion after exists2in
+#
 CREATE TABLE t1 ( a INT );
-INSERT INTO t1 VALUES (7),(0);
+INSERT INTO t1 VALUES (7),(0),(100);
 CREATE TABLE t2 ( b INT );
-INSERT INTO t2 VALUES (0),(8);
+INSERT INTO t2 VALUES (0),(8),(1000),(2000),(3000),(4000),(5000);
 SELECT * FROM t1 WHERE
 EXISTS ( SELECT * FROM t2 WHERE b = a )
 OR a > 0;
 a
 7
 0
+100
 explain extended
 SELECT * FROM t1 WHERE
 EXISTS ( SELECT * FROM t2 WHERE b = a )
 OR a > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`b`)))) or `test`.`t1`.`a` > 0
@@ -713,14 +717,14 @@ set optimizer_switch='exists_to_in=on';
 # correct calculation of reserved items (postreview-fix)
 #
 create table t1 (col1 int, col2 int, col3 int);
-insert into t1 values (1,2,3),(2,3,4),(4,5,6);
+insert into t1 values (1,2,3),(2,3,4),(4,5,6),(7,8,9);
 create table t2 as select * from t1;
 explain extended
 select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	
 Warnings:
 Note	1276	Field or reference 'test.t1.col1' of SELECT #2 was resolved in SELECT #1
 Note	1276	Field or reference 'test.t1.col2' of SELECT #2 was resolved in SELECT #1
@@ -730,6 +734,7 @@ col1	col2	col3
 1	2	3
 2	3	4
 4	5	6
+7	8	9
 drop table t1,t2;
 #
 # MDEV-3879: Exists2In: Wrong result (extra row) and unexpected
diff --git a/mysql-test/main/subselect_exists2in.test b/mysql-test/main/subselect_exists2in.test
index e70d643138b..bf884cc1fa9 100644
--- a/mysql-test/main/subselect_exists2in.test
+++ b/mysql-test/main/subselect_exists2in.test
@@ -238,13 +238,13 @@ group by a1,a2,b;
 
 drop table t1, t2, t3;
 
-#
-# LP BUG#901835 - incorrect semi-join conversion after exists2in
-#
+--echo #
+--echo # LP BUG#901835 - incorrect semi-join conversion after exists2in
+--echo #
 CREATE TABLE t1 ( a INT );
-INSERT INTO t1 VALUES (7),(0);
+INSERT INTO t1 VALUES (7),(0),(100);
 CREATE TABLE t2 ( b INT );
-INSERT INTO t2 VALUES (0),(8);
+INSERT INTO t2 VALUES (0),(8),(1000),(2000),(3000),(4000),(5000);
 
 SELECT * FROM t1 WHERE
   EXISTS ( SELECT * FROM t2 WHERE b = a )
@@ -562,7 +562,7 @@ set optimizer_switch='exists_to_in=on';
 --echo # correct calculation of reserved items (postreview-fix)
 --echo #
 create table t1 (col1 int, col2 int, col3 int); 
-insert into t1 values (1,2,3),(2,3,4),(4,5,6);
+insert into t1 values (1,2,3),(2,3,4),(4,5,6),(7,8,9);
 create table t2 as select * from t1;
 explain extended
 select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2);
diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result
index 29dcc706444..c9d68666ac7 100644
--- a/mysql-test/main/subselect_mat.result
+++ b/mysql-test/main/subselect_mat.result
@@ -1510,8 +1510,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on';
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	7	func,func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 COUNT(*)
 2
@@ -2193,9 +2192,8 @@ mysqltest1
 EXPLAIN EXTENDED
 SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
-1	PRIMARY	t1	eq_ref	db	db	764	information_schema.schemata.SCHEMA_NAME	1	100.00	Using where; Using index
-2	MATERIALIZED	schemata	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+1	PRIMARY	schemata	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Start temporary; Using temporary; Using filesort
+1	PRIMARY	t1	eq_ref	db	db	764	information_schema.schemata.SCHEMA_NAME	1	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t1`.`db` AS `db` from `test`.`t1` semi join (`information_schema`.`schemata`) where `test`.`t1`.`db` = `information_schema`.`schemata`.`SCHEMA_NAME` order by `test`.`t1`.`db` desc
 drop table t1;
@@ -2441,8 +2439,8 @@ alter table t1 add key(id);
 explain
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	id	id	4	NULL	9	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
+1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 1
@@ -2498,8 +2496,8 @@ INSERT INTO t2 VALUES  (11,11),(12,12),(13,13);
 CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2;
 explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	id	id	4	NULL	9	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
+1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 1
diff --git a/mysql-test/main/subselect_mat_cost.result b/mysql-test/main/subselect_mat_cost.result
index d46fb616d5e..9de70573189 100644
--- a/mysql-test/main/subselect_mat_cost.result
+++ b/mysql-test/main/subselect_mat_cost.result
@@ -60,47 +60,19 @@ Q1.1m:
 MATERIALIZATION: there are too many rows in the outer query
 to be looked up in the inner table.
 set statement optimizer_cache_hit_ratio=20 for EXPLAIN
-SELECT Name FROM Country
+SELECT count(*) FROM Country
 WHERE (Code IN (select Country from City where City.Population > 100000) OR
 Name LIKE 'L%') AND
-surfacearea > 1000000;
+surfacearea > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	Country	ALL	Name,SurfaceArea	NULL	NULL	NULL	239	Using where
 3	MATERIALIZED	City	ALL	Population,Country	NULL	NULL	NULL	4079	Using where
-set statement optimizer_cache_hit_ratio=20 for SELECT Name FROM Country
+set statement optimizer_cache_hit_ratio=20 for SELECT count(*) FROM Country
 WHERE (Code IN (select Country from City where City.Population > 100000) OR
 Name LIKE 'L%') AND
-surfacearea > 1000000;
-Name
-Algeria
-Angola
-Argentina
-Australia
-Bolivia
-Brazil
-Egypt
-South Africa
-Ethiopia
-Indonesia
-India
-Iran
-Canada
-Kazakstan
-China
-Colombia
-Congo, The Democratic Republic of the
-Libyan Arab Jamahiriya
-Mali
-Mauritania
-Mexico
-Mongolia
-Niger
-Peru
-Saudi Arabia
-Sudan
-Chad
-Russian Federation
-United States
+surfacearea > 100000;
+count(*)
+107
 Q1.1e:
 IN-EXISTS: the materialization cost is the same as above, but
 there are much fewer outer rows to be looked up, thus the
diff --git a/mysql-test/main/subselect_mat_cost.test b/mysql-test/main/subselect_mat_cost.test
index 12263e75cfc..6b7fa4fee64 100644
--- a/mysql-test/main/subselect_mat_cost.test
+++ b/mysql-test/main/subselect_mat_cost.test
@@ -74,15 +74,15 @@ set @@optimizer_switch = 'in_to_exists=on,semijoin=on,materialization=on,partial
 -- echo MATERIALIZATION: there are too many rows in the outer query
 -- echo to be looked up in the inner table.
 set statement optimizer_cache_hit_ratio=20 for EXPLAIN
-SELECT Name FROM Country
+SELECT count(*) FROM Country
 WHERE (Code IN (select Country from City where City.Population > 100000) OR
        Name LIKE 'L%') AND
-      surfacearea > 1000000;
+      surfacearea > 100000;
 
-set statement optimizer_cache_hit_ratio=20 for SELECT Name FROM Country
+set statement optimizer_cache_hit_ratio=20 for SELECT count(*) FROM Country
 WHERE (Code IN (select Country from City where City.Population > 100000) OR
        Name LIKE 'L%') AND
-      surfacearea > 1000000;
+      surfacearea > 100000;
 
 -- echo Q1.1e:
 -- echo IN-EXISTS: the materialization cost is the same as above, but
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result
index 29104376c46..e2ab6ae8a69 100644
--- a/mysql-test/main/subselect_no_exists_to_in.result
+++ b/mysql-test/main/subselect_no_exists_to_in.result
@@ -899,6 +899,9 @@ select (select a+1) from t1;
 NULL
 4.5
 drop table t1;
+#
+# Null with keys
+#
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -4441,10 +4444,13 @@ out_a	MIN(b)
 1	2
 2	4
 DROP TABLE t1;
+#
+# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+#
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
 INSERT INTO t1 VALUES (1),(2),(3),(4);
-INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2),(1000);
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 2
 2
@@ -4453,7 +4459,7 @@ EXPLAIN EXTENDED
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	/* select#1 */ select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(exists(/* select#2 */ select 1 from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` limit 1))
@@ -4462,8 +4468,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -5702,7 +5708,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 CREATE TABLE t1 (a int) ;
 INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
 CREATE TABLE t2 (a int, INDEX idx(a)) ;
-INSERT INTO t2 VALUES (NULL), (1), (NULL);
+INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000);
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
 a
@@ -5712,7 +5718,7 @@ SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 a
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index cc8c61c61dd..6078ac53feb 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -902,6 +902,9 @@ select (select a+1) from t1;
 NULL
 4.5
 drop table t1;
+#
+# Null with keys
+#
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -4441,10 +4444,13 @@ out_a	MIN(b)
 1	2
 2	4
 DROP TABLE t1;
+#
+# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+#
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
 INSERT INTO t1 VALUES (1),(2),(3),(4);
-INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2),(1000);
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 2
 2
@@ -4453,7 +4459,7 @@ EXPLAIN EXTENDED
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`a`
@@ -4462,8 +4468,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -5699,7 +5705,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 CREATE TABLE t1 (a int) ;
 INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
 CREATE TABLE t2 (a int, INDEX idx(a)) ;
-INSERT INTO t2 VALUES (NULL), (1), (NULL);
+INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000);
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
 a
@@ -5709,7 +5715,7 @@ SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 a
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result
index 6e557485796..b3d382889a3 100644
--- a/mysql-test/main/subselect_no_opts.result
+++ b/mysql-test/main/subselect_no_opts.result
@@ -898,6 +898,9 @@ select (select a+1) from t1;
 NULL
 4.5
 drop table t1;
+#
+# Null with keys
+#
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -4437,10 +4440,13 @@ out_a	MIN(b)
 1	2
 2	4
 DROP TABLE t1;
+#
+# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+#
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
 INSERT INTO t1 VALUES (1),(2),(3),(4);
-INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2),(1000);
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 2
 2
@@ -4449,7 +4455,7 @@ EXPLAIN EXTENDED
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	/* select#1 */ select 2 AS `2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where <cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`))
@@ -4458,8 +4464,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -5695,7 +5701,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 CREATE TABLE t1 (a int) ;
 INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
 CREATE TABLE t2 (a int, INDEX idx(a)) ;
-INSERT INTO t2 VALUES (NULL), (1), (NULL);
+INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000);
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
 a
@@ -5705,7 +5711,7 @@ SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 a
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index 6f457f76c0c..9246e09074d 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -901,6 +901,9 @@ select (select a+1) from t1;
 NULL
 4.5
 drop table t1;
+#
+# Null with keys
+#
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -4444,10 +4447,13 @@ out_a	MIN(b)
 1	2
 2	4
 DROP TABLE t1;
+#
+# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+#
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
 INSERT INTO t1 VALUES (1),(2),(3),(4);
-INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2),(1000);
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 2
 2
@@ -4457,7 +4463,7 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	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	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where 1
@@ -4466,8 +4472,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -5706,7 +5712,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 CREATE TABLE t1 (a int) ;
 INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
 CREATE TABLE t2 (a int, INDEX idx(a)) ;
-INSERT INTO t2 VALUES (NULL), (1), (NULL);
+INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000);
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
 a
@@ -5717,7 +5723,7 @@ WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 a
@@ -5726,9 +5732,8 @@ EXPLAIN
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	index	idx	idx	5	NULL	3	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t2	ref	idx	idx	5	test.t1.a	2	Using index; FirstMatch(t1)
 DROP TABLE t1,t2;
 #
 # BUG#752992: Wrong results for a subquery with 'semijoin=on'
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index 5cc7b1cb22d..0c5a21303c7 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -348,10 +348,10 @@ patient_uq	clinic_uq
 explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t6	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	MATERIALIZED	t7	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index
+2	DEPENDENT SUBQUERY	t7	unique_subquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
-Note	1003	/* select#1 */ select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(<in_optimizer>(`test`.`t6`.`clinic_uq`,`test`.`t6`.`clinic_uq` in ( <materialize> (/* select#2 */ select `test`.`t7`.`uq` from `test`.`t7` where 1 ), <primary_index_lookup>(`test`.`t6`.`clinic_uq` in <temporary table> on distinct_key where `test`.`t6`.`clinic_uq` = `<subquery2>`.`uq`))))
+Note	1003	/* select#1 */ select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where <expr_cache><`test`.`t6`.`clinic_uq`>(<in_optimizer>(`test`.`t6`.`clinic_uq`,<exists>(<primary_index_lookup>(<cache>(`test`.`t6`.`clinic_uq`) in t7 on PRIMARY))))
 select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
 ERROR 23000: Column 'a' in field list is ambiguous
 drop table t1,t2,t3;
@@ -898,6 +898,9 @@ select (select a+1) from t1;
 NULL
 4.5
 drop table t1;
+#
+# Null with keys
+#
 CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY  (a));
 CREATE TABLE t2 (a int(11) default '0', INDEX (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4);
@@ -1443,9 +1446,9 @@ a
 explain extended select * from t2 where t2.a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	4	100.00	Using where; Using index
-2	MATERIALIZED	t1	index	PRIMARY	PRIMARY	4	NULL	4	100.00	Using index
+2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`))))
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 a
 2
@@ -1614,21 +1617,21 @@ a3	1
 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	100.00	Using index
-2	MATERIALIZED	t2	index	s1	s1	6	NULL	2	100.00	Using index
+2	DEPENDENT SUBQUERY	t2	index	s1	s1	6	NULL	2	100.00	Using where; Using index
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on distinct_key where `test`.`t1`.`s1` = `<subquery2>`.`s1`)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
+Note	1003	/* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
 explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	100.00	Using index
-2	MATERIALIZED	t2	index	s1	s1	6	NULL	2	100.00	Using index
+2	DEPENDENT SUBQUERY	t2	index	s1	s1	6	NULL	2	100.00	Using where; Using index
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on distinct_key where `test`.`t1`.`s1` = `<subquery2>`.`s1`)))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
+Note	1003	/* select#1 */ select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
 explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	100.00	Using index
-2	MATERIALIZED	t2	index	s1	s1	6	NULL	2	100.00	Using index
+2	DEPENDENT SUBQUERY	t2	index	s1	s1	6	NULL	2	100.00	Using where; Using index
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on distinct_key where `test`.`t1`.`s1` = `<subquery2>`.`s1`)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
+Note	1003	/* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	100.00	Using index
@@ -3179,7 +3182,7 @@ INSERT INTO t2 VALUES (1),(2),(3);
 EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
-2	MATERIALIZED	t1	index	a	a	5	NULL	5	Using index
+2	SUBQUERY	t1	index_subquery	a	a	5	func	3	Using index; Full scan on NULL key
 SELECT a, a IN (SELECT a FROM t1) FROM t2;
 a	a IN (SELECT a FROM t1)
 1	1
@@ -4437,10 +4440,13 @@ out_a	MIN(b)
 1	2
 2	4
 DROP TABLE t1;
+#
+# Bug#32036 EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+#
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2 (a INT);
 INSERT INTO t1 VALUES (1),(2),(3),(4);
-INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2),(1000);
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 2
 2
@@ -4449,7 +4455,7 @@ EXPLAIN EXTENDED
 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
 Note	1003	/* select#1 */ select 2 AS `2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`a`))))
@@ -4458,8 +4464,8 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
 (SELECT 1 FROM t2 WHERE t1.a = t2.a));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
@@ -5695,7 +5701,7 @@ DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 CREATE TABLE t1 (a int) ;
 INSERT INTO t1 VALUES (NULL), (1), (NULL), (2);
 CREATE TABLE t2 (a int, INDEX idx(a)) ;
-INSERT INTO t2 VALUES (NULL), (1), (NULL);
+INSERT INTO t2 VALUES (NULL), (1), (NULL),(1000);
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX () WHERE t2.a = t1.a);
 a
@@ -5705,7 +5711,7 @@ SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	
 SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 a
@@ -5715,7 +5721,7 @@ SELECT * FROM t1
 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	MATERIALIZED	t2	index	idx	idx	5	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	idx	idx	5	func	2	Using index
 DROP TABLE t1,t2;
 #
 # BUG#752992: Wrong results for a subquery with 'semijoin=on'
diff --git a/mysql-test/main/subselect_partial_match.result b/mysql-test/main/subselect_partial_match.result
index 52c30492675..3d10e912f22 100644
--- a/mysql-test/main/subselect_partial_match.result
+++ b/mysql-test/main/subselect_partial_match.result
@@ -760,20 +760,18 @@ drop table t1,t2;
 # LP BUG#601156
 #
 CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL);
-INSERT INTO t1 VALUES (NULL,2);
-INSERT INTO t1 VALUES (4,NULL);
+INSERT INTO t1 VALUES (NULL,2), (4,NULL),(100,100);
 CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL);
-INSERT INTO t2 VALUES (6,NULL);
-INSERT INTO t2 VALUES (NULL,0);
+INSERT INTO t2 VALUES (6,NULL), (NULL,0),(1000,1000);
 set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on';
 set @tmp_optimizer_switch=@@optimizer_switch;
 set optimizer_switch='derived_merge=off,derived_with_keys=off';
 EXPLAIN EXTENDED
 SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
 Note	1003	/* select#1 */ select `table1`.`a1` AS `a1`,`table1`.`a2` AS `a2` from (/* select#2 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where !(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (/* select#3 */ select `test`.`t2`.`b2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where `test`.`t1`.`a1` = `<subquery3>`.`b2`))))) `table1`
 set optimizer_switch=@tmp_optimizer_switch;
@@ -783,11 +781,11 @@ DROP TABLE t1, t2;
 #
 set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off';
 create table t1 (a1 char(3) DEFAULT NULL, a2 char(3) DEFAULT NULL);
-insert into t1 values (NULL, 'a21'), (NULL, 'a22');
+insert into t1 values (NULL, 'a21'), (NULL, 'a22'), ('xxx','xxx');
 explain select * from t1 where (a1, a2) not in (select a1, a2 from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t1 where (a1, a2) not in (select a1, a2 from t1);
 a1	a2
 drop table t1;
diff --git a/mysql-test/main/subselect_partial_match.test b/mysql-test/main/subselect_partial_match.test
index fd1e6de716c..8fdd5e451ba 100644
--- a/mysql-test/main/subselect_partial_match.test
+++ b/mysql-test/main/subselect_partial_match.test
@@ -614,11 +614,9 @@ drop table t1,t2;
 --echo #
 
 CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL);
-INSERT INTO t1 VALUES (NULL,2);
-INSERT INTO t1 VALUES (4,NULL);
+INSERT INTO t1 VALUES (NULL,2), (4,NULL),(100,100);
 CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL);
-INSERT INTO t2 VALUES (6,NULL);
-INSERT INTO t2 VALUES (NULL,0);
+INSERT INTO t2 VALUES (6,NULL), (NULL,0),(1000,1000);
 
 set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on';
 
@@ -637,7 +635,7 @@ DROP TABLE t1, t2;
 set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off';
 
 create table t1 (a1 char(3) DEFAULT NULL, a2 char(3) DEFAULT NULL);
-insert into t1 values (NULL, 'a21'), (NULL, 'a22');
+insert into t1 values (NULL, 'a21'), (NULL, 'a22'), ('xxx','xxx');
 explain select * from t1 where (a1, a2) not in (select a1, a2 from t1);
 select * from t1 where (a1, a2) not in (select a1, a2 from t1);
 drop table t1;
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result
index b5e55333e0d..67df72d2209 100644
--- a/mysql-test/main/subselect_sj.result
+++ b/mysql-test/main/subselect_sj.result
@@ -91,9 +91,9 @@ select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
 id	select_type	tABle	type	possiBle_keys	key	key_len	ref	rows	filtered	ExtrA
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	MATERIALIZED	t10	index	PRIMARY	PRIMARY	4	NULL	10	100.00	Using index
+2	DEPENDENT SUBQUERY	t10	unique_suBquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (/* select#2 */ select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where `test`.`t1`.`A` = `<suBquery2>`.`pk`)))) where 1
+Note	1003	/* select#1 */ select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`t2`.`A`) in t10 on PRIMARY)))) where 1
 set @save_join_buffer_size=@@join_buffer_size;
 set join_buffer_size=8*1024;
 we shouldn't flatten if we're going to get a join of > MAX_TABLES.
@@ -1627,7 +1627,7 @@ drop table t1,t2;
 # BUG#787299: Valgrind complains on a join query with two IN subqueries
 #
 create table t1 (a int);
-insert into t1 values (1), (2), (3);
+insert into t1 values (1), (2), (3),(1000),(2000);
 create table t2 as select * from t1;
 select * from t1 A, t1 B
 where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
@@ -1635,16 +1635,18 @@ a	a
 1	1
 2	2
 3	3
+1000	1000
+2000	2000
 explain
 select * from t1 A, t1 B
 where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	5	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	C	ALL	NULL	NULL	NULL	NULL	3	
-3	MATERIALIZED	D	ALL	NULL	NULL	NULL	NULL	3	
+2	MATERIALIZED	C	ALL	NULL	NULL	NULL	NULL	5	
+3	MATERIALIZED	D	ALL	NULL	NULL	NULL	NULL	5	
 drop table t1, t2;
 # 
 # BUG#784441: Abort on semijoin with a view as the inner table
@@ -1996,13 +1998,12 @@ INSERT INTO t4 VALUES (0),(NULL),(-1),(-2),(-3);
 explain extended
 SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4 where f2 = 0 or f2 IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	PRIMARY	t4	ref_or_null	f2	f2	5	const	4	100.00	Using where; Using index; FirstMatch(t2)
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t4	ref_or_null	f2	f2	5	const	4	100.00	Using where; Using index
 Warnings:
-Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` and (`test`.`t4`.`f2` = 0 or `test`.`t4`.`f2` is null)
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f2` = `test`.`t2`.`f3` and `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` and (`test`.`t2`.`f3` = 0 or `test`.`t2`.`f3` is null)
 SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4 where f2 = 0 or f2 IS NULL);
 f1	f2	f3	f3
 2	0	0	0
diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test
index bc109ba1b9a..417ec52812f 100644
--- a/mysql-test/main/subselect_sj.test
+++ b/mysql-test/main/subselect_sj.test
@@ -1425,7 +1425,7 @@ drop table t1,t2;
 --echo # BUG#787299: Valgrind complains on a join query with two IN subqueries
 --echo #
 create table t1 (a int);
-insert into t1 values (1), (2), (3);
+insert into t1 values (1), (2), (3),(1000),(2000);
 create table t2 as select * from t1;
 select * from t1 A, t1 B
  where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result
index 95f80b1a271..794c86aef55 100644
--- a/mysql-test/main/subselect_sj2.result
+++ b/mysql-test/main/subselect_sj2.result
@@ -618,7 +618,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	MATERIALIZED	t3	index	PRIMARY	PRIMARY	4	NULL	10	Using index
+2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 drop table t0, t1, t2, t3;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -934,20 +934,22 @@ INSERT INTO t1 VALUES
 (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
 (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
 CREATE TABLE t2 (
-pk INT, d VARCHAR(1), e INT,
+pk INT, d VARCHAR(1), e INT, f int,
 PRIMARY KEY(pk), KEY(d,e)
 ) ENGINE=InnoDB;
-INSERT INTO t2 VALUES
+INSERT INTO t2 (pk,d,e) VALUES
 (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
 (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
 (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
 (15,'g',6),(16,'x',7),(17,'f',8);
+update t2 set f=pk/2;
 analyze table t1,t2;
 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
+# Original query, changed because of new optimizations
 explain
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
@@ -957,9 +959,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index; LooseScan
 1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index; FirstMatch(t2)
 1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	
+explain
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
-WHERE a = d AND ( pk < 2 OR d = 'z' )
+WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index_merge	PRIMARY,d	d,PRIMARY	4,4	NULL	2	Using sort_union(d,PRIMARY); Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
+1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	End temporary
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0
 );
 a	b	c
 Warnings:
diff --git a/mysql-test/main/subselect_sj2.test b/mysql-test/main/subselect_sj2.test
index 419deb6f7e7..31e6f3d7231 100644
--- a/mysql-test/main/subselect_sj2.test
+++ b/mysql-test/main/subselect_sj2.test
@@ -1115,26 +1115,35 @@ INSERT INTO t1 VALUES
 (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
 
 CREATE TABLE t2 (
-  pk INT, d VARCHAR(1), e INT,
+  pk INT, d VARCHAR(1), e INT, f int,
   PRIMARY KEY(pk), KEY(d,e)
 ) ENGINE=InnoDB;
 
-INSERT INTO t2 VALUES
+INSERT INTO t2 (pk,d,e) VALUES
 (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
 (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
 (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
 (15,'g',6),(16,'x',7),(17,'f',8);
+update t2 set f=pk/2;
 
 analyze table t1,t2;
 
+--echo # Original query, changed because of new optimizations
 explain
 SELECT * FROM t1 WHERE b IN (
   SELECT d FROM t2, t1
   WHERE a = d AND ( pk < 2 OR d = 'z' )
 );
+
+explain
 SELECT * FROM t1 WHERE b IN (
   SELECT d FROM t2, t1
-  WHERE a = d AND ( pk < 2 OR d = 'z' )
+  WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0
+);
+
+SELECT * FROM t1 WHERE b IN (
+  SELECT d FROM t2, t1
+  WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0
 );
 
 DROP TABLE t1, t2;
diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result
index 39f5c372d00..c1c1b9478c1 100644
--- a/mysql-test/main/subselect_sj2_jcl6.result
+++ b/mysql-test/main/subselect_sj2_jcl6.result
@@ -630,7 +630,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.a	3	Using where; Using join buffer (flat, BNLH join)
-2	MATERIALIZED	t3	index	PRIMARY	PRIMARY	4	NULL	10	Using index
+2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 drop table t0, t1, t2, t3;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -906,11 +906,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	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	4	test.t3.a	1	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t4	hash_ALL	NULL	#hash#$hj	4	test.t3.b	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
+1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	4	test.t3.a	1	Using where; Using join buffer (incremental, BNLH join)
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
-2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	
 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);
@@ -946,20 +945,22 @@ INSERT INTO t1 VALUES
 (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
 (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
 CREATE TABLE t2 (
-pk INT, d VARCHAR(1), e INT,
+pk INT, d VARCHAR(1), e INT, f int,
 PRIMARY KEY(pk), KEY(d,e)
 ) ENGINE=InnoDB;
-INSERT INTO t2 VALUES
+INSERT INTO t2 (pk,d,e) VALUES
 (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
 (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
 (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
 (15,'g',6),(16,'x',7),(17,'f',8);
+update t2 set f=pk/2;
 analyze table t1,t2;
 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
+# Original query, changed because of new optimizations
 explain
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
@@ -969,9 +970,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index; LooseScan
 1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index; FirstMatch(t2)
 1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+explain
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
-WHERE a = d AND ( pk < 2 OR d = 'z' )
+WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index_merge	PRIMARY,d	d,PRIMARY	4,4	NULL	2	Using sort_union(d,PRIMARY); Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
+1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0
 );
 a	b	c
 Warnings:
diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result
index 3d4d9361440..87f549e5efe 100644
--- a/mysql-test/main/subselect_sj2_mat.result
+++ b/mysql-test/main/subselect_sj2_mat.result
@@ -620,7 +620,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	MATERIALIZED	t3	index	PRIMARY	PRIMARY	4	NULL	10	Using index
+2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 drop table t0, t1, t2, t3;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -936,20 +936,22 @@ INSERT INTO t1 VALUES
 (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
 (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
 CREATE TABLE t2 (
-pk INT, d VARCHAR(1), e INT,
+pk INT, d VARCHAR(1), e INT, f int,
 PRIMARY KEY(pk), KEY(d,e)
 ) ENGINE=InnoDB;
-INSERT INTO t2 VALUES
+INSERT INTO t2 (pk,d,e) VALUES
 (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
 (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
 (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
 (15,'g',6),(16,'x',7),(17,'f',8);
+update t2 set f=pk/2;
 analyze table t1,t2;
 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
+# Original query, changed because of new optimizations
 explain
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
@@ -959,9 +961,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index; LooseScan
 1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index; FirstMatch(t2)
 1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	
+explain
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
-WHERE a = d AND ( pk < 2 OR d = 'z' )
+WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index_merge	PRIMARY,d	d,PRIMARY	4,4	NULL	2	Using sort_union(d,PRIMARY); Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
+1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	End temporary
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0
 );
 a	b	c
 Warnings:
@@ -1651,12 +1662,12 @@ 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	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	5	100.00	Using where; Using join buffer (flat, BNLH join)
 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)
 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
@@ -1664,12 +1675,12 @@ 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	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	100.00	
+1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	5	100.00	Using where; Using join buffer (flat, BNLH join)
 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)
 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
@@ -1709,7 +1720,7 @@ set optimizer_switch=@save_optimizer_switch;
 CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB;
 INSERT INTO t1 VALUES ('qux'),('foo');
 CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB;
-INSERT INTO t2 VALUES ('bar'),('foo'),('qux');
+INSERT INTO t2 VALUES ('bar'),('foo'),('qux'),('qq1'),('qq2');
 SELECT f1 FROM t1 
 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' )
 HAVING f1 != 'foo'
@@ -1723,7 +1734,7 @@ ORDER BY f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	f1	f1	11	NULL	2	Using where; Using index
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	11	func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 DROP TABLE t1,t2;
 #
 # MDEV-16225: wrong resultset from query with semijoin=on
diff --git a/mysql-test/main/subselect_sj2_mat.test b/mysql-test/main/subselect_sj2_mat.test
index 4b768652670..9417f777442 100644
--- a/mysql-test/main/subselect_sj2_mat.test
+++ b/mysql-test/main/subselect_sj2_mat.test
@@ -300,7 +300,7 @@ set optimizer_switch=@save_optimizer_switch;
 CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB;
 INSERT INTO t1 VALUES ('qux'),('foo');
 CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB;
-INSERT INTO t2 VALUES ('bar'),('foo'),('qux');
+INSERT INTO t2 VALUES ('bar'),('foo'),('qux'),('qq1'),('qq2');
 
 let $q=
 SELECT f1 FROM t1 
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index c56bf56289a..dafb6de702d 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -102,9 +102,9 @@ select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
 id	select_type	tABle	type	possiBle_keys	key	key_len	ref	rows	filtered	ExtrA
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join Buffer (flAt, BNL join)
-2	MATERIALIZED	t10	index	PRIMARY	PRIMARY	4	NULL	10	100.00	Using index
+2	DEPENDENT SUBQUERY	t10	unique_suBquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (/* select#2 */ select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where `test`.`t1`.`A` = `<suBquery2>`.`pk`)))) where 1
+Note	1003	/* select#1 */ select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`A` = `test`.`t1`.`A` And <in_optimizer>(`test`.`t1`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`t2`.`A`) in t10 on PRIMARY)))) where 1
 set @save_join_buffer_size=@@join_buffer_size;
 set join_buffer_size=8*1024;
 we shouldn't flatten if we're going to get a join of > MAX_TABLES.
@@ -1638,7 +1638,7 @@ drop table t1,t2;
 # BUG#787299: Valgrind complains on a join query with two IN subqueries
 #
 create table t1 (a int);
-insert into t1 values (1), (2), (3);
+insert into t1 values (1), (2), (3),(1000),(2000);
 create table t2 as select * from t1;
 select * from t1 A, t1 B
 where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
@@ -1646,16 +1646,18 @@ a	a
 1	1
 2	2
 3	3
+1000	1000
+2000	2000
 explain
 select * from t1 A, t1 B
 where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	5	
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	C	ALL	NULL	NULL	NULL	NULL	3	
-3	MATERIALIZED	D	ALL	NULL	NULL	NULL	NULL	3	
+2	MATERIALIZED	C	ALL	NULL	NULL	NULL	NULL	5	
+3	MATERIALIZED	D	ALL	NULL	NULL	NULL	NULL	5	
 drop table t1, t2;
 # 
 # BUG#784441: Abort on semijoin with a view as the inner table
@@ -2007,13 +2009,12 @@ INSERT INTO t4 VALUES (0),(NULL),(-1),(-2),(-3);
 explain extended
 SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4 where f2 = 0 or f2 IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	PRIMARY	t4	ref_or_null	f2	f2	5	const	4	100.00	Using where; Using index; FirstMatch(t2)
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (incremental, BNL join)
-2	MATERIALIZED	t4	ref_or_null	f2	f2	5	const	4	100.00	Using where; Using index
 Warnings:
-Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` and (`test`.`t4`.`f2` = 0 or `test`.`t4`.`f2` is null)
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f2` = `test`.`t2`.`f3` and `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` and (`test`.`t2`.`f3` = 0 or `test`.`t2`.`f3` is null)
 SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4 where f2 = 0 or f2 IS NULL);
 f1	f2	f3	f3
 2	0	0	0
diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result
index afd8e3b0734..d5148030edb 100644
--- a/mysql-test/main/subselect_sj_mat.result
+++ b/mysql-test/main/subselect_sj_mat.result
@@ -107,11 +107,10 @@ a1	a2
 explain extended
 select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1i	index	_it1_idx	_it1_idx	#	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	#	func	1	100.00	
-2	MATERIALIZED	t2i	index	it2i1,it2i3	it2i1	#	NULL	5	100.00	Using where; 
+1	PRIMARY	t2i	index	it2i1,it2i3	it2i1	#	NULL	5	50.00	Using where; Using index; LooseScan
+1	PRIMARY	t1i	ref	_it1_idx	_it1_idx	#	_ref_	1	100.00	
 Warnings:
-Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t2i`.`b1` > '0'
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t2i`.`b1` > '0'
 select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 a1	a2
 1 - 01	2 - 01
@@ -131,11 +130,10 @@ a1	a2
 explain extended
 select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1i	index	_it1_idx	_it1_idx	#	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	#	func,func	1	100.00	
-2	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	#	NULL	5	100.00	Using where; 
+1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	#	NULL	5	50.00	Using where; Using index; LooseScan
+1	PRIMARY	t1i	ref	_it1_idx	_it1_idx	#	_ref_	1	100.00	
 Warnings:
-Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t2i`.`b1` > '0'
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2` and `test`.`t2i`.`b1` > '0'
 select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 a1	a2
 1 - 01	2 - 01
@@ -278,11 +276,10 @@ a1	a2
 explain extended
 select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1i	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	100.00	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
-2	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	100.00	Using index
+1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	50.00	Using where; Using index; LooseScan
+1	PRIMARY	t1i	ref	it1i1,it1i2,it1i3	it1i3	18	test.t2i.b1,test.t2i.b2	1	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where 1
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where `test`.`t1i`.`a1` = `test`.`t2i`.`b1` and `test`.`t1i`.`a2` = `test`.`t2i`.`b2`
 select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
 a1	a2
 1 - 01	2 - 01
@@ -1546,8 +1543,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on';
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	7	func,func	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 COUNT(*)
 2
@@ -2232,9 +2228,8 @@ mysqltest1
 EXPLAIN EXTENDED
 SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
-1	PRIMARY	t1	eq_ref	db	db	764	information_schema.schemata.SCHEMA_NAME	1	100.00	Using where; Using index
-2	MATERIALIZED	schemata	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+1	PRIMARY	schemata	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Start temporary; Using temporary; Using filesort
+1	PRIMARY	t1	eq_ref	db	db	764	information_schema.schemata.SCHEMA_NAME	1	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t1`.`db` AS `db` from `test`.`t1` semi join (`information_schema`.`schemata`) where `test`.`t1`.`db` = `information_schema`.`schemata`.`SCHEMA_NAME` order by `test`.`t1`.`db` desc
 drop table t1;
@@ -2480,8 +2475,8 @@ alter table t1 add key(id);
 explain
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	id	id	4	NULL	9	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
+1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
 1
@@ -2537,8 +2532,8 @@ INSERT INTO t2 VALUES  (11,11),(12,12),(13,13);
 CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2;
 explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	id	id	4	NULL	9	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
+1	PRIMARY	t1	ref	id	id	4	test.t2.i1	2	Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
 1
diff --git a/mysql-test/main/type_time_6065.result b/mysql-test/main/type_time_6065.result
index 5df612e3001..c5fb790b656 100644
--- a/mysql-test/main/type_time_6065.result
+++ b/mysql-test/main/type_time_6065.result
@@ -2267,9 +2267,8 @@ outr.col_varchar_key IS NULL
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	outr	system	col_datetime_key	NULL	NULL	NULL	1	100.00	
-1	PRIMARY	outr2	index	col_time_key	col_time_key	4	NULL	20	100.00	Using where; Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
-2	MATERIALIZED	innr	ref	col_int_key	col_int_key	4	const	2	100.00	Using where
+1	PRIMARY	innr	ref	col_int_key	col_int_key	4	const	2	100.00	Using where; FirstMatch(outr)
+1	PRIMARY	outr2	index	col_time_key	col_time_key	4	NULL	20	100.00	Using where; Using index; Using join buffer (flat, BNL join)
 Warnings:
 Note	1003	select 1 AS `col_int_nokey` from `test`.`t3` `outr2` semi join (`test`.`t1` `innr`) where `test`.`innr`.`col_int_key` = 1 and `test`.`innr`.`pk` >= `test`.`innr`.`col_int_nokey` and `test`.`outr2`.`col_time_key` > '2001-11-04 19:07:55'
 SELECT outr.col_int_nokey
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index f5a4fc089da..fb6f5bae555 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -1093,10 +1093,11 @@ insert into t1 values (1);
 update v2 set s1 = 1;
 select * from v2;
 select * from t2;
-# scheck how VIEWs with subqueries work with prepared statements
+# check how VIEWs with subqueries work with prepared statements
 prepare stmt1 from "select * from v2;";
 execute stmt1;
 insert into t1 values (0);
+--sorted_result
 execute stmt1;
 deallocate prepare stmt1;
 drop view v2;
diff --git a/sql/handler.cc b/sql/handler.cc
index 0a2a086024c..677ab9f7cbe 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -8747,6 +8747,5 @@ void handler::set_optimizer_costs(THD *thd)
   optimizer_row_copy_cost= thd->variables.optimizer_row_copy_cost;
   optimizer_where_cmp_cost= thd->variables.optimizer_where_cmp_cost;
   optimizer_key_cmp_cost= thd->variables.optimizer_key_cmp_cost;
-  set_optimizer_cache_cost(cache_hit_cost(thd->variables.
-                                          optimizer_cache_hit_ratio));
+  set_optimizer_cache_cost(thd->optimizer_cache_hit_ratio);
 }
diff --git a/sql/opt_split.cc b/sql/opt_split.cc
index faec0f6e4d0..51759910faa 100644
--- a/sql/opt_split.cc
+++ b/sql/opt_split.cc
@@ -672,7 +672,7 @@ double spl_postjoin_oper_cost(THD *thd, double join_record_count, uint rec_len)
 {
   double cost;
   TMPTABLE_COSTS tmp_cost= get_tmp_table_costs(thd, join_record_count,
-                                               rec_len, 0);
+                                               rec_len, 0, 1);
   // cost to fill tmp table
   cost= tmp_cost.create + tmp_cost.write * join_record_count;
    // cost to perform post join operation used here
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 712955e6911..9e92231d519 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -2556,22 +2556,36 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map)
                                               &blobs_used);
         TMPTABLE_COSTS cost= get_tmp_table_costs(join->thd,
                                                  subjoin_out_rows, rowlen,
-                                                 blobs_used);
+                                                 blobs_used, 1);
+        double scan_cost, total_cost;
+        double row_copy_cost= ROW_COPY_COST_THD(thd);
+
         /*
           Let materialization cost include the cost to write the data into the
-          temporary table:
+          temporary table. Note that smj->materialization_cost already includes
+          row copy and compare costs of finding the original row.
         */ 
         sjm->materialization_cost.add_io(subjoin_out_rows, cost.write);
         sjm->materialization_cost.copy_cost+= cost.create;
 
         /*
           Set the cost to do a full scan of the temptable (will need this to 
-          consider doing sjm-scan):
-        */ 
-        sjm->scan_cost.reset();
-        sjm->scan_cost.add_io(sjm->rows, cost.lookup);
-
-        sjm->lookup_cost.convert_from_cost(cost.lookup);
+          consider doing sjm-scan). See ha_scan_time() for the basics of
+          the calculations.
+          We don't need to check the where clause for each row, so no
+          WHERE_COMPARE_COST is needed.
+        */
+        scan_cost= (TABLE_SCAN_SETUP_COST +
+                    (cost.block_size == 0 ? 0 :
+                     ((rowlen * (double) sjm->rows) / cost.block_size +
+                      TABLE_SCAN_SETUP_COST)));
+        total_cost= (scan_cost * cost.cache_hit_ratio * cost.avg_io_cost +
+                     row_copy_cost * sjm->rows);
+        sjm->scan_cost.convert_from_cost(total_cost);
+
+        /* When reading a row, we have also to check the where clause */
+        sjm->lookup_cost.convert_from_cost(cost.lookup +
+                                           WHERE_COMPARE_COST_THD(thd));
         sj_nest->sj_mat_info= sjm;
         DBUG_EXECUTE("opt", print_sjm(sjm););
       }
@@ -2662,9 +2676,11 @@ static uint get_tmp_table_rec_length(Ref_ptr_array p_items, uint elements,
 */
 
 TMPTABLE_COSTS
-get_tmp_table_costs(THD *thd, double row_count, uint row_size, bool blobs_used)
+get_tmp_table_costs(THD *thd, double row_count, uint row_size, bool blobs_used,
+                    bool add_copy_cost)
 {
   TMPTABLE_COSTS cost;
+  double row_copy_cost= add_copy_cost ? ROW_COPY_COST_THD(thd) : 0;
 
   /* From heap_prepare_hp_create_info(), assuming one hash key used */
   row_size+= sizeof(char*)*2;
@@ -2674,16 +2690,23 @@ get_tmp_table_costs(THD *thd, double row_count, uint row_size, bool blobs_used)
       blobs_used)
   {
     /* Disk based table */
-    cost.lookup= (DISK_TEMPTABLE_LOOKUP_COST *
-                  cache_hit_cost(thd->variables.optimizer_cache_hit_ratio));
-    cost.write=   cost.lookup;
-    cost.create=  DISK_TEMPTABLE_CREATE_COST;
+    cost.lookup=          ((DISK_TEMPTABLE_LOOKUP_COST *
+                            thd->optimizer_cache_hit_ratio)) + row_copy_cost;
+    cost.write=           cost.lookup + row_copy_cost;
+    cost.create=          DISK_TEMPTABLE_CREATE_COST;
+    cost.block_size=      DISK_TEMPTABLE_BLOCK_SIZE;
+    cost.avg_io_cost=     1.0;
+    cost.cache_hit_ratio= thd->optimizer_cache_hit_ratio;
   }
   else
   {
-    cost.lookup=  HEAP_TEMPTABLE_LOOKUP_COST;
-    cost.write=   cost.lookup;
-    cost.create=  HEAP_TEMPTABLE_CREATE_COST;
+    /* Values are as they are in heap.h */
+    cost.lookup=          HEAP_TEMPTABLE_LOOKUP_COST + row_copy_cost;
+    cost.write=           cost.lookup + row_copy_cost;
+    cost.create=          HEAP_TEMPTABLE_CREATE_COST;
+    cost.block_size=      0;
+    cost.avg_io_cost=     HEAP_TEMPTABLE_LOOKUP_COST;
+    cost.cache_hit_ratio= 1.0;
   }
   return cost;
 }
@@ -3623,11 +3646,15 @@ bool Duplicate_weedout_picker::check_qep(JOIN *join,
       records, and we will make 
       - sj_outer_fanout table writes
       - sj_inner_fanout*sj_outer_fanout lookups.
+
+      There is no row copy cost (as we are only copying rowid) and no
+      compare cost (as we are only checking if the row exists by
+      checking if we got a write error.
     */
     TMPTABLE_COSTS one_cost= get_tmp_table_costs(join->thd,
                                                  sj_outer_fanout,
                                                  temptable_rec_size,
-                                                 0);
+                                                 0, 0);
     double write_cost=
       COST_ADD(one_cost.create,
                COST_MULT(join->positions[first_tab].prefix_record_count,
@@ -6696,7 +6723,7 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
                                           &blobs_used);
     /* The cost of using the temp table */
     TMPTABLE_COSTS cost= get_tmp_table_costs(thd, inner_record_count_1,
-                                             rowlen, blobs_used);
+                                             rowlen, blobs_used, 1);
     /*
       The cost of executing the subquery and storing its result in an indexed
       temporary table.
@@ -6704,7 +6731,8 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
     double materialization_cost=
       COST_ADD(cost.create,
                COST_ADD(inner_read_time_1,
-                        COST_MULT(cost.write, inner_record_count_1)));
+                        COST_MULT((cost.write + WHERE_COMPARE_COST_THD(thd)),
+                                  inner_record_count_1)));
 
     materialize_strategy_cost=
       COST_ADD(materialization_cost,
@@ -6727,6 +6755,7 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
     }
     if (unlikely(thd->trace_started()))
     {
+      Json_writer_object trace_wrapper(thd);
       Json_writer_object trace_subquery(thd, "subquery_plan");
       trace_subquery.
         add("records", inner_record_count_1).
@@ -6768,6 +6797,7 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
 
     if (unlikely(thd->trace_started()))
     {
+      Json_writer_object trace_wrapper(thd);
       Json_writer_object trace_subquery(thd, "subquery_plan_revert");
       trace_subquery.add("choosen", "in_to_exists");
     }
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 23a1566e573..1c0750e4191 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1235,8 +1235,8 @@ void THD::init()
     avoid temporary tables replication failure.
   */
   variables.pseudo_thread_id= thread_id;
-
   variables.default_master_connection.str= default_master_connection_buff;
+  optimizer_cache_hit_ratio= cache_hit_ratio(variables.optimizer_cache_hit_ratio);
   ::strmake(default_master_connection_buff,
             global_system_variables.default_master_connection.str,
             variables.default_master_connection.length);
diff --git a/sql/sql_class.h b/sql/sql_class.h
index ac77a83c729..03c7fb6a847 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -2672,6 +2672,8 @@ public:
   struct  system_status_var org_status_var; // For user statistics
   struct  system_status_var *initial_status_var; /* used by show status */
   THR_LOCK_INFO lock_info;              // Locking info of this thread
+  double optimizer_cache_hit_ratio;     // From optimizer_cache_hit_ratio
+
   /**
     Protects THD data accessed from other threads:
     - thd->query and thd->query_length (used by SHOW ENGINE
diff --git a/sql/sql_const.h b/sql/sql_const.h
index f34f8ab69fd..4327e12394c 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -122,6 +122,12 @@
 #define READ_RECORD_BUFFER	(uint) (IO_SIZE*8) /* Pointer_buffer_size */
 #define DISK_BUFFER_SIZE	(uint) (IO_SIZE*16) /* Size of diskbuffer */
 
+/*
+  When reading big blocks, assume that each block of this size is
+  is of simlar cost as key lookup (1)
+*/
+#define DISK_FAST_READ_SIZE     ((uint) (IO_SIZE*16))
+
 #define FRM_VER_TRUE_VARCHAR (FRM_VER+4) /* 10 */
 #define FRM_VER_EXPRESSSIONS (FRM_VER+5) /* 11 */
 #define FRM_VER_CURRENT  FRM_VER_EXPRESSSIONS
@@ -218,7 +224,7 @@
 
 /* Convert ratio to cost */
 
-static inline double cache_hit_cost(uint ratio)
+static inline double cache_hit_ratio(uint ratio)
 {
   return (((double) (100 - ratio)) / 100.0);
 }
@@ -353,6 +359,8 @@ static inline double cache_hit_cost(uint ratio)
 #define HEAP_TEMPTABLE_CREATE_COST 1.0
 #define DISK_TEMPTABLE_LOOKUP_COST 1.0
 #define DISK_TEMPTABLE_CREATE_COST 4.0 /* Creating and deleting 2 temp tables */
+#define DISK_TEMPTABLE_BLOCK_SIZE  8192
+
 #define SORT_INDEX_CMP_COST 0.02
 
 #define COST_MAX (DBL_MAX * (1.0 - DBL_EPSILON))
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 3ee58ad8d14..0fae9589335 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -2468,10 +2468,13 @@ struct TMPTABLE_COSTS
   double create;
   double lookup;
   double write;
+  double avg_io_cost;
+  double cache_hit_ratio;
+  double block_size;
 };
 
 TMPTABLE_COSTS get_tmp_table_costs(THD *thd, double row_count, uint row_size,
-                                   bool blobs_used);
+                                   bool blobs_used, bool add_row_copy_cost);
 
 struct st_cond_statistic
 {
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 870953e58fa..97c2d1c5796 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -6822,6 +6822,15 @@ static Sys_var_ulong Sys_optimizer_max_sel_arg_weight(
   cost of finding the key, on cached pages, that we have to take into account.
 */
 
+static bool update_optimizer_cache_hit_ratio(sys_var *self, THD *thd,
+                                            enum_var_type type)
+{
+  if (type == OPT_SESSION)
+    thd->optimizer_cache_hit_ratio=
+      cache_hit_ratio(thd->variables.optimizer_cache_hit_ratio);
+  return 0;
+}
+
 static Sys_var_uint Sys_optimizer_cache_hit_ratio(
   "optimizer_cache_hit_ratio",
   "Expected hit rate of the row and index cache in storage engines. "
@@ -6829,7 +6838,7 @@ static Sys_var_uint Sys_optimizer_cache_hit_ratio(
   "empty and 99 means that value is almost always in the cache",
   SESSION_VAR(optimizer_cache_hit_ratio), CMD_LINE(REQUIRED_ARG),
   VALID_RANGE(0, 99), DEFAULT(DEFAULT_CACHE_HIT_RATIO), 1, NO_MUTEX_GUARD,
-  NOT_IN_BINLOG);
+  NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(update_optimizer_cache_hit_ratio));
 
 static Sys_var_double Sys_optimizer_key_copy_cost(
   "optimizer_key_copy_cost",