Extending MariaDB with Native Aggregate Plugins: Laying the Groundwork for HyperLogLog

MariaDB already allows developers to add new Pluggable Data Types and scalar Plugin Functions. One missing piece has been Pluggable Aggregate Functions operating on PDTs. That matters for functionality such as HyperLogLog, where an extension needs to aggregate values into a custom statistical sketch while preserving its native SQL type.

MDEV-40672 closes that architectural gap. Aggregate functions can now participate in MariaDB’s native aggregation infrastructure, including DISTINCT, window execution, prepared statements and Pluggable Data Types.

MariaDB already supports MariaDB_FUNCTION_PLUGIN, a mechanism for registering SQL functions that create regular server Item objects and can behave almost like built-in functions. Pluggable aggregate functions, however, have historically remained tied to the legacy UDF ABI:

CREATE AGGREGATE FUNCTION avgcost
RETURNS REAL
SONAME 'udf_example.so';

The legacy ABI predates MariaDB’s modern type system. It represents arguments and results using a small set of generic categories:

STRING_RESULT
REAL_RESULT
INT_RESULT
DECIMAL_RESULT

This is sufficient for traditional UDFs, but not for functions that need to accept and return Pluggable Data Types such as UUID, INET, vectors, statistics sketches, or other values with their own Type_handler and native binary representation. Actually HyperLogLog functionality that needs PDT as input and output for aggregation functions.

This article describes how the existing function plugin API has been extended to support native aggregate functions. The work is tracked in MDEV-40672: Pluggable aggregate functions and implemented in MariaDB/server pull request #5522. The core design principle is:

Do not create a second independent registry or another UDF ABI. Reuse MariaDB_FUNCTION_PLUGIN, allow its descriptor to declare an aggregate function, and let its builder create an Item_sum_plugin.

1. Why the Legacy Aggregate UDF ABI Is No Longer Sufficient

A legacy aggregate UDF exports a collection of C symbols:

name_init
name_clear
name_add
name_remove
name
name_deinit

State is normally stored in UDF_INIT::ptr, while arguments are passed through UDF_ARGS.

This design has several fundamental limitations.

Loss of Exact SQL Type Identity

UDF_ARGS::arg_type contains an Item_result, not a Type_handler. UUID, INET, or a custom vector is typically reduced to a string or binary representation.

The extension cannot reliably determine:

which pluggable type was passed;
which parameters instantiate that type;
what its native representation is;
which Field should store the result;
how to preserve the type through a temporary table or CTAS.

Limited Result Metadata

A UDF can adjust max_length, decimals, and maybe_null, but it cannot return a new plugin-provided Type_handler.

An aggregate that logically returns UUID therefore remains a STRING or BLOB from the SQL type system’s perspective.

A Separate Lifecycle

The legacy UDF API duplicates parts of MariaDB’s existing Item_sum lifecycle:

clear
add
remove
result
cleanup

The server already has native infrastructure for these operations:

Item_sum;
Aggregator_simple;
Aggregator_distinct;
window cursors;
temporary-table execution;
aggregate copy semantics.

It is more sustainable to connect plugins to this infrastructure than to maintain two parallel aggregate systems.

2. Why `MariaDB_FUNCTION_PLUGIN` Was Almost Sufficient

A function plugin descriptor stores a builder:

class Plugin_function
{
  int m_interface_version;
  Create_func *m_builder;
};

The builder returns:

Item *

Since Item_sum derives from Item, a function plugin could technically always return an aggregate item.

That alone is not enough because of parser timing.

Aggregate Context Must Be Known Before the Item Exists

While parsing:

my_aggregate(column)

the parser must already know that the expression is an aggregate in order to:

increment in_sum_expr;
reject nested aggregates;
resolve outer references correctly;
select the aggregation query block;
process window syntax correctly.

The function plugin builder runs only after the argument list has been parsed. If the function kind is known only from the resulting Item, the decision comes too late.

Legacy UDFs solved this by calling find_udf() early and checking UDFTYPE_AGGREGATE.

Native plugins therefore need to expose their function kind in the descriptor.

3. Scalar and Aggregate as a Builder Property

The function descriptor was not extended with a kind flag. Plugin_function still stores only a builder:

class Plugin_function
{
  int m_interface_version;
  Create_func *m_builder;
public:
  Plugin_function(Create_func *builder);
  Create_func *create_func();
};

Instead, the aggregate nature is a property of the builder class. The server already has a marker base class for aggregate builders:

