2 files changed, 35 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index be666547f26..15f1d6377e3 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -511,6 +511,20 @@ set join_cache_level = default;
 drop view v1;
 drop table t1,t2;
 #
+# MDEV-10693: cost-based choice between materialization and in-to-exists
+#             for a subquery from the expression used in ref access 
+#
+CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 INT) ENGINE=InnoDB;
+CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (3);
+SELECT * FROM t1
+WHERE NULL IN ( SELECT i2 FROM t2 
+WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 );
+i1
+DROP TABLE t1,t2,t3;
+#
 # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding
 #
 create table t1(a int) engine=innodb;
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
index b26c5036f3f..2451bc60fee 100644
--- a/mysql-test/t/subselect_innodb.test
+++ b/mysql-test/t/subselect_innodb.test
@@ -503,6 +503,27 @@ drop view v1;
 drop table t1,t2;
 
 --echo #
+--echo # MDEV-10693: cost-based choice between materialization and in-to-exists
+--echo #             for a subquery from the expression used in ref access 
+--echo #
+
+--source include/have_innodb.inc
+ 
+CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+ 
+CREATE TABLE t2 (i2 INT) ENGINE=InnoDB;
+ 
+CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (3);
+ 
+SELECT * FROM t1
+  WHERE NULL IN ( SELECT i2 FROM t2 
+                    WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 );
+
+DROP TABLE t1,t2,t3;
+
+--echo #
 --echo # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding
 --echo #
 create table t1(a int) engine=innodb;