Merge remote-tracking branch '5.5' into 10.0

20 files changed, 623 insertions, 45 deletions

diff --git a/client/mysqltest.cc b/client/mysqltest.cc
index f9715bfbfdb..2ed2a10e975 100644
--- a/client/mysqltest.cc
+++ b/client/mysqltest.cc
@@ -1721,12 +1721,23 @@ void log_msg(const char *fmt, ...)
 int cat_file(DYNAMIC_STRING* ds, const char* filename)
 {
   int fd;
-  int len;
-  char buff[16384];
+  size_t len;
+  char *buff;
 
   if ((fd= my_open(filename, O_RDONLY, MYF(0))) < 0)
     return 1;
-  while((len= (int)my_read(fd, (uchar*)&buff, sizeof(buff)-1, MYF(0))) > 0)
+
+  len= (size_t) my_seek(fd, 0, SEEK_END, MYF(0));
+  my_seek(fd, 0, SEEK_SET, MYF(0));
+  if (len == (size_t)MY_FILEPOS_ERROR ||
+      !(buff= (char*)my_malloc(len + 1, MYF(0))))
+  {
+    my_close(fd, MYF(0));
+    return 1;
+  }
+  len= my_read(fd, (uchar*)buff, len, MYF(0));
+  my_close(fd, MYF(0));
+
   {
     char *p= buff, *start= buff,*end=buff+len;
     while (p < end)
@@ -1749,7 +1760,7 @@ int cat_file(DYNAMIC_STRING* ds, const char* filename)
     *p= 0;
     replace_dynstr_append_mem(ds, start, p-start);
   }
-  my_close(fd, MYF(0));
+  my_free(buff);
   return 0;
 }
 
diff --git a/cmake/cpack_rpm.cmake b/cmake/cpack_rpm.cmake
index d684761187f..e5206d9690c 100644
--- a/cmake/cpack_rpm.cmake
+++ b/cmake/cpack_rpm.cmake
@@ -29,7 +29,12 @@ SET(CPACK_COMPONENTS_ALL Server ManPagesServer IniFiles Server_Scripts
                          Common Client SharedLibraries ClientPlugins)
 
 SET(CPACK_RPM_PACKAGE_NAME "MariaDB")
-SET(CPACK_PACKAGE_FILE_NAME "${CPACK_RPM_PACKAGE_NAME}-${VERSION}-${RPM}-${CMAKE_SYSTEM_PROCESSOR}")
+IF(CMAKE_VERSION VERSION_LESS "3.6.0")
+  SET(CPACK_PACKAGE_FILE_NAME "${CPACK_RPM_PACKAGE_NAME}-${VERSION}-${RPM}-${CMAKE_SYSTEM_PROCESSOR}")
+ELSE()
+  SET(CPACK_RPM_FILE_NAME "RPM-DEFAULT")
+  SET(CPACK_RPM_DEBUGINFO_PACKAGE ON)
+ENDIF()
 
 SET(CPACK_RPM_PACKAGE_RELEASE "1%{?dist}")
 SET(CPACK_RPM_PACKAGE_LICENSE "GPLv2")
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 09223d36429..db51bbb8186 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2274,6 +2274,94 @@ pk	f1	sq
 5	3	5
 set optimizer_switch= @save_optimizer_switch;
 DROP TABLE t1,t2;
+#
+# mdev-12838: scan of materialized of semi-join subquery in join
+#
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (
+dispatch_group varchar(32),
+assignment_group varchar(32),
+sys_id char(32),
+PRIMARY KEY (sys_id),
+KEY idx1 (dispatch_group),
+KEY idx2 (assignment_group)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+ugroup varchar(32),
+user varchar(32),
+sys_id char(32),
+PRIMARY KEY (sys_id),
+KEY idx3 (ugroup),
+KEY idx4 (user)
+) ENGINE=MyISAM;
+CREATE TABLE t3 (
+type mediumtext,
+sys_id char(32),
+PRIMARY KEY (sys_id)
+) ENGINE=MyISAM;
+set optimizer_switch='materialization=off';
+explain SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND 
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ref	idx3,idx4	idx4	35	const	2	Using index condition; Using where; Start temporary
+1	PRIMARY	t3_i	eq_ref	PRIMARY	PRIMARY	32	test.t2.ugroup	1	Using index condition; Using where
+1	PRIMARY	t1	ref	idx1,idx2	idx1	35	test.t3_i.sys_id	2	Using index condition; Using where; End temporary
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	32	test.t1.assignment_group	1	Using where; Using index
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND 
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+assignment_group
+df50316637232000158bbfc8bcbe5d23
+e08fad2637232000158bbfc8bcbe5d39
+ec70316637232000158bbfc8bcbe5d60
+7b10fd2637232000158bbfc8bcbe5d30
+ebb4620037332000158bbfc8bcbe5d89
+set optimizer_switch='materialization=on';
+explain SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND 
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+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	t1	ref	idx1,idx2	idx1	35	test.t2.ugroup	2	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	32	test.t1.assignment_group	1	Using where; Using index
+2	MATERIALIZED	t2	ref	idx3,idx4	idx4	35	const	2	Using index condition; Using where
+2	MATERIALIZED	t3_i	eq_ref	PRIMARY	PRIMARY	32	test.t2.ugroup	1	Using index condition; Using where
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND 
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+assignment_group
+df50316637232000158bbfc8bcbe5d23
+e08fad2637232000158bbfc8bcbe5d39
+ec70316637232000158bbfc8bcbe5d60
+7b10fd2637232000158bbfc8bcbe5d30
+ebb4620037332000158bbfc8bcbe5d89
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@save_optimizer_switch;
 # End of 5.5 tests
 #
 # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index dba4d049da3..57b0526c6a3 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -485,3 +485,20 @@ FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3
 ON (f3 = t2b.f2) );
 f1
 DROP TABLE t1,t2,t3;
