Alexander Barkov 90f8178f33 MDEV-10152 Add support for TYPE .. IS REF CURSOR ...

Adding support for the strict cursor data types:

Example 1a:
  TYPE rec0_t IS RECORD (a INT, VARCHAR(10));
  TYPE cur0_t IS REF CURSOR RETURN rec0_t;

Example 1b:
  TYPE rec0_t IS RECORD (a t1.a%TYPE, b t1.b%TYPE);
  TYPE cur0_t IS REF CURSOR RETURN rec0_t;

Example 1c:
  TYPE rec0_t IS RECORD (a INT, VARCHAR(10));
  r0 rec0_t;
  TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;

Example 1d:
  TYPE rec0_t IS RECORD (a t1.a%TYPE, b t1.b%TYPE);
  r0 rec0_t;
  TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;

Example2a:
  TYPE cur0_t IS REF CURSOR RETURN t1%ROWTYPE; -- t1 is a table

Example 2b:
  r0 t1%ROWTYPE;
  TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;

Example3a:
  CURSOR cursor_sample IS SELECT a,b FROM t1;
  TYPE cur0_t IS REF CURSOR RETURN cursor_sample%ROWTYPE;

Example3b:
  CURSOR cursor_sample IS SELECT a,b FROM t1;
  r0 cursor_sample%ROWTYPE;
  TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;

If a cursor variable is declared with a RETURN clause then:
1. At OPEN type the data type of the SELECT list row is compared
  for compatibility with the cursor RETURN data type.
  The SELECT list row must be assignable to the RETURN type row.
  If case if assignability is not meet, an error is raised
  Assignability means:
  - The arity of the SELECT list must be equal to the arity
    of the RETURN clause
  - Every n-th field of the SELECT list must be assignable to the
    n-th field of the RETURN Clause

2. At FETCH time, the data is fetched in two steps:
  a. On the first step the data is fetched into a virtual table
     with the row type described in the RETURN clause
  b. On the second step the data is copied from the virtual table
     to the target fetch list. Data type conversion can happen
     on this step.

Change details:

Adding new methods:
- sp_cursor::check_assignability_to
- Virtual_tmp_table::check_assignability_from
- Virtual_tmp_table::sp_set_from_select_list
- Virtual_tmp_table::sp_save_in_vtable
- Virtual_tmp_table::sp_save_in_target_list
- LEX::check_ref_cursor_components
- LEX::make_sp_instr_copy_struct_for_last_context_variables
- LEX::declare_type_ref_cursor
- sp_cursor::Select_fetch_into_spvars::send_data_with_return_type

Adding new members:
- sp_instr_copen_by_ref::m_cursor_name
- Select_fetch_into_spvars::m_return_type
- Select_materialize::m_cursor_name
- Select_materialize::m_return_type

Adding new virtual methods:
- Item::resolve_spvar_cursor_rowtype
- Type_handler::Spvar_definition_resolve_type_refs
- Server_side_cursor::check_assignability_to
- Overriding Select_materialize::prepare to raise an error when the cursor
  returned data type is not compatible with the RETURN clause

Making these methods virtual:
- Field::check_assignability_from

Adding new classes:
- sp_type_def_ref
- RowTypeBuffer

Adding new constructors to:
- Spvar_definition

Adding new helper methods (e.g. to reuse the code)
- Field::store_field_maybe_null
- ChanBuffer::append_ulonglong
- sp_pcontext::set_type_for_last_context_variables

Minor changes:
- Making TABLE::export_structure const
- Overriding Item_splocal::type_extra_attributes. It was forgotten in earlier changes.

Adding new error messages
- ER_CANNOT_CAST_ON_IDENT1_ASSIGNMENT_FOR_OPERATION
- ER_CANNOT_CAST_ON_IDENT2_ASSIGNMENT_FOR_OPERATION

2026-04-06 15:16:38 +04:00

12 KiB

Raw Permalink Blame History

View Raw