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
Sergei Petrunia
Factor out common code into get_mvi_index()
Yuchen Pei
MDEV-40168 Add some DDL tests
Sergei Petrunia
Make the MVI scan a real access method: QUICK_MVI_SELECT

JSON_CONTAINS() over a multi-valued index used to be optimized by
rewriting the WHERE clause: setup_mvi_for_join() injected a synthetic

  MATCH vcol AGAINST ('+k1 +k2' IN BOOLEAN MODE)

into join->conds and into select_lex->ftfunc_list, and the normal
fulltext machinery then picked it up as JT_FT access. The injected item
showed up in the plan and in the condition even though the user never
wrote it, and because it became ordinary ref access the scan was never
costed against the alternatives - it won by being in the WHERE clause.

Introduce QUICK_MVI_SELECT (QS_TYPE_MVI), a QUICK_SELECT_I that drives
the fulltext index directly through the handler API. Unlike FT_SELECT
there is no Item_func_match to have created the FT_INFO, so the quick
select creates it in reset() with ft_init_ext() and frees it with
close_search() in its destructor. Mvi_access::create_ft_item() is
replaced by build_ft_query(), which builds just the query string.

The analysis in setup_mvi_quick() is now kept: Mvi_context moves to the
header, is allocated on the mem_root and stored as JOIN::mvi_ctx, where
JOIN::get_mvi_access_for_table() looks it up. get_quick_record_count()
builds the quick select before test_quick_select() and keeps whichever
of the two is cheaper; test_quick_select() itself is untouched, so the
MVI quick is held in a local across the call (it deletes select->quick
on entry). The same save/compare is done around the second
test_quick_select() call in make_join_select(), which a LIMIT can reach.

A fulltext key never gets a bit in const_keys or keys, so mark the MVI
key of every table that has an access: the const_keys bit is what lets
the range analysis run for that table at all, the keys bit puts the
index into EXPLAIN's possible_keys.

Collect the accesses from the top-level AND-parts of the WHERE clause
only, instead of walking the whole condition. An MVI scan reads just the
rows the index matches, so it is only valid for a predicate that must
hold for every row of the result: for

  json_contains(j1->'$.tags','"a"') OR json_contains(j2->'$.tags','"a"')

scanning either index would drop the rows that only match the other
branch. The deleted add_ft_for_mvi() refused COND_OR_FUNC for the same
reason; walking the condition tree lost that, which only became visible
once the accesses were actually used.

Costs are placeholders (records=10, read_time=0.001) until the engine
can estimate a fulltext search. Note that while there is no estimate,
an MVI access is also taken when test_quick_select() produced no quick
select at all, without comparing it to the cost of a table scan.

TODO: This doesn't handle UPDATE/DELETE!  Should it be put into
  check_quick() call?
Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
MDEV-40168: JSON-over-fulltext: let the estimate consult the FTS cache.

fts_estimate_word_docs() probed only the on-disk auxiliary INDEX_[1..6]
table.  Documents inserted but not SYNCed yet are only in the in-memory
FTS cache, so they were missed entirely, and on a table that has never
been SYNCed the auxiliary table is empty and the estimate was simply
"unknown".

Look in the cache as well.  fts_index_cache_t::words is an rb tree of
fts_tokenizer_word_t, and fts_node_t::doc_count already holds the number
of documents in the node's ilist, so this is one rbt_search plus a walk
over a short vector: no ilist decoding and no I/O.

Three details:

  - the cache mutex is taken with trylock.  This runs during
    optimization, where a SYNC holding cache->lock across SQL execution
    would stall the optimizer; dropping the cache contribution is the
    better trade.

  - nodes flagged fts_node_t::synced are skipped.  An in-flight SYNC has
    already written them out, and the estimator reads the B-tree without
    a read view, so it sees those records; counting the node too would
    count its documents twice.

  - index_cache->words is NULL between fts_cache_clear() and
    fts_cache_init().  The query path never observes that because it
    runs after fts_init_index(); the estimate does not run it.

DB_RECORD_NOT_FOUND now means both sources are empty.  The cache on its
own cannot prove a word absent, because it is only complete once
fts_init_index() has run, and an estimate must have no side effects.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Make innodb_fts.estimate's clamped count deterministic

The "deleted rows are still counted" case failed about one full-suite run
in three:

  -Note 1105 fulltext_estimate('gamma')= 4
  +Note 1105 fulltext_estimate('gamma')= 5

The number it checks is the clamp in ha_innobase::fulltext_estimate(),
which is dict_table_get_n_rows() - the table statistics. The DELETE just
before it changes half the rows, which queues a background statistics
recalculation, and whether that has run by the time of the SELECT depends
on how loaded the machine is.