+#
+# MDEV-12963: min/max optimization optimizing away all tables employed
+#             for uncorrelated IN subquery used in a disjunct of WHERE
+#
+create table t1 (a int, index idx(a)) engine=myisam;
+insert into t1 values (4),(7),(1),(3),(9);
+select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5;
+a
+3
+7
+9
+explain
+select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	idx	idx	5	NULL	5	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+drop table t1;
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index c00ce7ec227..5d0c64e9ddb 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1612,3 +1612,25 @@ Warnings:
 Note	1003	select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where ((rand() < 0))
 drop table t1,t2;
 set optimizer_switch=@save_optimizer_switch;
+#
+# mdev-12855: materialization of a semi-join subquery + ORDER BY 
+#
+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');
+SELECT f1 FROM t1 
+WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' )
+HAVING f1 != 'foo'
+ORDER BY f1;
+f1
+qux
+explain SELECT f1 FROM t1 
+WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' )
+HAVING f1 != 'foo'
+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 index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	11	func	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 3f91b510c5b..4feab0c78b0 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2314,6 +2314,94 @@ pk	f1	sq
 5	3	5
 set optimizer_switch= @save_optimizer_switch;
 DROP TABLE t1,t2;
+#
+# mdev-12838: scan of materialized of semi-join subquery in join
+#
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (
+dispatch_group varchar(32),
+assignment_group varchar(32),
+sys_id char(32),
+PRIMARY KEY (sys_id),
+KEY idx1 (dispatch_group),
+KEY idx2 (assignment_group)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+ugroup varchar(32),
+user varchar(32),
+sys_id char(32),
+PRIMARY KEY (sys_id),
+KEY idx3 (ugroup),
+KEY idx4 (user)
+) ENGINE=MyISAM;
+CREATE TABLE t3 (
+type mediumtext,
+sys_id char(32),
+PRIMARY KEY (sys_id)
+) ENGINE=MyISAM;
+set optimizer_switch='materialization=off';
+explain SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND 
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ref	idx3,idx4	idx4	35	const	2	Using index condition; Using where; Start temporary
+1	PRIMARY	t3_i	eq_ref	PRIMARY	PRIMARY	32	test.t2.ugroup	1	Using index condition; Using where
+1	PRIMARY	t1	ref	idx1,idx2	idx1	35	test.t3_i.sys_id	2	Using index condition; Using where; End temporary
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	32	test.t1.assignment_group	1	Using where; Using index
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND 
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+assignment_group
+df50316637232000158bbfc8bcbe5d23
+e08fad2637232000158bbfc8bcbe5d39
+ec70316637232000158bbfc8bcbe5d60
+7b10fd2637232000158bbfc8bcbe5d30
+ebb4620037332000158bbfc8bcbe5d89
+set optimizer_switch='materialization=on';
+explain SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND 
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+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	t1	ref	idx1,idx2	idx1	35	test.t2.ugroup	2	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	32	test.t1.assignment_group	1	Using where; Using index
+2	MATERIALIZED	t2	ref	idx3,idx4	idx4	35	const	2	Using index condition; Using where
+2	MATERIALIZED	t3_i	eq_ref	PRIMARY	PRIMARY	32	test.t2.ugroup	1	Using index condition; Using where
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+t1.dispatch_group IN
+(SELECT t2.ugroup
+FROM t2, t3 t3_i
+WHERE t2.ugroup = t3_i.sys_id AND 
+t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+assignment_group
+df50316637232000158bbfc8bcbe5d23
+e08fad2637232000158bbfc8bcbe5d39
+ec70316637232000158bbfc8bcbe5d60
+7b10fd2637232000158bbfc8bcbe5d30
+ebb4620037332000158bbfc8bcbe5d89
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@save_optimizer_switch;
 # End of 5.5 tests
 #
 # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT
diff --git a/mysql-test/suite/parts/r/quoting.result b/mysql-test/suite/parts/r/quoting.result
new file mode 100644
index 00000000000..ba6a155e6ac
--- /dev/null
+++ b/mysql-test/suite/parts/r/quoting.result
@@ -0,0 +1,6 @@
+set sql_mode=ansi_quotes;
+create table t1 (i int) partition by range (i) (partition flush values less than maxvalue);
+set sql_mode=default;
+lock tables t1 read local;
+unlock tables;
+drop table t1;
diff --git a/mysql-test/suite/parts/t/quoting.test b/mysql-test/suite/parts/t/quoting.test
new file mode 100644
index 00000000000..297896fd9cf
--- /dev/null
+++ b/mysql-test/suite/parts/t/quoting.test
@@ -0,0 +1,10 @@
+#
+# MDEV-13017 LOCK TABLE fails with irrelevant error while working with tables affected by ANSI_QUOTES
+#
+--source include/have_partition.inc
+set sql_mode=ansi_quotes;
+create table t1 (i int) partition by range (i) (partition flush values less than maxvalue);
+set sql_mode=default;
+lock tables t1 read local;
+unlock tables;
+drop table t1;
diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test
index f92b7c5b148..5ab736cee15 100644
--- a/mysql-test/t/bootstrap.test
+++ b/mysql-test/t/bootstrap.test
@@ -59,6 +59,12 @@ drop table t1;
 SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb'
  and SUPPORT='YES';
 
+#
+# MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init
+#
+--error 1
+--exec $MYSQLD_BOOTSTRAP_CMD --myisam_recover_options=NONE
+
 --echo End of 5.5 tests
 
 --source include/not_windows_embedded.inc
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index 9e3ac603ec6..35f2b9588fe 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -507,3 +507,19 @@ SELECT * FROM t1
                   ON (f3 = t2b.f2) );
 
 DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-12963: min/max optimization optimizing away all tables employed
