======================================================================== WL#NNNNN: Pluggable table functions via a component service ======================================================================== Status: Requested (community feature request) Version: 9.x Reporter: Bug reference: Bug#87654321 SE: n/a (server layer + component service) ------------------------------------------------------------------------ PROGRESS ------------------------------------------------------------------------ A proof-of-concept implementation exists and passes a dedicated MTR suite (see "REFERENCE / PROTOTYPE" at the end). This worklog is filed to propose the feature for inclusion and to agree on the public interface before finalizing. ======================================================================== 1. HIGH-LEVEL SPECIFICATION ======================================================================== ------------------------------------------------------------------------ 1.1 Executive summary ------------------------------------------------------------------------ MySQL supports exactly one table function today: JSON_TABLE. A table function is a routine that appears in the FROM clause and produces a relation (a set of rows with a fixed set of typed columns), for example: SELECT t.* FROM JSON_TABLE(@doc, '$[*]' COLUMNS (...)) AS t; There is currently no public extension point that lets a plugin or a component contribute a new table function. Users who need set-returning functions must instead: - materialize data into a temporary table by hand, or - abuse a scalar UDF that returns a serialized blob and parse it in SQL, or - expose the data through a PERFORMANCE_SCHEMA plugin table, which is visible only under the performance_schema database and does not accept call-time arguments. This worklog proposes 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 t.* FROM my_series(1, 100) AS t; The design deliberately mirrors the udf_registration service (WL#8020) so that component authors already familiar with registering UDFs can register table functions with no new concepts. ------------------------------------------------------------------------ 1.2 Motivation / use cases ------------------------------------------------------------------------ UC1 Generators. generate_series(start, stop, step) style helpers, widely available in PostgreSQL and requested repeatedly for MySQL, become trivial to ship as a component. The prototype ships exactly this function as a worked example (see section 4): SELECT * FROM generate_series(1, 5) AS t; -- 1,2,3,4,5 UC2 Structured parsing. A component can expose a table function that turns a proprietary text/binary format (CSV line, protobuf blob, log record) into rows without a round-trip through JSON. UC3 System / diagnostic views with arguments. Unlike a PFS plugin table, a table function accepts arguments, e.g. buffer_pool_pages(instance_id). UC4 Bridging external data. A component that already talks to an external system can surface the result set to SQL as a table function, participating in joins, WHERE, GROUP BY, views, CTEs and prepared statements like any other relation. ------------------------------------------------------------------------ 1.3 User-visible behaviour ------------------------------------------------------------------------ REQ-1 A component MAY register a table function by name. After a successful INSTALL COMPONENT the name is usable in any FROM clause of any session. REQ-2 Invocation syntax: table_function_name '(' [expr [, expr]...] ')' AS alias An alias is mandatory, consistent with JSON_TABLE and derived tables (ER_TF_MUST_HAVE_ALIAS otherwise). REQ-3 The name lives in a namespace reachable ONLY from a table reference position. Using the same name as a scalar function ( SELECT my_series() ) MUST NOT resolve to the table function and MUST fail as it does today (ER_SP_DOES_NOT_EXIST). REQ-4 Referencing the name as a plain table ( FROM my_series ) without parentheses MUST NOT resolve to the table function. REQ-5 The result of a table function is a read-only relation. It carries SELECT_ACL only, exactly like a derived table; it cannot be the target of INSERT/UPDATE/DELETE. REQ-6 A registered table function participates fully in query processing: projection, filtering, JOIN (including being on either side and self-join), ORDER BY / LIMIT, GROUP BY / aggregation, subqueries (IN, correlated), UNION, derived tables, CTEs, CREATE VIEW, and PREPARE/EXECUTE with parameters. REQ-7 EXPLAIN MUST classify the source as a materialized table function (reusing the existing "Materialize table function" access path). REQ-8 UNINSTALL COMPONENT MUST NOT unload a component while one of its table functions is being executed by a concurrent session. In that case UNINSTALL MUST fail with ER_COMPONENTS_UNLOAD_CANT_DEINITIALIZE, mirroring the UDF lifecycle handshake. Once no execution is in flight, UNINSTALL MUST succeed and the name MUST disappear. REQ-9 A table function MAY accept call-time arguments and read their values. Arguments MAY be arbitrary expressions (1+1), column references, or ? placeholders; the values MUST be re-evaluated on every execution so that prepared statements and correlated uses observe the current parameter/outer-column values. ------------------------------------------------------------------------ 1.4 Non-goals ------------------------------------------------------------------------ NG-1 No SQL DDL (there is no CREATE TABLE FUNCTION statement); table functions exist only for the lifetime of the component that registered them, like component-registered UDFs. NG-2 No LATERAL-specific new semantics beyond what JSON_TABLE already has (a table function already behaves as LATERAL with respect to its arguments). NG-3 No per-function GRANT/REVOKE in the first iteration. Access control is discussed in section 1.5 as future work. NG-4 No support for table functions that mutate data. NG-5 No schema (database) qualification. A registered table function lives in a single process-global namespace keyed by name only, exactly like a UDF; two-part references such as db.my_series(...) are NOT resolved as table functions. This deliberately matches the UDF / JSON_TABLE model rather than the PostgreSQL set-returning-function model (where functions live in pg_proc under a schema, support db.schema.fn() and overloading). Schema-scoped table functions, overloading, and a data-dictionary persistence path are possible follow-ups (see 1.5 / section 5) but are out of scope for the first iteration. ------------------------------------------------------------------------ 1.5 Security considerations ------------------------------------------------------------------------ The result relation is granted SELECT_ACL like any internal table, and argument expressions that reference real columns are still subject to normal column-level privilege checks. Registration itself is a privileged operation (INSTALL COMPONENT). This matches the current privilege model of both JSON_TABLE and component-registered UDFs: once an administrator installs the component, any authenticated session may call the function. A future extension MAY add a dynamic privilege (e.g. EXECUTE_TABLE_FUNCTION) or an optional per-function privilege declared at registration time, checked during name resolution. This is intentionally left out of the first iteration to keep parity with the UDF model and is tracked separately. ------------------------------------------------------------------------ 1.6 Compatibility ------------------------------------------------------------------------ C1 No new reserved keyword is introduced. The parser dispatches on an already-non-reserved identifier followed by '(' inside the existing table_function grammar rule; existing statements are unaffected. C2 No change to the on-disk format, the data dictionary, or the replication stream. A table function is an execution-time construct only. C3 Existing JSON_TABLE behaviour is unchanged. ======================================================================== 2. LOW-LEVEL DESIGN ======================================================================== ------------------------------------------------------------------------ 2.1 Public component services ------------------------------------------------------------------------ Three services are added, declared in include/mysql/components/services/table_function_registration.h. 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(w, idx) set_longlong(w, idx, value, is_unsigned) set_double(w, idx, value) set_string(w, idx, str, length) emit_row(w) END_SERVICE_DEFINITION BEGIN_SERVICE_DEFINITION(table_function_args) arg_count(h, out_count) get_longlong(h, idx, out_value, out_is_null) get_double(h, idx, out_value, out_is_null) get_string(h, idx, out_str, out_length, out_is_null) END_SERVICE_DEFINITION Column description structure exposed to components: struct Tf_column_def { const char *name; enum_field_types type; unsigned int length; /* 0 = server default for the type */ unsigned int decimals; int not_null; int is_unsigned; }; Callback contract: Tf_describe_func(thd, args, out_columns, out_n_columns, out_state) Invoked once at parse time. Returns the output schema (an array of Tf_column_def) and an optional opaque per-statement state. The args handle allows the component to inspect the argument count (and, for constant arguments, their values) to shape the schema. Tf_fill_func(state, args, row_writer) Invoked once per execution. Reads argument values through the table_function_args service and emits rows by calling the table_function_row_writer service, one emit_row() per row. Tf_cleanup_func(state) Invoked after each execution to release the state. The table_function_args service reifies REQ-9: the opaque args handle is mapped back to the running Table_function_dynamic, and each getter evaluates the corresponding argument Item (val_int / val_real / val_str) at call time. Because evaluation happens after do_init_args() has fix_fields()'d the arguments, expression arguments and ? placeholders are supported and re-evaluated on every execution. The C ABI is intentionally minimal (no C++ types crossing the boundary) so that components in any language with a C FFI can participate. ------------------------------------------------------------------------ 2.2 Server-side registry ------------------------------------------------------------------------ A process-global registry (Tf_registry) maps name -> descriptor: - Descriptors are stored in owning slots so their address is stable while borrowed by a running statement. - Each descriptor carries a usage_count with the same semantics as udf_func::usage_count: * register() sets it to 1 (the registry's own reservation). * find() takes the read lock and atomically increments it, returning a borrowed pointer. * release() atomically decrements it. * remove() (UNINSTALL path) takes the write lock and can erase the entry only by CAS'ing usage_count 1 -> 0. If a borrow is outstanding the CAS fails and remove() returns failure, so unregister_table_function() reports failure to the component, making UNINSTALL COMPONENT fail with ER_COMPONENTS_UNLOAD_CANT_DEINITIALIZE (REQ-8). Locking: a single rwlock guards the map; find() holds the read lock while incrementing so it cannot race with a concurrent remove() that holds the write lock. ------------------------------------------------------------------------ 2.3 Parser and adapter ------------------------------------------------------------------------ Grammar: one new alternative is added to the existing table_function: rule in sql_yacc.yy: IDENT_sys '(' opt_udf_expr_list ')' opt_table_alias reduced into a new parse-tree node PT_table_factor_dynamic_function. IDENT_sys (rather than ident) is used so that reserved words are not accepted, avoiding grammar ambiguity and preserving compatibility (C1). During contextualization the node resolves the name against Tf_registry; an unknown name raises ER_SP_DOES_NOT_EXIST (REQ-3) and a plain identifier without parentheses continues to resolve as a table (REQ-4). Adapter: a Table_function subclass, Table_function_dynamic, wraps the component descriptor and implements the abstract Table_function interface: init() -> calls describe_cb, translates Tf_column_def entries into Create_field for the result tmp table. do_init_args() -> fix_fields() on the argument Items. fill_result_table()-> resets the result table, then calls fill_cb; the row-writer service stores field values and Table_function::write_row() handles the in-memory -> on-disk overflow. used_tables(), print(), walk(), do_fix_after_pullout(), do_cleanup() Because the whole feature is expressed as a Table_function subclass, ALL existing downstream machinery is reused UNCHANGED: Table_ref::is_table_function(), setup_table_function() sql_resolver (setup) join_optimizer / hypergraph (NewMaterializedTableFunctionAccessPath) MaterializedTableFunctionIterator (execution) EXPLAIN, SHOW CREATE VIEW, opt_trace This is the key design property: the server already had a clean Table_function abstraction; this worklog only adds a second concrete implementation of it plus the registration plumbing. ------------------------------------------------------------------------ 2.4 Row writer ------------------------------------------------------------------------ The row-writer handle passed to fill_cb is an opaque token that the service implementation maps back to the running Table_function_dynamic. The setters write into the current record buffer of the result tmp table (respecting nullability and the column charset), and emit_row() commits the record through Table_function::write_row(), resetting all columns to NULL for the next row. ------------------------------------------------------------------------ 2.5 Error handling ------------------------------------------------------------------------ - Missing alias: ER_TF_MUST_HAVE_ALIAS - Unknown / not-registered: ER_SP_DOES_NOT_EXIST ("TABLE FUNCTION x") - describe_cb failure: ER_WRONG_ARGUMENTS - UNINSTALL while in use: ER_COMPONENTS_UNLOAD_CANT_DEINITIALIZE - duplicate registration: register_table_function() returns true; INSTALL fails at component init. No new error codes are strictly required; existing codes cover all cases. ======================================================================== 3. INTERFACE SPECIFICATION SUMMARY ======================================================================== User interface: FROM name(args) AS alias SQL commands: none added (uses INSTALL/UNINSTALL COMPONENT) New services: table_function_registration, table_function_row_writer, table_function_args New privileges: none (see 1.5 for future work) New error codes: none New sysvars: none DD / replication: no change ======================================================================== 4. TESTING ======================================================================== A dedicated MTR suite covers: T1 Basic round trip: SELECT, projection, WHERE, aggregation, JOIN (with re-execution to prove the result table is reset), ORDER BY / LIMIT, EXPLAIN, protocol column metadata, install / uninstall. T2 Error paths: missing alias, unknown name, duplicate INSTALL, scalar invocation, table-style reference, double UNINSTALL. T3 Advanced usage: derived tables, CTE, UNION ALL, IN and correlated subqueries, CREATE VIEW, PREPARE/EXECUTE with parameters, self-join. T4 Lifecycle: a DEBUG_SYNC point parks a producer inside fill_result_table() while a second session issues UNINSTALL COMPONENT; the UNINSTALL is verified to fail with ER_COMPONENTS_UNLOAD_CANT_DEINITIALIZE and to succeed only after the producer completes (REQ-8). T5 Arguments (REQ-9): generate_series is driven with constant, expression (1+1, 2*3) and ? placeholder arguments; custom and negative step, empty and single-element ranges, aggregation over a large range (COUNT/SUM), LEFT JOIN with NULL-complement, and PREPARE/EXECUTE re-execution with different bound values. Bad argument count raises ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT and a zero step raises ER_WRONG_ARGUMENTS. Two demo components are shipped: - test_table_function registers demo_tf_all_types, returning three rows with one column per major SQL type, exercising every row-writer setter. - generate_series registers a PostgreSQL-style generate_series(start, stop [, step]) using the table_function_args service to read its call-time arguments (REQ-9). ======================================================================== 5. REFERENCES AND FUTURE WORK ======================================================================== Possible follow-ups (explicitly out of scope here, see 1.4 / 1.5): - A dynamic privilege (EXECUTE_TABLE_FUNCTION) or per-function privilege declared at registration time. - Schema-scoped table functions with db.name() resolution, overloading by argument signature, and data-dictionary persistence, bringing the model closer to PostgreSQL's set-returning functions (NG-5). References: WL#8020 UDF registration component service (interface model) Bug#87654321 Allow components to register custom SQL table functions (prototype patch) Related existing code: sql/table_function.{h,cc} -- Table_function base sql/table_function_dynamic.{h,cc} -- registry + adapter sql/parse_tree_nodes.{h,cc} -- table reference nodes sql/server_component/ table_function_registration_imp.{h,cc} -- service impls sql/iterators/composite_iterators.cc -- exec iterator components/test/generate_series.cc -- generate_series demo components/test/test_table_function.cc -- all-types demo include/mysql/components/services/table_function_registration.h include/mysql/components/services/udf_registration.h ========================================================================