Home - Waterfall Grid T-Grid Console Builders Recent Builds Buildslaves Changesources - JSON API - About

Console View


Categories: connectors experimental galera main
Legend:   Passed Failed Warnings Failed Again Running Exception Offline No data

connectors experimental galera main
ec41159ec1b6...
ec41159ec1b69a5138e0d7a53b71995e5075d064
Dave Gosselin
MDEV-38502: FULL OUTER JOIN get correct searchable condition

Fetches the ON condition from the FULL OUTER JOIN as the searchable condition.
We ignore the WHERE clause here because we don't want accidental conversions
from FULL JOIN to INNER JOIN during, for example, range analysis, as that
would produce wrong results.

GCOV shows that existing FULL OUTER JOIN tests exercise this new codepath.
eab832c336c7...
eab832c336c75e16a2a01014000d0872f6947ba6
Dave Gosselin
MDEV-38692: COALESCE() on NATURAL FULL JOIN result sets

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.
e732348ca022...
e732348ca022efc6773cd9351a30a8b58bcd3f4c
Dave Gosselin
MDEV-37932: Parser support FULL OUTER JOIN syntax

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.
ca0f5ac17b93...
ca0f5ac17b9380d7eadeefe2c525dde19cc90ad3
Dave Gosselin
MDEV-40620:  Record every FULL JOIN match on the right side

A FULL JOIN runs as a LEFT JOIN of its left side over its right side,
then a pass emits the right side rows that matched no left row.  The pass
reads a record of which right side rows matched, and a row missing from
that record is emitted as a row that matched nothing.

The WHERE predicates on the right side are held back by a guard that
stays closed until the first match of the current left side row, which is
what let the first match be recorded whatever the WHERE then made of the
row.  For a second or later matching row of the same left row the guard
is already open, so a WHERE predicate could reject the row before its
match was recorded, and the pass brought it back with the left side all
NULL.

The match is now recorded for every right side row whose match condition
holds.  Closing the guard again gives that condition on its own, since
the guard withholds exactly what is no part of the match.  Section 23
covers a left row with two matching right rows, one of them rejected by
the WHERE.  Before this change that row came back a second time with the
left side NULL, which the UNION of a LEFT JOIN and a RIGHT JOIN does not
produce.
be111d2cd60d...
be111d2cd60d035d555b71da019765cfb42ea0d3
Marko Mäkelä
MDEV-40080: innodb_log_archive=ON corruption

log_t::write_checkpoint(): Extend the the correct file.

log_t::archived_mmap_switch_prepare(): Tolerate a near-concurrent
buf_flush_archive_create() from the buf_flush_page_cleaner() thread.
bcead03af1e2...
bcead03af1e2c793321577bdba8264c79fff7e4a
Dave Gosselin
MDEV-38502: FULL OUTER JOIN get correct searchable condition

Move the temporary gate against FULL OUTER JOIN deeper into the
codebase, which causes the FULL OUTER JOIN query plans to have
more relevant information (hence the change).  In some cases, the
join order of nested INNER JOINs within the FULL OUTER JOIN changed.

Small cleanups in get_sargable_cond ahead of the feature work in
the next commit.
a6a26d8c8b7c...
a6a26d8c8b7c3fe2c88d12486d3e079195bb507c
Dave Gosselin
MDEV-38136: Prevent elimination of tables in a FULL OUTER JOIN

Prevent elimination of tables participating in a FULL OUTER JOIN during
eliminate_tables as part of phase one FULL OUTER JOIN development.

Move the functionality gate for FULL JOIN further into the codebase.

Fixes an old bug where, when running the server as a debug build and in
debug mode, a null pointer deference in
Dep_analysis_context::dbug_print_deps would cause a crash.
a3d8d9c42f17...
a3d8d9c42f17a2cce8f067f266cc0ac2ea1fad1e
Dave Gosselin
MDEV-XXXXX:  Keep an ON equality that a FULL JOIN left side reads by key

A ref access guarantees an equality only for the rows it read, so the
optimizer normally drops that equality from the outer join's ON
condition.  A table on the left side of a surviving FULL JOIN also
reaches the ON condition as a null row, produced by the pass that emits
the right side rows which never matched on the left, and the ON condition
has to reject those rows.  With the equality gone, nothing did, and a row
the outer join owed as a null complement was lost.