+--echo #             for uncorrelated IN subquery used in a disjunct of WHERE
+--echo #
+
+create table t1 (a int, index idx(a)) engine=myisam;
+insert into t1 values (4),(7),(1),(3),(9);
+
+select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5;
+explain
+select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5;
+
+drop table t1;
+
+
diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test
index 0f2892ae2dc..cfb6c8c2819 100644
--- a/mysql-test/t/subselect_sj2_mat.test
+++ b/mysql-test/t/subselect_sj2_mat.test
@@ -283,3 +283,23 @@ select * from t1 where (rand() < 0) and i in (select i from t2);
 
 drop table t1,t2;
 set optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # mdev-12855: materialization of a semi-join subquery + ORDER BY 
+--echo #
+
+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');
+
+let $q=
+SELECT f1 FROM t1 
+WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' )
+HAVING f1 != 'foo'
+ORDER BY f1;
+
+eval $q;
+eval explain $q;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 7b6c5818a04..c626d88e6ff 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1950,6 +1950,213 @@ set optimizer_switch= @save_optimizer_switch;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # mdev-12838: scan of materialized of semi-join subquery in join
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+CREATE TABLE t1 (
+  dispatch_group varchar(32),
+  assignment_group varchar(32),
+  sys_id char(32),
+  PRIMARY KEY (sys_id),
+  KEY idx1 (dispatch_group),
+  KEY idx2 (assignment_group)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2 (
+  ugroup varchar(32),
+  user varchar(32),
+  sys_id char(32),
+  PRIMARY KEY (sys_id),
+  KEY idx3 (ugroup),
+  KEY idx4 (user)
+) ENGINE=MyISAM;
+
+CREATE TABLE t3 (
+  type mediumtext,
+  sys_id char(32),
+  PRIMARY KEY (sys_id)
+) ENGINE=MyISAM;
+
+--disable_query_log
+
+INSERT INTO t1 VALUES 
+('e5d9f63237232000158bbfc8bcbe5dbf','f304ae0037332000158bbfc8bcbe5d4f',
+'5398c0e037003000158bbfc8bcbe5dbb'),
+('69d9f63237232000158bbfc8bcbe5dcb','7172ea0037332000158bbfc8bcbe5db6',
+'5c188ca037003000158bbfc8bcbe5dbc'),
+('577ed708d773020058c92cf65e61037a','699708d4d773020058c92cf65e61037c',
+'623a8cd4d773020058c92cf65e6103ea'),
+('96fb652637232000158bbfc8bcbe5db4','df50316637232000158bbfc8bcbe5d23',
+'6835bd6637232000158bbfc8bcbe5d21'),
+('e1d9f63237232000158bbfc8bcbe5db8','96346e0037332000158bbfc8bcbe5daa',
+'697880e037003000158bbfc8bcbe5dcd'),
+('25d9f63237232000158bbfc8bcbe5dbe','f304ae0037332000158bbfc8bcbe5d4f',
+'6a9804e037003000158bbfc8bcbe5d09'),
+('96fb652637232000158bbfc8bcbe5db4','e08fad2637232000158bbfc8bcbe5d39',
+'6d25f96637232000158bbfc8bcbe5d79'),
+('e9d9f63237232000158bbfc8bcbe5dc6','7172ea0037332000158bbfc8bcbe5db6',
+'702880e037003000158bbfc8bcbe5d94'),
+('a5d9f63237232000158bbfc8bcbe5dca','f304ae0037332000158bbfc8bcbe5d4f',
+'7188c0e037003000158bbfc8bcbe5d75'),
+('65d9f63237232000158bbfc8bcbe5dc4','f304ae0037332000158bbfc8bcbe5d4f',
+'778880e037003000158bbfc8bcbe5d9e'),
+('a1d9f63237232000158bbfc8bcbe5dc3','7172ea0037332000158bbfc8bcbe5db6',
+'7d0840e037003000158bbfc8bcbe5dde'),
+('21d9f63237232000158bbfc8bcbe5db7','96346e0037332000158bbfc8bcbe5daa',
+'7f6880e037003000158bbfc8bcbe5da7'),
+('96fb652637232000158bbfc8bcbe5db4','ec70316637232000158bbfc8bcbe5d60',
+'8025f96637232000158bbfc8bcbe5dd0'),
+('3dd9f63237232000158bbfc8bcbe5dcc','7172ea0037332000158bbfc8bcbe5db6',
+'823880e037003000158bbfc8bcbe5ded'),
+('96fb652637232000158bbfc8bcbe5db4','7b10fd2637232000158bbfc8bcbe5d30',
+'9a353d6637232000158bbfc8bcbe5dee'),
+('75d9f63237232000158bbfc8bcbe5dd0','ebb4620037332000158bbfc8bcbe5d89',
+'a558c0e037003000158bbfc8bcbe5d36'),
+('6dd9f63237232000158bbfc8bcbe5db5','96346e0037332000158bbfc8bcbe5daa',
+'bc78cca037003000158bbfc8bcbe5d74'),
+('add9f63237232000158bbfc8bcbe5dc7','7172ea0037332000158bbfc8bcbe5db6',
+'c53804a037003000158bbfc8bcbe5db8'),
+('fdd9f63237232000158bbfc8bcbe5dcd','7864ae0037332000158bbfc8bcbe5db8',
+'cfe740e037003000158bbfc8bcbe5de8'),
+('96fb652637232000158bbfc8bcbe5db4','3120fd2637232000158bbfc8bcbe5d42',
+'e2257d6637232000158bbfc8bcbe5ded'),
+('3c3725e237232000158bbfc8bcbe5da1','96346e0037332000158bbfc8bcbe5daa',
+'ee78c0e037003000158bbfc8bcbe5db5'),
+('a9d9f63237232000158bbfc8bcbe5dc0','7172ea0037332000158bbfc8bcbe5db6',
+'f00888a037003000158bbfc8bcbe5dd3'),
+('29d9f63237232000158bbfc8bcbe5db9','7172ea0037332000158bbfc8bcbe5db6',
+'fa0880e037003000158bbfc8bcbe5d70'),
+('b1d9f63237232000158bbfc8bcbe5dcf','ebb4620037332000158bbfc8bcbe5d89',
+'fa48c0e037003000158bbfc8bcbe5d28');
+
+INSERT INTO t2 VALUES
+('17801ac21b13200050fdfbcd2c0713e8','8e826bf03710200044e0bfc8bcbe5d86',
+'14c19a061b13200050fdfbcd2c07134b'),
+('577ed708d773020058c92cf65e61037a','931644d4d773020058c92cf65e61034c',
+'339888d4d773020058c92cf65e6103aa'),
+('df50316637232000158bbfc8bcbe5d23','92826bf03710200044e0bfc8bcbe5da9',
+'3682f56637232000158bbfc8bcbe5d44'),
+('b4f342b237232000158bbfc8bcbe5def','86826bf03710200044e0bfc8bcbe5d70',
+'38e4c2b237232000158bbfc8bcbe5dea'),
+('7b10fd2637232000158bbfc8bcbe5d30','8a826bf03710200044e0bfc8bcbe5d72',
+'4442b56637232000158bbfc8bcbe5d43'),
+('3120fd2637232000158bbfc8bcbe5d42','82826bf03710200044e0bfc8bcbe5d89',
+'49d2396637232000158bbfc8bcbe5d12'),
+('96fb652637232000158bbfc8bcbe5db4','86826bf03710200044e0bfc8bcbe5d79',
+'4e3ca52637232000158bbfc8bcbe5d3e'),
+('17801ac21b13200050fdfbcd2c0713e8','824fd523bf4320007a6d257b3f073963',
+'58c19a061b13200050fdfbcd2c07134e'),
+('699708d4d773020058c92cf65e61037c','901784d4d773020058c92cf65e6103da',
+'5bc708d4d773020058c92cf65e6103d5'),
+('75d9f63237232000158bbfc8bcbe5dd0','86826bf03710200044e0bfc8bcbe5d79',
+'6b52cb7237232000158bbfc8bcbe5ded'),
+('f253da061b13200050fdfbcd2c0713ab','8e826bf03710200044e0bfc8bcbe5d86',
+'81045e061b13200050fdfbcd2c071373'),
+('7b10fd2637232000158bbfc8bcbe5d30','8e826bf03710200044e0bfc8bcbe5d74',
+'8c42b56637232000158bbfc8bcbe5d3f'),
+('e5d9f63237232000158bbfc8bcbe5dbf','7a826bf03710200044e0bfc8bcbe5df5',
+'a7acfe3237232000158bbfc8bcbe5d78'),
+('8a5055c9c61122780043563ef53438e3','9ee1b13dc6112271007f9d0efdb69cd0',
+'a9aff553c6112276015a8006174bee21'),
+('8a4dde73c6112278017a6a4baf547aa7','9ee1b13dc6112271007f9d0efdb69cd0',
+'a9b2f526c61122760003ae07349d294f'),
+('aaccc971c0a8001500fe1ff4302de101','9ee1b13dc6112271007f9d0efdb69cd0',
+'aacceed3c0a80015009069bba51c4e21'),
+('65d9f63237232000158bbfc8bcbe5dc4','8d56406a0a0a0a6b004070b354aada28',
+'ac1bfa3237232000158bbfc8bcbe5dc3'),
+('b85d44954a3623120004689b2d5dd60a','97000fcc0a0a0a6e0104ca999f619e5b',
+'b77bc032cbb00200d71cb9c0c24c9c45'),
+('220f8e71c61122840197e57c33464f70','8d56406a0a0a0a6b004070b354aada28',
+'b9b74f080a0a0b343ba75b95bdb27056'),
+('e08fad2637232000158bbfc8bcbe5d39','82826bf03710200044e0bfc8bcbe5d80',
+'be02756637232000158bbfc8bcbe5d8b'),
+('ebb4620037332000158bbfc8bcbe5d89','7682abf03710200044e0bfc8bcbe5d25',
+'c0122f4437732000158bbfc8bcbe5d7d'),
+('96fb652637232000158bbfc8bcbe5db4','7a82abf03710200044e0bfc8bcbe5d27',
+'c23ca52637232000158bbfc8bcbe5d3b'),
+('22122b37c611228400f9ff91c857581d','9ee1b13dc6112271007f9d0efdb69cd0',
+'d23bbf5dac14641866947512bde59dc5'),
+('db53a9290a0a0a650091abebccf833c6','9ee1b13dc6112271007f9d0efdb69cd0',
+'db54a0f60a0a0a65002c54dcb72b4f41'),
+('e08fad2637232000158bbfc8bcbe5d39','8e826bf03710200044e0bfc8bcbe5d86',
+'f602756637232000158bbfc8bcbe5d88'),
+('699708d4d773020058c92cf65e61037c','8d59d601d7b3020058c92cf65e6103c2',
+'f718a241d7b3020058c92cf65e610332'),
+('df50316637232000158bbfc8bcbe5d23','9e826bf03710200044e0bfc8bcbe5da6',
+'fe82f56637232000158bbfc8bcbe5d4e'),
+('f972d6061b13200050fdfbcd2c0713e5','780395f0df031100a9e78b6c3df2631f',
+'ff4395f0df031100a9e78b6c3df2637e');
+
+INSERT INTO t3 VALUES 
+('87245e061b13200050fdfbcd2c0713cc','7172ea0037332000158bbfc8bcbe5db6'),
+('74af88c6c611227d0066386e74dc853d','74ad1ff3c611227d01d25feac2af603f'),
+('59e22fb137032000158bbfc8bcbe5d52','75d9f63237232000158bbfc8bcbe5dd0'),
+('98906fb137032000158bbfc8bcbe5d65','781da52637232000158bbfc8bcbe5db8'),
+('87245e061b13200050fdfbcd2c0713cc','7864ae0037332000158bbfc8bcbe5db8'),
+('87245e061b13200050fdfbcd2c0713cc','7b10fd2637232000158bbfc8bcbe5d30'),
+('59e22fb137032000158bbfc8bcbe5d52','81a880e037003000158bbfc8bcbe5df8'),
+('74af88c6c611227d0066386e74dc853d','8a4cb6d4c61122780043b1642efcd52b'),
+('1cb8ab9bff500200158bffffffffff62','8a4dde73c6112278017a6a4baf547aa7'),
+('1cb8ab9bff500200158bffffffffff62','8a5055c9c61122780043563ef53438e3'),
+('87245e061b13200050fdfbcd2c0713cc','96346e0037332000158bbfc8bcbe5daa'),
+('59e22fb137032000158bbfc8bcbe5d52','96fb652637232000158bbfc8bcbe5db4'),
+('59e22fb137032000158bbfc8bcbe5d52','a1d9f63237232000158bbfc8bcbe5dc3'),
+('59e22fb137032000158bbfc8bcbe5d52','a5d9f63237232000158bbfc8bcbe5dca'),
+('1cb8ab9bff500200158bffffffffff62','a715cd759f2002002920bde8132e7018'),
+('59e22fb137032000158bbfc8bcbe5d52','a9d9f63237232000158bbfc8bcbe5dc0'),
+('74af88c6c611227d0066386e74dc853d','aacb62e2c0a80015007f67f752c2b12c'),
+('74af88c6c611227d0066386e74dc853d','aaccc971c0a8001500fe1ff4302de101'),
+('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dbb'),
+('59e22fb137032000158bbfc8bcbe5d52','add9f63237232000158bbfc8bcbe5dc7'),
+('59e22fb137032000158bbfc8bcbe5d52','b1d9f63237232000158bbfc8bcbe5dcf'),
+('1cb8ab9bff500200158bffffffffff62','b85d44954a3623120004689b2d5dd60a'),
+('1cb8ab9bff500200158bffffffffff62','b97e89b94a36231201676b73322a0311'),
+('1cb8ab9bff500200158bffffffffff62','cfcbad03d711110050f5edcb9e61038f'),
+('1cb8ab9bff500200158bffffffffff62','d625dccec0a8016700a222a0f7900d06'),
+('1cb8ab9bff500200158bffffffffff62','db53580b0a0a0a6501aa37c294a2ba6b'),
+('1cb8ab9bff500200158bffffffffff62','db53a9290a0a0a650091abebccf833c6'),
+('1cb8ab9bff500200158bffffffffff62','dc0db135c332010016194ffe5bba8f23'),
+('87245e061b13200050fdfbcd2c0713cc','df50316637232000158bbfc8bcbe5d23'),
+('87245e061b13200050fdfbcd2c0713cc','e08fad2637232000158bbfc8bcbe5d39'),
+('59e22fb137032000158bbfc8bcbe5d52','e1d9f63237232000158bbfc8bcbe5db8'),
+('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5db4'),
+('59e22fb137032000158bbfc8bcbe5d52','e5d9f63237232000158bbfc8bcbe5dbf'),
+('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dba'),
+('59e22fb137032000158bbfc8bcbe5d52','e9d9f63237232000158bbfc8bcbe5dc6'),
+('87245e061b13200050fdfbcd2c0713cc','ebb4620037332000158bbfc8bcbe5d89'),
+('87245e061b13200050fdfbcd2c0713cc','ec70316637232000158bbfc8bcbe5d60'),
+('87245e061b13200050fdfbcd2c0713cc','f253da061b13200050fdfbcd2c0713ab'),
+('87245e061b13200050fdfbcd2c0713cc','f304ae0037332000158bbfc8bcbe5d4f'),
+('98906fb137032000158bbfc8bcbe5d65','f972d6061b13200050fdfbcd2c0713e5'),
+('59e22fb137032000158bbfc8bcbe5d52','fdd9f63237232000158bbfc8bcbe5dcd');
+
+--enable_query_log
+
+let $q=
+SELECT t1.assignment_group
+FROM t1, t3
+WHERE t1.assignment_group = t3.sys_id AND
+      t1.dispatch_group IN
+      (SELECT t2.ugroup
+       FROM t2, t3 t3_i
+       WHERE t2.ugroup = t3_i.sys_id AND 
+             t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
+             t2.user = '86826bf03710200044e0bfc8bcbe5d79');
+
+set optimizer_switch='materialization=off';
+eval explain $q;
+eval $q;
+
+set optimizer_switch='materialization=on';
+eval explain $q;
+eval $q;
+
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@save_optimizer_switch;
+
 --echo # End of 5.5 tests
 --echo #
 --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT
diff --git a/mysys/my_symlink.c b/mysys/my_symlink.c
index 72648d4c9a8..06f6a29e4a0 100644
--- a/mysys/my_symlink.c
+++ b/mysys/my_symlink.c
@@ -205,7 +205,7 @@ int my_realpath(char *to, const char *filename, myf MyFlags)
 
 const char *my_open_parent_dir_nosymlinks(const char *pathname, int *pdfd)
 {
-  char buf[PATH_MAX+1];
+  char buf[FN_REFLEN + 1];
   char *s= buf, *e= buf+1, *end= strnmov(buf, pathname, sizeof(buf));
   int fd, dfd= -1;
 
diff --git a/mysys/mysys_priv.h b/mysys/mysys_priv.h
index 51650567609..b33e155a718 100644
--- a/mysys/mysys_priv.h
+++ b/mysys/mysys_priv.h
@@ -110,12 +110,21 @@ const char *my_open_parent_dir_nosymlinks(const char *pathname, int *pdfd);
   res= AT;                                                              \
   if (dfd >= 0) close(dfd);                                             \
   return res;
-#elif defined(HAVE_REALPATH)
+#elif defined(HAVE_REALPATH) && defined(PATH_MAX)
 #define NOSYMLINK_FUNCTION_BODY(AT,NOAT)                                \
   char buf[PATH_MAX+1];                                                 \
   if (realpath(pathname, buf) == NULL) return -1;                       \
   if (strcmp(pathname, buf)) { errno= ENOTDIR; return -1; }             \
   return NOAT;
+#elif defined(HAVE_REALPATH)
+#define NOSYMLINK_FUNCTION_BODY(AT,NOAT)                                \
+  char *buf= realpath(pathname, NULL);                                  \
+  int res;                                                              \
+  if (buf == NULL) return -1;                                           \
+  if (strcmp(pathname, buf)) { errno= ENOTDIR; res= -1; }               \
+  else res= NOAT;                                                       \
+  free(buf);                                                            \
+  return res;
 #else
 #define NOSYMLINK_FUNCTION_BODY(AT,NOAT)                                \
   return NOAT;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 03204a627ed..cc63efa7d8c 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3433,6 +3433,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
   table_map remaining_tables= 0;
   table_map handled_tabs= 0;
   join->sjm_lookup_tables= 0;
+  join->sjm_scan_tables= 0;
   for (tablenr= table_count - 1 ; tablenr != join->const_tables - 1; tablenr--)
   {
     POSITION *pos= join->best_positions + tablenr;
@@ -3491,6 +3492,9 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
       for (i= tablenr; i != (first + sjm->tables - 1); i--)
         rem_tables |= join->best_positions[i].table->table->map;
 
+      for (i= first; i < first+ sjm->tables; i++)
+        join->sjm_scan_tables |= join->best_positions[i].table->table->map;
+
       POSITION dummy;
       join->cur_sj_inner_tables= 0;
       for (i= first + sjm->tables; i <= tablenr; i++)
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index be7824aae9e..5446db41a72 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -1870,6 +1870,22 @@ static int add_subpartition_by(File fptr)
   return err + add_partition_by(fptr);
 }
 
+static int add_name_string(File fptr, const char *name)
+{
+  int err;
+  String name_string("", 0, system_charset_info);
+  THD *thd= current_thd;
+  ulonglong save_sql_mode= thd->variables.sql_mode;
+  thd->variables.sql_mode&= ~MODE_ANSI_QUOTES;
+  ulonglong save_options= thd->variables.option_bits;
+  thd->variables.option_bits&= ~OPTION_QUOTE_SHOW_CREATE;
+  append_identifier(thd, &name_string, name, strlen(name));
+  thd->variables.sql_mode= save_sql_mode;
+  thd->variables.option_bits= save_options;
+  err= add_string_object(fptr, &name_string);
+  return err;
+}
+
 static int add_part_field_list(File fptr, List<char> field_list)
 {
   uint i, num_fields;
@@ -1881,15 +1897,7 @@ static int add_part_field_list(File fptr, List<char> field_list)
   err+= add_begin_parenthesis(fptr);
   while (i < num_fields)
   {
-    const char *field_str= part_it++;
-    String field_string("", 0, system_charset_info);
-    THD *thd= current_thd;
-    ulonglong save_options= thd->variables.option_bits;
-    thd->variables.option_bits&= ~OPTION_QUOTE_SHOW_CREATE;
-    append_identifier(thd, &field_string, field_str,
-                      strlen(field_str));
-    thd->variables.option_bits= save_options;
-    err+= add_string_object(fptr, &field_string);
+    err+= add_name_string(fptr, part_it++);
     if (i != (num_fields-1))
       err+= add_comma(fptr);
     i++;
@@ -1898,20 +1906,6 @@ static int add_part_field_list(File fptr, List<char> field_list)
   return err;
 }
 
-static int add_name_string(File fptr, const char *name)
-{
-  int err;
-  String name_string("", 0, system_charset_info);
-  THD *thd= current_thd;
-  ulonglong save_options= thd->variables.option_bits;
-  thd->variables.option_bits&= ~OPTION_QUOTE_SHOW_CREATE;
-  append_identifier(thd, &name_string, name,
-                    strlen(name));
-  thd->variables.option_bits= save_options;
-  err= add_string_object(fptr, &name_string);
-  return err;
-}
-
 static int add_int(File fptr, longlong number)
 {
   char buff[32];
diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc
index 63c3933db93..e34409b3532 100644
--- a/sql/sql_plugin.cc
+++ b/sql/sql_plugin.cc
@@ -306,6 +306,12 @@ static plugin_ref intern_plugin_lock(LEX *lex, plugin_ref plugin);
 static void intern_plugin_unlock(LEX *lex, plugin_ref plugin);
 static void reap_plugins(void);
 
+bool plugin_is_forced(struct st_plugin_int *p)
+{
+  return p->load_option == PLUGIN_FORCE ||
+         p->load_option == PLUGIN_FORCE_PLUS_PERMANENT;
+}
+
 static void report_error(int where_to, uint error, ...)
 {
   va_list args;
@@ -1360,7 +1366,7 @@ static int plugin_initialize(MEM_ROOT *tmp_root, struct st_plugin_int *plugin,
 
   if (options_only || state == PLUGIN_IS_DISABLED)
   {
-    ret= 0;
+    ret= !options_only && plugin_is_forced(plugin);
     goto err;
   }
 
@@ -1671,8 +1677,7 @@ int plugin_init(int *argc, char **argv, int flags)
   while ((plugin_ptr= *(--reap)))
   {
     mysql_mutex_unlock(&LOCK_plugin);
-    if (plugin_ptr->load_option == PLUGIN_FORCE ||
-        plugin_ptr->load_option == PLUGIN_FORCE_PLUS_PERMANENT)
+    if (plugin_is_forced(plugin_ptr))
       reaped_mandatory_plugin= TRUE;
     plugin_deinitialize(plugin_ptr, true);
     mysql_mutex_lock(&LOCK_plugin);
@@ -3536,8 +3541,7 @@ static int construct_options(MEM_ROOT *mem_root, struct st_plugin_int *tmp,
                                                   plugin_dash.length + 1);
   strxmov(plugin_name_with_prefix_ptr, plugin_dash.str, plugin_name_ptr, NullS);
 
-  if (tmp->load_option != PLUGIN_FORCE &&
-      tmp->load_option != PLUGIN_FORCE_PLUS_PERMANENT)
+  if (!plugin_is_forced(tmp))
   {
     /* support --skip-plugin-foo syntax */
     options[0].name= plugin_name_ptr;
@@ -3855,8 +3859,7 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp,
       We adjust the default value to account for the hardcoded exceptions
       we have set for the federated and ndbcluster storage engines.
     */
-    if (tmp->load_option != PLUGIN_FORCE &&
-        tmp->load_option != PLUGIN_FORCE_PLUS_PERMANENT)
+    if (!plugin_is_forced(tmp))
       opts[0].def_value= opts[1].def_value= plugin_load_option;
 
     error= handle_options(argc, &argv, opts, NULL);
@@ -3872,8 +3875,7 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp,
      Set plugin loading policy from option value. First element in the option
      list is always the <plugin name> option value.
     */
-    if (tmp->load_option != PLUGIN_FORCE &&
-        tmp->load_option != PLUGIN_FORCE_PLUS_PERMANENT)
+    if (!plugin_is_forced(tmp))
       plugin_load_option= (enum_plugin_load_option) *(ulong*) opts[0].value;
   }
 
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index 09a22ba0444..2c9a22d4bd9 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -121,7 +121,7 @@
 #define OPTION_AUTOCOMMIT       (1ULL << 8)    // THD, user
 #define OPTION_BIG_SELECTS      (1ULL << 9)     // THD, user
 #define OPTION_LOG_OFF          (1ULL << 10)    // THD, user
-#define OPTION_QUOTE_SHOW_CREATE (1ULL << 11)   // THD, user, unused
+#define OPTION_QUOTE_SHOW_CREATE (1ULL << 11)   // THD, user
 #define TMP_TABLE_ALL_COLUMNS   (1ULL << 12)    // SELECT, intern
 #define OPTION_WARNINGS         (1ULL << 13)    // THD, user
 #define OPTION_AUTO_IS_NULL     (1ULL << 14)    // THD, user, binlog
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6a281a4583e..7be9bfb7838 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1305,6 +1305,7 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S
       DBUG_PRINT("info",("Select tables optimized away"));
       zero_result_cause= "Select tables optimized away";
       tables_list= 0;				// All tables resolved
+      select_lex->min_max_opt_list.empty();
       const_tables= top_join_tab_count= table_count;
       /*
         Extract all table-independent conditions and replace the WHERE
@@ -2942,8 +2943,11 @@ void JOIN::exec_inner()
       if (sort_table_cond)
       {
 	if (!curr_table->select)
+	{
 	  if (!(curr_table->select= new SQL_SELECT))
 	    DBUG_VOID_RETURN;
+	  curr_table->select->head= curr_table->table;
+        }
 	if (!curr_table->select->cond)
 	  curr_table->select->cond= sort_table_cond;
 	else
@@ -8076,6 +8080,63 @@ bool JOIN_TAB::hash_join_is_possible()
 }
 
 
+/**
+  @brief
+  Check whether a KEYUSE can be really used for access this join table 
+
+  @param join    Join structure with the best join order 
+                 for which the check is performed
+  @param keyuse  Evaluated KEYUSE structure    
+
+  @details
+  This function is supposed to be used after the best execution plan have been
+  already chosen and the JOIN_TAB array for the best join order been already set.
+  For a given KEYUSE to access this JOIN_TAB in the best execution plan the
+  function checks whether it really can be used. The function first performs
+  the check with access_from_tables_is_allowed(). If it succeeds it checks
+  whether the keyuse->val does not use some fields of a materialized semijoin
+  nest that cannot be used to build keys to access outer tables.
+  Such KEYUSEs exists for the query like this:
+    select * from ot 
+    where ot.c in (select it1.c from it1, it2 where it1.c=f(it2.c))
+  Here we have two KEYUSEs to access table ot: with val=it1.c and val=f(it2.c).
+  However if the subquery was materialized the second KEYUSE cannot be employed
+  to access ot.
+
+  @retval true  the given keyuse can be used for ref access of this JOIN_TAB 
+  @retval false otherwise
+*/
+
+bool JOIN_TAB::keyuse_is_valid_for_access_in_chosen_plan(JOIN *join,
+                                                         KEYUSE *keyuse)
+{
+  if (!access_from_tables_is_allowed(keyuse->used_tables, 
+                                     join->sjm_lookup_tables))
+    return false;
+  if (join->sjm_scan_tables & table->map)
+    return true;
+  table_map keyuse_sjm_scan_tables= keyuse->used_tables &
+                                    join->sjm_scan_tables;
+  if (!keyuse_sjm_scan_tables)
+    return true;
+  uint sjm_tab_nr= 0;
+  while (!(keyuse_sjm_scan_tables & table_map(1) << sjm_tab_nr))
+    sjm_tab_nr++;
+  JOIN_TAB *sjm_tab= join->map2table[sjm_tab_nr];
+  TABLE_LIST *emb_sj_nest= sjm_tab->emb_sj_nest;    
+  if (!(emb_sj_nest->sj_mat_info && emb_sj_nest->sj_mat_info->is_used &&
+        emb_sj_nest->sj_mat_info->is_sj_scan))
+    return true;
+  st_select_lex *sjm_sel= emb_sj_nest->sj_subq_pred->unit->first_select(); 
+  for (uint i= 0; i < sjm_sel->item_list.elements; i++)
+  {
+    if (sjm_sel->ref_pointer_array[i] == keyuse->val)
+      return true;
+  }
+  return false; 
+}
+
+
 static uint
 cache_record_length(JOIN *join,uint idx)
 {
@@ -8623,6 +8684,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab,
   do
   {
     if (!(~used_tables & keyuse->used_tables) &&
+        join_tab->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse) &&
         are_tables_local(join_tab, keyuse->used_tables))    
     {
       if (first_keyuse)
@@ -8637,6 +8699,8 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab,
         {
           if (curr->keypart == keyuse->keypart &&
               !(~used_tables & curr->used_tables) &&
+              join_tab->keyuse_is_valid_for_access_in_chosen_plan(join,
+                                                                  keyuse) &&
               are_tables_local(join_tab, curr->used_tables))
             break;
         }
@@ -8671,6 +8735,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab,
   do
   {
     if (!(~used_tables & keyuse->used_tables) &&
+        join_tab->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse) &&
         are_tables_local(join_tab, keyuse->used_tables))
     { 
       bool add_key_part= TRUE;
@@ -8680,7 +8745,9 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab,
         {
           if (curr->keypart == keyuse->keypart &&
               !(~used_tables & curr->used_tables) &&
-               are_tables_local(join_tab, curr->used_tables))
+              join_tab->keyuse_is_valid_for_access_in_chosen_plan(join,
+                                                                  curr) &&
+              are_tables_local(join_tab, curr->used_tables))
 	  {
             keyuse->keypart= NO_KEYPART;
             add_key_part= FALSE;
@@ -8782,8 +8849,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
     do
     {
       if (!(~used_tables & keyuse->used_tables) &&
-          j->access_from_tables_is_allowed(keyuse->used_tables,
-                                           join->sjm_lookup_tables))
+	  j->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse))
       {
         if  (are_tables_local(j, keyuse->val->used_tables()))
         {
@@ -8853,8 +8919,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
     for (i=0 ; i < keyparts ; keyuse++,i++)
     {
       while (((~used_tables) & keyuse->used_tables) ||
-	     !j->access_from_tables_is_allowed(keyuse->used_tables,
-                                               join->sjm_lookup_tables) ||    
+	     !j->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse) ||
              keyuse->keypart == NO_KEYPART ||
 	     (keyuse->keypart != 
               (is_hash_join_key_no(key) ?
diff --git a/sql/sql_select.h b/sql/sql_select.h
index d51bca785a5..4f1b7bd7e05 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -534,6 +534,8 @@ typedef struct st_join_table {
             !(used_sjm_lookup_tables & ~emb_sj_nest->sj_inner_tables));
   }
 
+  bool keyuse_is_valid_for_access_in_chosen_plan(JOIN *join, KEYUSE *keyuse);
+
   void remove_redundant_bnl_scan_conds();
 } JOIN_TAB;
 
@@ -1000,6 +1002,11 @@ public:
     to materialize and access by lookups
   */
   table_map sjm_lookup_tables;
+  /** 
+    Bitmap of semijoin tables that the chosen plan decided
+    to materialize to scan the results of materialization
+  */
+  table_map sjm_scan_tables;
   /*
     Constant tables for which we have found a row (as opposed to those for
     which we didn't).
@@ -1341,6 +1348,7 @@ public:
     pre_sort_join_tab= NULL;
     emb_sjm_nest= NULL;
     sjm_lookup_tables= 0;
+    sjm_scan_tables= 0;
 
     exec_saved_explain= false;
     /*