MDEV-13699: Assertion `!new_field->field_name.str || strlen(new_field->field_name.str) == new_field->field_name.length' failed in create_tmp_table on 2nd execution of PS with semijoinbb-5.5-MDEV-13699

The problem was that SJ (semi-join) used secondary list (array) of subquery select list. The items there was prepared once then cleaned up (but not really freed from memory because it was made in statement memory).
Original list was not prepared after first execution because select was removed by conversion to SJ.
The solution is to use original list but prepare it first.

4 files changed, 98 insertions, 8 deletions

diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 74384141998..519d53eb98f 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -3153,4 +3153,30 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where (<cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))))) where 1
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-13699: Assertion `!new_field->field_name.str ||
+# strlen(new_field->field_name.str) == new_field->field_name.length'
+# failed in create_tmp_table on 2nd execution of PS with semijoin
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES  (3),(4);
+CREATE TABLE t3 (c INT);
+CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (5),(6);
+PREPARE stmt FROM 
+"SELECT * FROM t1
+  WHERE EXISTS ( 
+    SELECT * FROM t2 WHERE t1.a IN ( SELECT c AS fld FROM v3 )
+  )";
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+drop view v3;
+drop table t1,t2,t3;
+# End of 5.5 test
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 47dbdd782b5..32704060208 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -3167,6 +3167,32 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) and <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t3`.`c3` from `test`.`t3` where (<cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))))) where 1
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-13699: Assertion `!new_field->field_name.str ||
+# strlen(new_field->field_name.str) == new_field->field_name.length'
+# failed in create_tmp_table on 2nd execution of PS with semijoin
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES  (3),(4);
+CREATE TABLE t3 (c INT);
+CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (5),(6);
+PREPARE stmt FROM 
+"SELECT * FROM t1
+  WHERE EXISTS ( 
+    SELECT * FROM t2 WHERE t1.a IN ( SELECT c AS fld FROM v3 )
+  )";
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+drop view v3;
+drop table t1,t2,t3;
+# End of 5.5 test
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index acee1a67d63..0a35a61ecd3 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2841,5 +2841,35 @@ eval EXPLAIN EXTENDED $q2;
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # MDEV-13699: Assertion `!new_field->field_name.str ||
+--echo # strlen(new_field->field_name.str) == new_field->field_name.length'
+--echo # failed in create_tmp_table on 2nd execution of PS with semijoin
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+ 
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES  (3),(4); 
+ 
+CREATE TABLE t3 (c INT);
+CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (5),(6);
+ 
+PREPARE stmt FROM 
+  "SELECT * FROM t1
+  WHERE EXISTS ( 
+    SELECT * FROM t2 WHERE t1.a IN ( SELECT c AS fld FROM v3 )
+  )";
+EXECUTE stmt;
+EXECUTE stmt;
+EXECUTE stmt;
+
+drop view v3;
+drop table t1,t2,t3;
+
+--echo # End of 5.5 test
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index c21541c4b97..eb3c86255a5 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3725,21 +3725,29 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab)
   SJ_MATERIALIZATION_INFO *sjm= emb_sj_nest->sj_mat_info;
   THD *thd= tab->join->thd;
   /* First the calls come to the materialization function */
-  //List<Item> &item_list= emb_sj_nest->sj_subq_pred->unit->first_select()->item_list;
-  
+
   DBUG_ASSERT(sjm->is_used);
   /* 
     Set up the table to write to, do as select_union::create_result_table does
   */
   sjm->sjm_table_param.init();
   sjm->sjm_table_param.bit_fields_as_long= TRUE;
-  //List_iterator<Item> it(item_list);
   SELECT_LEX *subq_select= emb_sj_nest->sj_subq_pred->unit->first_select();
-  Item **p_item= subq_select->ref_pointer_array;
-  Item **p_end= p_item + subq_select->item_list.elements;
-  //while((right_expr= it++))
-  for(;p_item != p_end; p_item++)
-    sjm->sjm_table_cols.push_back(*p_item);
+  List_iterator<Item> it(subq_select->item_list);
+  Item *item;
+  while((item= it++))
+  {
+    /*
+      This semi-join replaced the subquery (subq_select) and so on
+      re-executing it will not be prepared. To use the Items from its
+      select list we have to prepare (fix_fields) them
+    */
+    if (!item->fixed && item->fix_fields(thd, it.ref()))
+      DBUG_RETURN(TRUE);
+    item= *(it.ref()); // it can be changed by fix_fields
+    DBUG_ASSERT(!item->name_length || item->name_length == strlen(item->name));
+    sjm->sjm_table_cols.push_back(item, thd->mem_root);
+  }
 
   sjm->sjm_table_param.field_count= subq_select->item_list.elements;
   sjm->sjm_table_param.force_not_null_cols= TRUE;