The patch detects if the processed PREPARE command is first part of the
pipelined DIRECT+EXECUTE commands with stmt id -1 for execution. For
that the vio class has been extended with peek method that lets us
verify that the server has already received next command, it is EXECUTE
with stmt id -1, and the packet is minimally complete.
If it is so, the execution is forwarded to the fused prepare+execute path
as if it was a single command. It lets a statement's execute leg run before
any real prepare/check_prepared_statement() has happened and the prepare
leg does basically query parsing only. Several places
downstream assumed that had already happened, so give Query_arena a single
predicate, is_direct_exec_unresolved() - true only for the one-shot fused
pass that never went through a real prepare - and fix each broken
assumption against it:
* Query cache: a cached response is replayed without ever running
mysql_execute_command(), so the buffered prepare-response hook never
fires; conversely, populating the cache captures that hook's bytes too
(net_real_write() sees the whole flushed buffer), poisoning the entry
for unrelated later clients. Direct-exec statements now opt out of the
query cache entirely (query_cache_is_applicable /
lex->safe_to_cache_query, saved and restored around just that one
execution). Covered by test_mdev_16670_qc_hit_hang and
test_mdev_16670_qc_poison.
* Reprepare: a mid-execute reprepare() (e.g. ER_NEED_REPREPARE) swaps in
a lex that DID go through a real prepare, so the statement is no
longer "unresolved" once that happens - tracked via a new
IS_DIRECT_EXEC_REPREPARED flag so the direct-exec special-casing only
applies to the original pass.
* System versioning / periods: skip_setup_conds() treated the fused pass
as an ordinary re-execution rather than a first resolution, so
vers_setup_conds()/period_setup_conds() never ran - a versioned table
or FOR PORTION OF statement used the wrong predicate. Now counted as
"first execute" via is_direct_exec_unresolved(). Covered by
test_mdev_16670_versioned_rows and test_mdev_16670_trigger_versioned.
* Unsupported statements check used to be done as part of the
check_prepared_statement, has been moved to the new
check_stmt_command_supported function called from the
Prepared_statement::prepare method. Covered by
test_mdev_16670_unsupported_ps.
* CALL statements and queries referencing a stored function are excluded
from the optimized path (skip_direct_exec_optimization()): their nested
sub-statements run with thd->cur_stmt still pointing at the outer
statement, which would otherwise misfire the buffered prepare-response
hook. Also, the stored function use would cause guaranteed re-prepare.
The flag is reset for them after the query parsing. Covered by
test_mdev_16670_demoted_function_call.
Also adds regression tests for the reply-pair on parse error
(test_mdev_16670_parse_error_reply_pair), SQLSTATE/errno preservation
through the execute-leg fallback (test_mdev_16670_fallback_sqlstate),
general_log coverage (test_mdev_16670_general_log), a statement killed
mid-fallback (test_mdev_16670_kill_during_fallback), and prebind usage of
mariadb_stmt_execute_direct() with real bound parameters
(test_mdev_16670_prebind_direct).
It's possible to compile with global variable opt_exec_direct that controls
if the optimization should be used. Its existence can be switched on
using cmake option WITH_DIRECT_EXEC_OPTIMIZE_SWITCH. By default it does
not exist, as it is believed to be unneeded in the general case.
create_and_prepare_stmt() helper added to perform common statement creation
routine, and erase_stmt_safely() helper - for cleaning in
create_and_prepare_stmt()'s prepare-failure cleanup, direct_exec_prepare_ok_fallback()'s
OOM branch, and mysqld_stmt_direct_execute()'s packet_error branch.
Assisted-by: Claude Sonnet 5
Allowing prepared statements in stored functions when
a stored function is used in an assignment right hand.
Both DEFAULT clause of a variable initialization and
the right side of the SET statement are supported:
CREATE PROCEDURE p1()
BEGIN
-- case 1: DEFAULT clause
DECLARE spvar1 INT DEFAULT f1_with_ps(); -- OK
-- case 2: SP variable assignment statement
DECLARE spvar2 INT;
SET spvar2= f1_with_ps(); -- OK
END;
- Only assignments to SP variables works for now:
* SET spvar= func_with_ps(); -- OK
* SET @uvar= func_with_ps(); -- Error
- Only bare function calls are supported for now. Using a function in
an expression does not make it PS-safe yet:
SET v= f1()+0;
- The parser now does not reject PS statements in stored functions.
PS applicability in stored functions is now detected at run time.
Note, PS statements in triggers are still prohibited by the parser.
- Functions with PS do not acquire MDL locks on tables, and no MDL is
taken on the routines themselves either. They work like procedures in
terms of table opening and routine locking: a concurrent DROP FUNCTION
can complete while such a function is executing.
- Functions with PS are not replicated as a single `SELECT f1()` call.
They are replicated per-statement, like procedures.
Helper changes:
- Changing the return result for LEX::sp_variable_declarations_init()
from void to bool to catch errors in the caller properly.
Misc:
- This patch incorporates fixes for the following bugs found during debugging:
MDEV-40224,MDEV-40225,MDEV-40226,MDEV-40227,MDEV-40240,
MDEV-40285,MDEV-40288,MDEV-40315,MDEV-40318,MDEV-40890,MDEV-40900,
MDEV-40901,MDEV-40913,MDEV-40914,MDEV-41013,MDEV-41015,MDEV-41019
Assisted-by: Claude - reviews and minor clean-ups
Move the initialization of context recorder, and replay after
run_set_statement_if_requested() is invoked in the
mysql_execute_command() in sql_parse.cc
This allows to record and save both Optimizer Context and Trace with
straightforward command sequence:
set optimizer_record_context=1, optimizer_trace=1;
explain select ... ;
select context from information_schema.optimizer_context;
select trace from information_schema.optimizer_trace;
- Introduce init_optimizer_context_recorder_if_needed() which is called
at query start. We n longer create the recorder lazily.
- Recorder functions no longer take MEM_ROOT to store data on as an
argument. The recorder knows the MEM_ROOT it should use.
This task loads the stats of the tables that are used in a query,
from the trace into the optimizer. This feature is controlled
by optimizer_replay_context, and points to an user defined variable name,
that has the context information.
The stats such as num_of_records present in the table, indexes if present,
their names, along with the average number of records_per_key
with in each index, cost of reading an index are loaded from the trace.
Additionally, stats from range analysis i.e. ranges, and the
corresponding number of records, along with their costs are also
loaded from the trace.
The trace context which is in JSON format is firstly set into the
user defined session variable, and this variable name is set to
optimizer_replay_context system variable.
Later, when a user issues a query, the contents of the json context is
parsed and an in memory representation is built using the class
Optimizer_context_replay. This class is then used by the optimizer
to update and save original values of the tables, indexes, and range stats
in the methods "set_statistics_for_table()" of sql_statistics.cc, and
"check_quick_select()" of opt_range.cc.
After the query gets finished, the statistics that were updated
in the optimizer are restored back to the saved original values.
The entry point for parsing the json structure is in
"mysql_execute_command()" of sql_parse.cc, and similarly exit point
i.e. to restore the saved stats of the optimizer is at the end
of the same method.
Two new warning/error messages are introduced:
ER_JSON_OPTIMIZER_REPLAY_CONTEXT_PARSE_FAILED, and
ER_JSON_OPTIMIZER_REPLAY_CONTEXT_MATCH_FAILED
This feature stores the basic stats of the base tables that are used in
a query, to the optimizer trace. This feature is also controlled by
optimizer_record_context, and is not enabled by default. The stats
such as num_of_records present in the table, indexes if present
then their names, along with the average number of records_per_key
with in each index are dumped to the trace. Additionally, stats from
range analysis of the queries are also dumped into the trace
The approach taken here is to extend the existing function
store_tables_context_in_trace() and add new dump_table_stats_to_trace()
to opt_trace_ddl_info.cc. For storing the range analysis stats into the
trace, they are first recorded in the hash defined in
Optimizer_Stats_Context_Recorder, and is used later while dumping the
stats into the trace from dump_table_stats_to_trace().
Several new tests are also added to opt_trace_store_stats.test
Binding DEFAULT to a LIMIT ? placeholder via EXECUTE ... USING is never
rejected at bind time. unit->set_limit() later evaluates it and sets
ER_INVALID_DEFAULT_PARAM in the Diagnostics_area, but SQLCOM_UPDATE (and
SQLCOM_UPDATE_MULTI, SQLCOM_INSERT_SELECT/REPLACE_SELECT) proceeded into
open_tables() regardless. A concurrent metadata change (e.g. ALTER TABLE
FORCE) then triggers Reprepare_observer::report_error(), which tries to
set ER_NEED_REPREPARE on an already-set DA and hits the assert.
Add the same thd->is_error() check after unit->set_limit() (as in
SQLCOM_DELETE), aborting before open_tables() is reached.
The "is it my own thread" check compared the login user name
(Security_context::user) and ignored the host, so u1@localhost could see
and kill threads of u1@'127.0.0.1'.
Compare the authenticated account instead:
* all comparisons are done in sctx->is_priv_user() now
* change user_matches() to priv_user_matches(), which uses is_priv_user()
* use it in KILL, KILL USER, SHOW PROCESSLIST, I_S.PROCESSLIST,
COM_PROCESS_INFO and SHOW EXPLAIN/ANALYZE FOR.
* all the remaining places use is_priv_user() directly instead
of doing strcmp: SHOW GRANTS, SHOW CREATE PROCEDURE, I_S.VIEWS,
the DEFINER clause, optimizer trace and change_security_context().
Assisted-by: Claude:claude-5-opus
WITH GRANT OPTION is not a privilege in the standard sense,
so whenever INFORMATION_SCHEMA visibility dictates "any privilege"
this does not include WITH GRANT OPTION, even though GRANT_ACL
is a privilege in MariaDB.
Assisted-By: Claude:claude-4.6-sonnet
check_stack_overrun() was compiled out under ASAN, so a deeply nested
expression recursed in Item_func::fix_fields() until the stack was
exhausted and the server crashed instead of reporting
ER_STACK_OVERRUN_NEED_MORE.
Since MDEV-34533 (Monty) the stack usage seems to be accounted correctly
under ASAN via my_get_stack_pointer(), so the check seems to works there
too.
Fix:
Remove the #ifndef __SANITIZE_ADDRESS__ guard from check_stack_overrun()
Add a test for ER_STACK_OVERRUN_NEED_MORE
SQLCOM_REVOKE_ALL needs it because SQLCOM_REVOKE does.
SQLCOM_DROP_ROLE needs it because SQLCOM_DROP_USER does.
SQLCOM_CREATE_SERVER / SQLCOM_ALTER_SERVER / SQLCOM_DROP_SERVER
need it because SQLCOM_CREATE_USER / etc do.
introduce a dedicated enum type for sql_command_flags
to simplify debugging:
(gdb) p sql_command_flags[SQLCOM_SELECT]
$2 = (CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS | CF_PREOPEN_TMP_TABLES | CF_CAN_BE_EXPLAINED | CF_OPTIMIZER_TRACE)
This patch implements support for implicit query block (QB) names in
optimizer hints, allowing hints to reference query blocks and tables
within derived tables, views and CTEs without requiring explicit
QB_NAME hints.
Examples.
-- Addressing a table inside a derived table using implicit QB name
select /*+ no_index(t1@dt) */ *
from (select * from t1 where a > 10) as DT;
-- this is an equivalent to:
select /*+ no_index(t1@dt) */ * from
(select /*+ qb_name(dt)*/ * from t1 where a > 10) as DT;
-- Addressing a query block corresponding to the derived table
select /*+ no_bnl(@dt) */ *
from (select * from t1, t2 where t.1.a > t2.a) as DT;
-- View
create view v1 as select * from t1 where a > 10 and b > 100;
-- referencing a table inside a view by implicit QB name:
select /*+ index_merge(t1@v1 idx_a, idx_b) */ *
from v1, t2 where v1.a = t2.a;
-- equivalent to:
create view v1 as select /*+ qb_name(qb_v1) */ *
from t1 where a > 10 and b > 100;
select /*+ index_merge(t1@qb_v1 idx_a, idx_b) */ *
from v1, t2 where v1.a = t2.a;
-- CTE
with aless100 as (select a from t1 where b <100)
select /*+ index(t1@aless100) */ * from aless100;
-- equivalent to:
with aless100 as (select /*+ qb_name(aless100) */ a from t1 where b <100)
select /*+ index(t1@aless100) */ * from aless100;
Limitations:
- Only SELECT statements support implicit QB names. DML operations
(UPDATE, DELETE, INSERT) only support explicit QB names
Originally two MDL_EXCLUSIVE are used for pure alias of derived table
and its schema which is empty db name. It is concurrency performance
overhead and there is no obvious reason why such MDL should exist.
MDEV-33985 changed pure alias check from `db.str` to `db.length` and
that allowed `empty_c_string` to be pure alias too. It is important
for MDEV-33985 fix with sequences. Now such kind of pure alias skips
MDL initialization and thus derived table fails falsely on BACKUP
namespace as it is default namespace for 0-filled structure (see TODO
comments).
The fix a) keeps MDL_NOT_INITIALIZED for uninitialized MDL and that
was the consequence of MDL_INTENTION_EXCLUSIVE value 0 (see TODO
comment).
And b) does not update MDL type for pure alias when view is
initialized, so it is MDL_NOT_INITIALIZED. `lock_table_names()` code
naturally skips such kind of MDL with (mdl_request.type <
MDL_SHARED_UPGRADABLE) condition when processing tables list.
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().
SHOW CREATE SERVER was accessible to any user with a global
privilege, without requiring FEDERATED ADMIN. Add a privilege
check before mysql_show_create_server() so that only users
with FEDERATED ADMIN can view server definitions.
