[Proposal]: Pluggable Table Functions: A Component Service for User-Defined Set-Returning Functions in MySQL

[kaiwangchen]

Pre-flight Checklist

• I have searched existing GitHub issues and did not find a duplicate proposal.
• I have removed or redacted sensitive information.

Primary Contact Name

Kaiwang Chen

Primary Contact Email

kaiwang.chen@gmail.com

Company / Organization

a public cloud provider

Role

Software Engineer

Additional Authors / Contributors

No response

Component

Server

Target Release (Optional)

9.x

Roadmap Section

Extensibility/Ecosystem

Related Issues / Pull Requests / References (Optional)

• Related feature request: Bug#120978 (Allow components to register custom SQL table functions)
• Interface model: WL#8020 (UDF registration component service)

Executive Summary

MySQL supports exactly one table function today: JSON_TABLE. A table
function appears in the FROM clause and produces a relation (rows with a
fixed set of typed columns). There is currently no public extension point
that lets a plugin or component contribute a new table function, so users
who need set-returning functions must work around it by materializing
temp tables by hand, abusing a scalar UDF that returns a serialized blob,
or exposing data through a PERFORMANCE_SCHEMA plugin table (visible only
under performance_schema and unable to take call-time arguments).

This proposal adds a component service, table_function_registration, that
lets a loadable component register a named table function. Once
registered, the function is usable anywhere a table reference is allowed,
exactly like JSON_TABLE:

SELECT * FROM generate_series(1, 5) AS t;   -- 1,2,3,4,5

