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.
SET sql_mode=ORACLE;
DELIMITER $$
CREATE OR REPLACE PACKAGE pkg AS
-- Declare a package public data type
TYPE varchar_array IS TABLE OF VARCHAR(2000) INDEX BY INTEGER;
END;
$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1 AS
v pkg.varchar_array; -- Use the package public data type
BEGIN
v(0):='test';
SELECT v(0);
END;
$$
DELIMITER ;
Note, the change is done only for sql_mode=ORACLE, because the TYPE
declaration is not available for the default mode.
Where package-wide types are available
--------------------------------------
- Variabe list type:
DECLARE var pkg1.type1;
- RETURN type for a package routine:
CREATE FUNCTION .. RETURN pkg1.type1 ...
- Parameter type for a package routine:
PROCEDURE p1(param1 pkg1.type1);
- Assoc array element type:
TYPE assoc1_t IS TABLE OF pkg1.type1 ...
- REF CURSOR RETURN type:
TYPE cur1_t IS REF CURSOR RETURN pkg1.type1;
Change details
--------------
- Adding a member Lex_length_and_dec_st::m_foreign_module_type
It's set to true when the data type was initialized from a TYPE
in foreign routine (e.g. in PACKAGE spec).
It's needed to prevent use of qualified identifiers in public contexts,
i.e. in schema public routine parameter types and schema publuc function
RETURN types.
Adding a helper method sp_head::check_applicability() which prevents
use of qualified types in public context.
- Adding a helper method sp_head::raise_unknown_data_type().
- Adding methods LEX::set_field_type_typedef_package_spec() for
2-step and 3-step qualified indentifiers.
It's used in field_type_all_with_typedefs which covers cases:
- Variabe list type : DECLARE var pkg1.type1;
- RETURN type : CREATE FUNCTION .. RETURN pkg1.type1 ...
- Parameter type : PROCEDURE p1(param1 pkg1.type1);
- Assoc array element type : TYPE assoc1_t IS TABLE OF pkg1.type1 ...
- Adding a method LEX::declare_type_ref_cursor_return_typedef().
It handles cases when a new TYPE REF CURSOR RETURN is declared,
for both for qualified RETURN types and non-qualified RETURN types:
- TYPE cur0_t IS REF CURSOR RETURN rec1_t;
- TYPE cur0_t IS REF CURSOR RETURN pkg1.rec1_t;
- TYPE cur0_t IS REF CURSOR RETURN db1.pkg1.rec1_t;
The code was moved from LEX::declare_type_ref_cursor() into
LEX::declare_type_ref_cursor_return_typedef() and extended
to cover qualified RETURN types.
- Adding a method Sql_path::find_package_spec_type().
It iterates through all schemas specified in @@path and searches
for the given type in the given package.
- Adding a helper method sp_pcontext::type_defs_add_ref_cursor()
to reuse the code.
- Adding a new method sp_package::get_typedef() to search
for TYPE definitions in PACKAGE specifications.
- Adding a new method sp_head::get_typedef_package_spec()
to search for TYPE definitions used by a PROCEDURE or FUNCTION.
- Adding a helper method
Sp_handler::sp_cache_routine_reentrant_suppress_errors
Adding a method Sp_handler::find_package_spec().
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.
This patch adds the following syntax:
OPEN c0 FOR LOCAL spvar_with_ps_name;
PREPARE LOCAL spvar_with_ps_name FROM 'dynamic sql';
EXECUTE LOCAL spvar_with_ps_name;
DEALLOCATE PREPARE LOCAL spvar_with_ps_name;
OPEN c0 FOR PREPARE stmt;
This statement:
OPEN c FOR 'dynamic sql'
was only allowed for SYS_REFCURSOR.
Additionally allow it for REF CURSOR with no RETURN clause, e.g.:
TYPE cur0_t IS REF CURSOR; -- No RETURN clause - OK for OPEN FOR
Note, REF CURSORs with RETURN clause are still not allowed for dynamic OPEN,
as expected.
MySQL 5.7+ supports shorthand JSON path operators as syntactic sugar:
col->'$.path' is equivalent to JSON_EXTRACT(col, '$.path')
col->>'$.path' is equivalent to JSON_UNQUOTE(JSON_EXTRACT(col, '$.path'))
This patch adds support for both operators in MariaDB.
Changes:
- sql/sql_lex.cc: Added lookahead on '-' to tokenise '->' and '->>'
as distinct tokens (JSON_ARROW and JSON_LONGVARROW).
- sql/sql_yacc.yy: Added grammar rules under primary_expr. The LHS is
restricted to primary_base_expr to avoid shift/reduce ambiguity.
Operators are left-associative with higher precedence than arithmetic.
Bison conflict counts updated from 70/71 to 72/73 accordingly.
- mysql-test/suite/json/t/mdev13594.test,
mysql-test/suite/json/r/mdev13594.result: New MTR test suite
verifying both operators across basic extraction, unquoting, nested
paths, and NULL handling. Passes with an ASAN-enabled build.
Tested by: Saahil Alam
This patch to mysqldump is only merged partially:
commit 666ff1db36
Author: Monty <monty@mariadb.org>
Date: Fri May 15 16:39:35 2026 +0300
MDEV-39519 Change mariadb-dump to use SHOW REPLICA STATUS instead of SHOW SLAVE STATUS
The functionality for --dump-slave cannot be merged as the underlying
functionality was removed in this commit:
commit 21c940d599
Author: ParadoxV5 <paradox.ver5@gmail.com>
Date: Thu Dec 4 21:22:20 2025 -0700
MDEV-37146 mariadb-dump: Replace SHOW SLAVE STATUS usage with information_schema.SLAVE_STATUS
Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
The lexer rewrites the raw query buffer when it takes a reversed executable
comment (digits -> spaces) or skips a forward/reversed comment (the '!' ->
space). With query_cache_strip_comments=OFF the cache key reads from this
buffer, so the lookup key (built before parsing) and the store key (built
after) disagree: the entry is inserted but never hit. Mark such queries as not
safe to cache to avoid polluting the cache. When query_cache_strip_comments=ON
caching remains enabled since its key comes from a separate comment-stripped
copy of the query buffer.
All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer Amazon Web
Services, Inc.
When a reversed executable comment (/*!!version */ or /*M!!version */)
is not executed on the master (server version >= specified version),
the binlog writer only neutralized the second '!' to a space, leaving
`/*! command */`, an executable comment the slave would then try to
execute.
Fix (non-executed path): also replace the first '!' with a space so the
SQL becomes /* (a plain comment). The restore path for unclosed comments
also restores both '!' characters.
Additionally, when a reversed executable comment IS executed on the
source (server version < specified version), the /*!!version syntax was
preserved verbatim in the binlog. A replica with a higher version could
re-evaluate the reversed condition, fail it, and skip the command —
causing source/replica divergence. Unlike forward executable comments
(/*!version */), where replication to same-or-higher versions guarantees
the replica also executes, reversed comments have the opposite semantics
and are not safe to preserve as-is.
Fix (executed path): overwrite the version digits with spaces in the raw
query buffer so the binlog contains /*!! (a non-versioned reversed
executable comment) which the replica always executes regardless of its
version.
Added rpl_reversed_comments.test mirroring rpl_conditional_comments.test
with reversed equivalents for all forward comment replication test cases:
mix of applied/not-applied, prepared statements, unclosed comments,
nested comments, delimiter edge case, and MariaDB-specific /*M!! syntax.
All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer Amazon Web
Services, Inc.
Reviewed-by: Brandon Nesterenko <brandon.nesterenko@mariadb.com>
This statement:
OPEN strict_cursor_variable FOR 'SELECT ...';
caused an unclear error message:
ERROR 4078: Illegal parameter data types row<2> and row<0> for operation 'OPEN..FOR'
Fixing the error to be clearer:
ERROR HY000: Incorrect usage of c0 and OPEN..FOR <dynamic string>
Note, this statement is not supported:
OPEN strict_cursor_variable FOR 'SELECT ...';
It can be rewritten:
- either to use a SELECT statement instead of the dynamic string:
OPEN strict_cursor_variable FOR SELECT ...;
- or to make c0 a weak cursor variable (i.e.without the RETURN clause)
OPEN weak_cursor_variable FOR 'SELECT ...';
The patch introduces the OLD_VALUE() expression to reference the value
of a column before it was updated. The parser is extended to support
RETURNING and OLD_VALUE(), and RETURNING expressions are stored in a
separate returning_list in SELECT_LEX with independent wildcard tracking.
RETURNING is rejected for multi-table UPDATE.
During setup of RETURNING fields, THD::is_setting_returning is used
when resolving fields, particularly for updates through views.
When resolving view fields, Item_direct_view_ref may point to the
view's item_list, losing the information about whether the value
should be old or new. The original item in returning_list still
contains the correct is_old_value_reference flag, which is copied
back to the resolved item.
OLD_VALUE() is implemented by extending Item_field with a new
Item_old_field class. Item_field::set_field() initializes
Field::ptr_old to the corresponding location in record[1],
which stores the old row during UPDATE execution.
When sending result rows, Item_old_field::send() temporarily switches
the field pointer from record[0] (current row) to ptr_old so
OLD_VALUE() returns the value before the update.
The UPDATE execution path is modified to send a result set when
RETURNING is present instead of an OK packet.
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.
Adding support for the strict cursor data types:
Example 1a:
TYPE rec0_t IS RECORD (a INT, VARCHAR(10));
TYPE cur0_t IS REF CURSOR RETURN rec0_t;
Example 1b:
TYPE rec0_t IS RECORD (a t1.a%TYPE, b t1.b%TYPE);
TYPE cur0_t IS REF CURSOR RETURN rec0_t;
Example 1c:
TYPE rec0_t IS RECORD (a INT, VARCHAR(10));
r0 rec0_t;
TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;
Example 1d:
TYPE rec0_t IS RECORD (a t1.a%TYPE, b t1.b%TYPE);
r0 rec0_t;
TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;
Example2a:
TYPE cur0_t IS REF CURSOR RETURN t1%ROWTYPE; -- t1 is a table
Example 2b:
r0 t1%ROWTYPE;
TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;
Example3a:
CURSOR cursor_sample IS SELECT a,b FROM t1;
TYPE cur0_t IS REF CURSOR RETURN cursor_sample%ROWTYPE;
Example3b:
CURSOR cursor_sample IS SELECT a,b FROM t1;
r0 cursor_sample%ROWTYPE;
TYPE cur0_t IS REF CURSOR RETURN r0%TYPE;
If a cursor variable is declared with a RETURN clause then:
1. At OPEN type the data type of the SELECT list row is compared
for compatibility with the cursor RETURN data type.
The SELECT list row must be assignable to the RETURN type row.
If case if assignability is not meet, an error is raised
Assignability means:
- The arity of the SELECT list must be equal to the arity
of the RETURN clause
- Every n-th field of the SELECT list must be assignable to the
n-th field of the RETURN Clause
2. At FETCH time, the data is fetched in two steps:
a. On the first step the data is fetched into a virtual table
with the row type described in the RETURN clause
b. On the second step the data is copied from the virtual table
to the target fetch list. Data type conversion can happen
on this step.
Change details:
Adding new methods:
- sp_cursor::check_assignability_to
- Virtual_tmp_table::check_assignability_from
- Virtual_tmp_table::sp_set_from_select_list
- Virtual_tmp_table::sp_save_in_vtable
- Virtual_tmp_table::sp_save_in_target_list
- LEX::check_ref_cursor_components
- LEX::make_sp_instr_copy_struct_for_last_context_variables
- LEX::declare_type_ref_cursor
- sp_cursor::Select_fetch_into_spvars::send_data_with_return_type
Adding new members:
- sp_instr_copen_by_ref::m_cursor_name
- Select_fetch_into_spvars::m_return_type
- Select_materialize::m_cursor_name
- Select_materialize::m_return_type
Adding new virtual methods:
- Item::resolve_spvar_cursor_rowtype
- Type_handler::Spvar_definition_resolve_type_refs
- Server_side_cursor::check_assignability_to
- Overriding Select_materialize::prepare to raise an error when the cursor
returned data type is not compatible with the RETURN clause
Making these methods virtual:
- Field::check_assignability_from
Adding new classes:
- sp_type_def_ref
- RowTypeBuffer
Adding new constructors to:
- Spvar_definition
Adding new helper methods (e.g. to reuse the code)
- Field::store_field_maybe_null
- ChanBuffer::append_ulonglong
- sp_pcontext::set_type_for_last_context_variables
Minor changes:
- Making TABLE::export_structure const
- Overriding Item_splocal::type_extra_attributes. It was forgotten in earlier changes.
Adding new error messages
- ER_CANNOT_CAST_ON_IDENT1_ASSIGNMENT_FOR_OPERATION
- ER_CANNOT_CAST_ON_IDENT2_ASSIGNMENT_FOR_OPERATION
Some data types:
- RECORD
- assoc arrays
- SYS_REFCURSOR
erroneously accepted non-relevant attributes:
- length
- scale
- character set / collation
- REF_SYSTEM_ID
Fixing to raise an error.
Details:
- Overriding get_column_attributes() in Type_handler_row,
Type_handler_sys_refcursor, Type_handler_assoc_array.
- Moving a piece of the old code into a new method
Type_handler::check_data_type_attributes(), to reuse it
for testing attributes for both plugin types and TYPE-def types.
- Reusing check_data_type_attributes() in methods
* LEX::set_field_type_udt_or_typedef()
* LEX::set_field_type_udt()
- Adding opt_binary into the rule field_type_all_with_typedefs,
into the grammar branch for the plugin types, as XMLTYPE needs it.
This is a preparatory refactoring patch to make MDEV-10152 simpler.
Under terms of MDEV-10152 we'll need LEX::sp_add_instr_fetch_cursor()
to get the target list as a parameter, to be able to rewrite it correctly
(to the data type of the REF CURSOR .. RETURN clause, instead of setting
it post-factum with help of the
method sp_instr_fetch_cursor::set_fetch_target_list().
Changes:
- Changing the result type of sp_add_instr_fetch_cursor() from
sp_instr_fetch_cursor to bool
- Renaming sp_add_instr_fetch_cursor() to sp_add_fetch_cursor()
to avoid problems during merge (the opposite return value in bool context)
- Adding a new parameter to sp_add_fetch_cursor(), so it looks like this:
bool sp_add_fetch_cursor(THD *thd,
const Lex_ident_sys_st &name,
const List<sp_fetch_target> &list);
- Adding a new target list parameter to constructors sp_instr_fetch_cursor,
sp_instr_cfetch, sp_instr_cfetch_by_ref,
- Remove the methods sp_instr_fetch_cursor::set_fetch_target_list(),
sp_instr_fetch_cursor::add_to_fetch_target_list(), as they aren't needed
any more.
- Removing from sql_yacc.yy the grammar rule sp_proc_stmt_fetch_head and
adding instead a new rule fetch_statement_source.
- Changing the grammar rule sp_proc_stmt_fetch to the following to
use the new rule fetch_statement_source.
- Adding a new helper constructor List(const T &a, MEM_ROOT *mem_root)
to create lists consisting of a single element.
- Reusing the new List constructor in a few places.
The first key point of this commit is changing Type_extra_attributes in the way
that the pointer to a generic attribute is now "Type_generic_attributes*" -
a pointer to a new class with vitrual method type_handler().
Before this change the pointer to a generic attribute was
"void *m_attr_const_void_ptr", which was error prone, as allowed
to pass a pointer to a wrong structure without control.
Another key point is that the SET/ENUM data type related structures
are now attributed by the changed class Type_typelib_attributes,
which now looks as follows:
class Type_typelib_attributes: public Sql_alloc,
public Type_generic_attributes,
public TYPELIB
(with virtual method type_handler()), instead of being attributed by
TYPELIB directly.
Details:
- Using Column_definition's method typelib() where the member m_typelib
was used directly. The related lines change anyway, because the member
is renamed m_typelib_attr.
- Adding a new class Type_generic_attributes.
Deriving the following classes from it:
* Type_typelib_attributes
* sp_type_def
- sp_type_def does not derive from Type_handler_hybrid_field_type any more,
because the method type_handler() is now implemented virtually.
- Removing Type_extra_attributes::m_attr_const_void_ptr and
adding Type_extra_attributes::m_attr_const_generic_attributes_ptr instead -
a pointer to the new class Type_generic_attributes.
Renaming methods to set and read it according to the new data type name.
- Type_typelib_attributes now derives from TYPELIB instead of having
a pointer to TYPELIB.
- Adding a new class Type_typelib_ptr_attributes. It's a replacement for
the old implementation of Type_typelib_attributes.
Instead of deriving from Type_typelib_attributes, Field_enum now derives
from Type_typelib_ptr_attributes. The latter can store/read itself into/from
Type_extra_attributes. It's a bridge between the new Type_typelib_attributes
and Type_extra_attributes.
- Changing parameter data type in a few methods in Field_enum from
a pointer to TYPELIB to a pointer to Type_typelib_attributes.
- Removing typelib related methods from Type_extra_attributes.
Moving this functionality into Type_typelib_ptr_attributes and
Column_definition_attributes.
This turns Type_extra_attributes into a data structure independent
from any data type specific structures/methods.
- Adding methods:
Column_definition_attributes::typelib_attr() - Column_definition derives it
Column_definition_attributes::typelib() - Column_definition derives it
Type_typelib_ptr_attributes::typelib_attr() - Field_enum derives it
Type_typelib_ptr_attributes::typelib() - Field_enum derives it
- Renaming the member Create_field::save_interval into
Create_field::save_typelib_attr.
Changing its data type from a pointer to TYPELIB into a pointer
to Type_typelib_attributes pointer.
- Removing the method Type_typelib_attributes::store().
Adding Type_typelib_ptr_attributes::save_in_type_extra_attributes() instead.
The new method name makes the code more readable.
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().