class Create_aggregate_func : public Create_native_func
{
protected:
  Create_aggregate_func() = default;
  virtual ~Create_aggregate_func() = default;
};

A scalar function’s builder derives from a scalar Create_func (typically Create_native_func); an aggregate function’s builder derives from Create_aggregate_func:

class Create_func: public Create_aggregate_func
{
public:
  Item *create_native(THD *thd, const LEX_CSTRING *name,
                      List<Item> *item_list) override
  {
    // ... argument checks ...
    return new (thd->mem_root) Item_sum_test_plugin_count(thd, item_list->head());
  }
};

Both are registered the same way; there is no AGGREGATE argument:

static Create_func creator;
static Plugin_function descriptor(&creator);

The parser recovers the kind with a dynamic_cast on the builder rather than reading a descriptor field:

aggregate= dynamic_cast<Create_aggregate_func *>(native_builder) != NULL;

No new plugin type is required. Both scalar and aggregate functions continue to use:

MariaDB_FUNCTION_PLUGIN

Why This Is Better Than a New Registry

There is still one function namespace.
Existing name lookup remains authoritative.
Functions share the same metadata facilities.
Plugin locking is not duplicated.
Scalar and aggregate members can live in the same shared library.
Existing scalar plugins continue to work unchanged.

4. Early Lookup in the Parser

Before parsing the arguments of a generic function call, the parser looks up the builder and asks:

Does a native builder exist?
Is that builder a Create_aggregate_func?

Create_func *native_builder= Schema::find_implied(thd)->
  find_native_function_builder(thd, sysname);
aggregate= dynamic_cast<Create_aggregate_func *>(native_builder) != NULL;

Legacy aggregate UDFs are still detected in the same place, by checking udf->type == UDFTYPE_AGGREGATE.

When the builder is an aggregate, the parser enters aggregate context before the arguments are parsed:

if (aggregate && unlikely(Lex->current_select->inc_in_sum_expr()))
{
  thd->parse_error();
  MYSQL_YYABORT;
}

After the arguments are parsed, the context is restored (Select->in_sum_expr--), the builder creates the Item, and the server validates the returned object with dynamic_cast instead of comparing a declared kind:

Item_sum *sum_item= item ? dynamic_cast<Item_sum *>(item) : NULL;
if (function_plugin && sum_item)
{
  Item_sum_plugin *plugin_item= dynamic_cast<Item_sum_plugin *>(sum_item);
  if (!plugin_item)
  {
    plugin_unlock(NULL, function_plugin);
    my_error(ER_INTERNAL_ERROR, MYF(0),
             "Aggregate function plugin did not return Item_sum_plugin");
    MYSQL_YYABORT;
  }
  // set_function_plugin() ties the locked plugin to the item
  ...
}

An aggregate plugin must return an Item_sum_plugin, not an arbitrary Item_sum, because Item_sum_plugin owns function and type plugin lifetime management.

Defending Against Malformed Plugins

Because the kind is derived from the builder and the returned item by dynamic_cast, a plugin can no longer mislabel itself through a mismatched descriptor field. The remaining failure mode is a function plugin whose builder returns an Item_sum that is not an Item_sum_plugin. That case is rejected with a controlled SQL error (ER_INTERNAL_ERROR, “Aggregate function plugin did not return Item_sum_plugin”) instead of an assertion or corrupted parser state.

5. `Item_sum_plugin`: A Minimal Native Aggregate API

The base class is intentionally small:

class Item_sum_plugin : public Item_sum
{
public:
  bool fix_fields(THD *, Item **) override;
  Item *aggregation_arg(uint i);
};

A derived aggregate implements only its own semantics:

class Item_sum_test_plugin_count : public Item_sum_plugin
{
  longlong count;

public:
  void clear() override;
  bool add() override;
  void remove() override;
  longlong val_int() override;
};

Item_sum_plugin centralizes common responsibilities:

aggregate identity;
generic fix_fields();
conservative quick_group=false behavior;
function plugin lifetime;
data type plugin dependencies;
argument access during DISTINCT replay.

6. The First Crash: Aggregate Arguments Must Be Fixed Correctly

The first test aggregate derived from the built-in Item_sum_count and worked. After switching to its own state, it derived directly from Item_sum_plugin, and the server crashed in:

Item_field::used_tables()
Item_sum::update_used_tables()
JOIN::optimize_inner()

The optimizer was not the root cause. The new base class no longer inherited the Item_sum_num::fix_fields() behavior that had previously fixed the arguments.