ANALYZE TABLE after the DELETE recalculates them on the spot and clears
the counter that would have triggered the background one, so the clamp
has one value to report.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Keep the MVI access of a table in its JOIN_TAB

The access was looked up through JOIN::get_mvi_access_for_table(), which
indexed a per-JOIN array by table->tablenr. Nothing else about how a
table is going to be read lives there, so put it where the rest does:
JOIN_TAB::mvi_access. Mvi_context is then just the result of the WHERE
analysis - the indexes and the accesses it found - and the choice of
which access a table uses is made per table, where it belongs.

setup_mvi_access_for_table() makes that choice and marks the index in
const_keys and keys, which make_join_statistics() used to do in a loop of
its own right after update_ref_and_keys(). It runs next to
add_group_and_distinct_keys(), the other place that adds to const_keys
for something the range optimizer would not find by itself, and just
before the range analysis those bits exist for.

get_quick_record_count() takes the JOIN_TAB rather than the TABLE now,
which is all it needed the JOIN for.

Also fix the header comment of Mvi_access::estimate_records(), which
still described the old fallback for a conjunctive access that could not
be estimated at all.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Do the MVI analysis one table at a time

setup_mvi_quick() ran once per JOIN, from JOIN::optimize_inner(): it
collected the MV indexes of every leaf table into one list, walked the
WHERE clause once, and left the accesses it found in JOIN::mvi_ctx for
setup_mvi_access_for_table() to dig through, filtering by
access->index->vcol->table == tab->table.

Nothing about it needed to be JOIN-wide. Each JOIN_TAB now has its own
Mvi_context describing the access to its own table, and everything
setup_mvi_quick() did happens in setup_mvi_access_for_table(): collect
that table's MV indexes, analyze the condition, pick the access. The
context is only kept when there is an access to use, so the chosen one is
Mvi_context::best and JOIN_TAB has a single MVI member. The table filter
becomes a DBUG_ASSERT: ctx->indexes holds only this table's indexes and
get_mvi_index() matches the predicate against those with Item::eq(),
which compares Field pointers, so an access can only ever be on the table
whose column the predicate names - even when two tables carry identical
MVI definitions.

The condition to analyze comes from get_sargable_cond(), the same one the
range analysis of that table uses twenty lines later. For a table on the
inner side of an outer join that is the ON expression rather than the
WHERE clause, so MVI access now works there too. It is sound for the same
reason the range optimizer may do it: the scan is a necessary, not a
sufficient condition, the JSON predicate stays in the ON expression and
does the exact filtering, and outer rows that find no match are
NULL-complemented as usual. The new test checks both, against the same
queries with IGNORE INDEX.

Two consequences of the analysis no longer being a pre-pass:

- It runs on the condition the range optimizer will see, after
  simplify_joins(), substitute_indexed_vcols_for_join() and
  optimize_cond(), rather than on the freshly parsed WHERE.

- Const tables are not analyzed at all, the per-table loop having
  skipped them before this point. They never get range analysis.

JOIN_TAB::mvi_ctx also needs no explicit reset between re-optimizations:
make_join_statistics() bzeroes the JOIN_TAB array.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Yuchen Pei
MDEV-40168 Resolve two TODOs

1. Merge conjunctives on the same index. Deduplicate while we are at it
2. Update costing

Co-Authored-By: Claude Opus 5 <[email protected]>
Yuchen Pei
MDEV-40168 Add testcases for when there's both a ft index and an mvi
Yuchen Pei
MDEV-40168 Fix cast to int arrays
Sergei Petrunia
Improve comments, formatting.
Sergei Petrunia
Estimate the number of records an MVI access will read

QUICK_MVI_SELECT carried records=10 and read_time=0.001, numbers picked
low enough that the access always won over a table scan. Ask the engine
instead, through the fulltext_estimate() added by the previous commit.

Mvi_access::estimate_records() estimates one element key at a time and
combines the answers the way the query combines the keys:

- A disjunctive access (JSON_OVERLAPS) reads the rows of every key, so
  the estimates add up.

- A conjunctive access (JSON_CONTAINS) reads the rows that have all of
  the keys, so the rarest key alone bounds the result. We use its
  estimate and drop the other keys from the query: reading the rarest
  key and letting the WHERE clause discard the rest is not worse than
  having the engine intersect the terms. This is the trade-off
  collect_mvi_keys() already makes for the keys it cannot encode - a
  shorter AND matches a superset of the rows, and the JSON predicate
  does the exact filtering.

The engine may be unable to estimate a key: ha_innobase only looks at
the fulltext auxiliary tables, so until the words are flushed out of the
FTS cache the answer is "unknown" for everything. Such a key takes no
part in the choice of the rarest one, and if not a single key could be
estimated the old guess stands and the query is left as it is.

