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.
Allowing prepared statements in stored functions when
a stored function is used in an assignment right hand.
Both DEFAULT clause of a variable initialization and
the right side of the SET statement are supported:
CREATE PROCEDURE p1()
BEGIN
-- case 1: DEFAULT clause
DECLARE spvar1 INT DEFAULT f1_with_ps(); -- OK
-- case 2: SP variable assignment statement
DECLARE spvar2 INT;
SET spvar2= f1_with_ps(); -- OK
END;
- Only assignments to SP variables works for now:
* SET spvar= func_with_ps(); -- OK
* SET @uvar= func_with_ps(); -- Error
- Only bare function calls are supported for now. Using a function in
an expression does not make it PS-safe yet:
SET v= f1()+0;
- The parser now does not reject PS statements in stored functions.
PS applicability in stored functions is now detected at run time.
Note, PS statements in triggers are still prohibited by the parser.
- Functions with PS do not acquire MDL locks on tables, and no MDL is
taken on the routines themselves either. They work like procedures in
terms of table opening and routine locking: a concurrent DROP FUNCTION
can complete while such a function is executing.
- Functions with PS are not replicated as a single `SELECT f1()` call.
They are replicated per-statement, like procedures.
Helper changes:
- Changing the return result for LEX::sp_variable_declarations_init()
from void to bool to catch errors in the caller properly.
Misc:
- This patch incorporates fixes for the following bugs found during debugging:
MDEV-40224,MDEV-40225,MDEV-40226,MDEV-40227,MDEV-40240,
MDEV-40285,MDEV-40288,MDEV-40315,MDEV-40318,MDEV-40890,MDEV-40900,
MDEV-40901,MDEV-40913,MDEV-40914,MDEV-41013,MDEV-41015,MDEV-41019
Assisted-by: Claude - reviews and minor clean-ups
Move the initialization of context recorder, and replay after
run_set_statement_if_requested() is invoked in the
mysql_execute_command() in sql_parse.cc
This allows to record and save both Optimizer Context and Trace with
straightforward command sequence:
set optimizer_record_context=1, optimizer_trace=1;
explain select ... ;
select context from information_schema.optimizer_context;
select trace from information_schema.optimizer_trace;
- 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.
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
This feature stores the basic stats of the base tables that are used in
a query, to the optimizer trace. This feature is also controlled by
optimizer_record_context, and is not enabled by default. The stats
such as num_of_records present in the table, indexes if present
then their names, along with the average number of records_per_key
with in each index are dumped to the trace. Additionally, stats from
range analysis of the queries are also dumped into the trace
The approach taken here is to extend the existing function
store_tables_context_in_trace() and add new dump_table_stats_to_trace()
to opt_trace_ddl_info.cc. For storing the range analysis stats into the
trace, they are first recorded in the hash defined in
Optimizer_Stats_Context_Recorder, and is used later while dumping the
stats into the trace from dump_table_stats_to_trace().
Several new tests are also added to opt_trace_store_stats.test
Binding DEFAULT to a LIMIT ? placeholder via EXECUTE ... USING is never
rejected at bind time. unit->set_limit() later evaluates it and sets
ER_INVALID_DEFAULT_PARAM in the Diagnostics_area, but SQLCOM_UPDATE (and
SQLCOM_UPDATE_MULTI, SQLCOM_INSERT_SELECT/REPLACE_SELECT) proceeded into
open_tables() regardless. A concurrent metadata change (e.g. ALTER TABLE
FORCE) then triggers Reprepare_observer::report_error(), which tries to
set ER_NEED_REPREPARE on an already-set DA and hits the assert.
Add the same thd->is_error() check after unit->set_limit() (as in
SQLCOM_DELETE), aborting before open_tables() is reached.
The "is it my own thread" check compared the login user name
(Security_context::user) and ignored the host, so u1@localhost could see
and kill threads of u1@'127.0.0.1'.
Compare the authenticated account instead:
* all comparisons are done in sctx->is_priv_user() now
* change user_matches() to priv_user_matches(), which uses is_priv_user()
* use it in KILL, KILL USER, SHOW PROCESSLIST, I_S.PROCESSLIST,
COM_PROCESS_INFO and SHOW EXPLAIN/ANALYZE FOR.
* all the remaining places use is_priv_user() directly instead
of doing strcmp: SHOW GRANTS, SHOW CREATE PROCEDURE, I_S.VIEWS,
the DEFINER clause, optimizer trace and change_security_context().
Assisted-by: Claude:claude-5-opus
WITH GRANT OPTION is not a privilege in the standard sense,
so whenever INFORMATION_SCHEMA visibility dictates "any privilege"
this does not include WITH GRANT OPTION, even though GRANT_ACL
is a privilege in MariaDB.
Assisted-By: Claude:claude-4.6-sonnet
check_stack_overrun() was compiled out under ASAN, so a deeply nested
expression recursed in Item_func::fix_fields() until the stack was
exhausted and the server crashed instead of reporting
ER_STACK_OVERRUN_NEED_MORE.
Since MDEV-34533 (Monty) the stack usage seems to be accounted correctly
under ASAN via my_get_stack_pointer(), so the check seems to works there
too.
Fix:
Remove the #ifndef __SANITIZE_ADDRESS__ guard from check_stack_overrun()
Add a test for ER_STACK_OVERRUN_NEED_MORE
SQLCOM_REVOKE_ALL needs it because SQLCOM_REVOKE does.
SQLCOM_DROP_ROLE needs it because SQLCOM_DROP_USER does.
SQLCOM_CREATE_SERVER / SQLCOM_ALTER_SERVER / SQLCOM_DROP_SERVER
need it because SQLCOM_CREATE_USER / etc do.
introduce a dedicated enum type for sql_command_flags
to simplify debugging:
(gdb) p sql_command_flags[SQLCOM_SELECT]
$2 = (CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS | CF_PREOPEN_TMP_TABLES | CF_CAN_BE_EXPLAINED | CF_OPTIMIZER_TRACE)
This patch implements support for implicit query block (QB) names in
optimizer hints, allowing hints to reference query blocks and tables
within derived tables, views and CTEs without requiring explicit
QB_NAME hints.
Examples.
-- Addressing a table inside a derived table using implicit QB name
select /*+ no_index(t1@dt) */ *
from (select * from t1 where a > 10) as DT;
-- this is an equivalent to:
select /*+ no_index(t1@dt) */ * from
(select /*+ qb_name(dt)*/ * from t1 where a > 10) as DT;
-- Addressing a query block corresponding to the derived table
select /*+ no_bnl(@dt) */ *
from (select * from t1, t2 where t.1.a > t2.a) as DT;
-- View
create view v1 as select * from t1 where a > 10 and b > 100;
-- referencing a table inside a view by implicit QB name:
select /*+ index_merge(t1@v1 idx_a, idx_b) */ *
from v1, t2 where v1.a = t2.a;
-- equivalent to:
create view v1 as select /*+ qb_name(qb_v1) */ *
from t1 where a > 10 and b > 100;
select /*+ index_merge(t1@qb_v1 idx_a, idx_b) */ *
from v1, t2 where v1.a = t2.a;
-- CTE
with aless100 as (select a from t1 where b <100)
select /*+ index(t1@aless100) */ * from aless100;
-- equivalent to:
with aless100 as (select /*+ qb_name(aless100) */ a from t1 where b <100)
select /*+ index(t1@aless100) */ * from aless100;
Limitations:
- Only SELECT statements support implicit QB names. DML operations
(UPDATE, DELETE, INSERT) only support explicit QB names
Originally two MDL_EXCLUSIVE are used for pure alias of derived table
and its schema which is empty db name. It is concurrency performance
overhead and there is no obvious reason why such MDL should exist.
MDEV-33985 changed pure alias check from `db.str` to `db.length` and
that allowed `empty_c_string` to be pure alias too. It is important
for MDEV-33985 fix with sequences. Now such kind of pure alias skips
MDL initialization and thus derived table fails falsely on BACKUP
namespace as it is default namespace for 0-filled structure (see TODO
comments).
The fix a) keeps MDL_NOT_INITIALIZED for uninitialized MDL and that
was the consequence of MDL_INTENTION_EXCLUSIVE value 0 (see TODO
comment).
And b) does not update MDL type for pure alias when view is
initialized, so it is MDL_NOT_INITIALIZED. `lock_table_names()` code
naturally skips such kind of MDL with (mdl_request.type <
MDL_SHARED_UPGRADABLE) condition when processing tables list.
Optimization in st_select_lex_unit::prepare() removes ORDER BY for
certain subqueries. That excludes ORDER BY items from being fixed, but
sl->window_funcs still contains window function items and those
related to optimized out ORDER BY are unfixed. The error about missing
window spec is thrown when the item is fixed. Hence we get redundant
processing of window function items without checking window spec
existence.
The fix removes the related window function items when ORDER BY is
optimized out. ORDER accumulates window_funcs at parser stage which
are then removed from SELECT_LEX::window_funcs. The fix also updates
similar optimization in mysql_make_view().
SHOW CREATE SERVER was accessible to any user with a global
privilege, without requiring FEDERATED ADMIN. Add a privilege
check before mysql_show_create_server() so that only users
with FEDERATED ADMIN can view server definitions.