The equality now stays in the condition for such a table.  The existing
deferral machinery moves it onto the FULL JOIN's right side under the
found match guard, so it is checked once the match has been recorded.

The shape that reaches this is a FULL JOIN whose right side is a nested
join, which is rejected until the phase 2 gates come off, so the covering
test arrives with the queries that shape allows.
9d462b254582...
9d462b254582a8932c2e8b76d583603b9d3f8ce1
Dave Gosselin
Reject a nested join on the right of a rewritten FULL JOIN

check_full_join_base_tables runs before simplify_joins and rejects the
disallowed FULL JOIN shapes that are visible in the parse tree.
simplify_joins can rewrite a FULL JOIN to a LEFT, RIGHT, or INNER
JOIN, so sometimes disallowed queries appear only afterward.

Add check_full_join_after_simplify, called from optimize_inner once
simplify_joins is done, to reject unsupported queries after
rewrite by simplify_joins.
9b10e372060e...
9b10e372060ed71192b26e12a2805e09d5344a7b
Dave Gosselin
Prototype version of FULL OUTER JOIN

This demonstrates FULL OUTER JOIN with nests on either side

There are bugs in the implementation at this point, development ongoing

(this commit intentionally empty)
9783213d8123...
9783213d81233d8b34b24914c77ca5093e244855
Marko Mäkelä
fixup! fa407e4b21bcf8f7cf7386a2ac159f81066474e9
962bb39a1bad...
962bb39a1badb409b2c45c55f2a66e7b5cd954ad
Dave Gosselin
Update table_elim for FULL JOIN base table check

Two EXPLAIN queries in table_elim place a nested join on the right
side of a FULL JOIN.  Phase 2 supports only base tables there, so
check_full_join_base_tables rejects them with
ER_FULL_JOIN_BASE_TABLES_ONLY.
942d9c1f52d6...
942d9c1f52d6cba68e0eec8d9a170531dfaa6bbb
Dave Gosselin
MDEV-38508: Constant table detection

If a table that's in a FULL OUTER JOIN is found to be a const
table, then don't allow the constant table optimization to
take place.

Later, when we support FULL OUTER JOIN on the inner side of
other join types then we may be able to relax this restriction.
931a515d4783...
931a515d4783945aa7357bd87e2ac722d8bcc718
Dave Gosselin
MDEV-37933: Rewrite [NATURAL] FULL OUTER to LEFT, RIGHT, or INNER JOIN

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;
8ab3ce0d75f0...
8ab3ce0d75f0d460ecaf3cf438e05fedb37880e8
Dave Gosselin
MDEV-40620:  Add optimizer switches for the four join transformations

Four switches, all on by default, so behavior does not change.  Each one
turns off a transformation that simplify_joins performs, which helps
narrow down where a wrong result comes from.

  full_join_rewrite    rewriting a FULL JOIN as a one sided outer join
                      when the WHERE clause rejects NULLs on one of its
                      operands.  Off keeps the FULL JOIN and forces the
                      null complement pass to run.
  outer_join_to_inner  converting an outer join to an inner join when a
                      conjunctive predicate rejects NULLs for one of
                      its inner tables.
  flatten_join_nests  replacing a join nest that carries no ON
                      expression with its children.  Off keeps the
                      nesting the parser produced, which costs plan
                      quality without changing results.
  simplify_joins      an umbrella over the other three.

Each decision moved out of the code that carries it out, so a switch has
one place to read.  classify_full_join already held the whole FULL JOIN
decision.  The outer join conversion shared a block with work that runs
for a plain inner join as well, so that decision moved to
classify_outer_join, and taking the dependencies of the enclosing nest,
moving the ON expression into the WHERE clause, and dropping the outer
join marks now happen both for a converted join and for one that was
already inner.  Registering a semi-join nest moved to
register_semijoin_nest, which always runs since it is the only producer
of the semi-join nest list, leaving the flattening test on its own.

Gating the call to simplify_joins rather than its three decisions would
give wrong results, since that pass is the only producer of the nest
attributes and table dependencies the rest of the optimizer reads.
join_transform_enabled holds the umbrella relationship in one place.

