Allowing prepared statements in stored functions when
a stored function is used in an assignment right hand.
Both DEFAULT clause of a variable initialization and
the right side of the SET statement are supported:
CREATE PROCEDURE p1()
BEGIN
-- case 1: DEFAULT clause
DECLARE spvar1 INT DEFAULT f1_with_ps(); -- OK
-- case 2: SP variable assignment statement
DECLARE spvar2 INT;
SET spvar2= f1_with_ps(); -- OK
END;
- Only assignments to SP variables works for now:
* SET spvar= func_with_ps(); -- OK
* SET @uvar= func_with_ps(); -- Error
- Only bare function calls are supported for now. Using a function in
an expression does not make it PS-safe yet:
SET v= f1()+0;
- The parser now does not reject PS statements in stored functions.
PS applicability in stored functions is now detected at run time.
Note, PS statements in triggers are still prohibited by the parser.
- Functions with PS do not acquire MDL locks on tables, and no MDL is
taken on the routines themselves either. They work like procedures in
terms of table opening and routine locking: a concurrent DROP FUNCTION
can complete while such a function is executing.
- Functions with PS are not replicated as a single `SELECT f1()` call.
They are replicated per-statement, like procedures.
Helper changes:
- Changing the return result for LEX::sp_variable_declarations_init()
from void to bool to catch errors in the caller properly.
Misc:
- This patch incorporates fixes for the following bugs found during debugging:
MDEV-40224,MDEV-40225,MDEV-40226,MDEV-40227,MDEV-40240,
MDEV-40285,MDEV-40288,MDEV-40315,MDEV-40318,MDEV-40890,MDEV-40900,
MDEV-40901,MDEV-40913,MDEV-40914,MDEV-41013,MDEV-41015,MDEV-41019
Assisted-by: Claude - reviews and minor clean-ups
Rename cloning methods of class Item and its descendants
in the following way:
(from) (to)
do_build_clone -> deep_copy
build_clone -> deep_copy_with_checks
do_get_copy -> shallow_copy
get_copy -> shallow_copy_with_checks
to better reflect their functionality.
Also make Item::deep_copy() and shallow_copy() protected.
Outside users should call deep_copy_with_checks()
and shallow_copy_with_checks().
Parser changes made by Alexander Barkov <bar@mariadb.com>.
Part of the tests made by Iqbal Hassan <iqbal@hasprime.com>.
Initially marking with ORA_JOIN flag made also by
Iqbal Hassan <iqbal@hasprime.com>.
Main idea is that parser mark fields with (+) with a flag
(ORA_JOIN).
During Prepare the flag bring to the new created items if needed.
Later after preparing (fix_firlds()) WHERE confition the
relations betweel the tables analyzed and tables reordered
so to make JOIN/LEFT JOIN operators in chain equivalent to
the query with oracle outer join operator (+).
Then the flags of (+) removed.
This patch adds support for associative arrays in stored procedures
for sql_mode=ORACLE.
The syntax follows Oracle's PL/SQL syntax for associative arrays -
TYPE assoc_array_t IS TABLE OF VARCHAR2(100) INDEX BY INTEGER;
or
TYPE assoc_array_t IS TABLE OF record_t INDEX BY VARCHAR2(100);
where record_t is a record type.
The following functions were added for associative arrays:
- COUNT - Retrieve the number of elements within the arra
- EXISTS - Check whether given key exists in the array
- FIRST - Retrieve the first key in the array
- LAST - Retrieve the last key in the array
- PRIOR - Retrieve the key before the given key
- NEXT - Retrieve the key after the given key
- DELETE - Remove the element with the given key or remove all elements
if no key is given
The arrays/elements can be initialized with the following methods:
- Constructor
i.e. array:= assoc_array_t('key1'=>1, 'key2'=>2, 'key3'=>3)
- Assignment
i.e. array(key):= record_t(1, 2)
- SELECT INTO
i.e. SELECT x INTO array(key)
TODOs:
- Nested tables are not supported yet.
i.e. TYPE assoc_array_t IS TABLE OF other_assoc_array_t INDEX BY INTEGER;
- Associative arrays comparisons are not supported yet.
The `Item` class methods `get_copy()`, `build_clone()`, and `clone_item()`
face an issue where they may be defined in a descendant class
(e.g., `Item_func`) but not in a further descendant (e.g., `Item_func_child`).
This can lead to scenarios where `build_clone()`, when operating on an
instance of `Item_func_child` with a pointer to the base class (`Item`),
returns an instance of `Item_func` instead of `Item_func_child`.
Since this limitation cannot be resolved at compile time, this commit
introduces runtime type checks for the copy/clone operations.
A debug assertion will now trigger in case of a type mismatch.
`get_copy()`, `build_clone()`, and `clone_item()` are no more virtual,
but virtual `do_get_copy()`, `do_build_clone()`, and `do_clone_item()`
are added to the protected section of the class `Item`.
Additionally, const qualifiers have been added to certain methods
to enhance code reliability.
Reviewer: Oleksandr Byelkin <sanja@mariadb.com>
Added back variable 'with_subquery' to Item class as a bit field.
This made the code shorter, faster (removed some virtual methods,
less code to create an initialized item etc) and made many Item's 7 bytes
smaller.
This is the last set of my patches the decreases the size of Item.
Some examples from gdb:
sizeof(Item): 144 -> 120
sizeof(Item_func) 208 -> 184
sizeof(Item_sum_max) 368 -> 344
Added back variable 'with_sum_func' to Item class as a bit field.
This made the code shorter, faster (removed some virtual methods,
less code to create an initialized item etc) and made many Item's 7 bytes
smaller.
The code is also easier to understand as 'with_sum_func' is threated as any
other Item variable when creating or copying items.
This patch introduces the optimization that allows range optimizer to
consider index range scans that are built employing NOT NULL predicates
inferred from WHERE conditions and ON expressions.
The patch adds a new optimizer switch not_null_range_scan.
Detailed: changes:
1. Moving Field specific code into new methods on Field:
- Field *Field::create_tmp_field(...)
- virtual void init_for_tmp_table(...)
2. Removing virtual Item::create_tmp_field().
Adding instead a new virtual method Item::create_tmp_field_ex().
Note, a virtual create_tmp_field() still exists, but only for Item_sum.
This resembles 10.0 code structure. Perhaps create_tmp_field() should
be removed from Item_sum and Item_sum descendants should override
create_tmp_field_ex() directly. This can be done in a separate commit.
3. Adding helper classes Tmp_field_src and Tmp_field_param,
to make the API for Item::create_tmp_field_ex() smaller
and easier to extend in the future.
4. Decomposing the public function create_tmp_field() into
virtual implementations for Item and a number of its descendants:
- Item_basic_value
- Item_sp_variable
- Item_name_const
- Item_result_field
- Item_field
- Item_ref
- Item_type_holder
- Item_row
- Item_func_sp
- Item_func_user_var
- Item_sum
- Item_sum_field
- Item_proc
5. Adding DBUG_ASSERT-only virtual implementations for
Item types that should not appear in create_tmp_table_ex(),
for easier debugging:
- Item_nodeset_func
- Item_nodeset_to_const_comparator
- Item_null_result
- Item_copy
- Item_ident_for_show
- Item_user_var_as_out_param
6. Moving public function create_tmp_field_from_field()
as a method to Item_field.
7. Removing Item::set_result_field(). It's not needed any more.
8. Cleanup: Removing the enum value "EXPR_CACHE_ITEM",
as it's not used for a very long time.
The logic and the implementation scheme are similar with the
MDEV-9197 Pushdown conditions into non-mergeable views/derived tables
How the push down is made on the example:
select * from t1
where a>3 and b>10 and
(a,b) in (select x,max(y) from t2 group by x);
-->
select * from t1
where a>3 and b>10 and
(a,b) in (select x,max(y)
from t2
where x>3
group by x
having max(y)>10);
The implementation scheme:
1. Search for the condition cond that depends only on the fields
from the left part of the IN subquery (left_part)
2. Find fields F_group in the select of the right part of the
IN subquery (right_part) that are used in the GROUP BY
3. Extract from the cond condition cond_where that depends only on the
fields from the left_part that stay at the same places in the left_part
(have the same indexes) as the F_group fields in the projection of the
right_part
4. Transform cond_where so it can be pushed into the WHERE clause of the
right_part and delete cond_where from the cond
5. Transform cond so it can be pushed into the HAVING clause of the right_part
The optimization is made in the
Item_in_subselect::pushdown_cond_for_in_subquery() and is controlled by the
variable condition_pushdown_for_subquery.
New test file in_subq_cond_pushdown.test is created.
There are also some changes made for setup_jtbm_semi_joins().
Now it is decomposed into the 2 procedures: setup_degenerate_jtbm_semi_joins()
that is called before optimize_cond() for cond and setup_jtbm_semi_joins()
that is called after optimize_cond().
New setup_jtbm_semi_joins() is made in the way so that the result of its work is
the same as if it was called before optimize_cond().
The code that is common for pushdown into materialized derived and into materialized
IN subqueries is factored out into pushdown_cond_for_derived(),
Item_in_subselect::pushdown_cond_for_in_subquery() and
st_select_lex::pushdown_cond_into_where_clause().
Renaming methods:
- Field::make_field(Send_field*) to make_send_field(..)
- Item::make_field(THD *,Send_field *) to make_send_field(..)
- Item::init_make_field(Send_field *, enum_field_type) to init_make_send_field(..)
These names looked similar to other functions that are used
for a very different purpose (creating Field instances):
- Public function "Field * make_field(..)"
- Method "Field *Column_defitinion::make_field(..)"
The rename makes it's easier to search the code using "grep".
Do not assume that it's always item->field_type() - this is not the case
in temporal comparisons (e.g. when comparing DATETIME column with a TIME
literal).
TODO:
- Make get_thd_memroot() inline
- To do this, we need to reduce dependence of include files, especially
so that sql_class.h is not depending in item.h
Make differentiation between pullout for merge and pulout of outer field during exists2in transformation.
In last case the field was outer and so we can safely start from name resolution context of the SELECT where it was pulled.
Old behavior lead to inconsistence between list of tables and outer name resolution context (which skips one SELECT for merge purposes) which creates problem vor name resolution.
This patch fills in a serious flaw in the
code that supports condition pushdown into
materialized views / derived tables.
If a predicate happened to contain a reference
to a mergeable view / derived table and it does
not depended directly on the target materialized
view / derived table then the predicate was not
considered as a subject to pusdown to this view
/ derived table.
The patch actually fixes the old defect of the optimizer that
could not extract keys for range access from IN predicates
with row arguments.
This problem was resolved in the mysql-5.7 code. The patch
supersedes what was done there:
- it can build range access when not all components of
the first row argument are refer to the columns of the table
for which the range access is constructed.
- it can use equality predicates to build range access
to the table that is not referred to in this argument.
This patch:
- Introduces a new virtuial method Type_handler::set_comparator_func
and moves pieces of the code from the switch in
Arg_comparator::set_compare_func into the corresponding
Type_handler_xxx::set_comparator_func.
- Adds Type_handler::get_handler_by_cmp_type()
- Moves Type_handler_hybrid_field_type::get_handler_by_result_type() to
a static method Type_handler::get_handler_by_result_type(),
for symmetry with similar methods:
* Type_handler::get_handler_by_field_type()
* Type_handler::get_handler_by_real_type()
* Type_handler::get_handler_by_cmp_type()
- Introduces Type_handler_row, to unify the code for the scalar
data types and the ROW data type (currently for comparison purposes only).
- Adds public type_handler_row, as it's now needed in item_row.h
- Makes type_handler_null public, as it's now needed in item_cmpfunc.h
Note, other type_handler_xxx will become public as well later.
- Removes the global variable Arg_comparator::comparator_matrix,
as it's not needed any more.