For an OR we cannot do that: we have to read that key and have no idea
what it costs. Give the access a DBL_MAX read_time and do not use it.
That has to be acted on in get_best_mvi_access() rather than left to the
cost comparison, because best_access_path() takes a quick select to be
cheaper than a table scan without checking - true of anything the range
optimizer proposes, but this access does not come from there.

read_time is now the cost of reading the estimated rows plus evaluating
the WHERE clause on them, which is what the join optimizer expects of a
quick select's read_time. It does not account for the fulltext search
that produces the rowids in the first place.

The trace prints the estimate, or says the access is unusable and why.

The two existing tests ran on tables whose words were still in the FTS
cache, so the JSON_OVERLAPS sections would have stopped using the index
entirely. They now flush the cache with OPTIMIZE TABLE under
innodb_optimize_fulltext_only, the way innodb_fts.estimate does. The
trace test gets a table with a skewed distribution ("bbb" in every row,
"aaa" in one) to show the conjunctive access keeping only the rarest key
and still producing the same rows as the same query with IGNORE INDEX.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Rex Johnston
PQ: re-split a chunk into a few pieces, not one per page

A benchmark of "select grp, sum(i) from perf group by grp" over a million
narrow rows showed the pages the engine touched jumping fivefold as the worker
count crossed a threshold, while the time barely moved: one more worker turned
19 chunks into 10127.

Exec_ctx::split() was the cause. It is reached when a worker runs dry and the
chunk it picked up was flagged to be divided, and it divides by asking
partition() for split_level 1 -- every child of the sub-tree's root. One level
down from a chunk that spans a whole sub-tree is one range per page below it,
so a chunk became hundreds or thousands of chunks where a handful would have
done. Each of those then costs a descent from the root when a worker takes it,
which is what the page counter was showing.

partition() is left alone; only the number of boundaries used changes. The
ranges it returns are contiguous intervals that share endpoints, so a run of
them merges into one by taking the first's start and the last's end, and every
m'th boundary is kept for a target of about one piece per worker.

The same benchmark, before and after, at the worker counts around the step:

    workers  chunks resplit  pages      chunks resplit  pages
      13      1403    2    20988          41    2    15540
      14        282    1    16508          29    1    15496
      15      15415    15    76984        240    15    16284

The re-split counts are unchanged, which is the point: the same chunks are
flagged and the same decisions are taken, only each division is now the size it
was meant to be. What was a fivefold rise in pages accessed is five per cent.

Those counts are the other half of this commit, because none of it was visible
before. ANALYZE FORMAT=JSON now reports, beside r_rows_per_worker on the table
that was divided:

    "r_rows_per_worker": [...],
    "r_chunks": 41,
    "r_chunks_resplit": 2,

r_rows_per_worker on its own cannot answer this. It is the cut and the order
the pull queue handed chunks out, together, so an uneven row split says nothing
about whether the index was cut badly or the chunks merely landed unevenly.
r_chunks is the cut by itself: every chunk the engine produced, the ones a
re-split made included, and how many were divided rather than scanned.

The counts come from the coordinator through a new handler method,
parallel_get_chunk_stats(), which defaults to reporting nothing so that an
engine that does not divide scans adds no output. They are read once the
workers have been joined, from the handler parallel_init_coordinator() ran on
-- the manager's, not a worker's copy -- and only for the driving table, which
is the only one divided.

Both are masked by include/analyze-format.inc: a chunk is only re-split when a
worker runs dry and asks, so how often that happens, and therefore how many
chunks exist at the end, depends on which worker finished first.

What this does not address: create_contexts() still flips from flagging the
tail of the chunk list to flagging all of it the moment the chunk count stops
exceeding the worker count, which is the step visible above between 14 workers
and 15. It is a discontinuity where a slope belongs, and it is now cheap rather
than expensive, but it is still there.

This commit was prepared with Claude Code: the re-split was identified from the
chunk counts once they existed, and the before-and-after table above is its
measurement of the same test with the coalescing removed and restored.

(Slightly) tidied up by Rex.

Then...

Three fixes from review of the coalescing commit, none a wrong answer, each a
number coming out other than intended.

Round the merge stride down, not up. m was ceil(size/target), which makes
ceil(size/m) groups, and for a size just over the target that collapses to half
of it: 16 boundaries at target 15 merged into 8 pieces. Floor overshoots
instead, bounded by twice the target -- and too many small pieces costs less
than too few large ones, since the whole reason a chunk is being split is that
a worker had nothing to do.