subselect_exists2in turns every switch off by rewriting the whole
optimizer_switch string, so it now turns flatten_join_nests off as well,
and the nest it leaves in place shows up in one line of that test's
expected output.  One switch list result file also gained
reorder_outer_joins, which had been left out when that switch was added.
84e0edc1f5cd...
84e0edc1f5cda79dae97a417b0a6ccded38e577a
Dave Gosselin
MDEV-XXXXX:  Save and restore the status of tables inside a run

Walking back over the tables a join buffer scan feeds from takes a copy
of each table's status and clears it, then puts the copy back when the
scan closes.  For a table standing for a run of other tables, the tables
in that run were left out of the copy but were still written to when the
copy was put back, so they received a value the copy had never taken.
They now take part in both directions.

The work for one table moved into a function that reaches the tables of
any run it stands for, however deeply those runs nest, which is what the
rest of the join order walking already does.

This is a defect in the handling of a materialized semi join, which is
the only kind of run a released version produces, so it is not specific
to FULL JOIN.  No query is known to reach it today.  The FULL JOIN work
that follows produces runs that nest, which reaches it.
829f3a2d1c56...
829f3a2d1c56274440a317b39c49a83ee3306826
Marko Mäkelä
Add POSIX_FADV_DONTNEED hints
7c099cddf148...
7c099cddf14890db8c8255eacbc040ae7ad98505
Dave Gosselin
MDEV-40620:  Drop the rowid filter for the FULL JOIN null complement scan

The pass that emits the right side rows of a FULL JOIN which never
matched reads the right table with a plain sequential scan rather than
the key lookup the plan chose.  A rowid filter belongs to that lookup
because it holds the primary keys the lookup was allowed to return, and
an engine is entitled to assume it never sees one outside an index read.
InnoDB asserts on !prebuilt->index->is_primary() when it does.

The filter is set aside for the length of the scan and put back once the
scan is closed, which is what the executor already does where it swaps a
key lookup for a table scan while running a subquery.  Section 24 covers
a plan that reads the right table by key with a filter on a second index.
6e51992d4960...
6e51992d4960b2fed52b3b8408d13243c2131729
Dave Gosselin
MDEV-39936:  Free FULL JOIN duplicate filters on allocation failure

alloc_full_join_duplicate_filters allocates one duplicate filter for
each right side FULL JOIN table in a range of join tabs.  When a later
allocation in the range failed, the filters created earlier in the same
call stayed allocated and leaked.

Free the filters created so far before returning the failure, both when
a recursive call for a bush child fails and when a filter's own
allocation or initialization fails.  A failed call now leaves no filters
allocated.
6cf21155fdf1...
6cf21155fdf16b9f915fee0bfe60537edb487f05
Marko Mäkelä
One POSIX_FADV_DONTNEED per file
68a6feda8676...
68a6feda8676e7706624ed812d13576f365bae0d
Dave Gosselin
MDEV-40620:  Split the FULL JOIN rewrite decision from its bookkeeping

Deciding whether a FULL JOIN can become a one sided outer join was mixed
with work that has to happen whatever that decision is.  The descent into
the left operand and the operand swap that the null complement pass
depends on were interleaved with the rewrite itself, so neither could be
turned off on its own.

The decision now lives in classify_full_join, which reads no table state
and changes nothing.  The descent moved to
simplify_full_join_left_operand and the survival handling to
keep_full_join.  This prepares an optimizer switch that turns the rewrite
off for debugging.

The branch that clears not_null_tables for a surviving FULL JOIN
asserted that conditions had moved out of the left operand, which held
only while the rewrite was always attempted and moved conditions were the
sole reason it could be declined.  The clearing is right whatever the
reason was, so the assert is gone and the comment states the invariant
without naming a cause.  Behavior is unchanged.
68683b7cfb3e...
68683b7cfb3e978a43e17855716bad0ae32c9cfd
Marko Mäkelä
MDEV-40791 SET GLOBAL innodb_log_archive=OFF triggers full flush

log_t::set_archive(): When a checkpoint needs to be forced, set a
minimum target that guarantees progress, instead of flushing
the entire buffer pool. Remove some duplicated reads of
last_checkpoint_lsn.

