cleanup_stranded_units() was added at the start of
st_select_lex_unit::cleanup() by 34a8209d66. This causes a
use-after-free when nested subqueries are merged into their parent
unit. With nested subqueries like:
SELECT * FROM t1
WHERE a IN (SELECT b FROM t2
WHERE a IN (SELECT c FROM t3 WHERE FALSE HAVING c < 0));
the stranded_clean_list chains the units as: Unit1 -> Unit2 -> Unit3.
Because cleanup_stranded_units() was called first, Unit1->cleanup()
would recursively trigger Unit2->cleanup(), which in turn would
trigger Unit3->cleanup(). Unit3's cleanup frees its heap-allocated
join structures. But since Unit3 was merged into Unit2, Unit2 still
holds references to Unit3's structures (e.g., st_join_table). When
control returns to Unit2 for its own local cleanup, it accesses
already-freed memory.
Fix: move cleanup_stranded_units() to the end of cleanup(). This way,
each unit completes its own local cleanup first—clearing its
references to any child structures—before triggering cleanup of its
stranded (child) units. This enforces a parent-first cleanup order.
Optimization in st_select_lex_unit::prepare() removes ORDER BY for
certain subqueries. That excludes ORDER BY items from being fixed, but
sl->window_funcs still contains window function items and those
related to optimized out ORDER BY are unfixed. The error about missing
window spec is thrown when the item is fixed. Hence we get redundant
processing of window function items without checking window spec
existence.
The fix removes the related window function items when ORDER BY is
optimized out. ORDER accumulates window_funcs at parser stage which
are then removed from SELECT_LEX::window_funcs. The fix also updates
similar optimization in mysql_make_view().
cleanup_stranded_units() was added at the start of
st_select_lex_unit::cleanup() by 34a8209d66. This causes a
use-after-free when nested subqueries are merged into their parent
unit. With nested subqueries like:
SELECT * FROM t1
WHERE a IN (SELECT b FROM t2
WHERE a IN (SELECT c FROM t3 WHERE FALSE HAVING c < 0));
the stranded_clean_list chains the units as: Unit1 -> Unit2 -> Unit3.
Because cleanup_stranded_units() was called first, Unit1->cleanup()
would recursively trigger Unit2->cleanup(), which in turn would
trigger Unit3->cleanup(). Unit3's cleanup frees its heap-allocated
join structures. But since Unit3 was merged into Unit2, Unit2 still
holds references to Unit3's structures (e.g., st_join_table). When
control returns to Unit2 for its own local cleanup, it accesses
already-freed memory.
Fix: move cleanup_stranded_units() to the end of cleanup(). This way,
each unit completes its own local cleanup first—clearing its
references to any child structures—before triggering cleanup of its
stranded (child) units. This enforces a parent-first cleanup order.
Lex::save_list contents remain from a previous query that has invalid
syntax and isn't reset when processing a new query.
We initialize this structure along with it's peer in LEX::start.
MDEV-38295 Recursive CTE usage leaks memory when not used in a top level select
During st_select_lex::cleanup() we assume that leaf tables and any
associated recursive table references can only be present when a join
structure is present. After MDEV-37220 this is no longer true.
We shift our cleanup routine out of the test for a join.
We add Multidelete_prelocking_strategy::handle_end() in the same manner
as Multiupdate_prelocking_strategy::handle_end() (but with minimal
processing as yet) to populate these leaf_tables in the outermost
select when it is a delete statement.
We add in a call to free_underlaid_joins() to the error processing section
of Sql_cmd_dml::execute to free up temporary tables that were allocated
in prepare, but not freed up when execute is bypassed because an error
occured during prepare.
Sergei Petrunia: Better comments for MDEV-38272, MDEV-38272
Approved by: Sergei Petrunia (sergey@mariadb.com)
Send ok packet earlier for SELECT queries that does not have any updates.
This is done in select_send::send_eof()
Axel's select benchmarks shows that this has a notable speed improvement:
1 Thread: 28% TPS speedup
8 Threads: 23%
64 Threads 6%
This was meassured with t_oltp_point_select With InnoDB running the client
over sockets.
Other things:
- Moved error reporting of LIMIT ROWS EXAMINED from handle_select() to
check_limit_rows_examined(). This is to ensure that the error is
reported before send_eof() is called.
- Removed duplicate "Query execution was interrupted" messages for the
same query. killed_for_exceeding_limit_rows_warning_given was introduced
for this purpose. We cannot use 'killed' flag in
killed_for_exceeding_limit_rows() to detect if we already have produced
a warning as the 'killed' in this case is reset for each union.
Reviewer: Sergei Golubchik <serg@mariadb.org>
Oracle mode has no priority between set-operators but the current
implementation with disable_index_if_needed() can not work with
arbitrary mix of distinct/non-distinct set-operators as the algorithm
is tied to the properly wrapped version of expression. Index can be
disabled only once and cannot be reenabled afterwards.
To adapt Oracle mode for the aforementioned implementation we
prioritize each set operator with parens in the order of
appearance. So the expression:
S1 op1 S2 op2 S3 op3 S4
will be rewritten like this:
(((S1 op1 S2) op2 S3) op3 S4).
The rewritten expression is valid for both Oracle and default mode and
renders the same result. Prioritizing is done with
create_priority_nest() but it was tweaked to work with derived to be
the first operand. Originally create_priority_nest() could wrap only
second operand, like this:
S1 op1 S2 op2 S3 -> S1 op1 (S2 op2 S3).
Now this behaviour is kept by non-NULL attach_to argument. With NULL
attach_to argument it produces this transformation:
S1 op1 S2 -> (S1 op1 S2), but to be strict it will look like this:
SELECT .. FROM (S1 op1 S2).
This expression is treated as the first one in the select list, so
nothing to "attach to".
On importance of not making fake_select_lex after
create_priority_nest() in parsed_select_expr_start(). This will fail
nastily in Protocol::valid_handler() check in store_longlong() for
EXPLAIN. This happens because select_options are inherited from newly
made fake_select_lex but without SELECT_DESCRIBE flag which is meant
to be assigned by mysql_explain_union() but union_needs_tmp_table() is
false (because now top-level unit has no set-operator, it is just
select from derived), so SELECT_DESCRIBE assignment is
skipped. Depending on that flag send_result_set_metadata() is
conditionally executed in JOIN::exec_inner(). And originally it is
done via select_describe() branch for top-level select when there is
no set operator (it is select from derived as explained above), not
via direct send_result_set_metadata().
On IS_OR_WAS_ORACLE. Sql_mode_save_for_frm_handling removes MODE_ORACLE
when view is registered or opened. We cannot return it back as this
conflicts with MDEV-12478 (tested by compat/oracle.func_concat). So we
are temporarily adding WAS_ORACLE flag instead. WAS_MODE_ORACLE is for
testing both WAS_ORACLE and MODE_ORACLE simultaneously.
For prepared statements with derived tables defined by CTEs, and
during second execution, there is a dangling pointer to an instance
of a JOIN object that no longer exists.
Normally, the join member of a SELECT_LEX is freed during a call to
st_select_lex::cleanup() which recursively traverses the query tree.
But due to CTE merging during mysql_derived_merge, the unit containing
the SELECT_LEX that would've been freed is cutoff from the query tree.
We now remember all such units in a linked list so that they're
cleaned up at the end of the lifetime of the query.
1. LIMIT ROWS EXAMINED clause allows to abort a query if a certain limit
of rows involved in processing is exceeded. This limitation should be only
enforced during the execution phase and not the optimization.
2. Unions are often executed using so-called "fake_select_lex" which
collects the final result from union parts. During that finalization
LIMIT ROWS EXAMINED must be ignored to avoid producing a potentially
incomplete result. There is a workaround at `st_select_lex_unit::exec()`
which deactivates the limit before fake_select_lex processing, and
re-activates it when the processing is finished. However, this re-activation
does not take into account whether the limit was active before the start
of fake_select_lex processing.
3. `st_select_lex_unit::exec()` can be invoked during the optimization
phase for evaluation of constant conditions with unions. At that time
the limit trigger is not activated. Given that the re-activation
mentioned above does not respect the previous state of the limit trigger,
a premature activation of the limit may happen.
This commit fixes that behavior by storing the state of the trigger
before its deactivation at `st_select_lex_unit::exec()` and re-activating
it only if the limit was active before.
4. This commit also removes the call to `thd->lex->set_rows_examined()`
from `st_select_lex_unit::exec_recursive()` which was probably copied
from `st_select_lex_unit::exec()`. But in the context of
`exec_recursive()` the call does not make sense as there is no
previous deactivation of the limit as at `exec()`.
Reviewed by: Dave Gosselin, Sergei Petrunia
Cleanup sequence comes after error state when not all units were prepared.
The With_element without rec_result was created for following unit:
"select 1 AS `1` union select 'x' AS x from x" the error happened
in select "select x AS x" so above unit was not prepared. The case
seems to be legal and the presence of rec_result must be checked during
the cleanup.
main/statistics_json.result is updated for f8ba5ced55 (MDEV-36099)
The test uses 'delete from t1' in many places and then populates
the table again. The natural order of rows in a MyISAM table is well
defined and the test was implicitly relying on that.
before f8ba5ced55 delete was deleting rows one by one, using
ha_myisam::delete_row() because the connection was stuck in rbr mode.
This caused rows to be shown in the reverse insertion order (because of
the delete link list).
MDEV-36099 fixes this bug and the server now correctly uses
ha_myisam::delete_all_rows(). This makes rows to be shown in the
insertion order as expected.
For all "UNION ALL" queries, the field rows_examined in the
slow query log is always being set to 0. However, this is not
the case with UNION queries although both UNION, and UNION ALL
share the same code.
The problem is that for UNION ALL queries, rows_examined field
in the thd object is not updated to the actual processed rows,
although they are being tracked in the sql_union.cc
Later, when the thd object is used to dump the examined rows
to the slow query log, it was only writing 0, as thd object was
never updated with the processed rows count.
This PR addresses the concern to write rows_examined field correctly
for UNION_ALL queries to the slow query log.
Oracle mode has different set operator precedence and handling (not by
standard). In Oracle mode the below test case is handled as-is, in
plain order from left to right. In MariaDB default mode follows SQL
standard and makes INTERSECT prioritized, so UNION is taken from
derived table which is INTERSECT result (here and below the same
applies for EXCEPT).
Non-distinct set operator (UNION ALL/INTERSECT ALL) works via unique
key release but it can be done only once. We cannot add index to
non-empty heap table (see heap_enable_indexes()). So every UNION ALL
before rightmost UNION DISTINCT works as UNION DISTINCT. That is
common syntax, MySQL, MSSQL and Oracle work that way.
There is union_distinct property which indicates the rightmost
distinct UNION (at least, so the algorithm works simple: it releases
the unique key after union_distinct in the loop
(st_select_lex_unit::exec()).
INTERSECT ALL code (implemented by MDEV-18844 in a896beb) does not
know about Oracle mode and treats union_distinct as the last
operation, that's why it releases unique key on union_distinct
operation. INTERSECT ALL requires unique key for it to work, so before
any INTERSECT ALL unique key must not be released (see
select_unit_ext::send_data()).
The patch tweaks INTERSECT ALL code for Oracle mode. In
disable_index_if_needed() it does not allow unique key release before
the last operation and it allows unfold on the last operation. Test
case with UNION DISTINCT following INTERSECT ALL at least does not
include invalid data, but in fact the whole INTERSECT ALL code could
be refactored for better semantical triggers.
The patch fixes typo in st_select_lex_unit::prepare() where
have_except_all_or_intersect_all masked eponymous data member which
wrongly triggered unique key release in st_select_lex_unit::prepare().
The patch fixes unknown error in case ha_disable_indexes() fails.
Note: optimize_bag_operation() does some operator substitutions, but
it does not run under PS. So if there is difference in test with --ps
that means non-optimized (have_except_all_or_intersect_all == true)
code path is not good.
Note 2: VIEW is stored and executed in normal mode (see
Sql_mode_save_for_frm_handling) hence when SELECT order is different
in Oracle mode (defined by parsed_select_expr_cont()) it must be
covered by --disable_view_protocol.
* rpl.rpl_system_versioning_partitions updated for MDEV-32188
* innodb.row_size_error_log_warnings_3 changed error for MDEV-33658
(checks are done in a different order)
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.
