Fix added along with a test for a case missed in the patch for MDEV-16751

4 files changed, 47 insertions, 4 deletions

diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 7f39f1fb788..eca3b760b65 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2436,6 +2436,23 @@ a
 drop procedure prepare_data;
 set @@optimizer_switch= @save_optimizer_switch;
 drop table t1,t2,t3;
+CREATE TABLE t1 ( id int NOT NULL, key(id));
+INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL);
+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	<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
+1
+1
+1
+drop table t1,t2;
+drop view v1;
 # End of 5.5 tests
 set @subselect_mat_test_optimizer_switch_value=null;
 set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 44006eadfc6..180c182a51a 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2476,4 +2476,21 @@ a
 drop procedure prepare_data;
 set @@optimizer_switch= @save_optimizer_switch;
 drop table t1,t2,t3;
+CREATE TABLE t1 ( id int NOT NULL, key(id));
+INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL);
+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	<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
+1
+1
+1
+drop table t1,t2;
+drop view v1;
 # End of 5.5 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 794545b53a7..c82c1e7acec 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -2221,4 +2221,13 @@ drop procedure prepare_data;
 set @@optimizer_switch= @save_optimizer_switch;
 drop table t1,t2,t3;
 
+CREATE TABLE t1 ( id int NOT NULL, key(id));
+INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL);
+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);
+SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
+drop table t1,t2;
+drop view v1;
 --echo # End of 5.5 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 45a1372988b..b2b2bcde80c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7445,11 +7445,11 @@ bool JOIN_TAB::keyuse_is_valid_for_access_in_chosen_plan(JOIN *join,
   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++)
   {
-    DBUG_ASSERT(sjm_sel->ref_pointer_array[i]->type() == Item::FIELD_ITEM);
-    if (keyuse->val->type() == Item::FIELD_ITEM)
+    DBUG_ASSERT(sjm_sel->ref_pointer_array[i]->real_item()->type() == Item::FIELD_ITEM);
+    if (keyuse->val->real_item()->type() == Item::FIELD_ITEM)
     {
-      Field *field = ((Item_field*)sjm_sel->ref_pointer_array[i])->field;
-      if (field->eq(((Item_field*)keyuse->val)->field))
+      Field *field = ((Item_field*)sjm_sel->ref_pointer_array[i]->real_item())->field;
+      if (field->eq(((Item_field*)keyuse->val->real_item())->field))
         return true;
     }
   }