A prepared statement runs preparation again on every execution, and
preparation merges any VIEWs named in the statement. A VIEW that
appears only inside a subquery is meant to be skipped by the merge for
insert pass during mysql_handle_derived, since it is not the target of
the DELETE.
The first execution prepares with the subquery still nested, so the
view is correctly skipped, and the DELETE succeeds. Optimization then
converts the IN subquery into a semi-join and adds its tables into the
first SELECT_LEX's table list (reassigning them to that SELECT_LEX).
This conversion is meant to persist for later executions.
Then the PS's second execution prepares against the already flattened
VIEW. mysql_handle_derived now finds the subquery's VIEW among the
first SELECT_LEX's tables and tries to merge it. mysql_handle_derived
already had some existing skip logic to recognize a VIEW, but the
semijoin conversion has already merged the VIEW into the first
SELECT_LEX, so the skip doesn't correctly apply. So a nested VIEW is
merged for insert with no table, causing a crash on the NULL table
pointer.
Solution is to skip a table during mysql_handle_derived when it is the
inner side of a semijoin, recognized by its embedding semijoin nest.
Outdated table_list->derived_result after first trigger run. It was
allocated on statment mem_root and freed when trigger procedure
finishes.
Next INSERT trigger on new view in mysql_derived_prepare() jumps to
exit on error before it had a chance to allocate new derived_result.
It jumps here:
797 if ((res= sl->handle_derived(lex, DT_PREPARE)))
798 goto exit;
and the allocation is later:
837 if ((!derived->is_with_table_recursive_reference() ||
838 !derived->derived_result) &&
839 !(derived->derived_result= new (thd->mem_root) select_unit(thd)))
840 DBUG_RETURN(TRUE); // out of memory
on exit it does destruction of already freed object from previous statement:
901 exit:
...
921 if (res)
922 {
923 if (!derived->is_with_table_recursive_reference())
924 {
...
927 delete derived->derived_result;
The most obvious fix is to cleanup derived_result when the TABLE_LIST
is reinited for new use. Also as a precaution the patch does the
cleanup after delete operator.
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().
Variant 2: "Don't call optimize_stage2 too early"
The affected query had Split-Materialized derived table in another
derived table:
select * -- select#1
from (
select * -- select#2
from t1,
(select * from t2 ... group by t2.group_col) DT -- select#3
where t1.col=t2.group_col
) TBL;
The optimization went as follows:
JOIN::optimize() calls select_lex->handle_derived(DT_OPTIMIZE) which
calls JOIN::optimize() for all (direct and indirect) children SELECTs.
select#1->handle_derived() calls JOIN::optimize() for select#2 and #3;
select#2->handle_derived() calls JOIN::optimize() for select#3 the second
time. That call would call JOIN::optimize_stage2(), assuming the query
plan choice has been finalized for select#3.
But after that, JOIN::optimize() for select#2 would continue and consider
Split-Materialized for select#3. This would attempt to pick another join
order and cause a crash.
The fix is to have JOIN::optimize() not call optimize_stage2() ever.
Finalizing the query plan choice is now done by a separate call:
select_lex->handle_derived(thd->lex, DT_OPTIMIZE_STAGE2)
which invokes JOIN::optimize_stage2() and saves the query plan.
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.
This patch includes a few changes to make the code easier to maintain:
- Renamed SQL_I_List::link_in_list to SQL_I_List::insert. link_in_list was
ambiguous as it could refer to a link or it could refer to a node
- Remove field_name local variable in multi_update::initialize_tables because
it is not used when creating the temporary tables
- multi_update changes:
- Move temp table callocs to init, a more natural location for them, and moved
tables_to_update to const member variable so we don't recompute it.
- Filter out jtbm tables and tables not in the update map, pushing those that
will be updated into an update_targets container. This simplifies checks and
loops in initialize_tables.
create templates
thd->alloc<X>(n) to use instead of (X*)thd->alloc(sizeof(X)*n)
and the same for thd->calloc(). By the default the type is char,
so old usage of thd->alloc(size) works too.
Fix for MDEV-31466 - add optional derived table column names.
Column names within a SELECT_LEX structure can be left in a non-reparsable
state (as printed out from *::print) after JOIN::prepare. This caused
an incorrect view definition to be written into the .FRM file.
Fixed by resetting item list names in SELECT_LEX structures representing
derived tables before writing out the view definition.
Reviewed by Igor Babaev (igor@mariadb.com)
Extend derived table syntax to support column name assignment.
(subquery expression) [as|=] ident [comma separated column name list].
Prior to this patch, the optional comma separated column name list is
not supported.
Processing within the unit of the subquery expression will use
original column names, outside the unit will use the new names.
For example, in the query
select a1, a2 from
(select c1, c2, c3 from t1 where c2 > 0) as dt (a1, a2, a3)
where a2 > 10;
we see the second column of the derived table dt being used both within,
(where c2 > 0), and outside, (where a2 > 10), the specification.
Both conditions apply to t1.c2.
When multiple unit preparations are required, such as when being used within
a prepared statement or procedure, original column names are needed for
correct resolution. Original names are reset within mysql_derived_reinit().
Item_holder items, used for result tables in both TVC and union preparations
are renamed before use within st_select_lex_unit::prepare().
During wildcard expansion, if column names are present, items names are
set directly after creation.
Reviewed by Igor Babaev (igor@mariadb.com)
Statements affected by this bug need all the following to be true
1) a derived table table or view whose specification contains a set
operation at the top level.
2) a grouping operator (group by/having) operating on a column alias
other than in the first select of the union/intersect
3) an outer condition that will be pushed into all selects in this
union/intersect, either into the where or having clause
When pushing a condition into all selects of a unit with more than one
select, pushdown_cond_for_derived() renames items so we can re-use the
condition being pushed.
These names need to be saved and reset for correct name resolution on
second execution of prepared statements.
Reviewed by Igor Babaev (igor@mariadb.com)
Improve performance of queries like
SELECT * FROM t1 WHERE field = NAME_CONST('a', 4);
by, in this example, replacing the WHERE clause with field = 4
in the case of ref access.
The rewrite is done during fix_fields and we disambiguate this
case from other cases of NAME_CONST by inspecting where we are
in parsing. We rely on THD::where to accomplish this. To
improve performance there, we change the type of THD::where to
be an enumeration, so we can avoid string comparisons during
Item_name_const::fix_fields. Consequently, this patch also
changes all usages of THD::where to conform likewise.
For queries with derived tables populated having some side-effect, we
will fill such a derived table more than once, but without clearing
its rows. Consequently it will have duplicate rows.
An example query exhibiting the problem is
SELECT STRAIGHT_JOIN c1 FROM t1 JOIN (SELECT @a := 0) x;
Since mysql_derived_fill will, for UNCACHEABLE_DEPENDENT tables, drop
all rows and repopulate, we relax the condition at line 1204: rather
than assume all uncacheable values prevent early return, we now
allow an early return for uncacheable values other than
UNCACHEABLE_DEPENDENT. In general, we only populate derived tables
once unless they're dependent tables.
This bug led to wrong result sets returned by the second execution of
prepared statements from selects using mergeable derived tables pushed
into external engine. Such derived tables are always materialized. The
decision that they have to be materialized is taken late in the function
mysql_derived_optimized(). For regular derived tables this decision is
usually taken at the prepare phase. However in some cases for some derived
tables this decision is made in mysql_derived_optimized() too. It can be
seen in the code of mysql_derived_fill() that for such a derived table it's
critical to change its translation table to tune it to the fields of the
temporary table used for materialization of the derived table and this
must be done after each refill of the derived table. The same actions are
needed for derived tables pushed into external engines.
Approved by Oleksandr Byelkin <sanja@mariadb.com>
Consider this query
SELECT t1.* FROM t1, (SELECT t2.b FROM t2 WHERE NOT EXISTS
(SELECT 1 FROM t3) GROUP BY b) sq where sq.b = t1.a;
If SELECT 1 FROM t3 is expensive, for example t3 has >
thd->variables.expensive_subquery_limit, first evaluation is deferred to
mysql_derived_fill(). There it is noted that, in the above case
NOT EXISTS (SELECT 1 FROM t3) is constant and false.
This causes the join variable zero_result_cause to be set to
"Impossible WHERE noticed after reading const tables" and the handler
for this join is never "opened" via handler::ha_open.
When mysql_derived_fill() is called for the next group of results, this
unopened handler is not taken into account.
reviewed by Igor Babaev (igor@mariadb.com)
Statements affected by this bug have all the following
1) select statements with a sub-query
2) that sub-query includes a group-by clause
3) that group-by clause contains an expression
4) that expression has a reference to view
When a view is used in a group by expression, and that group by can be
eliminated in a sub-query simplification as part of and outer condition
that could be in, exists, > or <, then the table structure left behind
will have a unit that contains a null select_lex pointer.
If this happens as part of a prepared statement, or execute in a stored
procedure for the second time, then, when the statement is executed, the table
list entry for that, now eliminated, view is "opened" and "reinit"ialized.
This table entry's unit no longer has a select_lex pointer.
Prior to MDEV-31995 this was of little consequence, but now following this
null pointer will cause a crash.
Reviewed by Igor Babaev (igor@mariadb.com)
This commit addresses column naming issues with CTEs in the use of prepared
statements and stored procedures. Usage of either prepared statements or
procedures with Common Table Expressions and column renaming may be affected.
There are three related but different issues addressed here.
1) First execution issue. Consider the following
prepare s from "with cte (col1, col2) as (select a as c1, b as c2 from t
order by c1) select col1, col2 from cte";
execute s;
After parsing, items in the select are named (c1,c2), order by (and group by)
resolution is performed, then item names are set to (col1, col2).
When the statement is executed, context analysis is again performed, but
resolution of elements in the order by statement will not be able to find c1,
because it was renamed to col1 and remains this way.
The solution is to save the names of these items during context resolution
before they have been renamed. We can then reset item names back to those after
parsing so first execution can resolve items referred to in order and group by
clauses.
2) Second Execution Issue
When the derived table contains more than one select 'unioned' together we could
reasonably think that dealing with only items in the first select (which
determines names in the resultant table) would be sufficient. This can lead to
a different problem. Consider
prepare st from "with cte (c1,c2) as
(select a as col1, sum(b) as col2 from t1 where a > 0 group by col1
union select a as col3, sum(b) as col4 from t2 where b > 2 group by col3)
select * from cte where c1=1";
When the optimizer (only run during the first execution) pushes the outside
condition "c1=1" into every select in the derived table union, it renames the
items to make the condition valid. In this example, this leaves the first item
in the second select named 'c1'. The second execution will now fail 'group by'
resolution.
Again, the solution is to save the names during context analysis, resetting
before subsequent resolution, but making sure that we save/reset the item
names in all the selects in this union.
3) Memory Leak
During parsing Item::set_name() is used to allocate memory in the statement
arena. We cannot use this call during statement execution as this represents
a memory leak. We directly set the item list names to those in the column list
of this CTE (also allocated during parsing).
Approved by Igor Babaev <igor@mariadb.com>
