This merges the patch series that fixes a lot of missing validation
checks for read_log_event(). The merge includes a few added fixes that
are needed in new code introduced in 12.3.
Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
Problem:
CREATE TABLE ... SELECT ... FOR UPDATE involving a MyISAM temporary table can trigger an assertion in close_thread_tables() when binary logging is enabled in MIXED mode.
Cause:
MyISAM temporary tables do not support row-level locking, so SELECT ... FOR UPDATE acquires a write lock even when the access is read-only. This causes the table to be incorrectly classified as a write operation, preventing binlog_truncate_trx_cache() from running and eventually triggering the assertion.
Fix:
Update decide_logging_format() to use tbl->updating to distinguish actual table modifications from read-only accesses. For read-only access to temporary non-transactional tables, mark the statement as STMT_READS_TEMP_NON_TRANS_TABLE instead of treating it as a write operation. This prevents incorrect write classification and avoids the assertion failure.
A parenthesized query expression that already has its own ORDER BY or
LIMIT and is followed by another ORDER BY, e.g.
(SELECT c1 FROM t1 ORDER BY c1 LIMIT 2) ORDER BY <expr> LIMIT 1
is wrapped into a derived table, and the outer ORDER BY/LIMIT is attached
to the wrapping select. The grammar pushed the inner select before
parsing the tail, and add_tail_to_query_expression_body_ext_parens()
created the wrapper only after the tail had been parsed. Anything that
is attached to the current select while the tail is being parsed was
therefore attached to the inner select, while the ORDER BY list itself
was moved to the wrapper:
- Window functions and their window specs were added to the inner
select's window_funcs/window_specs. The same Item_window_func was then
in the wrapper's ORDER BY and in the inner select's window function
list, which triggers the original assertion reported in MDEV-41225.
(SELECT 1 FROM t1 LIMIT 1)
ORDER BY PERCENTILE_DISC(1) WITHIN GROUP(ORDER BY TIME'0') OVER();
The function is computed by the inner select over the rows before its
LIMIT, so the outer ORDER BY sorts on wrong values
(e.g. COUNT(*) OVER () returned 3 for a 2-row result).
- Subqueries were registered as units of the inner select. An
uncorrelated subquery in the tail crashed the server with SIGSEGV when
it was executed by the outer filesort. A correlated one resolved its
outer references in the inner select (t1.c1) instead of the derived
table.
The decision to wrap depends only on whether the inner select already
has a tail and on whether the outer tail has ORDER BY. LIMIT and locking
clauses cannot contain window functions or subqueries.
We replace query_expression_tail with
query_expression_tail_with_order and query_expression_tail_no_order
so the mid-rule action knows whether an ORDER BY follows.
The new LEX::push_select_for_ext_parens_tail() creates the wrapper before
an ORDER BY tail is parsed when wrapping is required, and pushes it instead
of the inner select. The tail's items are then created in the wrapper's
context, and window functions and subqueries are attached to the wrapper.
add_tail_to_query_expression_body_ext_parens() takes the pushed select and
skips wrapping when it has already happened; the other cases keep the
previous logic. The grammar has the same number of conflicts as before.
A subquery in such a tail can no longer refer to the tables of the wrapped
query expression (e.g. t1.c1 above) and gets ER_BAD_FIELD_ERROR,
as those tables are not visible outside the derived table.
This commit was prepared with Claude Code (Opus 5.5). It traced the
wrong-result and crash cases to the parse-time registration of window
functions and subqueries in the inner select, tested the grammar split
with bison, wrote the code change and the brackets test.
Each nested BEGIN adds one sp_pcontext. Two walks over the finished
tree recursed once per nesting level and overran the thread stack.
~sp_pcontext() freed its children recursively. sp_head now threads a
single linked list through the contexts and frees them iteratively, so
teardown depth is constant and a context no longer frees its children.
retrieve_field_definitions() descends the children to build the
run-time frame. It emits them in run-time offset order, so it stays
recursive, but it now checks the stack and returns an error instead
of crashing.
MDEV-37325 unconditionally wrapped union subqueries in derived tables,
breaking name resolution of outer-scope/correlated references inside
the unions (producing ER_UNKNOWN_TABLE errors).
Delay the wrap until a following operator has a different linkage.
In Oracle mode all set operators share one priority and bind
left-to-right, so wrapping the accumulated prefix on each operator
change enforces it. This also corrects the row multiplicity of mixed
set operations toward left-to-right order.
create_priority_nest(): when the nest covers the whole prefix, point
the wrapper's first_nested at itself. Cut the prefix with
cut_next() before wrapping and re-register it on the outer unit;
Aleksey Midenkov:
In Oracle mode optimize_bag_operation() returns early, so union_distinct is
never recomputed and the stale value reaches execution.
Register the wrapper first, then run fix_distinct(): reset_distinct() now
scans the new outer chain (the wrapper alone) and correctly leaves
union_distinct NULL; a following DISTINCT operand re-establishes it.
On parsing the statement 'DECLARE var1,...,varN TYPE OF cur DEFAULT value'
an instance of the class Item_splocal that created on handling the clause
'DEFAULT value' is shared between two classes sp_instr_cursor_copy_struct and
sp_instr_set. In result, in case metadata changed and cursor has to be re-parsed,
items associated with the SP instruction sp_instr_cursor_copy_struct
are deallocated and one of deallocated items is the one representing the value
the `DEFAULT value` clause. On the other hand, the data member
sp_instr_set::m_value still references the deallocated item. It leads to server
abnormal termination on second run of a stored routine containing the statement
'DECLARE ... TYPE OF ... DEFAULT value'
To fix the issue pass owning of an instance of the class Item_splocal to
the class sp_instr_cursor_copy_struct and deallocated it explicitly on
closing the stored routine.
"NOT column" rewrites itself into "column = 0" in fix_fields().
However the rewrite is not done when the "column" is wrapped in an Item_ref.
Later, pushdown_from_having_into_where() will strip the Item_ref object
and will call
Item_func_not(Item_field())->fix_fields(thd, ref=NULL);
This call will attempt the rewrite and crash when writing into NULL pointer.
Fixed this in two places:
* Make Item_func_not::fix_fields() use real_item() and so recognize fields
wrapped in Item_ref-like objects.
* Make pushdown_from_having_into_where() pass the right pointer as
fix_fields() argument.
Problem:
Server crashes when a procedure with default parameter is executed and
default value for the parameter is evaluated after the procedure's
first execution. For example:
CREATE PROCEDURE p(x INT DEFAULT (SELECT 1)) ...
CALL p(1); -- first exec, marks mem_root read-only
CALL p(); -- tries to allocate on locked when executing DEFAULT
Fix:
Temporarily clear the read-only flag on the statement memory root to
allow the allocation. Also adds a debug hook to verify allocation
happens not more than once per prepared statement and per internal
table added to the list.
Function calls in INTERVAL expression of DDL have little
sense. SETVAL() fails because sequences require opened tables and
vers_set_interval() is called at earlier stage.
The fix throws ER_SUBQUERIES_NOT_SUPPORTED for sequence functions
SETVAL(), NEXTVAL(), LASTVAL() when the context does not allow
subselect (determined by clause_that_disallows_subselect).
Query optimization can merge derived tables (VIEWs being a type of derived
table) into outer queries, leaving behind stranded st_select_lex_unit objects
("stranded units") for post-query cleanup.
Previously, these were cleaned up recursively. For queries with many merged
derived tables, the deep recursion over the list of stranded units could
exhaust the stack. This change replaces the recursive cleanup with an
iterative loop to prevent stack overflows.
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.
Affected functions:
find_matching_field_pair(Item *item, List<Field_pair> pair_list)
get_corresponding_field_pair(Item *item, List<Field_pair> pair_list)
Both only traverse the pair_list.
They use List_iterator so we can't easily switch to using const-reference.
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().
Problem:
When parsing `a.b()`, LEX::make_item_func_or_method_call() could not find
a package body variable 'a'.
Fix:
When searching for a variable, search not only in the current parse context,
but also in the parent package body context (if exists).
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.
wrap_select_chain_into_derived() inserts wildcard asterisk over
wrapped SELECT. setup_wild() expands the item wildcard but it does not
update field_translation name which still contains the asterisk. Field
translation was implemented for BUG#5147 to keep view column names
intact, find_field_in_view() uses these field translation names.
Now, the fix uses full field list instead of the wildcard in
wrap_select_chain_into_derived() whenever it is possible.
Adding support for cursors on prepared statements.
- SQL Standard way:
DECLARE c CURSOR FOR stmt;
PREPARE stmt FROM 'SELECT ?';
OPEN c USING 1;
- Oracle-style way with SYS_REFCURSOR variables:
DECLARE
c SYS_REFCURSOR;
BEGIN
OPEN c FOR 'SELECT ?' USING 1;
* rename to follow the standard vprintf name convention
* remove `(LEX_CSTRING*) &lex_string` casts
* remove some redundant casts too
* LEX_CSTRING -> Lex_ident_db in path
* Hash_set<Sroutine_hash_entry>
* move assert out of the loop
* some comments and whitespace changes
Definition
==========
Implement the SET PATH statement.
```
SET PATH 'schema1, schema2, schema3';
```
Function
--------
In MariaDB, the SQL-path will also be used to lookup packages in
package routine invocations, so the following script:
SET PATH 'sys';
SELECT UTL_ENCODE.BASE64_DECODE('data');
invokes SYS.UTL_ENCODE.BASE64_DECODE('data'), i.e. the function
BASE64_DECODE() in the packge UTL_ENCODE in the database SYS
(if such package and package routine exist).
MariaDB implementation details
------------------------------
The ticket's requirement stated the grammar should support delimited
identifiers with backticks or double quotes within the string quote,
ie: the single tick. A simple parser was implemented to handle this
requirement in Sql_path::from_text.
Only 16 schemas are supported per session - we do not want too many
schemas in the path anyway since it will impact performance. This also
means that the path cache is a simple static array for easier memory
management.