Aim for twice the worker count, not once. The pieces a re-split produces are
never re-split again -- create_context() flags none of them -- so if the rows
inside the chunk are themselves skewed, having more pieces than workers is the
only slack left for evening that out. Twice keeps the piece count, and with it
the per-piece descent from the root, small, while leaving a second helping for
whoever finishes early.

The benchmark from the coalescing commit, re-measured across the same worker
counts:

    workers  chunks  resplit  pages
      13        69      2    15652
      14        45      1    15560
      15      484    15    17260
      16      511    15    17368

The 15-worker case reconciles exactly -- about 1027 leaf ranges per flagged
chunk, target 30, stride 34, so ~31 pieces each; 15 x 31 plus the 15 initial
chunks is the 484 -- and the headroom costs six per cent more page accesses
than the single-worker-count target did, against the fivefold the shattering
cost before coalescing existed. The step between 14 and 15 workers, where
create_contexts() goes from flagging the tail of the chunk list to flagging all
of it, is now a slope a reader can see the shape of rather than a cliff.

Make every statistic the parallel scan reports per-execution. quiesce_workers()
accumulated r_rows and its neighbours across executions of the same plan -- a
correlated subquery, a routine loop -- the way the serial path does, while
r_rows_per_worker and the chunk counts were overwritten each time, so ANALYZE
for a re-executed plan mixed totals with last-execution figures in one block.
The split and the chunk counts only describe one execution, and totals beside
them answer a different question about a different number of rows, so the
tracker now starts from zero each time the workers are reaped: everything
ANALYZE prints for a parallel scan describes the same, latest, run. This is a
deliberate departure from the serial convention, and r_scans is reset with the
rest so the figures stay consistent with each other.

This commit was prepared with Claude Code, from findings of its own review of
the coalescing commit: the half-target collapse and the mixed accumulate-and-
overwrite semantics were found by re-deriving what the arithmetic does at the
boundaries rather than re-running it.
Sergei Petrunia
Rename collect_mvi_vcols_for_table to collect_mvi_indexes_for_table

It collects Mv_index objects, not vcols.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Keep only the chosen Mvi_access in JOIN_TAB

JOIN_TAB held the whole Mvi_context the analysis produced, but outside
setup_mvi_access_for_table() the only thing ever read out of it was
mvi_ctx->best. The rest is scratch: indexes feeds get_mvi_index(),
accesses is what the last-wins loop picks best out of, and thd is there
for the mvi_analyze() callbacks.

So JOIN_TAB keeps the access itself, and the context becomes a local of
setup_mvi_access_for_table() - which is what the TODO there asked for:
nothing is allocated for a table that has no MVI key, or whose condition
yields no access. The access outliving the context is safe because
neither it nor the Mv_index it refers to belongs to the context: both are
allocated on the MEM_ROOT, and the lists only hold link nodes.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Factor out common code into Item_func_json_contains::get_mvi_access()

Item_func_json_contains::mvi_analyze() and ::create_ft_for_mvi() were
near-identical: both checked the arguments, looked up the matching MVI,
parsed the constant second argument and ran the same scan loop calling
encode_mvi_key(). They differed only in what they did with each encoded
key.

Move all of that into get_mvi_access(), which returns an Mvi_access, and
give Mvi_access two methods:

- add_key(), to collect one encoded element key,
- create_ft_item(), to build the

    MATCH vcol AGAINST ('+encoded_foo +encoded_bar ...' IN BOOLEAN MODE)

  item. It honors Mvi_access::conjunctive, so JSON_OVERLAPS will get the
  OR form for free.

mvi_analyze() and create_ft_for_mvi() are now thin wrappers around
get_mvi_access().

This also fixes a memory leak: the encoded keys were copied with
String::copy(), giving each String in Mvi_access::encoded a heap buffer
that is never freed (the Strings live on the MEM_ROOT, so their
destructors never run). Copy the keys onto the MEM_ROOT instead.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Undo whitespace changes to reduce diff size
Sergei Petrunia
Add comments
Dave Gosselin
MDEV-33616:  Match the macOS dlopen error in plugins.multiauth

The client reports why it could not load client_ed25519, and macOS names
every path that dlopen() tried.  Two expressions are added, one for the
chunk that holds the start of that message and one for the chunks that
continue it.

Whether the message arrives in one chunk or several depends on the
vardir, because the path appears four times in the dlopen text.  With
--vardir /Volumes/<repo>/var the line is 417 bytes and fits the 512 byte
buffer that --exec output is read in.  With the default vardir it does
not.

Both expressions stop at a newline.  reg_replace compiles with
REG_DOTALL, so an unrestricted .* runs past the line terminator whenever
the whole message reaches the replacement in one chunk, and the error
line then joins the line after it.
Dave Gosselin
MDEV-33616:  Charge and credit the same size for the recovery buffer