The design deliberately mirrors the udf_registration service (WL#8020) so
that component authors familiar with UDF registration can register table
functions with no new concepts, and it is expressed as a second concrete
implementation of the server's existing Table_function abstraction so
that the resolver, optimizer, iterator, EXPLAIN and view machinery are
reused unchanged.

User / Developer Stories

• As an extension author, I want to register a set-returning function
  from my component, so that my data source can appear in a FROM clause
  and participate in joins, WHERE, GROUP BY, views and CTEs.
• As a SQL user, I want a generate_series(start, stop [, step]) generator
  like PostgreSQL's, so that I can produce numeric ranges without a base
  table.
• As a DBA, I want argument-driven diagnostic views (e.g.
  buffer_pool_pages(instance_id)), which PERFORMANCE_SCHEMA plugin tables
  cannot offer.
• As an integrator, I want to bridge an external system into SQL as a
  table function so it composes with the rest of a query.

Proposed Scope

• A component service to register/unregister a table function by name.
• A row-writer service for components to emit typed rows.
• An argument-reader service so functions can read call-time arguments
  (constants, expressions and ? placeholders), re-evaluated per execution.
• One new grammar alternative in the existing table_function: rule; a
  server-side registry and a Table_function adapter subclass.
• Two demo components (all-types demo + generate_series) and an MTR suite.

Out of Scope / Future Work

• No SQL DDL (no CREATE TABLE FUNCTION); functions live only for the
  lifetime of the registering component, like component-registered UDFs.
• No schema (database) qualification, overloading, or data-dictionary
  persistence. The name lives in a single process-global namespace keyed
  by name only, matching the UDF / JSON_TABLE model rather than
  PostgreSQL's schema-scoped pg_proc functions. Schema-scoped table
  functions are a possible follow-up.
• No per-function GRANT/REVOKE in the first iteration (see Security).
• No table functions that mutate data.

References

• WL#8020: UDF registration component service (interface model).
• Existing Table_function abstraction (sql/table_function.{h,cc}) and
  MaterializedTableFunctionIterator, reused unchanged.
• PostgreSQL set-returning functions (prior art for generate_series).

Functional Requirements

• The server MUST let a component register a table function by name; after
  INSTALL COMPONENT the name MUST be usable in any session's FROM clause
  as name(args) AS alias (alias mandatory, like JSON_TABLE).
• The name MUST be reachable only from a table-reference position; it MUST
  NOT resolve in the scalar-function namespace (SELECT name() MUST still
  fail with ER_SP_DOES_NOT_EXIST), and a bare "FROM name" without
  parentheses MUST NOT resolve to the table function.
• The result MUST be a read-only relation (SELECT_ACL only), participating
  fully in projection, filtering, JOIN, ORDER BY/LIMIT, aggregation,
  subqueries, UNION, derived tables, CTEs, views and PREPARE/EXECUTE.
• EXPLAIN MUST classify the source as a materialized table function.
• A table function MAY accept call-time arguments and read their values;
  arguments MAY be expressions or ? placeholders and MUST be re-evaluated
  on every execution.
• UNINSTALL COMPONENT MUST NOT unload a component while one of its table
  functions is executing in another session; in that case it MUST fail
  with ER_COMPONENTS_UNLOAD_CANT_DEINITIALIZE, and MUST succeed once no
  execution is in flight.

Non-functional Requirements

• Compatibility: no new reserved keyword; existing statements and
  JSON_TABLE behaviour unchanged.
• Security: registration is a privileged operation (INSTALL COMPONENT);
  argument expressions remain subject to column-level privilege checks.
• Concurrency: registry access is rwlock-protected; the unload handshake
  uses a usage_count with the same semantics as udf_func::usage_count.
• Portability: the service ABI is pure C so any language with a C FFI can
  register table functions.
• Maintainability: implemented as a Table_function subclass so downstream
  code is reused unchanged.

Impact Areas

• SQL syntax or statements
• Configuration options or system variables
• Command-line options or utilities
• User-visible behavior
• Observability
• Security or privilege model
• Protocol or replication behavior
• Upgrade / downgrade compatibility
• Performance or resource usage
• Files, persistence, or metadata formats
• APIs or internal interfaces
• Testing or QA coverage needs

Summary of the Approach

Add three component services and a thin server-side adapter. A process-
global registry maps a name to a descriptor of component callbacks. The
grammar gains one alternative in the existing table_function: rule that
resolves the name against the registry and constructs a Table_function
subclass (Table_function_dynamic), which drives the component's
describe/fill/cleanup callbacks. Everything below the Table_function
abstraction (resolver, optimizer, materialization iterator, EXPLAIN) is
unchanged.

User Interface

INSTALL COMPONENT "file://component_generate_series";
SELECT * FROM generate_series(1, 5) AS t;          -- 1..5
SELECT * FROM generate_series(1, 10, 2) AS t;       -- 1,3,5,7,9
UNINSTALL COMPONENT "file://component_generate_series";

Configuration / Knobs

No new system variables or CLI options. New extension points only:

• table_function_registration : register/unregister a table function
• table_function_row_writer : set_null/set_longlong/set_double/
  set_string/emit_row
• table_function_args : arg_count/get_longlong/get_double/
  get_string

Observability

Registered table functions appear in EXPLAIN as a materialized table
function access path. No new instrumentation is added in the first
iteration; Performance Schema exposure of registered functions is a
possible follow-up.

User Procedure

An extension author writes describe/fill/cleanup callbacks, calls
register_table_function() from the component's init and
unregister_table_function() from deinit. A DBA installs the component;
any authenticated session may then call the function in a FROM clause.

Security Considerations

Registration requires INSTALL COMPONENT. The result relation carries
SELECT_ACL like a derived table, and argument expressions that reference
real columns still go through normal column-level privilege checks. This
matches the current model of JSON_TABLE and component-registered UDFs:
once installed, any session may call the function. A future extension may
add an EXECUTE_TABLE_FUNCTION dynamic privilege or a per-function
privilege declared at registration time.

Compatibility and Behavior Changes

No new reserved keyword (dispatch is on a non-reserved identifier followed
by '(' inside the existing grammar rule). No on-disk format, data-
dictionary or replication change. JSON_TABLE behaviour is unchanged; a
table function is an execution-time construct only.

Block Diagram

flowchart TD
A["Component (init)"] -->|register_table_function| R["Tf_registry (global, rwlock)"]
Q["SQL: FROM name(args) AS t"] --> P["Parser: table_function rule\nPT_table_factor_dynamic_function"]
P -->|find(name)| R
P --> ADA["Table_function_dynamic (adapter)"]
ADA -->|describe / fill / cleanup| A
ADA --> IT["MaterializedTableFunctionIterator (unchanged)"]
ADA -->|row_writer + args services| A
U["UNINSTALL COMPONENT"] -->|unregister -> remove()| R
R -.->|usage_count CAS 1->0 fails while in use| U

Interface Specification

BEGIN_SERVICE_DEFINITION(table_function_registration)
register_table_function(name, describe_cb, fill_cb, cleanup_cb)
unregister_table_function(name, was_present)
END_SERVICE_DEFINITION

BEGIN_SERVICE_DEFINITION(table_function_row_writer)
set_null / set_longlong / set_double / set_string / emit_row
END_SERVICE_DEFINITION

BEGIN_SERVICE_DEFINITION(table_function_args)
arg_count / get_longlong / get_double / get_string
END_SERVICE_DEFINITION

struct Tf_column_def { name; type; length; decimals; not_null; is_unsigned; };
describe_cb(thd, args, out_columns, out_n_columns, out_state)
fill_cb(state, args, row_writer)
cleanup_cb(state)

Registry: name -> descriptor (std::unique_ptr, stable address); each
descriptor has a usage_count (== udf_func::usage_count semantics): register
reserves 1, find() increments under read lock, release() decrements,
remove() erases only by CAS'ing 1 -> 0 (fails while borrowed -> UNINSTALL
fails with ER_COMPONENTS_UNLOAD_CANT_DEINITIALIZE).

Proposed Implementation Plan

1. Registry + Table_function_dynamic adapter (sql/table_function_dynamic).
2. Service headers + implementations; register with mysql_server.
3. One grammar alternative + PT_table_factor_dynamic_function node.
4. Argument-reader service and adapter arg accessors.
5. Demo components (all-types + generate_series) and MTR suite.
6. Documentation / worklog.

QA Notes

• Edge cases: missing alias; unknown/unregistered name; scalar-style
  invocation; bare table-style reference; duplicate INSTALL; double
  UNINSTALL; empty and single-element ranges; zero step; wrong argument
  count; large ranges; NULL-complement in LEFT JOIN.
• Regression risks: grammar ambiguity between table name and table
  function (mitigated by using IDENT_sys and dispatching on the trailing
  '('); result-table reuse across re-execution (result table is reset per
  fill).
• Suggested test coverage: basic/errors/advanced/unload-in-use plus a
  generate_series matrix (constants, expressions, ? placeholders,
  PREPARE/EXECUTE re-execution, custom/negative step); error paths assert
  ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT and ER_WRONG_ARGUMENTS.
• Automation opportunities: the concurrent unload-in-use case is driven by
  a DEBUG_SYNC point, making the lifecycle handshake deterministically
  testable.

[kaiwangchen]

slides_120978_kaiwangchen.pdf