JOIN::select_list_used_tables is computed by
JOIN::eval_select_list_used_tables(), but never read. Its last reads
(in the code that optimizes DISTINCT on a subset of the tables, and in
JOIN_TAB::save_explain_data()) were removed by:
commit 2cfc450bf7
Author: Igor Babaev <igor@askmonty.org>
Date: Tue Feb 9 2016
This is the consolidated patch for mdev-8646: "Re-factor the code
for post-join operations".
Remove the member and the function that computes it.
Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
The member is assigned in JOIN::prepare() but never read. Its last
reads (in the EXPLAIN code) were removed by:
commit 46a22a5be7
Author: bell@sanja.is.com.ua
Date: Thu Sep 26 2002
new EXPLAIN
fixed bug in mysql-test/create-test-result
fixed bug in union-subselect engine
Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
The member was assigned in JOIN::init(), JOIN::optimize_inner() and
JOIN::optimize_stage2(), but never read. Its last reads, the arguments
of prepare_sum_aggregators() calls, were removed by:
commit 2cfc450bf7
Author: Igor Babaev <igor@askmonty.org>
Date: Tue Feb 9 2016
This is the consolidated patch for mdev-8646: "Re-factor the code
for post-join operations".
Since then the callers compute the value themselves from
is_using_agg_loose_index_scan().
JOIN::prepare_sum_aggregators() has a parameter of the same name, which
is unrelated and is kept.
Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
These were added by cf50e13fbd (MDEV-6080: Allowing storage engine to
shortcut group by queries) to remember the join_tab array and table_count
before the group_by_handler replaced them.
The last assignment of non-NULL values to them was removed by:
commit 2cfc450bf7
Author: Igor Babaev <igor@askmonty.org>
Date: Tue Feb 9 2016
This is the consolidated patch for mdev-8646: "Re-factor the code
for post-join operations".
Since then original_join_tab was only set to 0, so the block in
JOIN::cleanup() that restored join_tab and table_count from them never
ran. Remove the block and both members.
Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
JOIN::no_field_update was added in the initial import (2000) and has
never been used.
JOIN::sum_funcs2 and JOIN::sum_funcs_end2 were last used before:
commit 2cfc450bf7
Author: Igor Babaev <igor@askmonty.org>
Date: Tue Feb 9 2016
This is the consolidated patch for mdev-8646: "Re-factor the code
for post-join operations".
Since then sum_funcs2 was only zeroed in JOIN::init() and
sum_funcs_end2 was not referenced at all.
Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
For simple single-schema SELECT queries on information_schema.tables,
bypass ha_write_tmp_row() and stream rows directly via the protocol.
Add EXPLAIN is_fast_path visibility and MTR coverage, including Windows
EXPLAIN casing normalization.
tmp_memory_table_size is limiting the size of internal temporary memory
tables. max_heap_table_size is there to limiting the size of explictely
created memory tables. max_heap_table_size can be much larger than
tmp_memory_table_size as the memory used by temporary tables is in the
control of the user.
This commit changes the usage of max_heap_table_size for internal buffers
to min(max_heap_table_size, tmp_memory_table_size), like we do for
internal temporary tables.
This changes the in memory buffer allocations for:
- GROUP_CONCAT()
- Calculating the cost for scanning memory tables (the original code was
wrong here as it used the wrong size for memory tables).
- ANALYZE TABLE buffer sizes for calculating distinct column values
Other things:
- Add THD::ram_limitation() to provide consistent memory limitations
in all code that used variables.tmp_memory_table_size as buffers.
If tmp_memory_table_size == 0, then 8192 is used.
This replaces Item_sum::ram_limitation which used 1024 as min buffer,
which is way to little for any practical case.
- Added security guard in heap_prepare_hp_create_info to ensure that
max_table_size is calculated same way as in MariaDB server.
- Fixed initial memory allocations for Item_func_group::concat which
allocated 'max allowed memory' at start. Now it allocates only 1/16
of that memory at start.
`COUNT(DISTINCT)` collects the distinct values in a temporary table
with a unique constraint over the aggregate's arguments, and treats a
duplicate key error from the write as "value already seen":
```c
if (!table->file->is_fatal_error(error, HA_CHECK_DUP))
return FALSE; // duplicate, not an error
```
For a blob argument the record holds only a pointer to the value, so
`Aggregator_distinct::setup()` cannot use the `Unique` tree, which
compares raw record bytes, and every value goes through that write
instead.
When such a write overflows the in-memory table,
`create_internal_tmp_table_from_heap()` copies the stored rows to an
on-disk table and then writes the row that overflowed, which until
then was held in `record[0]` alone. Whether a duplicate key error on
that last write is fatal is decided by the caller's
`ignore_last_dupp_key_error` argument, and `Aggregator_distinct::add()`
passed **0** three lines below the code that ignores the very same
condition. The statement failed with
ERROR 1169 (23000): Can't write, because of unique constraint,
to table '(temporary)'
Pass **1** instead, so that a duplicate arriving through the conversion
is discarded exactly like one arriving through the ordinary write. The
result is `table->file->stats.records` of that table, so not storing
the duplicate is what makes the count right.
The argument is the same upstream, where it is unreachable: a
temporary table with a blob column was created on the on-disk engine
to begin with, so the conversion was never entered for the only
tables whose pending row can be a duplicate. Supporting blob columns
in the in-memory engine made the table start in memory and convert.
New tests `heap.count_distinct_blob_convert` and
`heap.count_distinct_blob_convert_debug`.
A write rejected as a duplicate returns its record to the free list and
never reaches the allocation of the blob value, so only the first copy
of a value makes the in-memory table grow, and the write that finds it
full is the second copy of the value stored last. That holds only while
a record slot is what the table runs out of first. Blob values come out
of the same space, and only a write that is not a duplicate ever
allocates one, so when a blob allocation is the one that hits the limit,
the pending row is not a duplicate at all.
Which of the two runs out first follows from how records and blob values
pack together, not from any threshold on the value width. Of 24 measured
combinations of width and `max_heap_table_size`, 20 convert but only 8
reach a duplicate pending row, so asserting that the table was converted
does not establish that the ignored duplicate was reached.
The first test uses widths measured to overflow on a record slot. The
second removes the dependency on that measurement, injecting the
duplicate through a new debug point in
`Tmp_table_default_copier::copy_rows()`, beside the one the row copy
loop already carries. Every value is present twice, so whichever copy
the injected duplicate discards, the other one is still written and the
count does not depend on which write overflowed.
The status counter is read with the in-memory limit restored. The status
table is materialized into a temporary table of its own, and its
VARIABLE_VALUE column is wide enough to be stored as a blob, so under
the shrunken limit that table can overflow and be converted as well, and
would then report its own conversion.
`create_internal_tmp_table_from_heap()` writes the pending `record[0]`,
the row whose write filled the in-memory table, into the new table.
Since **MDEV-40376** (`636f154bb49`) that write happens *before*
`ha_end_bulk_insert()` rather than after it.
`ha_maria::start_bulk_insert()` disables **all** indexes of an internal
temporary table that is about to receive at least
`MARIA_MIN_ROWS_TO_DISABLE_INDEXES` (100) rows:
```c
if (file->open_flags & HA_OPEN_INTERNAL_TABLE)
{
/* Internal table; If we get a duplicate something is very wrong */
file->update|= HA_STATE_CHANGED;
index_disabled= share->base.keys > 0;
maria_clear_all_keys_active(file->s->state.key_map);
}
```
`maria_write()` then skips `_ma_check_unique()` entirely, so the unique
constraint that implements `DISTINCT` for a key too wide to be an index
is not enforced. The rows copied out of the in-memory table are already
distinct and need no checking against each other, but the pending row is
exactly the row whose duplicate status is unknown, and it was written
inside that window. A `SELECT DISTINCT` over wide columns could
therefore return a duplicate row.
Note that the justification given in `636f154bb49` is not the mechanism
at work here. It refers to the bulk insert key *tree*, a different
branch of `ha_maria::start_bulk_insert()`; setting
`bulk_insert_buffer_size=0` does not avoid the problem.
The fix splits the copy in two:
1. `Tmp_table_row_copier` gains a second virtual,
`write_pending_row()`, defaulting to a no-op.
2. `copy_rows()` now only copies the rows the in-memory table holds.
3. `create_internal_tmp_table_from_heap()` calls
`ha_end_bulk_insert()` and then `write_pending_row()`, so the
pending row is written with the indexes of the new table back in
place and a duplicate of an already copied row is detected.
`Window_rowid_remapper` keeps writing its pending row within
`copy_rows()` and inherits the no-op default. Its new position is only
known once the rows before it have been written, and nothing is lost by
writing it with the indexes still disabled: it replaces a row that is
already in the table rather than adding one, and an update of a window
function value cannot collide with another row, as a deduplicating key
is not built on the columns it changes.
The new test covers `SELECT DISTINCT`, `SELECT DISTINCT ... ORDER BY`,
`GROUP BY`, `UNION` and `INSERT ... SELECT DISTINCT`, and asserts that
the conversion actually happened so that a future sizing change cannot
silently void the coverage.
A single-row table is read once during optimization and its row kept in
`record[0]` for the rest of the statement. `JOIN::optimize_stage2()`
then releases the lock on every const table, on the premise stated in
its own comment: *"It's safe to ignore result code as all tables where
opened for read only."*
That premise assumes a read leaves a **copy** of the row behind. MEMORY
with a blob does not. `hp_read_blobs()` answers the read by pointing
`record[0]` at the blob data inside `HP_SHARE` rather than copying it,
so from the moment the lock is dropped another connection is free to
overwrite, free or recycle those bytes -- and the statement goes on
reading them. The result is a const table whose value changes in the
middle of the statement using it, and a read of freed memory.
Let a caller that keeps reading a row after the unlock ask for such
tables to be left alone. `GET_LOCK_SKIP_ZERO_COPY_ROWS` drops them
from the lock set `get_lock_data()` builds, exactly as
`GET_LOCK_SKIP_SEQUENCES` already does, and the const-table unlock in
`JOIN::optimize_stage2()` passes it.
Which tables those are is for the engine to say rather than for the
lock layer to infer. The 64-bit `table_flags()` space is full, so a
second word `table_flags2()` carries the first such property,
`HA2_CANNOT_ACCESS_ROWDATA_AFTER_UNLOCK`, and `ha_heap::open()`
raises it for any table that has a blob.
The skip has to be opt-in rather than a rule. `mysql_lock_remove()`
also reaches `mysql_unlock_some_tables()`, and there the unlock is
permanent and must not be skipped.
A MEMORY blob const table now stays read-locked for the whole statement
and blocks writers, which is the price every non-const MEMORY table
already pays.
The regression test parks the reader with `GET_LOCK()` rather than with
a stored function. A stored function puts the statement into prelocked
mode, and the const-table unlock is skipped entirely in that mode, so
the code path under test would never run.
The gate that parks it is taken with `--disable_ps2_protocol` in force.
`--ps-protocol` executes every complete `SELECT` twice and compares the
two result sets, and `GET_LOCK()` is recursive, so a doubled
acquisition would outlive the single `RELEASE_LOCK()` that opens the
gate again.
`save_window_function_values()` did not take into account that one can
get `HA_ERR_RECORD_FILE_FULL` on `ha_update_row()`. With blob support in
heap, it can now happen more easily (expressions wider than 512
characters are promoted to TEXT in tmp tables, and each update of a blob
result column allocates a continuation record). However, it could also
happen with Aria tables, which is apparently not tested. The error was
returned as a plain `true` with an empty diagnostics area: debug builds
hit `Assertion '0'` in `Protocol::end_statement()`, release builds send
a bare OK packet after the result set metadata, which the client
mis-parses (appears as a hang / lost connection).
Errors of the computation are now exposed:
`save_window_function_values()` calls `handler::print_error()` for any
failure it does not recover from, the previously ignored `ha_rnd_pos()`
return values are checked and reported, and `compute_window_func()`
distinguishes a read error from EOF and stops the row scan as soon as
the per-row loop fails.
The overflow itself is recovered from by converting the tmp table to
the disk-based tmp engine in place: the rows keep their identity and
their contents, only their positions change, and those are translated
in the rowid sequence that the computation is built on.
The filesort result of a window sort is a sequence of row positions
that covers every row of the table exactly once (the sort is set up
without a limit) and is the only place where the positions are kept:
the row scan and all frame cursors read the rows through it. The
conversion (`Window_rowid_remapper`) therefore copies the rows in the
order of that sequence, which makes the new position of a row known as
soon as the row has been written, and stores it back into the very slot
the old position was read from.
For the new position to always fit into the slot, a window sort stores
the row positions in slots of a fixed 8 bytes
(`Filesort::min_ref_length`, set to the new `TMP_TABLE_MAX_REF_LENGTH`),
which holds the position of any engine an internal tmp table can use: a
pointer into memory or a data file offset. `make_sortkey()` zero-pads a
shorter position to its slot, and `SORT_INFO::ref_length` carries the
slot width to the readers, so `init_read_record()` no longer re-derives
it from `handler::ref_length`, which changes when the table is
converted. Sorts that do not set a minimum, and engines whose positions
are wider than 8 bytes, are unchanged byte for byte. A sequence held in
memory is thereby rewritten in place, keeping its layout, so the frame
cursors' places in it stay valid.
A sequence held in a temporary file is written and read through an
`IO_CACHE`, which encrypts the file when tmp file encryption is
enabled, so the file can not be rewritten in place: the old sequence is
streamed out of its cache and the translated sequence into a new cached
temporary file, which then replaces the old one under the cache. The
frame cursors' slave caches stay linked to the master through
`next_file_user` across the replacement, and are re-created from the
new master afterwards (`Frame_cursor::rowids_rewritten()`). Should
re-creating one fail, the cursor forgets its already-released cache, so
that it is not released a second time when the cursor is destroyed, and
reports the failure.
The row whose update did not fit is written from `record[0]`, which
holds its new image, instead of being read from the table, so the
conversion applies the update that failed. Blob values of `record[0]`
that were read from the HEAP table can point into the handler's shared
blob reassembly buffer (`hp_read_blobs()`, blob values that span
multiple allocation blocks), which reading the other rows overwrites,
so they are first given memory of their own.
When the window sort was set up with a deferred filter (a HAVING clause
deferred to the final ORDER BY sort), the sequence omits the rows the
filter rejected and the conversion drops them: every later reader of
the table applies the same filter, either directly or by reading the
table through a sort that does, so such rows can never reach the
result.
`create_internal_tmp_table_from_heap()` gains a `Tmp_table_row_copier`
hook for the copy from the HEAP table to the Aria or MyISAM table that
replaces it. The plain copy loop and the append of the pending
`record[0]` become `Tmp_table_default_copier`, used when the caller
passes no copier, so both cases of the conversion run through the same
code, defined together with `Window_rowid_remapper` just before the
function. `ha_end_bulk_insert()` is called also when the copy fails, so
the new handler does not keep bulk insert state when its table is
dropped; the pending row is thereby written while the bulk insert is
still active, which does not delay its duplicate detection, as bulk
insert does not cache unique keys (`maria_init_bulk_insert()` skips
`HA_NOSAME` keys). An ignored duplicate of the pending row is passed to
the caller in `Tmp_table_row_copier::duplicate_row_error`, which sets
`*is_duplicate`.
Carrying the running computation over the conversion, instead of
re-running it, has two visible consequences:
- Compound expressions containing window functions (`items_to_copy`)
are evaluated exactly once per row, as they are when the table does
not overflow, so the conversion does not have to be refused when
such an expression is non-deterministic (`RAND_TABLE_BIT`: a user
variable assignment, a non-deterministic stored function); those
statements produce their result.
- The statement sorts once, so sort related aggregate warnings, e.g.
the `max_sort_length` truncation counts, are reported for one pass.
The rows are copied in the order of the sequence, so the converted
table does not hold them in the order they were inserted in. This is
safe even when that order is what the table was built for
(`TABLE::keep_row_order`, set for `ROWNUM()` with GROUP BY or ORDER
BY): before HEAP supported blobs, such a tmp table was created in the
disk engine from the start and these statements simply ran, so refusing
here would be a regression against that behavior.
1. `ROWNUM()` values are materialized into the tmp table rows during
the fill, before the window computation begins; the copy moves them
verbatim, so their pairing with the rows can not change.
2. The rowid sequence is translated in place, so the running window
computation continues over exactly the same row order, and
tie-sensitive window function values (`ROW_NUMBER`, frames over tied
keys) keep the values the interrupted pass had already produced.
3. Consumers that scan the converted table afterwards see the rows in
the copy order. Among rows with equal sort keys that order differs
from the insertion order, but such tie order is unspecified and
already differs between the memory and disk tmp engines.
The converted table keeps `keep_row_order= true` so the copy order is
also the order the disk engine preserves from then on.
Tests, in `heap.blob_window_overflow` unless noted: a single window
over all rows and a partitioned window overflow mid-computation and
must transparently convert; the side-effecting window expression test
verifies that the user variable is assigned exactly once per row,
against the same query that does not overflow; a fourth test covers the
sequence being an array in memory instead of a merge file; a fifth test
covers the `keep_row_order` conversion, verifying that every `ROWNUM()`
value keeps its insertion-order pairing with its row across the
conversion; a sixth test covers blob values larger than a HEAP
allocation block surviving the conversion of the pending row;
`heap.blob_window_overflow_encrypt` runs the conversion with encrypted
temporary files; `heap.blob_window_overflow_debug` covers the failure
to re-create a slave cache of the sequence
(`simulate_window_seq_slave_oom`), which previously hung the server in
the cursor destructor.
Seven code fixes, a new test, and test re-recordings for issues found
by CI on PR #5222.
**NULL dereference in `create_tmp_field()`**: `SYS_REFCURSOR` plugin
returns NULL from `make_new_field()` (cursor values cannot be
materialized). The feature added `result->flags |= FIELD_PART_OF_TMP_UNIQUE`
without a NULL check. Added `if (result)` guard.
**xmltype identity loss and recursive CTE reclength mismatch in
`Item_type_holder::create_tmp_field_ex()`**: the blob_key dispatch now
requires both: (1) `type_handler_for_tmp_table()` returns
`blob_key_type_handler()`, AND (2) `dynamic_cast<Type_handler_blob_common*>`
confirms the original type is a native blob. Condition 1 excludes xmltype
(its override returns itself). Condition 2 excludes VARCHAR types promoted
via `varstring_type_handler()` -> `too_big_for_varchar()` ->
`blob_type_handler()`. Without condition 2, wide VARCHAR in recursive CTEs
(e.g. `cast('...' as varchar(1000))`) was promoted to `Field_blob_key` in
the main UNION DISTINCT table (`part_of_unique_key=true`) but stayed as
`Field_varchar` in the incremental table (`part_of_unique_key=false`),
causing a `reclength` mismatch assertion in
`select_union_recursive::send_data()` (`main.json_equals` crash).
**Spurious `reclength > HA_MAX_REC_LENGTH` in `pick_engine()`**: the
original `choose_engine()` (both 10.11 and upstream/main) never had a
reclength check. MDEV-38975 introduced it when replacing the
`blob_fields` condition. HEAP has no internal reclength limit --
`hp_create.c` stores `uint reclength` and allocates blocks of that size;
`max_supported_record_length()` is only checked in `unireg.cc` during
user-facing CREATE TABLE. I_S tables like SLAVE_STATUS routinely have
reclength ~880KB (13 bare `Varchar()` columns). The check forced them to
Aria where `fill_slave_status()` returned 0 rows. Removed the check and
the unused `reclength` parameter from `pick_engine()`.
**Multi-update `tmp_memory_table_size` override**: the 10.11 feature
overrode `big_tables=FALSE` for multi-update dedup tables. The forward-port
translated this as `tmp_memory_table_size=SIZE_T_MAX` when the variable
was 0. But `big_tables=FALSE` was a soft "don't force disk" hint, while
`tmp_memory_table_size=SIZE_T_MAX` overrides the user's explicit
`tmp_memory_table_size=0` directive. Since main removed `big_tables`
entirely (MDEV-19713), the override is not needed. Removed.
**Zero-length key rejection in `check_tmp_key()`**: reject `key_len == 0`
to prevent useless zero-length keys from being created by `add_tmp_key()`.
Reachable when all key parts are CHAR(0) NOT NULL: `key_length()` returns
0, the field is not nullable (no HA_KEY_NULL_LENGTH) and not
VARCHAR/BLOB/GEOMETRY (no HA_KEY_BLOB_LENGTH), so `fld_store_len` is 0
for every part. Without this guard, `check_tmp_key()` would accept the
key (0 <= max_key_length), and the optimizer would create a ref key that
cannot distinguish any rows. Added `heap.char0_key` test exercising this
via a materialized derived table with CHAR(0) NOT NULL join columns.
**Non-deterministic `column_compression` test**: HEAP blob support allows
compressed VARCHAR/TEXT temp tables to stay in HEAP instead of falling to
Aria, changing row iteration order. Added `--sorted_result` to the two
MDEV-24726 subqueries that lack `ORDER BY`.
Test changes:
- `spatial_utility_function_collect`: added ORDER BY to window function
that lacked it (results were engine-row-order-dependent)
- `tmp_space_usage`: removed multi-update override; forced disk for
MDEV-34016/34060 Aria-specific test sections (blob I_S tables now
stay in MEMORY)
- `blob_update_overflow`: replaced `SHOW STATUS LIKE 'Created_tmp_%'`
with targeted I_S query (Created_tmp_files varies on sanitizer builds)
- 'funcs_1.is_tables_is' ; re-recorded test as
INFORMATION_SCHEMA.SLAVE_STATUS is now back as MEMORY table
- `column_compression`: added `--sorted_result` for MDEV-24726 queries
- `char0_key` (new): CHAR(0) NOT NULL derived table ref key rejection
- Re-recorded 8 tests for expected "temp table stays in MEMORY" changes
Remove the HA_NO_BLOBS restriction from the HEAP engine, allowing
the optimizer to keep temporary tables with BLOB/TEXT columns in
memory when they fit within max_heap_table_size / tmp_memory_table_size
limits. Additionally, advertise HA_CAN_GEOMETRY so explicit
CREATE TABLE ... ENGINE=MEMORY with GEOMETRY columns works.
Unlike other HEAP blob implementations (e.g. Percona), this patch
provides full HASH index support on blob columns, enabling efficient
lookups, GROUP BY, and DISTINCT operations directly in HEAP without
falling back to disk.
Architecture
------------
BLOB data is stored using continuation records -- additional fixed-size
records allocated from the same HP_BLOCK that holds regular rows. This
reuses existing allocation, free list, and size accounting with minimal
structural change, and avoids per-blob my_malloc() calls.
The existing single-byte visibility flag is extended into a flags byte
with bits for HP_ROW_HAS_CONT, HP_ROW_IS_CONT, HP_ROW_CONT_ZEROCOPY,
HP_ROW_SINGLE_REC, and HP_ROW_MULTIPLE_REC. Continuation records are
grouped into variable-length runs -- contiguous sequences within a leaf
block. Only the first record of each run carries a 10-byte header
(next_cont pointer + run_rec_count); inner records are pure payload.
Three storage formats, detected by flag bits via inline predicates:
Case A (HP_ROW_SINGLE_REC): single record, no header, data at
offset 0. Zero-copy read.
Case B (HP_ROW_CONT_ZEROCOPY): single run, multiple records.
Header in rec 0, data contiguous in rec 1..N-1. Zero-copy read
via chain + recbuffer.
Case C (HP_ROW_MULTIPLE_REC): one or more runs linked via
next_cont. Reassembly into blob_buff required.
Run allocation uses a two-phase strategy: (1) peek-then-unlink walk
of the free list detecting contiguous groups, (2) tail allocation
from HP_BLOCK for remaining data. A Step 3 scavenge fallback
walks the entire free list when tail allocation fails.
HP_SHARE::total_records tracks all physical records (primary +
continuation), while HP_SHARE::records remains the logical count
used by hash bucket mapping.
Reassembly buffer (HP_INFO::blob_buff) follows the same pattern as
InnoDB's blob_heap -- allocated once, grown via my_realloc, freed
on heap_reset()/close. Zero-copy cases (A/B) return pointers
directly into HP_BLOCK with no copy.
Full HASH index key handling for BLOB columns: hp_rec_hashnr(),
hp_rec_key_cmp(), hp_key_cmp(), hp_make_key(), hp_hashnr() are
extended for HA_BLOB_PART segments. Hash pre-check optimization
skips expensive blob materialization when hashes differ. PAD SPACE
collation semantics are preserved for blob key comparisons.
Field_blob_key (Monty) produces HEAP-native key format (4-byte length
+ 8-byte data pointer) directly, eliminating key buffer translation
between the SQL layer and HEAP engine.
SQL layer changes
-----------------
pick_engine() (new, extracted from choose_engine()): replaced the
blob_fields check with a reclength > HA_MAX_REC_LENGTH guard.
choose_engine() calls pick_engine() with the real reclength.
pick_engine() is also called early in finalize() with reclength=0
to predict whether the engine will be HEAP, enabling blob-aware
GROUP BY key setup that avoids unnecessary m_using_unique_constraint.
finalize(): HEAP+blob uses fixed-width rows; GROUP BY key setup sets
key_part_flag from field, uses item max_length for blob key sizing.
store_length initialized for all GROUP BY key parts. key_type uses
field->binary() to determine FIELDFLAG_BINARY vs text collation.
DISTINCT key setup skips null-bits helper for HEAP.
remove_duplicates(): blob check moved before HEAP check to fall
through to remove_dup_with_compare().
Aggregator_distinct::add(): overflow-to-disk conversion via
create_internal_tmp_table_from_heap() for non-dup write errors.
Expression cache disabled for HEAP+blob (key format incompatibility).
FULLTEXT early detection in mysql_derived_prepare(): forces disk
engine via TMP_TABLE_FORCE_MYISAM when outer query uses MATCH.
Deferred blob chain free (MDEV-39732): heap_delete() saves chain
pointers to pending_blob_chains, flushed on next mutation or
heap_reset()/close. Prevents dangling zero-copy pointers during
binlog_log_row().
REPLACE safety (MDEV-39825): HP_SHARE::write_can_replace flag
forces copy mode in hp_read_blobs(), preventing blob data corruption
from freed-then-reused continuation records during REPLACE.
Geometry GROUP_CONCAT fix (MDEV-39761): downgrade Field_geom to
Field_blob for GROUP_CONCAT temp tables in both expression creation
paths. Type_handler_geometry::type_handler_for_tmp_table() added.
Geometry GROUP BY key fix (MDEV-39871): detect when new_key_field()
produced non-blob Field_varstring for a blob column, replace with
Field_blob_key.
Performance
-----------
Non-blob tables: zero regression. Every blob-specific code path is
guarded by if (share->blob_count). No new allocations, no format
changes, no hash function changes for non-blob keys.
Blob tables: eliminates file creation/deletion overhead and page cache
management. For single-run blobs (common case), the read path is
entirely zero-copy.
Limitations
-----------
1. No BTREE indexes on blob columns (HASH only)
2. No partial-key prefix indexing for blobs
3. 2x memory for Case C reads only (A/B are 1x)
4. No blob compression
5. 65,535 records per run (uint16 cap, auto-split)
6. max_heap_table_size applies to continuation records
7. Expression cache disabled for HEAP+blob
8. FULLTEXT forces disk engine
Linked bugs fixed:
- MDEV-39703: mroonga fulltext test ordering
- MDEV-39723: ER_DUP_ENTRY on GROUP BY with blob column
- MDEV-39724: crash in hp_is_single_rec with GROUP BY
- MDEV-39732: slave crash in hp_free_run_chain on blob replication
- MDEV-39761: Field_geom::store() assertion in GROUP_CONCAT
- MDEV-39782: RBR ER_KEY_NOT_FOUND on HEAP blob UPDATE
- MDEV-39825: blob data corruption on REPLACE into HEAP table
- MDEV-39871: crash in my_hash_sort_bin on GROUP BY with geometry
Reviewed by: Michael Widenius <monty@mariadb.org>
Monty reviewed the entire patch. Areas where he suggested changes
or contributed code:
- Field_blob_key class (HEAP-native blob key format, 4-byte length +
data pointer)
- Duplicate key fix on HEAP-to-Aria conversion
- hp_blob_key_length() uint32 fix
- hp_rec_hashnr_stored removal
- type_handler_for_tmp_table() param cleanup
- Type_handler_geometry::type_handler_for_tmp_table() virtual
- blob pointer bzero()
- find_unique_row() double-materialization fix
- Tail reclaim review
- Batch tail allocation review
- hp_update.c cleanup
- Field_blob_compressed temp table fix
- row_pack_length() dedup
- pack_length_no_ptr() removal
- Race condition fix in HEAP
- MDEV-39703 mroonga test fix
- MDEV-39825 write_can_replace optimization
- is_text_key_segment removal (field->binary() simplification)
- Documentation (Docs/internal-temporary-tables.txt)
Contribution by: Alexander Barkov <bar@mariadb.com>
Type_handler::make_and_init_table_field_ex() -- refactored temp table
field creation from inline code in sql_select.cc into type handler
virtual methods (sql_type.cc, sql_type_geom.cc), enabling clean
per-type-handler field creation for HEAP blob promotion.
Atomic CREATE OR REPLACE allows to keep an old table intact if the
command fails or during the crash. That is done by renaming the
original table to temporary name, as a backup and restoring it if the
CREATE fails. When the command is complete and logged the backup
table is deleted.
Atomic replace algorithm
Two DDL chains are used for CREATE OR REPLACE:
ddl_log_state_create (C) and ddl_log_state_rm (D).
1. (C) Log rename of ORIG to TMP table (Rename TMP to original).
2. Rename orignal to TMP.
3. (C) Log CREATE_TABLE_ACTION of ORIG (drops ORIG);
4. Do everything with ORIG (like insert data)
5. (D) Log drop of TMP
6. Write query to binlog (this marks (C) to be closed in
case of failure)
7. Execute drop of TMP through (D)
8. Close (C) and (D)
If there is a failure before 6) we revert the changes in (C)
Chain (D) is only executed if 6) succeded (C is closed on
crash recovery).
Foreign key errors will be found at the 1) stage.
Additional notes
- CREATE TABLE without REPLACE and temporary tables is not affected
by this commit.
set @@drop_before_create_or_replace=1 can be used to
get old behaviour where existing tables are dropped
in CREATE OR REPLACE.
- CREATE TABLE is reverted if binlogging the query fails.
- Engines having HTON_EXPENSIVE_RENAME flag set are not affected by
this commit. Conflicting tables marked with this flag will be
deleted with CREATE OR REPLACE.
- Replication execution is not affected by this commit.
- Replication will first drop the conflicting table and then
creating the new one.
- CREATE TABLE .. SELECT XID usage is fixed and now there is no need
to log DROP TABLE via DDL_CREATE_TABLE_PHASE_LOG (see comments in
do_postlock()). XID is now correctly updated so it disables
DDL_LOG_DROP_TABLE_ACTION. Note that binary log is flushed at the
final stage when the table is ready. So if we have XID in the
binary log we don't need to drop the table.
- Three variations of CREATE OR REPLACE handled:
1. CREATE OR REPLACE TABLE t1 (..);
2. CREATE OR REPLACE TABLE t1 LIKE t2;
3. CREATE OR REPLACE TABLE t1 SELECT ..;
- Test case uses 6 combinations for engines (aria, aria_notrans,
myisam, ib, lock_tables, expensive_rename) and 2 combinations for
binlog types (row, stmt). Combinations help to check differences
between the results. Error failures are tested for the above three
variations.
- expensive_rename tests CREATE OR REPLACE without atomic
replace. The effect should be the same as with the old behaviour
before this commit.
- Triggers mechanism is unaffected by this change. This is tested in
create_replace.test.
- LOCK TABLES is affected. Lock restoration must be done after new
table is created or TMP is renamed back to ORIG
- Moved ddl_log_complete() from send_eof() to finalize_ddl(). This
checkpoint was not executed before for normal CREATE TABLE but is
executed now.
- CREATE TABLE will now rollback also if writing to the binary
logging failed. See rpl_gtid_strict.test
backup ddl log changes
- In case of a successfull CREATE OR REPLACE we only log
the CREATE event, not the DROP TABLE event of the old table.
ddl_log.cc changes
ddl_log_execute_action() now properly return error conditions.
ddl_log_disable_entry() added to allow one to disable one entry.
The entry on disk is still reserved until ddl_log_complete() is
executed.
On XID usage
Like with all other atomic DDL operations XID is used to avoid
inconsistency between master and slave in the case of a crash after
binary log is written and before ddl_log_state_create is closed. On
recovery XIDs are taken from binary log and corresponding DDL log
events get disabled. That is done by
ddl_log_close_binlogged_events().
On linking two chains together
Chains are executed in the ascending order of entry_pos of execute
entries. But entry_pos assignment order is undefined: it may assign
bigger number for the first chain and then smaller number for the
second chain. So the execution order in that case will be reverse:
second chain will be executed first.
To avoid that we link one chain to another. While the base chain
(ddl_log_state_create) is active the secondary chain
(ddl_log_state_rm) is not executed. That is: only one chain can be
executed in two linked chains.
The interface ddl_log_link_chains() was defined in "MDEV-22166
ddl_log_write_execute_entry() extension".
Atomic info parameters in HA_CREATE_INFO
Many functions in CREATE TABLE pass the same parameters. These
parameters are part of table creation info and should be in
HA_CREATE_INFO (or whatever). Passing parameters via single
structure is much easier for adding new data and
refactoring.
Aria changes:
- Fixed issue in Aria engine with CREATE + locked tables
that data was not properly commited in some cases in
case of crashes.
InnoDB related changes (by Marko Mäkelä):
- table_name_t::is_create_or_replace(): A new predicate to check for
CREATE OR REPLACE TABLE will rename an old table to
and eventually drop after creating the replacement.
- dict_table_t::parse_name(): Do acquire MDL on #sql-create- names
for partitioned tables.
- dict_table_rename_in_cache(): On CREATE OR REPLACE TABLE ... SELECT,
forget the original dict_table_t::mdl_name so that purge will
acquire MDL on the #sql-create- name instead. In this way, the
MDL_EXCLUSIVE that the CREATE OR REPLACE TABLE holds on the
user-visible name will not unnecessarily block any purge of old history
until the very end when the #sql-create- table will be dropped.
- ha_innobase::delete_table(): Do not check FOREIGN KEY consistency
when dropping an #sql-create- table.
- row_rename_table_for_mysql(): Update SYS_FOREIGN.ID also
when renaming to #sql-create- in order to avoid any
duplicate key error when CREATE OR REPLACE TABLE is
creating some FOREIGN KEY constraints by names
that existed in the old table.
Other changes:
- Removed some auto variables in log.cc for better code readability.
- Fixed old bug that CREATE ... SELECT would not be able to auto repair
a table that is part of the SELECT.
- Marked MyISAM that it does not support ROLLBACK (not required but
done for better consistency with other engines).
- maria_create_trn_for_mysql() does not register a new transaction
handler for commits. This was needed to ensure create or replace
will not end with an active transaction.
- We do not get anymore warnings about "Engine not supporting atomic
create" when doing a legal CREATE OR REPLACE on a table with
foreign key constraints.
- Updated VIDEX engine flags to disable CREATE SEQUENCE.
- Removed mysql_mutex_unlock(&LOCK_gdl) / mysql_mutex_lock(&LOCK_gdl)
around calls to binlog as these are unsafe. The binlog code uses
global variables that needs protection from other caller.
- EITS data is preserved if create or replace fails if
drop_before_create_or_replace=OFF. If ON, then create or replace
will drop EITS before the drop of the original table (as before).
- Using CREATE OR REPLACE on a encrypted table that the user cannot
decrypt will fail instead of replacing the encrypted table.
The encrypted table will unchanged.
Known issues:
- One cannot use create or replace on an InnoDB tables that has foreign
key references point to it
- CREATE OR REPLACE TEMPORARY table is not full atomic. Any conflicting
table will always be dropped before creating a new one. (Old behaviour).
Bug fixes related to this MDEV:
MDEV-36435 Assertion failure in finalize_locked_tables()
MDEV-36439 Assertion `thd_arg->lex->sql_command != SQLCOM_CREATE_SEQUENCE...
MDEV-36498 Failed CoR in non-atomic mode no longer generates DROP in RBR...
MDEV-36508 Temporary files #sql-create-....frm occasionally stay after
crash recovery
MDEV-38479 Crash in CREATE OR REPLACE SEQUENCE when new sequence cannot
be created
MDEV-36497 Assertion failure after atomic CoR with Aria under lock in
transactional context
MDEV-36501 EITS data is lost after failed attempt to CREATE OR REPLACE
table
MDEV-36493 Atomic CREATE OR REPLACE ... SELECT blocks InnoDB purge
MDEV-39367 MSAN/valgrind errors in temp_file_size_cb_func,
main.tmp_space_usage fails
MDEV-39446 Atomic CREATE OR REPLACE fails if a table cannot be decrypted
MDEV-40776 Atomic CREATE OR REPLACE silently breaks the foreign key
MDEV-40765 Assertion `"unexpected references" == 0' failed upon failing
CREATE OR REPLACE
MDEV-40994 Atomic CoR: Transaction not registered for MariaDB 2PC, but
transaction is active
Reverted commits:
- MDEV-36685 "CREATE-SELECT may lose in binlog side-effects of
stored-routine" as it did not take into account that it safe to
clear binlogs if the created table is non transactional and there
are no other non transactional tables used.
This was done because it caused extra logging when it is not
needed (not using any non transactional tables) and it also did
not solve side effects when using statement based loggging.
setup_group no longer writes Item::marker. The ONLY_FULL_GROUP_BY
check now tests GROUP BY membership by walking the GROUP BY list.
A query that defines a WINDOW but never refers to it could crash in
add_key_field, for example
WITH cte AS (SELECT i FROM (SELECT i FROM t1 GROUP BY i) dt
WINDOW w AS (PARTITION BY i))
SELECT a.i FROM cte a JOIN cte b ON a.i=b.i WHERE a.i != 5;
A query that defines a WINDOW goes through setup_group, which set
marker to MARKER_UNDEF_POS (-1) on each GROUP BY expression so that
the ONLY_FULL_GROUP_BY check could skip it. Other code reads marker
as a set of flag bits (-1 sets all bits).
Item_direct_view_ref::grouping_field_transformer_for_where then took
the ref as flagged for substitution and followed a path that ends in
the crash.
The ONLY_FULL_GROUP_BY check was the only reader of that value, so
MARKER_UNDEF_POS is removed. The necessary check is local to the
setup_group function.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
An assert was present in method recompute_join_cost_with_limit()
to make sure the recomputed cost is always >= 0.
Although, the assert was correct, it was failing in
CLang compiled versions due to floating point comparison,
when we set sql_select_limit=1, and
optimizer_join_limit_pref_ratio=1;
In GCC compiled versions, the partial_join_cost was computed to +0.0.
However, in CLang version, the cost turned out to be -0.0.
Changed the assert such that partial_join_cost, would now be checked for
a value >= -DBL_EPSILON. Following it, we set the partial_join_cost to
0, if it has a negative value.
Adds a static JOIN_TAB_RANGE::create(thd, count) helper that allocates
a JOIN_TAB array and wraps it in a JOIN_TAB_RANGE, replacing the
inline allocation in JOIN::get_best_combination(). Also gives
JOIN_TAB_RANGE::start and ::end default initializers and documents
their half-open range convention.
Aggregates lacked the SQL-standard FILTER clause,
forcing CASE-based workarounds that reduced readability across (sum, avg, count, …).
This update introduces the ability to specify a FILTER clause for aggregate functions,
allowing for more granular control over which rows are included in the aggregation.
Also, improves standards compliance and makes queries clearer and more readable.
The FILTER(WHERE ...) condition may contain any expression allowed in regular WHERE clauses,
except window functions, and outer references.
Introduces new dbug_print() functions for the following types:
dbug_print(NESTED_JOIN)
dbug_print(JOIN)
dbug_print(TABLE_LIST)
dbug_print(JOIN_TAB)
dbug_print(List<Item>)
Typically pointers to instances of the given types are passed.
The dbug_print(List<Item>) is intended to show result row before
sending to client (e.g., invoke in debugger while tracing end_send)
but may work in other contexts.
These functions produce nicely formatted output, please use them
in conjunction with the formatted printfs available in GDB or LLDB:
(gdb) printf "%s", dbug_print(join_tab)
(lldb) p printf("%s", dbug_print(table))
Example output from dbug_print(JOIN) in LLDB. The last line with
value 1302 is the number of characters produced by the printf
command.
(lldb) p printf("%s",dbug_print(join))
JOIN [0x15801bfe8] table_count=3 const_tables=0
join_list 0x158018340 [1 element(s)]:
--- #0 ---
[0x15801af10] "(nest_last_join)" join=INNER (outer_join=0) nested_join=0x15801b618 (elements=2)
NESTED_JOIN [0x15801b618]
used_tables: 0x0
not_null_tables: 0x0
n_tables: 0
counter: 0
nest_type: 1 (JOIN_OP_NEST)
nj_map: 0x0
join_list:
join_list 0x15801b618 [2 element(s)]:
--- #0 ---
[0x15801a308] "t3" join=LEFT (outer_join=1) map=0x4
ON: `test`.`t2`.`a` = `test`.`t3`.`a`
--- #1 ---
[0x158019b30] "(nest_last_join)" join=INNER (outer_join=0) nested_join=0x15801a238 (elements=2)
NESTED_JOIN [0x15801a238]
used_tables: 0x0
not_null_tables: 0x0
n_tables: 0
counter: 0
nest_type: 0
nj_map: 0x0
join_list:
join_list 0x15801a238 [2 element(s)]:
--- #0 ---
[0x1580187c0] "t1" join=RIGHT (outer_join=2) map=0x1
ON: `test`.`t1`.`a` = `test`.`t2`.`a`
--- #1 ---
[0x158018f08] "t2" join=INNER (outer_join=0) map=0x2
(int) 1302
There are 2 problems: -
1. The REPLACE statement that is recorded doesn't store the
value of geometry type field correctly.
2. The table definition that got recorded has fields with non-null constraint,
and no default value is specified.
Also, the "REPLACE INTO" statement that gets stored in the context,
doesn't have any value specified for these non-null fields.
Solution is to: -
1. When using REPLACE INTO statement, store all the non-numeric values in HEX,
whenever conversion from field's charset to output's charset is lossy.
2. Instead of storing only the column values that were projected in the
query, store all the non-virtual column values into the recorded
REPLACE INTO statement.
Implementation details: -
1. Introduce a new method is_charset_conversion_lossless() in filesort.cc,
to check if the output charset to which field's data is being written to,
results in a lossless conversion. If so, non-numeric values being witten
using REPLACE INTO statement are stored in string representation,
else they are converted to HEX.
2. Modify join_read_const(), and join_read_system() methods in sql_select.cc,
and opt_sum_query() method in opt_sum.cc the following way: -
a. Extend the read_set to make sure, we read all the non-virtual column
using Optimizer_context_recorder::prepare_captured_row_read().
This method also saves the original read_set.
b. Read the row.
c. Dump the row into the context when no error is noticed while
reading. Irrespective of the error, restore back the read_set state to
the original using Optimizer_context_recorder::finish_captured_row_read()
Functions that record actions on base tables accept TABLE* argument and
use table->s->{db,table_name} as the table's name.
The only different usage is in store_optimizer_context() where
we encounter a TABLE_LIST object which may be a base table or a VIEW.
- Introduce init_optimizer_context_recorder_if_needed() which is called
at query start. We n longer create the recorder lazily.
- Recorder functions no longer take MEM_ROOT to store data on as an
argument. The recorder knows the MEM_ROOT it should use.
1. Add escaping in strings if they contain quotes in the names
2. Record CONST insert statement for MYISAM system table
3. Add tests to oad the sql_script file that is produced with
record_context=ON and then use it to run same queries on empty tables.
This task loads the stats of the tables that are used in a query,
from the trace into the optimizer. This feature is controlled
by optimizer_replay_context, and points to an user defined variable name,
that has the context information.
The stats such as num_of_records present in the table, indexes if present,
their names, along with the average number of records_per_key
with in each index, cost of reading an index are loaded from the trace.
Additionally, stats from range analysis i.e. ranges, and the
corresponding number of records, along with their costs are also
loaded from the trace.
The trace context which is in JSON format is firstly set into the
user defined session variable, and this variable name is set to
optimizer_replay_context system variable.
Later, when a user issues a query, the contents of the json context is
parsed and an in memory representation is built using the class
Optimizer_context_replay. This class is then used by the optimizer
to update and save original values of the tables, indexes, and range stats
in the methods "set_statistics_for_table()" of sql_statistics.cc, and
"check_quick_select()" of opt_range.cc.
After the query gets finished, the statistics that were updated
in the optimizer are restored back to the saved original values.
The entry point for parsing the json structure is in
"mysql_execute_command()" of sql_parse.cc, and similarly exit point
i.e. to restore the saved stats of the optimizer is at the end
of the same method.
Two new warning/error messages are introduced:
ER_JSON_OPTIMIZER_REPLAY_CONTEXT_PARSE_FAILED, and
ER_JSON_OPTIMIZER_REPLAY_CONTEXT_MATCH_FAILED
When starting to do a full table/index scan without a WHERE or JOIN
condition, tell the storage engine so and the corresponding
ulong-truncated LIMIT.
Include an innodb implementation: added an innodb switch
table_lock_on_full_scan, so that when the switch is on, on receiving
the full scan signal from the sql layer, if the truncated LIMIT is
ULONG_MAX (likely no LIMIT), attempt to acquire a table lock.
Updated tests that have different results with the switch on.
The three deadlock_*_race tests cannot reach their DEBUG_SYNC race
under a table lock (it degenerates to a timeout), and the three I_S
tests only restate a lock-mode change already covered
by innodb_full_scan.test.
(Comment and code edited by Sergei Petrunia <sergey@mariadb.com> and
Thirunarayanan Balathandayuthapani <thiru@mariadb.com>)
When a NATURAL JOIN operates on a derived table (or view with
derived_merge=off) having more than 64 columns, the optimizer's
generate_derived_keys_for_table() would:
1) Overflow a 32-bit shift: (key_part_map)(1 << parts) when parts >= 32
2) Create a derived key with more parts than Bitmap<64>/key_part_map can hold
3) Crash on DBUG_ASSERT(prefix_size <= width) in Bitmap<64>::is_prefix(65)
Fix: cap the number of hash-join KEYUSE entries for a materialized derived
table at the handler's max_key_parts(). Excess columns are excluded from the
key, so generate_derived_keys_for_table() never builds a key with more parts
than key_part_map (64 bits) or Bitmap<64> can represent. Also fix the shift
to cast before shifting: (key_part_map) 1 << parts.
This patch to mysqldump is only merged partially:
commit 666ff1db36
Author: Monty <monty@mariadb.org>
Date: Fri May 15 16:39:35 2026 +0300
MDEV-39519 Change mariadb-dump to use SHOW REPLICA STATUS instead of SHOW SLAVE STATUS
The functionality for --dump-slave cannot be merged as the underlying
functionality was removed in this commit:
commit 21c940d599
Author: ParadoxV5 <paradox.ver5@gmail.com>
Date: Thu Dec 4 21:22:20 2025 -0700
MDEV-37146 mariadb-dump: Replace SHOW SLAVE STATUS usage with information_schema.SLAVE_STATUS
Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
