Phase 2 rejected two shapes. A FULL JOIN whose right operand was a
nested join raised ER_FULL_JOIN_BASE_TABLES_ONLY, and a FULL JOIN on the
inner side of an enclosing LEFT or RIGHT JOIN raised
ER_FULL_JOIN_NOT_ALLOWED_IN_OUTER_JOIN. Both shapes are allowed now and
neither error is raised any more.
A FULL JOIN emits the rows of one side that never matched with the other
side null complemented. An enclosing join's condition has to see those
rows complete, which it cannot do while the operand is only a set of
tables spread through the join order. An operand that is a nested join
is therefore computed into a temporary table before the enclosing join
runs, so one JOIN_TAB stands for the whole operand and carries the FULL
JOIN's own marks.
The chosen plan has to keep such an operand on an unbroken span of the
join order, and that span becomes a run of JOIN_TABs of its own, the way
a materialized semi join already is. Nothing outside the operand is
positioned while the operand is computed, so a table inside it can carry
no condition that names an outside table, an equality class that spans
the boundary is split at it, and the outer join scope of a join outside
it stops there. A nest goes on naming a table that table elimination
removed, and an eliminated table is a const table, so the test for
entering the run leaves the eliminated tables out. The test for
entering a materialized semi join run reads the same way.
The operand keeps a copy of its own ON expression, since simplifying the
join tree merges an inner join's ON into the enclosing outer join's ON,
which is not equivalent for a FULL JOIN because it changes which rows of
the right side never match. An operand can itself be built from more
than one join, so an ON expression can sit a level or two below the top
of it, and every such expression is recorded on the operand as a whole,
which is the nest that reads them back. A predicate over the operand is
checked again at the entry standing for it, once the null complementing
has happened. A FULL JOIN's left side WHERE predicates are deferred to
the table that completes its right side even when that side is a nest.
The enclosing ON contributes no access path for the operand.
Reading a row back restores what a nest table cannot hold on its own,
whether the row was null complemented for a column declared NOT NULL,
and the status that says whether the table holds a usable row at all.
The JOIN_TAB standing for a run covers every table of the nest, the
eliminated ones included. The plan output therefore skips only a
JOIN_TAB that is a table of the query, since a JOIN_TAB standing for a
run is never eliminated.
The FULL JOIN code moves out of sql_select.cc, sql_base.cc and table.cc
into opt_full_join.cc and opt_full_join.h. JOIN::full_joins holds a
Full_join_info, declared in opt_full_join_info.h, for each FULL JOIN
that simplify_joins did not rewrite. It holds the two sides, the WHERE
conjuncts held back for the FULL JOIN, the tab that reads its right
side, the duplicate filter of the right rows that matched, and the next
FULL JOIN whose null complement pass runs from the same tab. It
replaces fj_dups, fj_first_target and fj_next_target on JOIN_TAB and
fj_left_cond on TABLE_LIST. The list is built for each JOIN, so a
prepared statement starts every execution with nothing held.
opt_join_runs.cc and opt_join_runs.h hold the runs.
get_best_combination records every run of the chosen join order in a
Join_run_layout before it writes any JOIN_TAB, and the layout gives the
exact number of top level JOIN_TABs. make_join_select walks the plan
with Join_tab_walk, which keeps the tables a run sets aside on entry
until the walk reaches the run's placeholder. A FULL JOIN run can be
inside a materialized semi join, so the walk keeps the runs on a stack.
make_join_select decides again which conditions wait for a FULL JOIN's
right partner, on each condition about to be attached to a table.
Distributing the WHERE builds conditions for one table out of
disjunctions that span both sides, and a HAVING predicate moved into
the WHERE can name the left side alone, so the choice made while the
WHERE is rewritten misses both. That earlier choice stays, since it
keeps the left side out of ref and range analysis. Whether a condition
reads a table after the partner is decided from the join order
positions of the tables it reads.
An enclosing join's ON supplies no key value for a table on a FULL
JOIN's left side, because the null complement pass is complete only
once every left row has been read. A key value that reads only tables
of the same left side is still allowed. An ORDER BY over a FULL JOIN
that simplify_joins kept sorts through a temporary table, because the
null complement pass emits right rows after the scan of the first
table. EXPLAIN gathers the tables of each run at its own depth, and an
operand nest inside a materialized semi join reports under that semi
join's id.
Sections 22 to 28 cover the new shapes. The section covering more than
one matching right side row becomes Section 28, which adds a nest operand
to the same case, and the two sections that came with the fixes before
this one are renumbered to follow it. Sections 35 to 42 cover an ON
expression below the top of an operand, an enclosing ON that keeps the
left side NULLs, operand nests that table elimination shrinks, a
condition offered to a table inside a run, a prepared WHERE that folds
away on a later execution, a FULL JOIN run inside a materialized semi
join, and a left side condition over a table before the partner.
Sections 8, 18 and 27 gain left side predicates that appear only once
the WHERE is rewritten. The helper that gives every column an index of
its own comes with them, ahead of the axis that turns it on.
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
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.
FULL JOIN yields result sets with columns from both tables participating in
the join (for the sake of explanation, assume base tables). However,
NATURAL FULL JOIN should show unique columns in the output.
Given the following query:
SELECT * FROM t1 NATURAL JOIN t2;
transform it into:
SELECT COALESCE(t1.f_1, t2.f_1), ..., COALESCE(t1.f_n, t2.f_n) FROM
t1 NATURAL JOIN t2;
This change applies only in the case of NATURAL FULL JOIN. Otherwise,
NATURAL JOINs work as they have in the past, which is using columns
from the left table for the resulting column set.
Allow auto partitioning by interval in PARTITION BY RANGE COLUMNS
PARTITION BY RANGE COLUMNS (col_name)
INTERVAL interval [AUTO]
(
PARTITION partition_name VALUES LESS THAN (value)
[, PARTITION partition_name VALUES LESS THAN (value) ... ]
)
where
- col_name is the name of one column of type DATE or DATETIME or
TIMESTAMP
- at least one partition is supplied, and the highest partition cannot
have MAXVALUE range
- INTERVAL interval is a positive time interval. it can be mariadb
format or oracle NUMTODSINTERVAL/NUMTOYMINTERVAL format. Like
versioning, the smallest unit is second, i.e. no subsecond like
microsecond.
- DATE column cannot have interval with values less than a day
- Subpartitions are allowed, but restricted to existing subpartition
types, i.e. [LINEAR] (KEY|HASH)
When performing one of the following DML statements on such a table,
it will first add partitions by the specified interval until the
partition covers the current time:
- INSERT
- INSERT ... SELECT
- LOAD
- UPDATE
- REPLACE
- REPLACE ... SELECT
Partition addition will not cause an implicit commit like DDL normally
does.
The partitions are named pN.
Otherwise the table behaves exactly the same as a normal RANGE COLUMNS
partitioned table.
Note that TIMESTAMP is not allowed as a type for PARTITION BY RANGE
COLUMNS otherwise.
Including the following fixes:
1.
MDEV-40088 Disallow subqueries in INTERVAL clause in range interval auto partitioning
This is consistent with system time (versioning) partitioning. Also
consistent is that both allow expressions otherwise.
2.
MDEV-40048 Allow trigger and LOCK TABLES to work with range interval auto partitioning
When a range interval auto partitioned table is the target of a
trigger, the triggering statement is not necessarily one that would
cause the auto-creation of new partitions, so we need to account for
that.
Also added support for LOCK TABLES ... WRITE.
Improved tests coverage by adapting tests from versioning.partition.
3.
MDEV-40467 Improve Y2038/2106 problem error message
When nearing the end times TIMESTAMP_MAX_VALUE, auto-partition
creation may encounter overflow when calculating new partition range.
This patch improves the message of such an overflow
Analysis:
When OLD_VALUE() is used on a view, field resolution creates an
Item_direct_view_ref instead of an Item_ref where real_item() points to the
expression. But Item_old_field::fix_fields() continues assuming that a Field
object was available, but view references do not have a field pointer,
which resulted in a crash.
Fix:
Store the real referenced item in Item_old_field::expr and use it when
the OLD_VALUE() reference does not have a Field object. This allows
OLD_VALUE() to work with view fields and avoids dereferencing a NULL
field pointer. Fix relevant methods accordingly.
- Change thd->backup_commit_lock from MDL_request* to MDL_ticket*,
and convert related call sites in handler.cc, log.cc, sql_class.cc,
and xa.cc to use MDL_ACQUIRE_LOCK.
- Convert reload_acl_and_cache() in sql_reload.cc, purge_master_logs()
and reset_master() in sql_repl.cc to MDL_ACQUIRE_LOCK, holding the
ticket in a local MDL_ticket* (these functions used a local
MDL_request originally and should not touch thd->backup_commit_lock).
- Convert acquire_lock() in partition_info.cc to MDL_ACQUIRE_LOCK.
- Add MDL_REQUEST_LIST_ADD() helper for enqueuing lock requests into
MDL_request_list, and convert call sites in sql_base.cc and sp.cc.
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.
Add new acquire_lock() overloads on MDL_context that combine MDL_request
initialization and lock acquisition into a single call, returning
MDL_ticket* directly. Convert applicable call sites across the codebase
to use the new ACQUIRE_LOCK macro.
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.
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.
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
1. `TABLE_LIST::init_derived` is called twice during derived tables
processing: first time from `mysql_derived_init` and the second
time from `mysql_derived_prepare`. Both times there is a check
for whether an optimizer hint or switch setting allows a derived
table to be merged.
However, it is not necessary to restrict merging during the
initialization call, it is enough to apply hints/switch setting
during the preparation phase.
2. `open_normal_and_derived_tables()` runs processing of all phases
of derived tables handling with a single call. But for future
commits it is required to separate DT initialization from other
phases. This commit implements the separation.
Problem:
========
A single InnoDB purge worker thread can process undo logs from different
tables within the same batch. But get_purge_table(), open_purge_table()
incorrectly assumes that a 1:1 relationship between a purge worker thread
and a table within a single batch. Based on this wrong assumtion,
InnoDB attempts to reuse TABLE objects cached in thd->open_tables for
virtual column computation.
1) Purge worker opens Table A and caches the TABLE pointer in thd->open_tables.
2) Same purge worker moves to Table B in the same batch, get_purge_table()
retrieves the cached pointer for Table A instead of opening Table B.
3) Because innobase::open() is ignored for Table B, the virtual column
template is never initialized.
4) virtual column computation for Table B aborts the server
Solution:
========
- Introduced purge_table class which has the following
purge_table: Stores either TABLE* (for tables with indexed virtual
columns) or MDL_ticket* (for tables without) in a single union
using LSB as a flag.
For tables with indexed virtual columns: opens TABLE*, accesses
MDL_ticket* via TABLE->mdl_ticket
For tables without indexed virtual columns: stores only MDL_ticket*.
trx_purge_attach_undo_recs(): Coordinator opens both dict_table_t*
and TABLE* with proper MDL protection. Workers access cached
table pointers from purge_node_t->tables without opening
their own handles
purge_sys.coordinator_thd: Distinguish coordinator from workers
in cleanup logic. Skip innobase_reset_background_thd() for
coordinator thread to prevent premature table closure during
batch processing. Workers still call cleanup to release their
thread-local resources
trx_purge_close_tables():
Rewrite for purge coordinator thread
1) Close all dict_table_t* objects first
2) Call close_thread_tables() once for all TABLE* objects
3) Release MDL tickets last, after tables are closed
Added table->lock_mutex protection when reading (or) writing
vc_templ->mysql_table and mysql_table_query_id. Clear cached
TABLE* pointers before closing tables to prevent stale pointer
access
Declared open_purge_table() and close_thread_tables() in trx0purge.cc
Declared reset_thd() in row0purge.cc and dict0stats_bg.cc.
Removed innobase_reset_background_thd()
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.
Problem:
========
A single InnoDB purge worker thread can process undo logs from different
tables within the same batch. But get_purge_table(), open_purge_table()
incorrectly assumes that a 1:1 relationship between a purge worker thread
and a table within a single batch. Based on this wrong assumtion,
InnoDB attempts to reuse TABLE objects cached in thd->open_tables for
virtual column computation.
1) Purge worker opens Table A and caches the TABLE pointer in thd->open_tables.
2) Same purge worker moves to Table B in the same batch, get_purge_table()
retrieves the cached pointer for Table A instead of opening Table B.
3) Because innobase::open() is ignored for Table B, the virtual column
template is never initialized.
4) virtual column computation for Table B aborts the server
Solution:
========
- Introduced purge_table class which has the following
purge_table: Stores either TABLE* (for tables with indexed virtual
columns) or MDL_ticket* (for tables without) in a single union
using LSB as a flag.
For tables with indexed virtual columns: opens TABLE*, accesses
MDL_ticket* via TABLE->mdl_ticket
For tables without indexed virtual columns: stores only MDL_ticket*.
trx_purge_attach_undo_recs(): Coordinator opens both dict_table_t*
and TABLE* with proper MDL protection. Workers access cached
table pointers from purge_node_t->tables without opening
their own handles
purge_sys.coordinator_thd: Distinguish coordinator from workers
in cleanup logic. Skip innobase_reset_background_thd() for
coordinator thread to prevent premature table closure during
batch processing. Workers still call cleanup to release their
thread-local resources
trx_purge_close_tables():
Rewrite for purge coordinator thread
1) Close all dict_table_t* objects first
2) Call close_thread_tables() once for all TABLE* objects
3) Release MDL tickets last, after tables are closed
Added table->lock_mutex protection when reading (or) writing
vc_templ->mysql_table and mysql_table_query_id. Clear cached
TABLE* pointers before closing tables to prevent stale pointer
access
Declared open_purge_table() and close_thread_tables() in trx0purge.cc
Declared reset_thd() in row0purge.cc and dict0stats_bg.cc.
Removed innobase_reset_background_thd()
Problem:
When a prepared statement uses `DEFAULT` with sequence, on its
second or subsequent execution, server tries to allocate TABLE_LIST
object (used for adding sequence table to `internal_tables` list) on
statement's memory root. This fails with an assert because statement's
memory root is marked read-only after the first execution.
CREATE SEQUENCE s;
CREATE TABLE t (a INT DEFAULT(NEXTVAL(s)));
PREPARE stmt FROM "UPDATE t SET a = ?";
EXECUTE stmt USING 3; -- first execution, marks mem_root read-only
EXECUTE stmt USING DEFAULT; -- tries to allocate on second execution
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.
The data in MEMORY tables is temporary and is lost when the server
restarts. Thus, when these tables are rediscovered empty,
it’s well-intentioned to record TRUNCATE statements to
the binary log, complete with an increment to the `@@gtid_binlog_pos`.
However, those unaware of this mechanism may not expect this increment
and its sign of replication divergence, namely in `@@gtid_strict_mode`.
This commit adds a binary log warning
when such a table is found `implicit_emptied`.
Since general uses of memory tables may already expect this effect,
to avoid unnecessary verbosity, this warning only emits from
`@@read_only` servers with CHANGE MASTER configured. `@@read_only`
indicates that the server should not receive changes; for a slave,
it means it only expects replication to binlog changes with GTID.
Reviewed-by: Brandon Nesterenko <brandon.nesterenko@mariadb.com>
The data in MEMORY tables is temporary and is lost when the server
restarts. Thus, it is well-intentioned to record TRUNCATE statements
to the binary log when these tables are rediscovered empty. However,
as entries with no explicit user query associated (especially the lack
of SHUTDOWN on crashes), those unaware of this mechanism can find them
unexpected, not to mention their significance downstream in replication.
This commit adds a comment to these automatically generated
TRUNCATE entries to briefly self-describe their source and purpose.
As a part of the generated query, this comment is visible together
with the TRUNCATE itself in SHOW BINLOG EVENTS and `mariadb-binlog`,
while maintaining seamlessness in replication.
There are no other changes in behaviour or storage engine API.
Reviewed-by: Andrei Elkin <andrei.elkin@mariadb.com>
Followup commit for 4802bfe4f9.
After returning error from check_vers_constants() the stack
immediately returns error status up to mysql_admin_table() where it
does close_thread_tables(). The latter expects MDL acquired.
The bug discloses the generic problem of fallback mechanism throwing
an error when the upper frame does close_thread_tables().
* 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
don't create a permanent stack of paths on the heap and in THD.
Do it on the stack like for sql_mode, sctx, abort_on_warning, and
other THD properties that often need to be changed temporarily.
Fix Sql_path constructor.
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.
Issue:
On galera write node INSERT statements does not acquire MDL locks on it's all child
tables and thereby wsrep certification keys are also added for limited tables, but
on applier nodes it does acquire MDL locks for all child tables. This can result
into MDL BF-BF conflict on applier node when transactions referring to parent and
child tables are executed concurrently. For example:
Tables with foreign keys: t1<-t2<-t3<-t4
Conflicting transactions: INSERT t1 and DROP TABLE t4
Wsrep certification keys taken on write node:
- for INSERT t1: t1 and t2
- for DROP TABLE t4: t4
On applier node MDL BF-BF conflict happened between two transaction because
MDL locks on t1, t2, t3 and t4 were taken for INSERT t1, which conflicted
with MDL lock on t4 taken by DROP TABLE t4.
The Wsrep certification keys helps in resolving this MDL BF-BF conflict by
prioritizing and scheduling concurrent transactions. But to generate Wsrep
certification keys it needs to open and take MDL locks on all the child tables.
On applier nodes Write_rows event is implicitly a REPLACE, deleting all conflicting
rows which can cause cascading FK actions and locks on foreign key children tables.
Solution:
For Galera applier nodes the Write_rows event is considered pure INSERT
which will never cause cascading FK actions and locks on foreign key children tables.
In find_field_in_view(), we call field_it.create_item() which
creates item on a statement mem_root.
Then we set its name. Make sure the name is allocated on a statement
mem_root, too.
in SIMULTANEOUS_ASSIGNMENT there is no need to switch value items
to new nullable copies of table Field's - they must refer to old
values in the row, which can never be null anyway.
skipping this redundant step simplifies moving field to record[1]
and back in fill_record()
SELECT privilege
Analysis:
When setup_fields() is called, the want_privilege is set to NO_ACL, so
correct priveleges are not checked.
Fix:
Since RETURNING requires SELECT_ACL privelige, when we are setting up
the returning fields for the given query, set want_privilege to SELECT_ACL.
Reset to original value of want_privilege once done.
table->pos_in_table_list is problematic, it can become
dangled when an SP is reparsed. Let's avoid it and directly
prefer TABLEs where a sequence is ready for inserts.
followup for 516f68af9e
