In an extended set operation (select_unit_ext, used when EXCEPT ALL or
INTERSECT ALL is present) the HEAP unique index is released once, at
the node pointed to by union_distinct, assuming every operation after
it is a plain UNION ALL that can be unfolded. That does not hold for a
trailing EXCEPT ALL: select_unit_ext::send_data() still calls
find_unique_row() for it (only the UNION ALL branch checks
is_index_enabled), so it dereferenced the already released index and
the server crashed in hp_rec_key_cmp().
optimize_bag_operation() now postpones the index release past such a
trailing EXCEPT ALL, so the index is available while it is executed.
Keeping the index alive uncovered a second problem. A leading
INTERSECT that stays inline in the extended operation (instead of being
materialized in a derived table) left the rows that did not match every
INTERSECT operand in the temporary table. select_unit::send_eof()
removes them for a materialized subsequence, but the select_unit_ext
override did not, so once the crash was gone the following operations
counted those stale rows and produced a wrong multiset.
select_unit_ext::send_eof() already scans the temporary table (to reset
the duplicate counter, to fold INTERSECT ALL counters, or to unfold),
so the filtering of the non-matching INTERSECT records is done inside
those loops (guarded by the filter_intersect flag) and the table is
still scanned only once.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
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.
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.
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.
The problems were that:
1) resources was freed "asimetric" normal execution in send_eof,
in case of error in destructor.
2) destructor was not called in case of SP for result objects.
(so if the last SP execution ended with error resorces was not
freeded on reinit before execution (cleanup() called before next
execution) and destructor also was not called due to lack of
delete call for the object)
Result cleanup() renamed to reset_for_next_ps_execution() to better
reflect function().
All result method revised and freeing resources made "symetric".
Destructor of result object called for SP.
Added skipped invalidation in case of error in insert.
Removed misleading naming of reset(thd) (could be mixed with
with reset()).
When UNION ALL is used with LIMIT ROWS EXAMINED, and when the limit is
exceeded for a SELECT that is not the last in the UNION, interrupt the
execution and call end_eof on the result. This makes sure that the
results are sent, and the query result status is conclusive rather
than empty, which would cause an assertion failure.
on disable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE) the engine does
not know that the long unique is logically unique, because on the
engine level it is not. And the engine disables it,
Change the disable_indexes/enable_indexes API. Instead of the enum
mode, send a key_map of indexes that should be enabled. This way the
server will decide what is unique, not the engine.
Found memory leaks were introduced by the commit
a896bebfa6
MDEV-18844 Implement EXCEPT ALL and INTERSECT ALL operations
and caused by using a statement arena instead a runtime arena for
allocation of objects having temporary life span by their nature.
Aforementioned memory leaks were produced by running queries
that typically use select with intersect, union or table values
constructors.
To fix these memory leaks use the runtime arena for allocation
of Item_field objects used by set operations.
Additionally, OOM handling added on allocation of aforementioned
Item_field objects.
This patch fixes the issue with passing the DEFAULT or IGNORE values to
positional parameters for some kind of SQL statements to be executed
as prepared statements.
The main idea of the patch is to associate an actual value being passed
by the USING clause with the positional parameter represented by
the Item_param class. Such association must be performed on execution of
UPDATE statement in PS/SP mode. Other corner cases that results in
server crash is on handling CREATE TABLE when positional parameter
placed after the DEFAULT clause or CALL statement and passing either
the value DEFAULT or IGNORE as an actual value for the positional parameter.
This case is fixed by checking whether an error is set in diagnostics
area at the function pack_vcols() on return from the function pack_expression()
MDEV-32441 SENT_ROWS shows random wrong values when stored function
is selected.
MDEV-32281 EXAMINED_ROWS is not populated in
information_schema.processlist upon SELECT.
Added ROWS_SENT to information_schema.processlist
This is to have the same information as Percona server (SENT_ROWS)
To ensure that information_schema.processlist has correct values for
sent_rows and examined_rows I introduced two new variables to hold the
total counts so far. This was needed as stored functions and stored
procedures will reset the normal counters to be able to count rows for
each statement individually for slow query log.
Other things:
- Selects with functions shows in processlist the total examined_rows
and sent_rows by the main statement and all functions.
- Stored procedures shows in processlist examined_rows and sent_rows
per stored procedure statement.
- Fixed some double accounting for sent_rows and examined_rows.
- HANDLER operations now also supports send_rows and examined_rows.
- Display sizes for MEMORY_USED, MAX_MEMORY_USED, EXAMINED_ROWS and
QUERY_ID in information_schema.processlist changed to 10 characters.
- EXAMINED_ROWS and SENT_ROWS changed to bigint.
- INSERT RETURNING and DELETE RETURNING now updates SENT_ROWS.
- As thd is always up to date with examined_rows, we do not need
to handle examined row counting for unions or filesort.
- I renamed SORT_INFO::examined_rows to m_examined_rows to ensure that
we don't get bugs in merges that tries to use examined_rows.
- Removed calls of type "thd->set_examined_row_count(0)" as they are
not needed anymore.
- Removed JOIN::join_examined_rows
- Removed not used functions:
THD::set_examined_row_count()
- Made inline some functions that where called for each row.