main.large_pages fails on macOS with "Warning: Memory not freed: 16375"
at shutdown and no accompanying safemalloc report.  The residual stays
at 16375 whether innodb_buffer_pool_size is 8M or 128M, and dropping
--large-pages makes it go away.

recv_sys_t::find_checkpoint() asks for tmp_buf_size, which is
MTR_SIZE_MAX + 9, or 1048585 bytes.  my_large_malloc() rounds that up to
a multiple of the large page size and charges the rounded figure to
global_memory_used, while recv_sys_t::tmp_free() credits back the
1048585 that was requested.  The page size here is 16384, 1048585 rounds
up to 1064960, and the difference is the 16375 reported.  The caller
cannot see the rounded figure because ut_malloc_dontdump() takes the
size by value and, with a null ut_new_pfx_t, has nowhere to report what
it allocated.  ut_malloc_dontdump_size() writes the size back, and
recv_sys_t keeps it in tmp_buf_alloc_size for the free.  tmp_buf_size
remains the capacity that parse() asserts against.

Only macOS rounds up.  my_get_large_page_sizes() has no huge page
interface to consult there, so its fallback branch reports the ordinary
page size as the only large page size and the plain mmap() always
succeeds.  On Linux the candidate is 2 MiB, the MAP_HUGETLB mapping
fails with ENOMEM when no huge pages are reserved, and the retry loop
settles on large_page_size == 0, which records the request unrounded.
No memory was lost either way, since munmap() rounds its length up to a
whole page.  The counter was wrong, and the counter is what MTR checks.

Co-Authored-By: Claude Opus 5 <[email protected]>
Sergei Petrunia
Trivial cleanups and comments
Sergei Petrunia
MDEV-40168: JSON-over-fulltext: add estimates.

Add records_in_range-like estimates for fulltext index
Sergei Petrunia
Move the JSON function MVI code into opt_mvi_jsonfuncs.cc

opt_multi_valued_index.cc held two separate concerns: the index side
(how a value is encoded into the index, the access descriptor, the quick
select) and the predicate side (which JSON functions can be computed
from an MVI, which of their arguments holds the indexed expression, and
what to search the index for). Split the second one out.

Moved verbatim:

  get_mvi_index()
  collect_mvi_keys()
  Item_func_json_contains::get_mvi_access() and ::mvi_analyze()
  Item_func_json_overlaps::get_mvi_access() and ::mvi_analyze()
  add_mvi_access()

The only code change is that encode_mvi_key() is no longer static: it is
used both by Item_func_mvi_encode::val_str_ascii(), which stays, and by
collect_mvi_keys(), which moves. It is declared in
opt_multi_valued_index.h now. The other three moved helpers had no
callers outside the moved code and stay static.

Item_func_mvi_encode is not a JSON function and stays put: it is how the
values get into the index in the first place.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
More comments, code readability. No functional changes.
Sergei Petrunia
MDEV-40168: JSON-over-fulltext: move the estimator into fts0est.cc.

Pure code motion, no functional change.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Yuchen Pei
MDEV-40168 nested array handling and json validation
Sergei Petrunia
Inline the mvi_key variable into its only use

mysql_prepare_alter_table() computed it at the top of the key loop and read
it ~290 lines below, at the one place that wants it. Nothing in between can
change the answer, and the early call also ran for keys that never reach the
assignment.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Luke Lu
MDEV-40385 use-of-uninitialized-value in Binary_string::c_ptr()

SELECT KDF('','',1000,256) triggered an MSAN use-of-uninitialized-value
report in Binary_string::c_ptr() (sql/sql_string.h) reached from
Item_func_kdf::val_str().

The optional 4th argument (kdf_name) was evaluated into a result buffer
and then read as a C string with c_ptr(). When that argument is an
integer literal such as 256, Item_int::val_str() writes the digits "256"
into the buffer without appending a trailing NUL, and String::alloc()
intentionally skips reallocation, so the buffer stays non-"alloced" and
unterminated. c_ptr() then reads Ptr[str_length] to test for an existing
terminator, reading an uninitialized byte. The buffer is a stack-resident
ValueBuffer from Protocol::send_result_set_row, so the byte is validly
addressable but never initialized; only MSAN re-poisons the stack scope,
which is why the report is MSAN-only and Valgrind does not flag it.

Use c_ptr_safe() instead of c_ptr() when reading the kdf_name argument.
c_ptr_safe() writes the NUL terminator after a capacity check without
first reading Ptr[str_length], while c_ptr() reads that byte to detect an
existing terminator. Behaviour is unchanged: a non-matching kdf_name still
yields ER_STD_INVALID_ARGUMENT and NULL, and valid names (pbkdf2_hmac,
hkdf) still work.