When switching innodb_log_archive from OFF to ON,
accurately remember whether any log records may have been
written with get_sequence_bit() == 0, to ensure that a
checkpoint will be waited for on a subsequent switch
from ON to OFF.

log_t::set_recovered(): If innodb_log_archive=ON, only make a
future set_archive(false) trigger a checkpoint if we may have
recovered a server that was killed between set_archive(true)
and write_checkpoint().
6558a205f70e...
6558a205f70efb9266e5dc810430a7537db530c4
Dave Gosselin
MDEV-39014: FULL JOIN Phase 2

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.
60a3cbe758a0...
60a3cbe758a03057e56ed0fa09a25570def78e1c
Dave Gosselin
MDEV-39936:  Defer left side WHERE predicates of a surviving FULL JOIN

A FULL JOIN runs as a LEFT JOIN of its left side over its right side,
followed by a pass that emits the right rows that never matched a left
row.  That second pass is correct only if the first pass records every
left to right match, so it must read every left row and reach the right
side for each match.

A WHERE predicate that references only the left side was applied
directly during the first pass.  It pruned left rows in the nested loop,
and it could build a ref or range access on the left side.  Either way a
left row was dropped before its match was recorded, and the matching
right row then reappeared in the second pass as a right-only row.  In a
FULL JOIN the left side is null complemented in the right-only rows just
as the right side is in the left-only rows, so its predicates are inner
side predicates and must be deferred the same way.

Before access selection, lift the WHERE conjuncts that reference a
surviving FULL JOIN's left side but not its right side out of the WHERE
and hold them on the right side partner.  Removed from the WHERE they
build no access on the left side, so it is read in full.
make_join_select reattaches them to the right partner under the found
match guard, so they apply only after the match is recorded.  A conjunct
that also references the right side stays in place, since the right side
already defers it.

Also tighten List_iterator::swap_next to assert that it is positioned on
a valid element instead of returning nullptr, since the FULL JOIN
rewrite only calls it in that state.
60988aac6694...
60988aac6694705a57fc21978ae05ae505cb327e
Dave Gosselin
Remove unnecessary and unused 'top' parameter from simplify_joins.
5d29a3cb35a5...
5d29a3cb35a5f078b92fa246965227d537f688e0
Alexander Barkov
MDEV-39563 Implement UPDATE ... RETURNING ... INTO

Adding support for UPDATE .. RETURNING .. INTO queries.

For example:

  UPDATE t1 SET a=10,b=20 RETURNING a,b INTO va,vb;
  UPDATE t1 SET a=10,b=20 RETURNING a,b INTO @a,@b;

Limitations:
1. These types of queries:
  - REPLACE .. RETURNING .. INTO
  - DELETE .. RETURNING .. INTO
  - INSERT .. RETURNING .. INTO
  do not work - they return an error.
  They will be implemented separately, when needed.

2. UPDATE..RETURNING..INTO with --binlog_format=statement is not allowed
  and an error is raised.

3. Using OLD_VALUE(col) inside UPDATE..RETURNING..INTO is not allowed
  and an error is raised.

Notes:

1. ANALYZE and EXPLAIN
  Both
    ANALYZE UPDATE .. RETURNING .. INTO ..
    EXPLAIN UPDATE .. RETURNING .. INTO ..
  return this error:
    'RETURNING..INTO' is not allowed in this context

2. Behavior on no data

  a. If the updated table contains no rows, no errors are raised.

  b. In case of degenerated plans (WHERE 1=0, LIMIT 0),
    no errors are raised.

  c. If there are some rows, but non of them match the WHERE condition,
    then this error is raised:
      No data - zero rows fetched, selected, or processed

  d. If some rows where found but none of them actually
    got changed by the SET, still this error is raised:
      No data - zero rows fetched, selected, or processed
    The error message might be misleading. However, if we read
    it as "zero rows [that required updates] fetched", it looks OK.
    Let's don't introduce a new error message for now.

Helper changes:

1. The grammar in analyze_stmt_command was changed to have
  LEX::analyze_stmt set to true earlier, so
  LEX::set_returning_into_result() already knows if this
  is an ANALYZE statement.

2. The Sql_cmd_update constructor is now called earlier in the grammar,
  to be able to call Sql_cmd_update::set_with_old_value_items()
  in the SET and RETURNING clauses.

