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.
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
escape user and host names in view frm
in fact, there is no reason to write any field as is, unescaped,
so let's remove FILE_OPTIONS_STRING in the next release
1.
Only allow #mysql50# prefix on table names that
can not be decoded from a file name to a table name.
It is an error to put this prefix on a name that can be accessed
as a valid table name without a prefix. Use `t1` not `#mysql50#t1`,
use `a-b` not `#mysql50#a@002db`.
2.
Don't allow creation of new tables with the #mysql50# prefix.
Except when in SQLCOM_ALTER_DB_UPGRADE - main.upgrade test runs
ALTER DATABASE `#mysql50#mysqltest-1` UPGRADE DATA DIRECTORY NAME;
This command moves tables from the old db name to the new db name,
and some of these tables also have old #mysql50# names.
3.
Also don't allow to create such databases, views, partitions,
subpartitions, sequences, triggers.
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
Remove the unscoped #pragma GCC diagnostic ignored
"-Wdeprecated-declarations" from include/violite.h and replace all
sprintf/vsprintf calls with snprintf/vsnprintf across the codebase.
Where possible, pass actual buffer sizes as function parameters
instead of using hardcoded constants or recomputing strlen().
Notable API changes:
- get_date(): added to_len parameter for buffer size
- nice_time(), end_timer() in client/mysql.cc: added buff_size
- generate_new_name(): added name_size parameter
- calc_md5(): added buffer_size parameter (fixes off-by-one)
- MD5_HASH_TO_STRING macro: added _size parameter
- Added MYSQL_UDF_MAX_RESULT_LENGTH constant in mysql_com.h
Also fixed a pre-existing bug in sql/rpl_mi.cc where the ellipsis
"..." was written to the wrong buffer (dbuff instead of buff).
Vendored code is excluded: extra/readline, extra/wolfssl,
wsrep-lib, libmariadb, storage/mroonga/vendor, zlib.
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().
MDEV-28650 (7a88776dc1) fixed the case when the DEFAULT flag is
inside the table definition but it can be also in DML command. When we
assign it from the table definition in mysql_make_view() we lose it in
the current command.
The fix does disjunction assignment to keep both values. It is
signalling DML_prelocking_strategy::handle_table() to call
add_internal_tables(), so it will correctly open associated sequence
table.
* 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.
default_used was missing as view is parsed on its own
lex. extend_table_list() decides maybe_need_prelocking based on
default_used and prelocking_strategy->handle_table() was skipped for
view, so internal_tables was not updated (they could be stale from
previous statement).
Query blocks have implicit names, such as `select#1`, formulated
by appending their select number to the string `select#`. This patch
allows hints to scope their applicability by implicit query block
name. For example,
SELECT /*+ JOIN_ORDER(@`select#2` t1, t2) */ ...
@`select#2` is an implicit query block name. Users can control hint
applicability per query block without first naming the blocks with
QB_NAME().
Hints may now be specified within VIEWs during their creation and
they are applied locally within that VIEW's scope. For example,
CREATE VIEW v1 AS
SELECT /*+ IGNORE_INDEX(t1 idx1) */ FROM t1 ... GROUP BY ... HAVING ...
In many cases and for some parts of the VIEW, the query plan
doesn't really depend on how the VIEW is used, so it makes sense
to control a part of the query plan from the VIEW definition.
Implicit names are not yet supported in VIEWs. Attempting to create
a VIEW with an implicit name reference will cause the server to create
the VIEW, but it will emit a warning and exclude that hint from the query.
This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.
Works as a regular stored routine variable, parameter and return value:
- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function
Note, strongly typed REF CURSOR will be added separately.
Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:
- select_results.h:
class select_result_sink
class select_result
class select_result_interceptor
- sp_cursor.h:
class sp_cursor_statistics
class sp_cursor
- sp_rcontext_handler.h
class Sp_rcontext_handler and its descendants
The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array
- A new class Statement_rcontext which contains data shared
between sub-statements of a compound statement.
It has a member m_statement_cursors of the sp_cursor_array data type,
as well as open cursor counter. THD inherits from Statement_rcontext.
- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
It is designed to store uint16 references -
positions of the cursor in THD::m_statement_cursors.
- Type_handler_sys_refcursor suppresses some derived numeric features.
When a SYS_REFCURSOR variable is used as an integer an error is raised.
- A new abstract class sp_instr_fetch_cursor. It's needed to share
the common code between "OPEN cur" (for static cursors) and
"OPER cur FOR stmt" (for SYS_REFCURSORs).
- New sp_instr classes:
* sp_instr_copen_by_ref - OPEN sys_ref_curor FOR stmt;
* sp_instr_cfetch_by_ref - FETCH sys_ref_cursor INTO targets;
* sp_instr_cclose_by_ref - CLOSE sys_ref_cursor;
* sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
the execution goes out of the BEGIN..END block
where SYS_REFCURSOR variables are declared.
- New methods in LEX:
* sp_open_cursor_for_stmt - handles "OPEN sys_ref_cursor FOR stmt".
* sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
static cursors and SYS_REFCURSORs.
* sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.
- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
* Item_func_cursor_isopen
* Item_func_cursor_found
* Item_func_cursor_notfound
* Item_func_cursor_rowcount
- A new system variable @@max_open_cursors - to limit the number
of cursors (static and SYS_REFCURSORs) opened at the same time.
Its allowed range is [0-65536], with 50 by default.
- A new virtual method Type_handler::can_return_bool() telling
if calling item->val_bool() is allowed for Items of this data type,
or if otherwise the "Illegal parameter for operation" error should be raised
at fix_fields() time.
- New methods in Sp_rcontext_handler:
* get_cursor()
* get_cursor_by_ref()
- A new class Sp_rcontext_handler_statement to handle top level statement
wide cursors which are shared by all substatements.
- A new virtual method expr_event_handler() in classes Item and Field.
It's needed to close (and make available for a new OPEN)
unused THD::m_statement_cursors elements which do not have any references
any more. It can happen in various moments in time, e.g.
* after evaluation parameters of an SQL routine
* after assigning a cursor expression into a SYS_REFCURSOR variable
* when leaving a BEGIN..END block with SYS_REFCURSOR variables
* after setting OUT/INOUT routine actual parameters from formal
parameters.
