A JOIN_TAB that reads a temporary table standing for several other
tables owned a separately allocated JOIN_TAB_RANGE describing those
tables, and the only way to ask whether a JOIN_TAB had such a range was
to test that pointer. The range is now a member of the JOIN_TAB and
carries the kind of range it is, so the question has a name,
has_bush_children. st_nested_join::nest_type becomes an enumeration for
the same reason.
Asking whether a JOIN_TAB owned a range and asking whether that range
holds the inner tables of a materialized semi join were the same
question, so every place that reaches a semi join through a range's
first JOIN_TAB relied on a materialized semi join being the only thing a
range could hold. is_sjm_nest now reads the kind, and the places that
reach a semi join that way either ask it or assert it. Places that only
care whether a range exists at all keep asking that instead.
A kind is named for the range a materialized FULL JOIN nest will hold,
which an enclosing join's condition needs so that it sees a nest's rows
complete, including the rows the nest produced by null complementing.
Nothing produces one yet, so behaviour is unchanged.
In phase 1, FULL [OUTER] JOIN was only supported when simplify_joins()
could rewrite it into an equivalent LEFT, RIGHT, or INNER JOIN based
on NULL-rejecting WHERE predicates. Queries that could not be
rewritten raised ER_NOT_SUPPORTED_YET. (Phase 1 was not released.)
This commit removes that restriction by adding proper support for FULL
JOIN by executing a 'LEFT JOIN pass' that emits matched rows and left
null-complemented rows, then a second "null-complement" pass which
rescans the right table to emit null-complement rows that were never
matched.
FULL JOIN supports nested joins on the left of the FULL JOIN,
NATURAL FULL JOIN, semi-joins, CTEs / derived tables (kept
materialized when they participate in a FULL JOIN), prepared
statements, stored procedures, and aggregates. Examples:
SELECT * FROM (d1 FULL JOIN d2 ON d1.a = d2.a)
FULL JOIN t3 ON d1.a = t3.a;
SELECT * FROM t1 NATURAL FULL JOIN t2;
SELECT * FROM t1 INNER JOIN t2 FULL JOIN t3 ON t1.a = t3.a;
PREPARE st FROM
'SELECT COUNT(*) FROM t1 FULL JOIN t2 ON t1.a = t2.a';
Limitations:
- Statistics and cost estimates for the null-complement pass have
not been fully implemented; the optimizer may under- or
over-estimate FULL JOIN costs in plans involving multiple
FULL JOINs. Again, a follow-up will optimize the cost calculations.
- Optimizations for constant tables not fully supported.
- Nested tables on the right side of a FULL JOIN are not yet supported.
Rewrite FULL OUTER JOIN queries as either LEFT, RIGHT, or INNER JOIN
by checking if and how the WHERE clause rejects nulls.
For example, the following two queries are equivalent because the
WHERE condition rejects nulls from the left table and allows matches
in the right table (or NULL from the right table) for the remaining
rows:
SELECT * FROM t1 FULL JOIN t2 ON t1.v = t2.v WHERE t1.v IS NOT NULL;
SELECT * FROM t1 LEFT JOIN t2 ON t1.v = t2.v;
SELECT * FROM t1 FULL JOIN t2 ON t1.v = t2.v WHERE t1.a=t2.a;
SELECT * FROM t1 INNER JOIN t2 ON t1.v = t2.v WHERE t1.a=t2.a;
Allow FULL OUTER JOIN queries to proceed through name resolution.
Permits limited EXPLAIN EXTENDED support so tests can prove that the
JOIN_TYPE_* table markings are reflected when the query is echoed back by the
server. This happens in at least two places: via a Warning message during
EXPLAIN EXTENDED and during VIEW .frm file creation.
While the query plan output is mostly meaningless at this point, this
limited EXPLAIN support improves the SELECT_LEX print function for the new
JOIN types.
TODO: fix PS protocol before end of FULL OUTER JOIN development
Syntax support for FULL JOIN, FULL OUTER JOIN, NATURAL FULL JOIN, and
NATURAL FULL OUTER JOIN in the parser.
While we accept full join syntax, such joins are not yet supported.
Queries specifying any of the above joins will fail with
ER_NOT_SUPPORTED_YET.
Add the counter LEX::has_full_outer_join so we can see how many FULL JOINs
are present in the query.
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
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.
removed:
* DES encryption
* --secure-auth
* --old
* spider table options: bfz, btt, cmd, ctp, cwg, isa, ilm, ios, smd, stc, stl
extended under old-mode:
* YEAR(2), still available when old-mode=2_DIGIT_YEAR
un-deprecated:
* keep_files_on_create, originally (MDEV-23570) the idea was to make it
TRUE and deprecate. It cannot be removed when FALSE, but TRUE breaks
mariabackup.aria_backup where a table is altered from Aria to InnoDB
during a backup, so both t.MAD/t.MAI and t.ibd gets into a backup.
* 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.
