MDEV-16584 SP with a cursor inside a loop wastes THD memory aggressively

Problem:

push_cursor() created sp_cursor instances on THD::main_mem_root,
which is freed only after the SP instructions loop.

Changes:
- Moving sp_cursor declaration from sp_rcontext.h to sql_class.h
- Deriving sp_instr_cpush from sp_cursor. So now sp_cursor is created
  only once (at the SP parse time) and then reused on all loop iterations
- Adding a new method reset() into sp_cursor (and its parent classes)
  to reset an sp_cursor instance before reuse.
- Moving former sp_cursor members m_fetch_count, m_row_count, m_found
  into a separate class sp_cursor_statistics. This helps to reuse
  the code in sp_cursor constructors, and in sp_cursor::reset()
- Adding a helper method sp_rcontext::pop_cursor().
- Adding "THD*" parameter to so_rcontext::pop_cursors() and pop_all_cursors()
- Removing "new" and "delete" from sp_rcontext::push_cursor() and
  sp_rconext::pop_cursor().
- Fixing sp_cursor not to derive from Sql_alloc, as it's now allocated
  only as a part of sp_instr_cpush (and not allocated separately).
- Moving lex_keeper->disable_query_cache() from sp_cursor::sp_cursor()
  to sp_instr_cpush::execute().
- Adding tests

1 files changed, 7 insertions, 80 deletions

diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h
index 33d76e1c85a..40d636b3529 100644
--- a/sql/sp_rcontext.h
+++ b/sql/sp_rcontext.h
@@ -293,23 +293,20 @@ public:
   // Cursors.
   /////////////////////////////////////////////////////////////////////////
 
-  /// Create a new sp_cursor instance and push it to the cursor stack.
+  /// Push a cursor to the cursor stack.
   ///
-  /// @param lex_keeper SP-instruction execution helper.
-  /// @param i          Cursor-push instruction.
+  /// @param cursor The cursor
   ///
-  /// @return error flag.
-  /// @retval false on success.
-  /// @retval true on error.
-  bool push_cursor(THD *thd, sp_lex_keeper *lex_keeper);
+  void push_cursor(sp_cursor *cur);
 
+  void pop_cursor(THD *thd);
   /// Pop and delete given number of sp_cursor instance from the cursor stack.
   ///
   /// @param count Number of cursors to pop & delete.
-  void pop_cursors(size_t count);
+  void pop_cursors(THD *thd, size_t count);
 
-  void pop_all_cursors()
-  { pop_cursors(m_ccount); }
+  void pop_all_cursors(THD *thd)
+  { pop_cursors(thd, m_ccount); }
 
   sp_cursor *get_cursor(uint i) const
   { return m_cstack[i]; }
@@ -429,74 +426,4 @@ private:
   Bounds_checked_array<Item_cache *> m_case_expr_holders;
 }; // class sp_rcontext : public Sql_alloc
 
-///////////////////////////////////////////////////////////////////////////
-// sp_cursor declaration.
-///////////////////////////////////////////////////////////////////////////
-
-class Server_side_cursor;
-typedef class st_select_lex_unit SELECT_LEX_UNIT;
-
-/* A mediator between stored procedures and server side cursors */
-
-class sp_cursor : public Sql_alloc
-{
-private:
-  /// An interceptor of cursor result set used to implement
-  /// FETCH <cname> INTO <varlist>.
-  class Select_fetch_into_spvars: public select_result_interceptor
-  {
-    List<sp_variable> *spvar_list;
-    uint field_count;
-    bool send_data_to_variable_list(List<sp_variable> &vars, List<Item> &items);
-  public:
-    Select_fetch_into_spvars(THD *thd_arg): select_result_interceptor(thd_arg) {}
-    uint get_field_count() { return field_count; }
-    void set_spvar_list(List<sp_variable> *vars) { spvar_list= vars; }
-
-    virtual bool send_eof() { return FALSE; }
-    virtual int send_data(List<Item> &items);
-    virtual int prepare(List<Item> &list, SELECT_LEX_UNIT *u);
-};
-
-public:
-  sp_cursor(THD *thd_arg, sp_lex_keeper *lex_keeper);
-
-  virtual ~sp_cursor()
-  { destroy(); }
-
-  sp_lex_keeper *get_lex_keeper() { return m_lex_keeper; }
-
-  int open(THD *thd);
-
-  int open_view_structure_only(THD *thd);
-
-  int close(THD *thd);
-
-  my_bool is_open()
-  { return MY_TEST(server_side_cursor); }
-
-  bool found() const
-  { return m_found; }
-
-  ulonglong row_count() const
-  { return m_row_count; }
-
-  ulonglong fetch_count() const
-  { return m_fetch_count; }
-
-  int fetch(THD *, List<sp_variable> *vars, bool error_on_no_data);
-
-  bool export_structure(THD *thd, Row_definition_list *list);
-
-private:
-  Select_fetch_into_spvars result;
-  sp_lex_keeper *m_lex_keeper;
-  Server_side_cursor *server_side_cursor;
-  ulonglong m_fetch_count; // Number of FETCH commands since last OPEN
-  ulonglong m_row_count;   // Number of successful FETCH since last OPEN
-  bool m_found;            // If last FETCH fetched a row
-  void destroy();
-
-}; // class sp_cursor : public Sql_alloc
-
 #endif /* _SP_RCONTEXT_H_ */