| Bug #120978 | Pluggable table functions via a component service | ||
|---|---|---|---|
| Submitted: | 22 Jul 14:52 | Modified: | 22 Jul 15:35 |
| Reporter: | Kaiwang CHen (OCA) | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[22 Jul 15:35]
Kaiwang CHen
========================================================================
Supplement: call-time arguments + generate_series demo
========================================================================
Since the original report, the prototype has been extended so table
functions can read their call-time argument values. This makes a real
generate_series possible:
SELECT * FROM generate_series(1, 5) AS t; -- returns 1,2,3,4,5
A third component service is added alongside the two already described:
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
The describe_cb and fill_cb callbacks now each receive an opaque args
handle. The service maps it back to the running table function and
evaluates the corresponding argument expression (val_int / val_real /
val_str). Evaluation happens after the argument Items are fix_fields()'d,
so arguments may be arbitrary expressions (1+1, 2*3) or ? placeholders,
and are re-evaluated on every execution (correct for PREPARE/EXECUTE and
correlated uses).
Updated callback signatures:
describe_cb(thd, args, out_columns, out_n_columns, out_state)
fill_cb(state, args, row_writer)
A second demo component, generate_series, registers a PostgreSQL-style
generate_series(start, stop [, step]) that reads its arguments through
this service. Additional MTR coverage:
- constant, expression (1+1, 2*3) and ? placeholder arguments
- custom and negative step; empty and single-element ranges
- aggregation over a large range (COUNT=100, SUM=5050)
- LEFT JOIN with NULL-complement
- PREPARE/EXECUTE re-executed with different bound values
- wrong argument count -> ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
- zero step -> ER_WRONG_ARGUMENTS
Still no new reserved keyword, error code, sysvar, privilege, data-
dictionary or replication change. The updated worklog attachment
reflects this (new REQ-9, NG-5, the table_function_args service, and
the generate_series test matrix).
[22 Jul 15:48]
Kaiwang CHen
first commit: Allow components to register custom SQL table functions (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug_120978_1.patch (application/octet-stream, text), 77.89 KiB.
[22 Jul 15:48]
Kaiwang CHen
second commit: Let table functions read their call-time arguments (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug_120978_2.patch (application/octet-stream, text), 30.51 KiB.
[22 Jul 15:50]
Kaiwang CHen
The work log
Attachment: WL-pluggable-table-functions.txt (text/plain), 19.12 KiB.
Description: 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): SELECT t.* FROM JSON_TABLE(@doc, '$[*]' COLUMNS (...)) AS t; There is no public extension point that lets a plugin or a component contribute a new table function. 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 as a PERFORMANCE_SCHEMA plugin table (which is visible only under the performance_schema database and cannot take call-time arguments). This is a feature request for a Component Service that lets a loadable component register a named table function, usable anywhere a table reference is allowed, exactly like JSON_TABLE: SELECT t.* FROM my_series(1, 100) AS t; Motivating use cases: - Generators such as generate_series(start, stop, step). - Turning a proprietary text/binary record into rows without going through JSON. - System/diagnostic views that accept arguments (unlike PFS plugin tables). - Bridging an external data source into SQL so it can participate in joins, WHERE, GROUP BY, views, CTEs and prepared statements. The proposal deliberately mirrors the udf_registration service (WL#8020) so component authors already familiar with registering UDFs can register table functions with no new concepts. See the attached worklog for the full High-Level Specification and Low-Level Design. Expected/requested behaviour: - A component registers a table function by name; after INSTALL COMPONENT the name is usable in any session's FROM clause as name(args) AS alias (alias mandatory, like JSON_TABLE). - The name is reachable only from a table-reference position; it does not collide with the scalar function namespace, and a plain "FROM name" without parentheses is not the table function. - The result is a read-only relation (SELECT only), participating fully in projection, filtering, JOIN, ORDER BY/LIMIT, aggregation, subqueries, UNION, derived tables, CTEs, views and PREPARE/EXECUTE. - UNINSTALL COMPONENT must refuse to unload while one of the component's table functions is executing in another session (ER_COMPONENTS_UNLOAD_CANT_DEINITIALIZE), mirroring the UDF lifecycle handshake. No new reserved keyword, no data-dictionary or replication change, and JSON_TABLE behaviour is unchanged. How to repeat: There is no way to add a table function without patching the server. To reproduce the current limitation: 1. Try to obtain a simple integer series in the FROM clause without an existing base table: SELECT * FROM generate_series(1, 5) AS t; -- ERROR 1305 (42000): FUNCTION test.generate_series does not exist 2. Confirm no supported extension mechanism produces a FROM-clause relation with call-time arguments: - A scalar UDF (CREATE FUNCTION ... SONAME / component udf_registration) can only return one scalar value, not a set of rows. - A PERFORMANCE_SCHEMA plugin table (pfs_plugin_table service) is reachable only as performance_schema.<table> and cannot take arguments such as generate_series(1, 5). - JSON_TABLE is hard-coded in the grammar (JSON_TABLE_SYM) and its Table_function subclass (Table_function_json) is instantiated directly in the parser; there is no registry or factory to add another one. Hence today the only way to add a table function is to modify sql_yacc.yy plus the parser and add a new Table_function subclass and rebuild the server. Suggested fix: Add a component service, table_function_registration, plus a helper table_function_row_writer service, and a thin server-side adapter. Interface (see attached worklog for details): 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 - describe_cb runs at parse time and returns the output schema (an array of {name, type, length, decimals, not_null, is_unsigned}). - fill_cb runs once per execution and emits rows through the row-writer service. - cleanup_cb releases per-statement state. Server side: - A process-global registry (name -> descriptor) with a usage_count that follows udf_func::usage_count semantics: register() reserves 1, find() increments under a read lock, release() decrements, and remove() (UNINSTALL) can erase only by CAS'ing 1 -> 0. If a borrow is outstanding the CAS fails, unregister reports failure, and UNINSTALL COMPONENT fails with ER_COMPONENTS_UNLOAD_CANT_DEINITIALIZE. - One new grammar alternative in the existing table_function: rule: IDENT_sys '(' opt_udf_expr_list ')' opt_table_alias reduced into a new parse-tree node that resolves the name against the registry during contextualization (unknown name -> ER_SP_DOES_NOT_EXIST). IDENT_sys avoids adding a reserved keyword. - A Table_function subclass (adapter) that wraps the component callbacks: init() translates the described columns into the result tmp table, do_init_args() fixes the argument Items, fill_result_table() calls fill_cb and writes rows via the base class write_row() (in-memory -> on-disk overflow handled for free). Because the feature is expressed as another Table_function subclass, all existing downstream code is reused unchanged: is_table_function(), setup_table_function(), the resolver, the (hypergraph) optimizer's NewMaterializedTableFunctionAccessPath, MaterializedTableFunctionIterator, EXPLAIN and SHOW CREATE VIEW. No new error code, sysvar, privilege, data-dictionary or replication change is required. A working prototype exists and passes a dedicated MTR suite covering basic queries, error paths, advanced usage (CTE/UNION/view/prepared statements/self-join) and the concurrent unload-in-use handshake, plus a demo component that returns rows with one column per major SQL type. The full HLS/LLD is provided as an attachment.