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>
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
The attached_condition field of the produced json text had
"String with illegal unicode symbol" instead of the proper unicode
value.
The reason is that, the item field when being written to the json
writer in write_item(), used a String with charset my_charset_bin,
and that prevented the string from being escaped.
Solution is to change: -
1. charset to my_charset_utf8mb4_bin in sql_explain.cc.
2. Item::print_value(), handle string type specially if
the charset is my_charset_bin by appending hex value.
Expose InnoDB's Adaptive Hash Index (AHI) statistics through ANALYZE
FORMAT=JSON output and global status variables to provide query-level
and system-level visibility into AHI usage and effectiveness.
This allows DBAs and developers to monitor how well
the adaptive hash index is serving their workloads on a per-query basis.
The r_ahi_stats object (nested inside r_engine_stats) now reports four
key metrics: ahi_searches (successful AHI lookups), ahi_searches_btree
(AHI misses requiring B-tree fallback), ahi_rows_added (rows inserted
into AHI), and ahi_pages_added (pages indexed by AHI). These same
metrics are exposed globally via innodb_status_variables.
Transaction-local tracking eliminates contention by accumulating
statistics in trx_t fields (n_sea, n_non_sea, n_ahi_rows_added,
n_ahi_pages_added) and aggregating them into global atomic counters
during trx_t::commit_cleanup() and trx_t::free(),
following the pattern established for buf_pool.stat.n_page_gets.
AHI counters are now organized as data members of the btr_sea structure
where they logically belong, using anonymous unions to enable both
atomic writes and direct non-atomic reads.
- btr_ahi_inc_searches(): Increment trx->n_sea when AHI lookup succeeds.
- btr_ahi_inc_searches_btree(): Increment trx->n_non_sea when AHI lookup
fails and falls back to B-tree search.
- btr_ahi_inc_rows_added(): Increment trx->n_ahi_rows_added when rows
are added to the adaptive hash index structure.
- btr_ahi_inc_pages_added(): Increment trx->n_ahi_pages_added when new
pages are indexed by AHI.
- btr_cur_t::search_leaf(): Call btr_ahi_inc_searches() on successful
AHI hit and btr_ahi_inc_searches_btree() on AHI miss.
- btr_sea: Add four counter members with anonymous unions to allow atomic
writes and lockless reads: hit_count/hit_count_nonatomic (successful AHI
lookups), miss_count/miss_count_nonatomic (B-tree searches after AHI miss),
rows_added/rows_added_nonatomic (rows added to AHI), and
pages_added/pages_added_nonatomic (pages added to AHI). Also includes
hit_count_old and miss_count_old variant for computing deltas in monitor
output.
- btr0cur.h/btr0cur.cc: Remove global declarations and definitions of
btr_cur_n_sea, btr_cur_n_non_sea, btr_cur_n_sea_old, btr_cur_n_non_sea_old,
btr_ahi_n_rows_added, and btr_ahi_n_pages_added as they are now data
members of btr_sea.
- export_var_t: Remove innodb_ahi_hit, innodb_ahi_miss, innodb_ahi_rows_added,
and innodb_ahi_pages_added members. MySQL status variables now point
directly to btr_search counters.
- innodb_status_variables[]: Change AHI status variable pointers from
&export_vars.innodb_ahi_* to &btr_search.*_nonatomic, enabling zero-copy
reads: adaptive_hash_hash_searches points to &btr_search.hit_count_nonatomic,
adaptive_hash_non_hash_searches to &btr_search.miss_count_nonatomic,
adaptive_hash_rows_added to &btr_search.rows_added_nonatomic, and
adaptive_hash_pages_added to &btr_search.pages_added_nonatomic.
- MONITOR_OVLD_ADAPTIVE_HASH_ROW_ADDED, MONITOR_OVLD_ADAPTIVE_HASH_PAGE_ADDED:
Convert monitor counters to "overload" type that read from atomic counters
btr_search.rows_added and btr_search.pages_added respectively instead of
maintaining separate statistics.
- trx_t::commit_cleanup() and trx_t::free(): Update transaction cleanup to
increment btr_search.hit_count, btr_search.miss_count, btr_search.rows_added,
and btr_search.pages_added instead of the old global counters.
- trace_engine_stats(): Output r_ahi_stats object with all four AHI
counters in JSON format when any AHI activity is detected during query
execution.
- ha_handler_stats: Added ahi_searches, ahi_searches_btree, ahi_rows_added,
and ahi_pages_added fields to track per-query AHI statistics.
- Add mtr parameter to btr_search_move_or_delete_hash_entries(),
btr_cur_t::search_info_update(), btr_search_update_hash_on_insert(),
btr_search_update_hash_ref(), btr_search_info_update_hash(), and
btr_search_build_page_hash_index() to allow updating AHI statistics.
- ahi_stats.test: Comprehensive verification of AHI statistics reporting
across different scenarios: insufficient accesses (no AHI build),
threshold triggering (AHI construction), heavy warmup (full AHI
utilization), and disabled AHI (verify zero statistics).
- check_ahi_status.inc: Reusable include file for executing queries with
configurable warmup repetitions and extracting AHI statistics from
ANALYZE FORMAT=JSON output using JSON path expressions.
valgrind reported use of an un-initialized value, when trying to write
analyzed plan to json. Instead of fetching c_str() from the string
object and passing it to the writer object's add_string() method, one could
use an overridden add_string() that accepts a different string object.
This would avoid the valgrind warnings.
There are two types of partition index scans. Ordered and unordered.
Ordered scan is used in common cases, including when the partitioned
table is the first in the join order and when ORDER BY ... DESC.
Whereas unordered scan is used when the partitioned table is not the
first in the join order, or in mrr when mrr_mode does not have
HA_MRR_SORTED. It can be unclear whether ordered or unordered scan has
been used. Therefore in this patch we add a field
"partitions_index_scan_method" to ANALYZE FORMAT=JSON to reveal with
method has been used. Possible values include
"merge_ordered_scans" (for ordered scan)
"iterate_over_partitions" (for unordered scan) and "both" (when both
have been used).
This commits adds the "materialization" block to the output of
EXPLAIN/ANALYZE FORMAT=JSON when materialized subqueries are involved
into processing. In the case of ANALYZE additional runtime information
is displayed, such as:
- chosen strategy of materialization
- number of partial match/index lookup loops
- sizes of partial match buffers
The symptoms were: take a server with no activity and a table that's
not in the buffer pool. Run a query that reads the whole table and
observe that r_engine_stats.pages_read_count shows about 2% of the table
was read. Who reads the rest?
The cause was that page prefetching done inside InnoDB was not counted.
This counts page prefetch requests made in buf_read_ahead_random() and
buf_read_ahead_linear() and makes them visible in:
- ANALYZE: r_engine_stats.pages_prefetch_read_count
- Slow Query Log: Pages_prefetched:
This patch intentionally doesn't attempt to count the time to read the
prefetched pages:
* there's no obvious place where one can do it
* prefetch reads may be done in parallel (right?), it is not clear how
to count the time in this case.
- Change the comments in class ha_handler_stats to say the members
are in ticks, not milliseconds.
- In sql_explain.cc, adjust the scale to print milliseconds.
Part#2, variant 2: Make the printed r_ values in JSON output consistent.
After this patch, ANALYZE output has:
- r_index_rows (NEW) - Observed number of rows before ICP or Rowid Filtering
checks. This is a per-scan average. like r_rows and "rows" are.
- r_rows (AS BEFORE) - Observed number of rows after ICP and Rowid Filtering.
- r_icp_filtered (NEW) - Observed selectivity of ICP condition.
- (AS BEFORE) observed selectivity of Rowid Filter is in
$.rowid_filter.r_selectivity_pct
- r_total_filtered - Observed combined selectivity: fraction of rows left
after applying ICP condition, Rowid Filter, and attached_condition.
This is now comparable with "filtered" and is printed right after it.
- r_filtered (AS BEFORE) - Observed selectivity of "attached_condition".
Tabular ANALYZE output is not changed. Note that JSON's r_filtered and
r_rows have the same meanings as before and have the same meaning as in
tabular output.
(Based on the original patch by Jason Cu)
Part #1:
- Add ha_handler_stats::{icp_attempts,icp_match}, make
handler_index_cond_check() increment them.
- ANALYZE FORMAT=JSON now prints r_icp_filtered based on these counters.
Before this patch, the code in Item_field::print() used
this convention (described in sql_explain.h:ExplainDataStructureLifetime):
- By default, the table that Item_field refers to is accessible.
- ANALYZE and SHOW {EXPLAIN|ANALYZE} may print Items after some
temporary tables have been dropped. They use
QT_DONT_ACCESS_TMP_TABLES flag. When it is ON, Item_field::print
will not access the table it refers to, if it is a temp.table
The bug was that EXPLAIN statement also may compute subqueries (depending
on subquery context and @@expensive_subquery_limit setting). After the
computation, the subquery calls JOIN::cleanup(true) which drops some of
its temporary tables. Calling Item_field::print() that refer to such table
will cause an access to free'd memory.
In this patch, we take into account that query optimization can compute
a subquery and discard its temporary tables. Item_field::print() now
assumes that any temporary table might have already been dropped.
This means QT_DONT_ACCESS_TMP_TABLES flag is not needed - we imply it is
always present.
But we also make one exception: derived tables are not freed in
JOIN::cleanup() call. They are freed later in close_thread_tables(),
at the same time when regular tables are closed.
Because of that, Item_field::print may assume that temp.tables
representing derived tables are available.
Initial patch by: Rex Jonston
Reviewed by: Monty <monty@mariadb.org>
