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>
4561ccad91