MDEV-15941 Explicit cursor FOR loop does not close the cursor

5 files changed, 29 insertions, 12 deletions

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index cb6edc4a788..7a8cd410e86 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5891,6 +5891,26 @@ bool LEX::sp_for_loop_cursor_finalize(THD *thd, const Lex_for_loop_st &loop)
   return sp_while_loop_finalize(thd);
 }
 
+bool LEX::sp_for_loop_outer_block_finalize(THD *thd,
+                                           const Lex_for_loop_st &loop)
+{
+  Lex_spblock tmp;
+  tmp.curs= MY_TEST(loop.m_implicit_cursor);
+  if (unlikely(sp_block_finalize(thd, tmp))) // The outer DECLARE..BEGIN..END
+    return true;
+  if (!loop.is_for_loop_explicit_cursor())
+    return false;
+  /*
+    Explicit cursor FOR loop must close the cursor automatically.
+    Note, implicit cursor FOR loop does not need to close the cursor,
+    it's closed by sp_instr_cpop.
+  */
+  sp_instr_cclose *ic= new (thd->mem_root)
+                       sp_instr_cclose(sphead->instructions(), spcont,
+                                       loop.m_cursor_offset);
+  return ic == NULL || sphead->add_instr(ic);
+}
+
 /***************************************************************************/
 
 bool LEX::sp_declare_cursor(THD *thd, const LEX_CSTRING *name,
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 8ea9a551a65..89c5688ad96 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3802,6 +3802,7 @@ public:
            sp_for_loop_cursor_finalize(thd, loop) :
            sp_for_loop_intrange_finalize(thd, loop);
   }
+  bool sp_for_loop_outer_block_finalize(THD *thd, const Lex_for_loop_st &loop);
   /* End of FOR LOOP methods */
 
   bool add_signal_statement(THD *thd, const class sp_condition_value *value);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 0d440bf0c2c..f915895a260 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -4729,9 +4729,7 @@ sp_labeled_control:
           }
           pop_sp_loop_label                    // The inner WHILE block
           {
-            Lex_spblock tmp;
-            tmp.curs= MY_TEST($4.m_implicit_cursor);
-            if (unlikely(Lex->sp_block_finalize(thd, tmp))) // The outer DECLARE..BEGIN..END
+            if (unlikely(Lex->sp_for_loop_outer_block_finalize(thd, $4)))
               MYSQL_YYABORT;
           }
         | sp_label REPEAT_SYM
@@ -4781,12 +4779,10 @@ sp_unlabeled_control:
           sp_proc_stmts1
           END FOR_SYM
           {
-            Lex_spblock tmp;
-            tmp.curs= MY_TEST($3.m_implicit_cursor);
             if (unlikely(Lex->sp_for_loop_finalize(thd, $3)))
               MYSQL_YYABORT;
             Lex->sp_pop_loop_empty_label(thd); // The inner WHILE block
-            if (unlikely(Lex->sp_block_finalize(thd, tmp))) // The outer DECLARE..BEGIN..END
+            if (unlikely(Lex->sp_for_loop_outer_block_finalize(thd, $3)))
               MYSQL_YYABORT;
           }
         | REPEAT_SYM
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 8c4d1e897dc..d95e87c524a 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -4676,9 +4676,7 @@ sp_labeled_control:
           }
           pop_sp_loop_label                    // The inner WHILE block
           {
-            Lex_spblock tmp;
-            tmp.curs= MY_TEST($4.m_implicit_cursor);
-            if (unlikely(Lex->sp_block_finalize(thd, tmp))) // The outer DECLARE..BEGIN..END
+            if (unlikely(Lex->sp_for_loop_outer_block_finalize(thd, $4)))
               MYSQL_YYABORT;
           }
         | labels_declaration_oracle REPEAT_SYM
@@ -4728,12 +4726,10 @@ sp_unlabeled_control:
           sp_proc_stmts1
           END LOOP_SYM
           {
-            Lex_spblock tmp;
-            tmp.curs= MY_TEST($3.m_implicit_cursor);
             if (unlikely(Lex->sp_for_loop_finalize(thd, $3)))
               MYSQL_YYABORT;
             Lex->sp_pop_loop_empty_label(thd); // The inner WHILE block
-            if (unlikely(Lex->sp_block_finalize(thd, tmp))) // The outer DECLARE..BEGIN..END
+            if (unlikely(Lex->sp_for_loop_outer_block_finalize(thd, $3)))
               MYSQL_YYABORT;
           }
         | REPEAT_SYM
diff --git a/sql/structs.h b/sql/structs.h
index d530dd73b7c..d8b95a3509a 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -720,6 +720,10 @@ public:
     *this= other;
   }
   bool is_for_loop_cursor() const { return m_upper_bound == NULL; }
+  bool is_for_loop_explicit_cursor() const
+  {
+    return is_for_loop_cursor() && !m_implicit_cursor;
+  }
 };