3. Sql_cmd_dml::lex is now set during the constructor time.
  It makes things easier:
  - Sql_cmd_update::returns_result_set() needs the lex.
  - Sql_cmd_delete::orig_multitable and Sql_cmd_update::orig_multitable
    are not needed any more.
    They were used only in Sql_cmd_delete::sql_command_code() and
    Sql_cmd_update::sql_command_code().
    Sql_cmd_dml::sql_command_code() now returns lex->sql_command.
    The overrides Sql_cmd_delete::sql_command_code() and
    Sql_cmd_update::sql_command_code() were removed.
5752af996c4d...
5752af996c4db0116fb9294d2ca817bbce976566
Dave Gosselin
MDEV-37995: FULL OUTER JOIN name resolution

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
4611612bd8c7...
4611612bd8c768c93a2eb55279d2574332d807c2
Dave Gosselin
MDEV-XXXXX:  Keep the last top level base table search in bounds

Attaching a condition to the last table of a join walks back from the end
of the join order looking for the last table of the top level plan.  The
search decremented an unsigned index without a lower bound, so a plan
whose last entries all stand for runs of other tables ran the index past
zero and the walk read memory before the array.

The search now stops at the start of the array.

The shape that reaches this is a FULL JOIN with a nested join on both
sides, which is rejected until the phase 2 gates come off, so the
covering test arrives with the queries that shape allows.  The defect
itself is older than the FULL JOIN work and does not depend on it.
428079cb68ac...
428079cb68acf579c80c018e9777080ce29a3c92
Dave Gosselin
MDEV-40620:  Run the FULL JOIN tests over more engines and switches

full_join.test runs once for every point of three crossed axes, the
optimizer switch axis in full_join.combinations, the storage engine axis
in full_join_engine.combinations and the index axis in
full_join_index.combinations, thirty runs in all.  The result file in the
tree is the run with the default engine, no added indexes and the
switches at their defaults.  Every other run's expected output is a diff
against it, so a run whose output matches it has no file of its own, and
a query added to the test shows up in all thirty runs.

The index axis gives every column of every base table a non-unique index
on itself.  The work is decided by what the database holds at the point
full_join_add_indexes.inc is sourced, so a section sources it after the
tables it creates.  A non-unique index is the only kind that can be added
without knowing the data, since a primary key or a unique index would
fail on the duplicates and the NULLs that many sections put in a join
column on purpose.

The switch axis holds the four switches at their defaults and each of
them off in turn, which is what makes a wrong result attributable to one
transformation.
3eadd9946ba2...
3eadd9946ba22cb5aac1d2b786c4fdc90252c2ec
Dave Gosselin
Address Monty's Phase 2 Review Feedback
3939e9780f2b...
3939e9780f2b8df26d3af5f762107ced183f6124
Dave Gosselin
MDEV-40620:  Name the kind of range a JOIN_TAB stands for

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.
319f6ce9bfa2...
319f6ce9bfa21f0be0c0a97dee7582a60552c72c
Dave Gosselin
MDEV-39967:  no bug, but preserve test case
2c0c3be6a7cb...
2c0c3be6a7cbd38269f7f7f971ef915a80342f56
Dave Gosselin
MDEV-39936:  Preserve a FULL JOIN when its left nest moves conditions

The rewrite of a FULL JOIN to a RIGHT JOIN lost rows when the left
operand was a nested join.  rewrite_full_outer_joins recurses into the
left nest, and that recursion can move the ON conditions of the nest's
inner join children into the WHERE clause.  Those moved conditions
filter the nest's rows correctly only while the nest stays on the outer
side of the join.

The rewrite to a RIGHT JOIN makes the nest the inner side of the
resulting LEFT JOIN, where the moved conditions reject its null
complemented rows and drop them.  Detect the move by comparing the WHERE
pointer before and after the recursion, since every move reassigns it.
When a condition moved, skip the rewrite and let the FULL JOIN survive,
and zero not_null_tables so the caller does not turn the surviving FULL
JOIN into an inner join.
218642dc5646...
218642dc5646064392d8d3512e5140471c5ae093
Dave Gosselin
MDEV-39569: Skip FULL JOIN rewrite to inner side of an outer join