The argument Item_field remained unresolved, leaving its field pointer as NULL.

A generic Item_sum_plugin::fix_fields() must:

call init_sum_func_check();
fix every argument;
collect expression flags;
call fix_length_and_dec();
acquire type plugin dependencies;
call check_sum_func();
preserve orig_args;
mark the Item as fixed.

The lesson is broader than this bug:

If an extension author must know which inherited fix_fields() implementation used to perform hidden work, the external API is not constrained enough.

7. Pluggable Data Types End to End

The goal was not merely to pass UUID as bytes. The aggregate had to preserve its exact result type:

SELECT test_plugin_first(uuid_column)
FROM t;

The result must retain the UUID Type_handler.

A CTAS query verifies the metadata path:

CREATE TABLE result AS
SELECT test_plugin_first(uuid_column) AS value
FROM t;

SHOW CREATE TABLE result;

The expected column is:

`value` uuid

not VARCHAR, VARBINARY, or BLOB.

A Type-Preserving Aggregate

test_plugin_first derives from both:

Item_sum_plugin
Type_handler_hybrid_field_type

Its fix_length_and_dec() preserves:

the argument’s Type_std_attributes;
the argument’s Type_handler;
nullability;
extra type attributes.

Its result interface includes:

const Type_handler *type_handler() const override;
bool val_native(THD *, Native *) override;
const Type_handler *real_type_handler() const override;

8. Why the Aggregate Uses `Item_cache`

The aggregate could store native bytes in a private buffer, but it would then have to implement every conversion itself:

val_str;
val_int;
val_real;
val_decimal;
get_date;
val_native;
type-specific conversions.

Instead, it asks the argument for the correct cache implementation:

value = args[0]->get_cache(thd);
value->setup(thd, args[0]);

For UUID, the server creates Item_cache_fbt, which reads through val_native().

Benefits include:

the cache is selected by the Type_handler;
the aggregate does not know the UUID binary layout;
the normal Item API remains available;
one aggregate works with built-in and plugin types;
result conversion and temporary fields use server infrastructure.

9. Cleanup and `Item_cache` Lifetime

The first PDT test returned the correct UUID and then crashed after query execution:

pure virtual method called
Item_sum_test_plugin_first::cleanup()
Query_arena::free_items()

The aggregate called:

value->clear();

from its own cleanup method. However, the Item_cache is an independent Item in the query arena and could already have been destroyed before the aggregate.

The aggregate cleanup should only clear its pointer:

value = nullptr;

The query arena owns the cache itself.

The aggregate’s runtime clear() method can still reset the cache while it is known to be alive.

10. `DISTINCT`: Two Different Storage Paths

Native Aggregator_distinct uses two different mechanisms.

Fixed-Size and Normally Comparable Values

Item
  -> temporary Field representation
  -> Unique tree
  -> tree walk
  -> aggregate add()

Unique stores unique binary keys in memory and can spill sorted chunks to disk when needed.

BLOB and TEXT Values

Item
  -> internal temporary TABLE with a unique key
  -> handler table scan
  -> aggregate add()

A BLOB cannot safely be represented as a fixed raw record key because the record may contain pointers and variable-length metadata.

11. Why the Existing `SUM(DISTINCT)` Path Does Not Work for PDTs

The non-COUNT path in Aggregator_distinct was designed for numeric aggregates and calls:

make_num_distinct_aggregator_field()

Fixed-binary handlers such as UUID deliberately reject this operation.

Plugin aggregates therefore use the general temporary-table path, similar to COUNT(DISTINCT), but instead of applying special count logic, the server replays unique records through the plugin’s ordinary add() method.

This supports:

integers;
UUID;
TEXT and BLOB;
other pluggable types with a valid temporary Field.

12. `aggregation_arg()` Instead of Direct `args[]` Access

During ordinary aggregation, the plugin must read the current source row. During DISTINCT replay, the current value lives in an internal temporary Field.

The plugin therefore calls:

aggregation_arg(0)

In ordinary mode, this returns:

args[0]

During replay, it returns an Item_field bound to the current temporary record.

For example:

value->store(aggregation_arg(0));
value->cache_value();

The plugin remains unaware of whether the server is using Unique, a HEAP table, or an on-disk temporary table.

13. The Second Silent Bug: Missing `endup()`

The initial UUID DISTINCT test formally passed after running MTR with --record, but the recorded result was NULL.

