Pluggable aggregate functions

Background

MariaDB provides MariaDB_FUNCTION_PLUGIN as a modern alternative to the legacy UDF interface for scalar functions. Function plugins can use the native server Item and Type_handler infrastructure and therefore are not limited to the capabilities of the old UDF ABI.

Aggregate functions implemented outside the server still rely primarily on the legacy interface:

CREATE AGGREGATE FUNCTION function_name

RETURNS return_type

SONAME 'library_name';

The legacy aggregate UDF ABI uses exported symbols such as:

<name>_init

<name>_clear

<name>_add

<name>_remove

<name>

<name>_deinit

Arguments and results are represented using the generic Item_result categories:

STRING_RESULT

REAL_RESULT

INT_RESULT

DECIMAL_RESULT

This interface cannot preserve the identity, attributes, or native representation of a pluggable data type. Values of such types must be converted to one of the legacy representations, usually a string or binary string. As a result, an aggregate UDF cannot behave like a native aggregate whose argument or result has a Type_handler supplied by a MariaDB_DATA_TYPE_PLUGIN.

Current limitations

MariaDB_FUNCTION_PLUGIN exports a Create_func builder. In principle, such a builder can return an Item_sum, so the existing plugin type and most of the aggregate execution infrastructure can be reused.

However, aggregate functions must be identified before their arguments are parsed. Currently, the generic function grammar enters the aggregate parsing context only when find_udf() finds a legacy aggregate UDF. A MariaDB_FUNCTION_PLUGIN is resolved later, after its arguments have already been parsed.

There are additional UDF-specific assumptions in window-function parsing: a generic function used with OVER is currently accepted only when it is an Item_sum with UDF_SUM_FUNC.

Consequently, returning an Item_sum from an existing function plugin builder is not sufficient to provide a complete and supported aggregate function plugin interface.

Goal

Add first-class support for native aggregate function plugins as the modern alternative to the legacy aggregate UDF ABI.

The implementation should preferably reuse MariaDB_FUNCTION_PLUGIN and the existing Create_func/Item_sum infrastructure rather than introduce an independent plugin lookup and lifecycle system.

The plugin descriptor, or an equivalent lookup interface, must expose whether a function is scalar or aggregate early enough for the parser to establish the correct aggregate context before parsing the arguments.

Requirements

1. Plugin registration and resolution

• Allow a function plugin to declare that it implements an aggregate function.
• Make the scalar/aggregate kind available before parsing the function arguments.
• Resolve aggregate function plugins through the native function plugin registry.
• Avoid a second independent function registry unless required by the plugin infrastructure.
• Expose aggregate function plugins through the same metadata facilities as other function plugins, including INFORMATION_SCHEMA.SQL_FUNCTIONS.
• Detect and report naming conflicts consistently with scalar function plugins, native functions, stored functions, and legacy UDFs.

2. Native aggregate execution

The API must integrate with the existing Item_sum execution model and support:

• initialization and setup;
• clearing the state for a new group;
• adding a row to the state;
• final result retrieval;
• cleanup;
• cloning or copying an aggregate item when required by query execution;
• empty-input and NULL semantics;
• temporary-table and materialization execution paths.

The implementation should reuse the existing Aggregator infrastructure rather than duplicate group or DISTINCT processing in every plugin.

3. Pluggable data types — mandatory

Aggregate function plugins must be able to:

• inspect the actual Type_handler of every argument;
• accept arguments implemented by MariaDB_DATA_TYPE_PLUGIN;
• return a result whose type_handler() is supplied by a data type plugin;
• consume and produce the native representation through Item::val_native(), Field::store_native(), or the corresponding Type_handler conversion methods;
• preserve relevant type attributes required for fields, temporary tables, materialized results, UNION, CASE, and other type aggregation operations;
• reject unsupported argument type combinations during function fixing with a normal SQL error;
• avoid unconditional conversion of pluggable values to STRING_RESULT, INT_RESULT, REAL_RESULT, or DECIMAL_RESULT.

Support for both pluggable input and pluggable result types is required. Supporting only a pluggable argument converted to a built-in result is not sufficient.

4. DISTINCT

Native aggregate plugins must support the normal aggregate syntax where applicable:

plugin_aggregate(DISTINCT expression)

DISTINCT processing should use the existing server aggregation infrastructure and the comparison or native representation rules of the argument's Type_handler.

5. Window functions

Aggregate function plugins must be usable with OVER under the same rules as native aggregate functions.

The interface must support the existing optional inverse aggregation contract:

• supports_removal()
• remove()

An aggregate implementing removal should work efficiently with moving window frames. Aggregates without removal must follow the same supported fallback or rejection rules as native aggregates.

Window parsing must not be restricted specifically to UDF_SUM_FUNC.

6. Plugin and data type lifetime

A query using an aggregate function plugin must keep the function plugin loaded for the lifetime of all related Item and aggregate state objects.

If the aggregate references a Type_handler from a data type plugin, that data type plugin must also remain loaded while the handler, native values, temporary fields, or aggregate results are in use.

Concurrent execution and plugin unload must not leave dangling builders, Item_sum objects, aggregate state, or Type_handler pointers.

This work should be coordinated with the existing function-plugin locking problem tracked by MDEV-20846.

7. Backward compatibility

• Existing scalar MariaDB_FUNCTION_PLUGIN implementations must continue to work unchanged.
• Existing legacy scalar and aggregate UDFs must continue to work.
• CREATE AGGREGATE FUNCTION ... SONAME ... and the mysql.func representation must remain supported.
• Native aggregate plugins and legacy aggregate UDFs must be able to coexist.
• This task introduces a replacement API for new implementations; it does not remove the legacy UDF ABI.

Suggested implementation direction

Extend the function plugin descriptor so that lookup can return the following information before parsing arguments:

• the function kind: scalar or aggregate;
• the corresponding Create_func builder.

The aggregate builder should create an Item_sum implementation. The resulting item can use the existing virtual aggregate lifecycle:

setup()

clear()

add()

supports_removal()

remove()

cleanup()

It should provide the normal native Item result interfaces, including:

type_handler()

val_native()

val_str()

val_int()

val_real()

val_decimal()

A separate MariaDB_AGGREGATE_FUNCTION_PLUGIN type should be introduced only if the existing plugin descriptor cannot be extended compatibly. It should not create a duplicate lookup, metadata, locking, or unload mechanism.

Acceptance criteria

1. A test plugin registers a native aggregate without using CREATE AGGREGATE FUNCTION ... SONAME.
2. The aggregate works with:
   1. a query without GROUP BY;
   2. multiple GROUP BY groups;
   3. empty input;
   4. nullable arguments;
   5. temporary-table and materialization execution paths.
3. The aggregate accepts an argument whose type is provided by a test MariaDB_DATA_TYPE_PLUGIN, without first reducing it to a legacy Item_result representation.
4. The aggregate returns a pluggable data type:
   1. its result reports the plugin Type_handler;
   2. the result can be selected directly;
   3. the result can be stored in a column of that type;
   4. the result survives a temporary table or materialized derived table;
   5. its native representation remains valid.
5. Unsupported argument types produce a deterministic SQL error during function fixing.
6. The following syntax works using the type handler's comparison or native representation rules:

   plugin_aggregate(DISTINCT value)

7. The aggregate can be used as a window function.
8. A test aggregate implementing remove() works with a moving window frame.
9. The server safely handles attempts to unload the function or data type plugin while a statement is using it.
10. The function is listed in INFORMATION_SCHEMA.SQL_FUNCTIONS.
11. Existing function plugin tests and legacy aggregate UDF tests continue to pass.
12. Developer documentation and an example aggregate plugin are added.

Out of scope

• Removal of the legacy UDF ABI.
• Automatic migration of entries from mysql.func.
• A stable language-neutral or Rust ABI for native aggregate plugins.
• Distributed or parallel partial-state aggregation.
• A generic serialized aggregate-state interchange format.

Related issues

• MDEV-20837 — Add MariaDB_FUNCTION_PLUGIN
• MDEV-20846 — Add proper unlocking for MariaDB_FUNCTION_PLUGIN
• MDEV-23290 — Add overloading support in MariaDB_FUNCTION_PLUGIN
• MDEV-4912 — Data type plugin API version 1
• MDEV-11478 — Result data type aggregation for pluggable data types