MDEV-10598 Variable declarations can go after cursor declarations

Based on a contributed patch from Jerome Brauge.

7 files changed, 94 insertions, 28 deletions

diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 5b7e4f854e7..16dfbe9b700 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -2423,6 +2423,23 @@ sp_head::do_cont_backpatch()
   }
 }
 
+
+bool
+sp_head::sp_add_instr_cpush_for_cursors(THD *thd, sp_pcontext *pcontext)
+{
+  for (uint i= 0; i < pcontext->frame_cursor_count(); i++)
+  {
+    const sp_pcursor *c= pcontext->get_cursor_by_local_frame_offset(i);
+    sp_instr_cpush *instr= new (thd->mem_root)
+                             sp_instr_cpush(instructions(), pcontext, c->lex(),
+                                            pcontext->cursor_offset() + i);
+    if (instr == NULL || add_instr(instr))
+      return true;
+  }
+  return false;
+}
+
+
 void
 sp_head::set_info(longlong created, longlong modified,
                   st_sp_chistics *chistics, sql_mode_t sql_mode)
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 8322a92636e..d8709feb084 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -573,6 +573,9 @@ public:
   void
   do_cont_backpatch();
 
+  /// Add cpush instructions for all cursors declared in the current frame
+  bool sp_add_instr_cpush_for_cursors(THD *thd, sp_pcontext *pcontext);
+
   char *name(uint *lenp = 0) const
   {
     if (lenp)
diff --git a/sql/sp_pcontext.h b/sql/sp_pcontext.h
index f6ed0ace60c..f8ca45da4ae 100644
--- a/sql/sp_pcontext.h
+++ b/sql/sp_pcontext.h
@@ -659,6 +659,15 @@ public:
   /// Find cursor by offset (for SHOW {PROCEDURE|FUNCTION} CODE only).
   const sp_pcursor *find_cursor(uint offset) const;
 
+  const sp_pcursor *get_cursor_by_local_frame_offset(uint offset) const
+  { return &m_cursors.at(offset); }
+
+  uint cursor_offset() const
+  { return m_cursor_offset; }
+
+  uint frame_cursor_count() const
+  { return m_cursors.elements(); }
+
   uint max_cursor_index() const
   { return m_max_cursor_index + m_cursors.elements(); }
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index d0d60b2c7b2..7e5d29cd675 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5628,7 +5628,7 @@ bool LEX::sp_for_loop_cursor_finalize(THD *thd, const Lex_for_loop_st &loop)
 
 bool LEX::sp_declare_cursor(THD *thd, const LEX_STRING name,
                             sp_lex_cursor *cursor_stmt,
-                            sp_pcontext *param_ctx)
+                            sp_pcontext *param_ctx, bool add_cpush_instr)
 {
   uint offp;
   sp_instr_cpush *i;
@@ -5639,12 +5639,18 @@ bool LEX::sp_declare_cursor(THD *thd, const LEX_STRING name,
     return true;
   }
   cursor_stmt->set_cursor_name(name);
-  i= new (thd->mem_root)
-       sp_instr_cpush(sphead->instructions(), spcont, cursor_stmt,
-                      spcont->current_cursor_count());
-  return i == NULL ||
-         sphead->add_instr(i) ||
-         spcont->add_cursor(name, param_ctx, cursor_stmt);
+
+  if (spcont->add_cursor(name, param_ctx, cursor_stmt))
+    return true;
+
+  if (add_cpush_instr)
+  {
+    i= new (thd->mem_root)
+         sp_instr_cpush(sphead->instructions(), spcont, cursor_stmt,
+                        spcont->current_cursor_count() - 1);
+    return i == NULL || sphead->add_instr(i);
+  }
+  return false;
 }
 
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5f02bbac9b6..8afc02a099c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3165,7 +3165,8 @@ public:
 
   bool sp_declare_cursor(THD *thd, const LEX_STRING name,
                          class sp_lex_cursor *cursor_stmt,
-                         sp_pcontext *param_ctx);
+                         sp_pcontext *param_ctx, bool add_cpush_instr);
+
   bool sp_open_cursor(THD *thd, const LEX_STRING name,
                       List<sp_assignment_lex> *parameters);
   Item_splocal *create_item_for_sp_var(LEX_STRING name, sp_variable *spvar,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index a796d8af40d..1b87196686f 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -3022,7 +3022,7 @@ sp_decl_body:
           }
         | ident CURSOR_SYM FOR_SYM sp_cursor_stmt
           {
-            if (Lex->sp_declare_cursor(thd, $1, $4, NULL))
+            if (Lex->sp_declare_cursor(thd, $1, $4, NULL, true))
               MYSQL_YYABORT;
             $$.vars= $$.conds= $$.hndlrs= 0;
             $$.curs= 1;
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 021d6701f7a..415ea119fa6 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -1335,7 +1335,9 @@ END_OF_INPUT
 
 %type <num>  sp_decl_idents sp_handler_type sp_hcond_list
 %type <spcondvalue> sp_cond sp_hcond sqlstate signal_value opt_signal_value
-%type <spblock> sp_decl_body sp_decl_body_list opt_sp_decl_body_list
+%type <spblock> sp_decl_body_list opt_sp_decl_body_list
+%type <spblock> sp_decl_non_handler sp_decl_non_handler_list
+%type <spblock> sp_decl_handler sp_decl_handler_list opt_sp_decl_handler_list
 %type <spblock_handlers> sp_block_statements_and_exceptions
 %type <sp_instr_addr> sp_instr_addr
 %type <sp_cursor_name_and_offset> sp_cursor_name_and_offset
@@ -2444,12 +2446,37 @@ opt_sp_decl_body_list:
         ;
 
 sp_decl_body_list:
-          sp_decl_body ';' { $$= $1; }
-        | sp_decl_body_list sp_decl_body ';'
+          sp_decl_non_handler_list
           {
-            if (Lex->sp_declarations_join(&$$, $1, $2))
+            if (Lex->sphead->sp_add_instr_cpush_for_cursors(thd, Lex->spcont))
               MYSQL_YYABORT;
           }
+          opt_sp_decl_handler_list
+          {
+            $$.join($1, $3);
+          }
+        | sp_decl_handler_list
+        ;
+
+sp_decl_non_handler_list:
+          sp_decl_non_handler ';' { $$= $1; }
+        | sp_decl_non_handler_list sp_decl_non_handler ';'
+          {
+            $$.join($1, $2);
+          }
+        ;
+
+sp_decl_handler_list:
+          sp_decl_handler ';' { $$= $1; }
+        | sp_decl_handler_list sp_decl_handler ';'
+          {
+            $$.join($1, $2);
+          }
+        ;
+
+opt_sp_decl_handler_list:
+          /* Empty*/ { $$.init(); }
+        | sp_decl_handler_list
         ;
 
 qualified_column_ident:
@@ -2527,7 +2554,7 @@ type_or_rowtype:
         | ROWTYPE_SYM  { $$= 1; }
         ;
 
-sp_decl_body:
+sp_decl_non_handler:
           sp_decl_idents
           {
             Lex->sp_variable_declarations_init(thd, $1);
@@ -2581,18 +2608,6 @@ sp_decl_body:
             $$.vars= $$.hndlrs= $$.curs= 0;
             $$.conds= 1;
           }
-        | sp_handler_type HANDLER_SYM FOR_SYM
-          {
-            if (Lex->sp_handler_declaration_init(thd, $1))
-              MYSQL_YYABORT;
-          }
-          sp_hcond_list sp_proc_stmt
-          {
-            if (Lex->sp_handler_declaration_finalize(thd, $1))
-              MYSQL_YYABORT;
-            $$.vars= $$.conds= $$.curs= 0;
-            $$.hndlrs= 1;
-          }
         | CURSOR_SYM ident_directly_assignable
           {
             Lex->sp_block_init(thd);
@@ -2603,13 +2618,28 @@ sp_decl_body:
             sp_pcontext *param_ctx= Lex->spcont;
             if (Lex->sp_block_finalize(thd))
               MYSQL_YYABORT;
-            if (Lex->sp_declare_cursor(thd, $2, $6, param_ctx))
+            if (Lex->sp_declare_cursor(thd, $2, $6, param_ctx, false))
               MYSQL_YYABORT;
             $$.vars= $$.conds= $$.hndlrs= 0;
             $$.curs= 1;
           }
         ;
 
+sp_decl_handler:
+          sp_handler_type HANDLER_SYM FOR_SYM
+          {
+            if (Lex->sp_handler_declaration_init(thd, $1))
+              MYSQL_YYABORT;
+          }
+          sp_hcond_list sp_proc_stmt
+          {
+            if (Lex->sp_handler_declaration_finalize(thd, $1))
+              MYSQL_YYABORT;
+            $$.vars= $$.conds= $$.curs= 0;
+            $$.hndlrs= 1;
+          }
+        ;
+
 opt_parenthesized_cursor_formal_parameters:
           /* Empty */
         | '(' sp_fdparams ')'
@@ -3849,7 +3879,7 @@ sp_for_loop_bounds:
           {
             DBUG_ASSERT(Lex->sphead);
             LEX_STRING name= {C_STRING_WITH_LEN("[implicit_cursor]") };
-            if (Lex->sp_declare_cursor(thd, name, $4, NULL))
+            if (Lex->sp_declare_cursor(thd, name, $4, NULL, true))
               MYSQL_YYABORT;
             $$.m_direction= 1;
             if (!($$.m_index= new (thd->mem_root) sp_assignment_lex(thd, thd->lex)))