The regression cases added to main.func_kdf reproduce the report only
under an MSAN-instrumented build; on a normal build they pass on both the
unfixed and fixed server because the stray read is harmless without a
sanitizer.

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.
Sergei Petrunia
Adjust the MVI tests to the estimate that reads the FTS cache

fulltext_estimate() used to see only the on-disk auxiliary table, so the
multi-valued index tests, which insert and immediately EXPLAIN, got
"unknown" for every element key: the conjunctive accesses fell back to a
guess of 10 rows and the disjunctive ones were dropped for want of a
cost. Both tests worked around that with OPTIMIZE TABLE under
innodb_optimize_fulltext_only. The estimate consults the cache now, so
the workaround is gone and the row counts in the plans are real.

Two sections of the trace test were written before the estimate existed
and no longer showed what they said they did:

- "Several element keys" printed one range, not several, because a
  conjunctive access now keeps only the rarest key. It runs on a table
  with a skewed distribution instead ("bbb" in every row, "aaa" in one),
  which makes the choice of key visible rather than a tie, and checks
  the rows against the same query with IGNORE INDEX.

- The JSON_OVERLAPS section is where several ranges are printed now, so
  it says so, along with the estimates adding up.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Move QUICK_MVI_SELECT into opt_multi_valued_index.cc
Sergei Petrunia
A plain KEY is the only index type allowed over an ARRAY

    create table t1 (j json, unique key idx ((CAST(j->'$.a' AS CHAR(6) ARRAY))));
    create table t1 (j json, primary key ((CAST(j->'$.a' AS CHAR(6) ARRAY))));
    create table t1 (j json, fulltext key idx ((CAST(j->'$.a' AS CHAR(6) ARRAY))));

were all accepted without a word, and all produced the same thing: a plain
index. The key type the user wrote was simply overwritten with
Key::FULLTEXT, so the table ended up with no unique constraint, or no
primary key, or with a FULLTEXT index that MATCH() finds nothing in - the
index holds encoded element keys, not the text.

What the server builds for an ARRAY is a fulltext index over those encoded
elements, and it can only mean what a plain KEY means. Say so: reject any
other type, in the grammar, before that overwrite loses what was asked for.
CONSTRAINT ... UNIQUE and the ALTER TABLE forms go the same way. SPATIAL
and VECTOR are already syntax errors for an ARRAY key part; they are in the
switch anyway so it stays exhaustive.

FOREIGN KEY is unaffected: it builds its key with Key::MULTIPLE and cannot
be told apart here. It is rejected, further down, by the engine - "Foreign
key constraint is incorrectly formed".