Aggregator_distinct::add() only collects unique values. The actual aggregate state is computed later by:

Aggregator_distinct::endup();

The test_plugin_first result accessors did not call endup(), so replay never happened.

Every result boundary must finalize the aggregator:

val_int();
val_real();
val_str();
val_decimal();
get_date();
val_native();
is_null();

is_null() is particularly important. An expression such as:

aggregate(DISTINCT value) IS NULL

may never invoke any val_* accessor.

The testing lesson is equally important:

MTR --record does not validate correctness. It only records current behavior. Newly recorded results must still be reviewed semantically.

14. Window Functions

The generic function parser was extended so that OVER accepts:

UDF_SUM_FUNC
PLUGIN_SUM_FUNC

A scalar plugin used with OVER is still rejected.

For a regular aggregate, MariaDB’s window executor chooses between two paths.

Invertible Aggregate

When:

supports_removal() == true

the server moves the frame incrementally:

add(new row)
remove(expired row)

The test count aggregate implements remove().

Non-Invertible Aggregate

When removal is not supported, the server uses Frame_scan_cursor:

clear()
scan complete frame
add(each row)

test_plugin_first(UUID) verifies this fallback and confirms that the PDT result remains UUID.

15. Empty Window Frames and Negative COUNT

An additional test used this frame:

ROWS BETWEEN 2 FOLLOWING AND 3 FOLLOWING

At the end of a partition, the frame is empty. The first remove() implementation used:

DBUG_ASSERT(count > 0);
count--;

In a release build the assertion was absent, and the result became -1.

The corrected implementation follows built-in Item_sum_count behavior:

if (aggr->arg_is_null(false))
  return;
if (count > 0)
  count--;

This defect did not appear in ordinary cumulative-frame tests.

16. Why `DISTINCT OVER` Is Still Rejected

Consider a moving frame containing:

[A, A, B]

After removing one A, the distinct set must still contain A.

An inverse DISTINCT implementation needs multiplicity state:

A -> 2
B -> 1

The current Aggregator_distinct stores unique values but not a reference count for each value.

Therefore:

plugin_aggregate(DISTINCT value) OVER (...)

returns ER_NOT_SUPPORTED_YET, just like built-in SUM(DISTINCT), AVG(DISTINCT), and COUNT(DISTINCT) window aggregates.

A correct first implementation could recompute each frame in full. An efficient inverse implementation requires a dedicated multiplicity map.

17. The Most Dangerous Problem: Plugin Lifetime

The original function lookup performed this sequence:

plugin_ref plugin = plugin_lock_by_name(...);
Create_func *builder = descriptor->create_func();
plugin_unlock(plugin);
return builder;

After the unlock, the server could still retain:

a builder pointer;
an Item vtable;
aggregate callbacks;
type handlers;
aggregate state.

An UNINSTALL SONAME could unload the shared library and leave these pointers dangling.

18. Why the LEX Plugin Lock Is Not Enough

plugin_lock_by_name(thd, ...) records a reference in the current LEX. This is convenient for a normal statement.

Prepared statement processing, however, eventually calls:

lex_unlock_plugins(lex);

while preserving the Item tree for future EXECUTE operations.

The aggregate function plugin reference must therefore survive not just parsing or preparation, but the complete lifetime of the prepared Item tree.

19. A Shared Lifetime Holder

Item_sum_plugin uses a server-owned shared lifetime holder:

original Item_sum_plugin --+
execution copy ------------+--> shared lifetime holder
window copy ---------------+
                                 +-- function plugin_ref
                                 +-- data type plugin_refs

The original and its copies increment the holder’s internal reference count. They do not attempt to acquire a new plugin lock after UNINSTALL, because the plugin may already be in PLUGIN_IS_DELETED state.

The final destructor releases:

the function plugin reference;
argument type plugin references;
the result type plugin reference.

References are intentionally not released from cleanup(), because a prepared Item is reused across executions.

20. Data Type Dependencies

An aggregate may depend on different shared libraries:

function plugin A
argument data type plugin B
result data type plugin C

After fix_length_and_dec(), Item_sum_plugin retains the plugins that own:

every argument Type_handler;
the result Type_handler.

This protects:

Item caches;
val_native();
temporary fields;
comparison callbacks;
materialized results;
prepared executions.

21. Verifying the Unload Lifecycle

Prepared Statement

PREPARE stmt FROM
  'SELECT test_plugin_count(v) FROM t';

UNINSTALL SONAME 'func_test';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSTALL SONAME 'func_test';

