This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.
Works as a regular stored routine variable, parameter and return value:
- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function
Note, strongly typed REF CURSOR will be added separately.
Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:
- select_results.h:
class select_result_sink
class select_result
class select_result_interceptor
- sp_cursor.h:
class sp_cursor_statistics
class sp_cursor
- sp_rcontext_handler.h
class Sp_rcontext_handler and its descendants
The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array
- A new class Statement_rcontext which contains data shared
between sub-statements of a compound statement.
It has a member m_statement_cursors of the sp_cursor_array data type,
as well as open cursor counter. THD inherits from Statement_rcontext.
- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
It is designed to store uint16 references -
positions of the cursor in THD::m_statement_cursors.
- Type_handler_sys_refcursor suppresses some derived numeric features.
When a SYS_REFCURSOR variable is used as an integer an error is raised.
- A new abstract class sp_instr_fetch_cursor. It's needed to share
the common code between "OPEN cur" (for static cursors) and
"OPER cur FOR stmt" (for SYS_REFCURSORs).
- New sp_instr classes:
* sp_instr_copen_by_ref - OPEN sys_ref_curor FOR stmt;
* sp_instr_cfetch_by_ref - FETCH sys_ref_cursor INTO targets;
* sp_instr_cclose_by_ref - CLOSE sys_ref_cursor;
* sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
the execution goes out of the BEGIN..END block
where SYS_REFCURSOR variables are declared.
- New methods in LEX:
* sp_open_cursor_for_stmt - handles "OPEN sys_ref_cursor FOR stmt".
* sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
static cursors and SYS_REFCURSORs.
* sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.
- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
* Item_func_cursor_isopen
* Item_func_cursor_found
* Item_func_cursor_notfound
* Item_func_cursor_rowcount
- A new system variable @@max_open_cursors - to limit the number
of cursors (static and SYS_REFCURSORs) opened at the same time.
Its allowed range is [0-65536], with 50 by default.
- A new virtual method Type_handler::can_return_bool() telling
if calling item->val_bool() is allowed for Items of this data type,
or if otherwise the "Illegal parameter for operation" error should be raised
at fix_fields() time.
- New methods in Sp_rcontext_handler:
* get_cursor()
* get_cursor_by_ref()
- A new class Sp_rcontext_handler_statement to handle top level statement
wide cursors which are shared by all substatements.
- A new virtual method expr_event_handler() in classes Item and Field.
It's needed to close (and make available for a new OPEN)
unused THD::m_statement_cursors elements which do not have any references
any more. It can happen in various moments in time, e.g.
* after evaluation parameters of an SQL routine
* after assigning a cursor expression into a SYS_REFCURSOR variable
* when leaving a BEGIN..END block with SYS_REFCURSOR variables
* after setting OUT/INOUT routine actual parameters from formal
parameters.
Calling a stored function that uses a cursor inside its body
could produce the error ER_NO_SUCH_TABLE on the second execution
in case the cursor uses multi-table query and one of the tables
is a temporary table just created before querying the cursor and
dropped just after the query has been executed.
The reason for issue is that re-parsing of failed a SP instruction
caused be create/drop of the temporary table used LEX object
left from previous parsing of a SP instruction's query instead
re-initialize the lex object before parsing.
To fix the issue, add initialization of lex for cursor's
statement before re-parsing the query of a failed SP instruction.
This bug report was caused by implementation of the task MDEV-5816
(Stored programs: validation of stored program statements).
Changing metadata of a table that has a trigger on AFTER UPDATE or AFTER DELETE
resulted in unexpected output of the error ER_TRG_NO_SUCH_ROW_IN_TR.
It was caused by the fact that characteristics of the trigger dependent on
changed table's metadata wasn't set in a new lex object created on re-parsing
of a failing trigger statement.
To fix the bug the data member lex->trg_chistics.action_time and
lex->trg_chistics.event must be set into real values of the trigger
whose statement being re-parsed.
This is the follow-up patch for the task MDEV-5816 that fixes assert failure
that happened after recompilation of a stored routine containing a cursor
on its second execution.
The reason of assertion hit is that a state of the SP instruction sp_instr_cpush
wasn't reset after its SQL statement re-compiled.
To fix the issue the virtual method sp_lex_instr::on_after_expr_parsing
is overridden in the derived class sp_instr_cpush. Implementation of this method
does resetting of the data member sp_instr_cpush::m_metadata_changed
Additionally, implementation of the method
sp_instr_set_trigger_field::on_after_expr_parsing
has been slightly modified to set the data member
sp_instr_set_trigger_field::value
just before successful return. This data member is used to check whether this
SP instruction is still valid or should be re-compiled.
Resetting this data member before an instance of the class Item_trigger_field
be successfully allocated theoretically could lead to clearing of instruction's
state despite the fact that memory allocation was failed.
The follow-up patch to check in mtr tests that recompilation of
a SP's instruction doesn't lead to eviction of SP from sp_cache.
This patch adds the debug keyword 'check_sp_cache_not_invalidated'
checked in sp_cache_flush_obsolete. In case this debug keyword
is set the macros DBUG_SUICIDE() called to cause test failure.
The function sp_cache_flush_obsolete() is called on opening
a stored routine. So setting this keyword before second execution
of some stored routine that supposed to cause recompilation of
SP's statement will guarantee that this stored routine not evicted
from sp_cache.
Suggested approach has one limitation - the statement
CREATE/ALTER/DROP VIEW
forces invalidation of the whole sp_cache (by invoking the function
sp_cache_invalidate()).
So, for those tests (actually, there are very small number of such tests)
that create/alter/drop a view before the second execution of some stored
routine, the debug keyword 'check_sp_cache_not_invalidated' isn't set.
The proposal to add some way a check that a stored routine is not force out
from sp_cache on re-parsing a failing statement of a stored routine was
done during reiew, that is the reason the proposed change has been formatted
as a separate patch.