The count of key parts is now also checked in the grammar, and not only in
init_key_part_spec(). Otherwise the first ARRAY part of a two-part key sets
the type to FULLTEXT, and the second part reports "Incorrect usage of
FULLTEXT and ARRAY" for a key nobody declared FULLTEXT. As a side effect
KEY idx (c,(CAST(... ARRAY))) now gives the same "max 1 parts" error as the
other orders, instead of ER_BAD_FT_COLUMN for `c'.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Rex Johnston
PQ: re-split a chunk into a few pieces, not one per page

A benchmark of "select grp, sum(i) from perf group by grp" over a million
narrow rows showed the pages the engine touched jumping fivefold as the worker
count crossed a threshold, while the time barely moved: one more worker turned
19 chunks into 10127.

Exec_ctx::split() was the cause. It is reached when a worker runs dry and the
chunk it picked up was flagged to be divided, and it divides by asking
partition() for split_level 1 -- every child of the sub-tree's root. One level
down from a chunk that spans a whole sub-tree is one range per page below it,
so a chunk became hundreds or thousands of chunks where a handful would have
done. Each of those then costs a descent from the root when a worker takes it,
which is what the page counter was showing.

partition() is left alone; only the number of boundaries used changes. The
ranges it returns are contiguous intervals that share endpoints, so a run of
them merges into one by taking the first's start and the last's end, and every
m'th boundary is kept for a target of about one piece per worker.

The same benchmark, before and after, at the worker counts around the step:

    workers  chunks resplit  pages      chunks resplit  pages
      13      1403    2    20988          41    2    15540
      14        282    1    16508          29    1    15496
      15      15415    15    76984        240    15    16284

The re-split counts are unchanged, which is the point: the same chunks are
flagged and the same decisions are taken, only each division is now the size it
was meant to be. What was a fivefold rise in pages accessed is five per cent.

Those counts are the other half of this commit, because none of it was visible
before. ANALYZE FORMAT=JSON now reports, beside r_rows_per_worker on the table
that was divided:

    "r_rows_per_worker": [...],
    "r_chunks": 41,
    "r_chunks_resplit": 2,

r_rows_per_worker on its own cannot answer this. It is the cut and the order
the pull queue handed chunks out, together, so an uneven row split says nothing
about whether the index was cut badly or the chunks merely landed unevenly.
r_chunks is the cut by itself: every chunk the engine produced, the ones a
re-split made included, and how many were divided rather than scanned.

The counts come from the coordinator through a new handler method,
parallel_get_chunk_stats(), which defaults to reporting nothing so that an
engine that does not divide scans adds no output. They are read once the
workers have been joined, from the handler parallel_init_coordinator() ran on
-- the manager's, not a worker's copy -- and only for the driving table, which
is the only one divided.

Both are masked by include/analyze-format.inc: a chunk is only re-split when a
worker runs dry and asks, so how often that happens, and therefore how many
chunks exist at the end, depends on which worker finished first.

What this does not address: create_contexts() still flips from flagging the
tail of the chunk list to flagging all of it the moment the chunk count stops
exceeding the worker count, which is the step visible above between 14 workers
and 15. It is a discontinuity where a slope belongs, and it is now cheap rather
than expensive, but it is still there.

This commit was prepared with Claude Code: the re-split was identified from the
chunk counts once they existed, and the before-and-after table above is its
measurement of the same test with the coalescing removed and restored.

(Slightly) tidied up by Rex.
Sergei Petrunia
Make optimizer trace print "range", not "index_merge" for MVI quick selects.
Dave Gosselin
MDEV-33616:  Skip the redo log upgrade tests without sparse file support

innodb.log_upgrade and innodb.log_upgrade_101_flags build 8GB redo log
files by seeking past the end of an empty file and writing a single
byte.  That needs a filesystem which leaves the skipped range
unallocated.  HFS on macOS allocates every block of it instead, so the
write fails with ENOSPC and the test reports a perl failure.

include/have_sparse_files.inc probes the vardir by writing one byte 64MB
into an empty file and comparing the allocated block count against that
offset.  The offset stays above 16MB since APFS allocates the whole
range for a file smaller than that rather than recording a hole.
Sergei Petrunia
Only allow one key part in an index over an ARRAY

    create table t1 (j json, key idx ((CAST(j->'$.a' AS CHAR(6) ARRAY)),
                                      (CAST(j->'$.b' AS CHAR(6) ARRAY))));

was accepted without a word. Each ARRAY key part gets an internal column of
its own, and they all became key parts of one fulltext key: the tokens of
both arrays end up mixed in a single index, and the optimizer would then
search that index for the keys of one array and get the rows of the other as
well. There is also no way to show such a key, or to read one back.

init_key_part_spec() now rejects a key that has an ARRAY key part and more
than one key part, on both the CREATE TABLE and the ALTER TABLE path.

The other order, KEY idx (c,(CAST(... ARRAY))), was already rejected: the
ARRAY part makes the key FULLTEXT, and `c' cannot be part of one.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
opt_mvi_jsonfuncs.cc: Move the code, const-ify, add comments.
Sergei Petrunia
Add optimizer trace for the multi-valued index access

get_best_mvi_access() picked an Mvi_access and wrapped it in a
QUICK_MVI_SELECT without recording anything, so there was no way to see
which index was chosen or what it would search that index for: the
rows_estimation trace showed a range_analysis that found nothing, and
then a plan using a key the trace never mentioned.

Print a "multi_value_index_use" object:

  {
    "table": "t1",
    "index": "idx",
    "ranges": ["616161"]
  }

Mvi_access::print_json() fills in the index and the element keys,
following TRP_RANGE::trace_basic_info(): same "index" / "ranges" member
names, so an MVI entry reads like a range scan's.

The keys are printed in their encoded form, which is not readable. That
is what is stored in the index and what we search for, so it is still
the useful thing to print; making it readable can come later. It is
plain ASCII (hex plus the xx/xxxx padding from encode_mvi_key()), so it
needs no JSON escaping.

get_best_mvi_access() runs inside the "rows_estimation" array, so the
named object needs an object of its own around it, the same way
make_join_statistics() and the sel_arg_alloc_limit_hit trace do it.
Without it the writer hits an assertion in
Single_line_formatting_helper::on_add_member().

The new test is a separate file because optimizer trace tests need
not_embedded.inc, and putting that in multi_valued_index.test would skip
the whole feature test on embedded builds. It cross-checks the printed
keys against mvi_encode() over the indexed column, which produces the
tokens the index is actually built from.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Show multi-valued indexes in SHOW CREATE TABLE

    create table t1 (c int, j json,
                    key idx ((CAST(j->'$.tags' AS CHAR(6) ARRAY))));