The server reports a busy-plugin warning, the prepared statement continues to work, and successful installation after DEALLOCATE proves that the final reference was released.

Concurrent Query

One connection runs an aggregate query blocked in GET_LOCK. Another connection executes UNINSTALL SONAME.

After the lock is released, the query completes correctly, and the library is unloaded only after execution references disappear.

Dynamic PDT

A prepared aggregate uses test_int8, after which the data type plugin is removed before EXECUTE. Execution continues because the aggregate lifetime holder retains the data type plugin.

22. Testing Strategy

The main function_plugin test covers:

registration;
aggregation without GROUP BY;
grouped execution;
NULL behavior;
prepared statements;
basic window execution;
UUID input and result;
CTAS;
materialization;
PDT lifetime;
concurrent unload.

function_plugin_extra covers:

incorrect argument counts;
forbidden aggregate contexts;
ROLLUP;
CTEs and derived tables;
multiple aggregate instances;
grouped DISTINCT;
BLOB temporary-table replay;
Unique spill;
dynamic test types;
UUID through UNION and CASE;
empty moving frames;
automatic reprepare;
multiple prepared statements;
connection close without DEALLOCATE;
malformed descriptors.

function_plugin_negative covers:

CHECK, DEFAULT, and generated columns;
nested aggregates;
invalid window use;
invalid PDT input;
missing type and function plugins;
duplicate-key and NOT NULL errors;
statement timeout;
KILL QUERY during deferred unload;
failed reprepare;
views, procedures, and triggers after unload;
privilege errors;
repeated result access.

23. Why Negative Tests Matter More Than the Happy Path

The happy path validates only one sequence:

register
parse
execute
return result

Production failures occur on partial lifecycle paths:

plugin locked
builder succeeded
fix_fields failed

or:

prepared Item exists
plugin marked deleted
execution copy created

or:

DISTINCT temporary table created
query killed during replay

Negative tests are what expose:

leaked plugin references;
double unlocks;
stale Item caches;
unbalanced parser context;
missing endup();
aggregate state underflow;
use-after-dlclose.

24. Architectural Results

The implementation demonstrates that no new plugin type is required.

The necessary pieces are:

add a function kind to Plugin_function;
perform early lookup in the parser;
provide Item_sum_plugin;
use Type_handler and Item_cache for PDTs;
integrate plugin aggregates into Aggregator_distinct;
reuse the existing window cursor framework;
tie plugin lifetime to Item lifetime.

The legacy UDF ABI remains unchanged and supported.

25. Remaining Limitations

C++ ABI

Function and aggregate plugins still use MariaDB internal C++ classes and must be rebuilt for the supported server release.

Dynamic PDT Lifetime

The aggregate path now retains data type references, but a complete server-wide dependency model for custom-type columns requires an extension-level lifecycle system.

`DISTINCT` Windows

DISTINCT OVER remains unsupported.

Parameterized Types

MariaDB does not yet provide a universal external abstraction for concrete type instances such as VECTOR(1536).

Extension Packaging

Multiple declarations in one .so do not yet form an atomic, versioned extension group.

26. The Next Step: A Stable Author-Facing API

Item_sum_plugin makes native aggregate plugins possible, but it does not solve the broader complexity of MariaDB’s external C++ API.

A future API should provide a declarative builder:

make_aggregate<State, &result>("hll_union_agg")
  .argument(HLL)
  .returns(HLL)
  .clear<&clear>()
  .accumulate<&accumulate>()
  .build();

A server-owned adapter should create the Item_sum_plugin and manage copies, DISTINCT, windows, and plugin lifetime.

The extension author should implement only state transitions and result production.

27. Conclusion

Moving from a scalar function plugin to an aggregate function plugin is not a matter of adding one add() callback.

The implementation must coordinate:

parser timing;
aggregate context;
Item lifecycle;
Pluggable Data Types;
native representations;
cache ownership;
DISTINCT storage and replay;
window remove and fallback paths;
prepared statements;
extension unload;
execution copies;
negative error paths.

The central result is:

A native aggregate function plugin should be a real Item_sum, but the extension author should not have to reimplement server infrastructure manually.

The current implementation extends MariaDB_FUNCTION_PLUGIN to aggregate functions while preserving MariaDB’s native type system. The next architectural step is a compact stable descriptor and a type-safe SDK, allowing developers and AI-assisted tools to generate only aggregate business logic rather than MariaDB’s internal mechanics.