Prevent simplify_joins from rewriting a chained FULL JOIN into a query
where a FULL JOIN could end up on the inner side of another outer
join.  Of course, this means that we will have a null complement pass
that the rewritten query would have avoided.  Once we support FULL
JOINs on the inner side of outer joins, in phase 3, then we can relax
this constraint.
1f8a280ec0ae...
1f8a280ec0ae4827599d07453b81e7291064e6f5
Dave Gosselin
MDEV-40620:  Let the join order walks cross nested runs

A JOIN_TAB that stands for a run of other JOIN_TABs was only ever the
result of a materialized semi join, and a semi join cannot hold another,
so the functions that walk the join order were written for runs that
never nest.  Entering a run descended one level, leaving one popped one
level, and the test for having run off the end compared against the
join's own plan even while the walk was inside a run held elsewhere.  The
breadth first walk read a JOIN_TAB with no owning run as being in the
join's own plan, stepped out of a run by assuming the JOIN_TAB the run
hangs from sits in that plan, and searched only that plan for the next
run to enter, so a run held inside another was never reached at all.

The walks now enter and leave as many runs as they stand at the edge of,
and each bounds itself by the run it is actually in.  The comparison that
orders two tables by their place in the join order brings both to a
common run before comparing.  The breadth first walk works from the run
holding the current JOIN_TAB, steps within that run while it has more,
enters the runs its JOIN_TABs stand for, and when a run has none left to
enter carries on just past the JOIN_TAB that run hangs from.

Nothing produces a nested run yet, so every loop still runs once and the
order is unchanged.
1f48be3ae10f...
1f48be3ae10f0edc18142f441607258bb1221b41
Dave Gosselin
MDEV-40620:  Allow a nested join as a FULL JOIN operand

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.

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.  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.

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.
18537f146cc3...
18537f146cc3e77298dd7da445bdd260854b7258
Dave Gosselin
MDEV-40620:  Null complement a merged derived table inside a FULL JOIN

A FULL JOIN's null complement pass marks the whole opposite operand as a
null row before it emits a row of the side it rescans.  The walk that
marks the operand treated a TABLE_LIST as either a plain table or a nest.
A merged derived table or view is both, its own placeholder and the
tables it was merged into, so the walk marked the placeholder and
stopped.  The query reads the fields of the merged tables, which still
held the values of an earlier row, and those values reached the client
where NULL belonged.

The walk now descends into the nested tables as well as marking the
placeholder.  Section 22 covers the shape with the flattening of join
nests turned off, which leaves the derived table in a nest of its own so
the walk reaches it.  Before this change the FULL JOIN returned the left
side value 3 against the right side rows 3 and 4, where the UNION of a
LEFT JOIN and a RIGHT JOIN returns NULL.
11d5d3d0af32...
11d5d3d0af32487fc05b3f0e60251f0100fe161e
Dave Gosselin
MDEV-39746: FULL JOIN with a nested join on the right loses rows

The outermost FULL JOIN's right operand can be a nested join rather
than a single base table.  The parser places the nest on the right
when the outermost FULL JOIN's ON is the last one written, because the
parser keeps the outermost FULL JOIN pending until its ON arrives, and
the inner FULL JOINs reduce first into a nest that becomes the right
operand.
alloc_full_join_duplicate_filters allocates the fj_dups filter on a
JOIN_TAB carrying JOIN_TYPE_FULL | JOIN_TYPE_RIGHT, so with the
FULL|RIGHT bits on the nest, which is never a JOIN_TAB, no filter was
allocated and the null complement pass never fired.  The unmatched
rows from the right side were never emitted, producing a result with
missing rows.

Add swap_full_join_sides, called from rewrite_full_outer_joins
when a FULL JOIN survives simplify_joins with a leaf on the left
and a nested join on the right.  FULL JOIN is symmetric on its
operands, so swapping does not change query semantics; after the
swap the leaf carries the FULL|RIGHT bits and the rescan target
is a single base table.
0bf99d18c414...
0bf99d18c41451ea7fe0eabd4329fa351a4d804c
Marko Mäkelä
fixup! 9783213d81233d8b34b24914c77ca5093e244855
BuildBot (0.8.9) working for the MariaDB project.
Page built: Thu 20 Aug 2026 20:28:18 (UTC)