printed the two columns and no index at all. Nobody had decided to hide it:
it inherited invisibility from the internal column that backs it. The
grammar makes DB_MVI_<n> INVISIBLE_FULL, init_from_binary_frm_image() turns
a hidden key part into a hidden key, and store_create_info() skips keys with
HA_INVISIBLE_KEY. Long unique hash keys - the other kind of key built over a
column the user cannot name - are already exempted from that; exempt the
multi-valued index the same way, and print it as

  KEY `idx` ((cast(json_extract(`j`,'$.tags') as char(6) array)))

which is the expression it was declared with and all it takes to re-create
it. The internal column stays out of the output: it cannot be printed as a
column, since there is no syntax that would recreate the pairing.

Item_func_mvi_encode::print() cannot produce that form. Its output is what
pack_expression() puts in the FRM, and that is read back as a call of
mvi_encode(), the only form the parser accepts outside an index definition.
So the CAST spelling gets a printer of its own, sharing the type printing.

SHOW INDEX and I_S.STATISTICS list the key now too - the key part is let
through the invisibility filter - so they no longer need
debug_dbug=test_invisible_index, and the tests stop setting it (it also
injected a stray invisible1 column and key into their output).

The same HA_INVISIBLE_KEY drove mysql_prepare_alter_table(), which drops
such keys from the list of keys carried into the rebuilt table - and
INVISIBLE_FULL columns from the list of columns. So

    ALTER TABLE t1 ADD COLUMN x INT;

silently dropped the index. The key survives now, and its column is carried
over with it, for exactly as long as the key lives: DROP KEY takes the
column with it, so the name is free again afterwards.

While at it, make_internal_field_name() looped forever when create_list is
empty: dup_found started at true and the loop that clears it does not run.
The MVI path is the only caller that can hit that, and it does - with
ALTER TABLE ... ADD KEY ((CAST(... ARRAY))), which used to hang the server
and now works.

A fulltext key over several arrays,

    KEY idx ((CAST(j->'$.a' AS CHAR(6) ARRAY)),
            (CAST(j->'$.b' AS CHAR(6) ARRAY)))

has no single expression to print and no syntax of its own to be read back,
so it stays hidden, exactly as before. The optimizer still uses its parts.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Make JSON_OVERLAPS sargable for multi-valued indexes

Both argument orders are handled, since JSON_OVERLAPS is symmetric:

  JSON_OVERLAPS(array_indexed_expr, '[foo, bar, ...]')
  JSON_OVERLAPS('[foo, bar, ...]', array_indexed_expr)

JSON_CONTAINS is true when ALL of the elements have a match, JSON_OVERLAPS
when ANY of them does, so the access it produces has conjunctive=false and
build_ft_query() leaves the keys optional instead of prefixing them with
'+'.

The two get_mvi_access() implementations share collect_mvi_keys(), which
is the scan of the JSON literal that used to sit inside
Item_func_json_contains::get_mvi_access().

The two differ in one way beyond the flag. An element that cannot be
encoded for the index (a number against a CHAR array, say) is skipped for
JSON_CONTAINS: dropping a key from an AND makes the index scan less
selective, so it still returns a superset of the rows the predicate
matches and the predicate does the exact filtering afterwards.

That reasoning does not hold for an OR. A row can satisfy the predicate
through the very element we failed to encode, and MVI_ENCODE skips such
elements as well, so that row has no key in the index for the scan to find
it by - dropping the key would lose it. So for a disjunctive access we
give up instead of skipping. With a row {"tags": [123]} in the table,

  select * from t1 where json_overlaps(j->'$.tags','[123,"aaa"]')

must not use the index, and the test checks its result against the same
query with IGNORE INDEX.

Also print "match": "all"/"any" in the optimizer trace. Now that an access
can be either, the printed ranges alone did not say whether a row has to
have all of the keys or just one.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>
Sergei Petrunia
Move the multi-valued key part DDL out of the grammar

multi_valued_key_part: built a whole schema object in its action: the hidden
DB_MVI_<n> column with its MVI_ENCODE() vcol, the rewrite of the key into an
invisible fulltext one, and the key part naming the column. That is DDL, and
it belongs with the rest of the multi-valued index code, not in sql_yacc.yy
where nobody reading opt_multi_valued_index.cc will find it.

It becomes add_mvi_key_part(), which returns the key part or NULL if it
raised an error, and the production is three lines. check_mvi_key_type()
follows its only caller and turns static, so sql_table.h loses a declaration
and the forward `class Key;` that existed only for it.

No behaviour change.

Co-Authored-By: Claude Opus 5 (1M context) <[email protected]>