A fix and a test case for Bug#15217 "Using a SP cursor on a table created

 with PREPARE fails with weird error".
More generally, re-executing a stored procedure with a complex SP cursor query
could lead to a crash.

The cause of the problem was that SP cursor queries were not optimized 
properly at first execution: their parse tree belongs to sp_instr_cpush,
not sp_instr_copen, and thus the tree was tagged "EXECUTED" when the
cursor was declared, not when it was opened. This led to loss of optimization
transformations performed at first execution, as sp_instr_copen saw that the
query is already "EXECUTED" and therefore either not ran first-execution 
related blocks or wrongly rolled back the transformations caused by 
first-execution code.
The fix is to update the state of the parsed tree only when the tree is
executed, as opposed to when the instruction containing the tree is executed.
Assignment if i->state is moved to reset_lex_and_exec_core.


mysql-test/r/sp.result:
  Test results fixed (Bug#15217)
mysql-test/t/sp.test:
  Add a test case for Bug#15217
sql/sp_head.cc:
  Move assignment of stmt_arena->state to reset_lex_and_exec_core

3 files changed, 51 insertions, 1 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index ff378f1f43b..d3874c769fa 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4990,4 +4990,25 @@ CALL bug18037_p2()|
 DROP FUNCTION bug18037_f1|
 DROP PROCEDURE bug18037_p1|
 DROP PROCEDURE bug18037_p2|
+drop table if exists t3|
+drop procedure if exists bug15217|
+create table t3 as select 1|
+create procedure bug15217()
+begin
+declare var1 char(255);
+declare cur1 cursor for select * from t3;
+open cur1;
+fetch cur1 into var1;
+select concat('data was: /', var1, '/');
+close cur1;
+end |
+call bug15217()|
+concat('data was: /', var1, '/')
+data was: /1/
+flush tables |
+call bug15217()|
+concat('data was: /', var1, '/')
+data was: /1/
+drop table t3|
+drop procedure bug15217|
 drop table t1,t2;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 1d21a5da187..66498198157 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5888,6 +5888,33 @@ DROP FUNCTION bug18037_f1|
 DROP PROCEDURE bug18037_p1|
 DROP PROCEDURE bug18037_p2|
 
+#
+# Bug#15217 "Using a SP cursor on a table created with PREPARE fails with
+#           weird error". Check that the code that is supposed to work at
+#           the first execution of a stored procedure actually works for
+#           sp_instr_copen.
+
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug15217|
+--enable_warnings
+create table t3 as select 1|
+create procedure bug15217()
+begin
+  declare var1 char(255);
+  declare cur1 cursor for select * from t3;
+  open cur1;
+  fetch cur1 into var1;
+  select concat('data was: /', var1, '/');
+  close cur1;
+end |
+# Returns expected result
+call bug15217()|
+flush tables |
+# Returns error with garbage as column name
+call bug15217()|
+drop table t3|
+drop procedure bug15217|
 
 #
 # BUG#NNNN: New bug synopsis
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 3b29a841966..ef2f895c8b2 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -1075,7 +1075,6 @@ sp_head::execute(THD *thd)
     thd->net.no_send_error= 0;
     if (i->free_list)
       cleanup_items(i->free_list);
-    i->state= Query_arena::EXECUTED;
     
     /* 
       If we've set thd->user_var_events_alloc to mem_root of this SP
@@ -2210,6 +2209,9 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
     m_lex->mark_as_requiring_prelocking(NULL);
   }
   thd->rollback_item_tree_changes();
+  /* Update the state of the active arena. */
+  thd->stmt_arena->state= Query_arena::EXECUTED;
+
 
   /*
     Unlike for PS we should not call Item's destructors for newly created