Skip to content

Commit c6094a1

Browse files
zhuqi-lucaskosiew
authored andcommitted
Support UDTFs in information_schema.routines / SHOW FUNCTIONS (apache#23438)
# Which issue does this PR close? - Closes apache#23437. # Rationale for this change `information_schema.routines` and `SHOW FUNCTIONS` currently enumerate only scalar / aggregate / window UDFs. Table functions (UDTFs) registered via `SessionContext::register_udtf` are omitted, making them undiscoverable through SQL. Discovery matters because downstream tooling (DataFusion CLI, Massive's atlas SQL surface, dbt-datafusion, etc.) uses these SQL surfaces to list available functions. # What changes are included in this PR? **1. Snapshot table functions into the information_schema provider.** `Session::table_functions()` cannot exist on the `Session` trait today: `TableFunction` lives in `datafusion-catalog`, which already depends on `datafusion-session` (both `TableProvider::scan` and `TableFunction` take `&dyn Session`). Adding a trait method returning `&HashMap<String, Arc<TableFunction>>` would reverse that edge and create a crate-dependency cycle. `ScalarUDF` / `AggregateUDF` / `WindowUDF` don't hit this because they live in `datafusion-expr`, which sits below `datafusion-session`. This is the same underlying constraint as apache#23348. A follow-up PR will move `TableFunction` (or hoist the relevant traits) so the builder can be deleted. For this PR, `SessionState.table_functions` is snapshotted into `InformationSchemaProvider` via `with_table_functions()` at construction. The provider is built per-query, so the snapshot stays fresh. **2. Emit UDTF rows in `information_schema.routines` only.** `make_routines`: one row per UDTF with `routine_type = "FUNCTION"`, `function_type = "TABLE"`, `data_type = "TABLE"`. UDTFs deliberately do NOT appear in `information_schema.parameters`. A same-named scalar UDF (e.g. `generate_series` exists as both a scalar UDF in `functions-nested` and a UDTF in `functions-table`) would cross-join with a UDTF row keyed only by (name, rid) and produce spurious `TABLE`-typed variants of every scalar signature in `SHOW FUNCTIONS`. **3. Rewrite the `SHOW FUNCTIONS` SQL to UNION UDTFs directly from `routines`.** The old query joined `parameters p (INNER)` requiring both IN and OUT rows before joining `routines`. UDTFs have no IN parameters, so they fell out. The new plan sources scalar / aggregate / window signatures from `parameters` as before, and UDTFs from a separate `UNION` branch that reads `routines` directly (guarded by `function_type = TABLE`). This avoids the cross-join blowup mentioned above. # Are these changes tested? Verified locally by registering a UDTF and running `SHOW FUNCTIONS`: ``` > SHOW FUNCTIONS LIKE 'stale%' +---------------+-------------+---------------+ | function_name | return_type | function_type | | stale_files | TABLE | TABLE | +---------------+-------------+---------------+ ``` Happy to add sqllogictests in `datafusion/sqllogictest/test_files/information_schema.slt` if maintainers prefer. # Are there any user-facing changes? Yes: `SHOW FUNCTIONS` output now includes table functions. `information_schema.routines` gains one row per registered UDTF. `information_schema.parameters` is intentionally unchanged (see rationale in point 2 above). Existing rows are unchanged.
1 parent a04e271 commit c6094a1

4 files changed

Lines changed: 109 additions & 19 deletions

File tree

‎datafusion/catalog/src/information_schema.rs‎

Lines changed: 45 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
//! [Information Schema]: https://en.wikipedia.org/wiki/Information_schema
2121
2222
use crate::streaming::StreamingTable;
23+
use crate::table::TableFunction;
2324
use crate::{CatalogProviderList, SchemaProvider, TableProvider};
2425
use arrow::array::builder::{BooleanBuilder, UInt8Builder};
2526
use arrow::{
@@ -81,14 +82,28 @@ impl InformationSchemaProvider {
8182
/// Creates a new [`InformationSchemaProvider`] for the provided `catalog_list`
8283
pub fn new(catalog_list: Arc<dyn CatalogProviderList>) -> Self {
8384
Self {
84-
config: InformationSchemaConfig { catalog_list },
85+
config: InformationSchemaConfig {
86+
catalog_list,
87+
table_functions: HashMap::new(),
88+
},
8589
}
8690
}
91+
92+
/// Attach the session's table (UDTF) functions so that they appear in
93+
/// `information_schema.routines` / `SHOW FUNCTIONS`.
94+
pub fn with_table_functions(
95+
mut self,
96+
table_functions: HashMap<String, Arc<TableFunction>>,
97+
) -> Self {
98+
self.config.table_functions = table_functions;
99+
self
100+
}
87101
}
88102

89103
#[derive(Clone, Debug)]
90104
struct InformationSchemaConfig {
91105
catalog_list: Arc<dyn CatalogProviderList>,
106+
table_functions: HashMap<String, Arc<TableFunction>>,
92107
}
93108

94109
impl InformationSchemaConfig {
@@ -301,6 +316,26 @@ impl InformationSchemaConfig {
301316
)
302317
}
303318
}
319+
320+
// Table functions (UDTFs) don't have scalar signatures; their return
321+
// type is always a table, so emit a single row per UDTF with
322+
// routine_type = "FUNCTION", function_type = "TABLE" and
323+
// data_type = "TABLE".
324+
for name in self.table_functions.keys() {
325+
builder.add_routine(
326+
catalog_name,
327+
schema_name,
328+
name,
329+
"FUNCTION",
330+
// No signature is available for UDTFs; report deterministic
331+
// = false to stay conservative.
332+
false,
333+
Some(&"TABLE"),
334+
"TABLE",
335+
None::<String>,
336+
None::<String>,
337+
)
338+
}
304339
Ok(())
305340
}
306341

@@ -400,6 +435,14 @@ impl InformationSchemaConfig {
400435
}
401436
}
402437

438+
// UDTFs deliberately do NOT appear in `information_schema.parameters`.
439+
// A same-named scalar UDF (e.g. `generate_series` exists as both a
440+
// scalar UDF in functions-nested and a UDTF in functions-table) would
441+
// cross-join with a UDTF row keyed only by (name, rid) and produce
442+
// spurious `TABLE`-typed variants of every scalar signature in
443+
// SHOW FUNCTIONS. `show_functions_to_plan` sources UDTFs directly
444+
// from `information_schema.routines` via a UNION branch instead.
445+
403446
Ok(())
404447
}
405448

@@ -1522,6 +1565,7 @@ mod tests {
15221565
async fn make_tables_uses_table_type() {
15231566
let config = InformationSchemaConfig {
15241567
catalog_list: Arc::new(Fixture),
1568+
table_functions: HashMap::new(),
15251569
};
15261570
let mut builder = InformationSchemaTablesBuilder {
15271571
catalog_names: StringBuilder::new(),

‎datafusion/core/src/execution/session_state.rs‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -349,9 +349,10 @@ impl SessionState {
349349
let resolved_ref = self.resolve_table_ref(table_ref);
350350
if self.config.information_schema() && *resolved_ref.schema == *INFORMATION_SCHEMA
351351
{
352-
return Ok(Arc::new(InformationSchemaProvider::new(Arc::clone(
353-
&self.catalog_list,
354-
))));
352+
return Ok(Arc::new(
353+
InformationSchemaProvider::new(Arc::clone(&self.catalog_list))
354+
.with_table_functions(self.table_functions.clone()),
355+
));
355356
}
356357

357358
self.catalog_list

‎datafusion/sql/src/statement.rs‎

Lines changed: 49 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -2631,17 +2631,35 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
26312631
"".to_string()
26322632
};
26332633

2634+
// Scalar / aggregate / window functions are resolved by joining
2635+
// parameters (IN rows aggregated per OUT row) with routines.
2636+
// Table functions (UDTFs) don't have parameter rows, so they are
2637+
// sourced directly from routines via a UNION branch. Restricting
2638+
// the JOIN to non-TABLE routines prevents same-named scalar+UDTF
2639+
// pairs (e.g. `generate_series`) from cross-joining.
2640+
let where_clause = where_clause.replace("p.function_name", "sc.function_name");
26342641
let query = format!(
26352642
r#"
26362643
SELECT DISTINCT
2637-
p.*,
2638-
r.function_type function_type,
2639-
r.description description,
2640-
r.syntax_example syntax_example
2641-
FROM
2642-
(
2644+
sc.function_name,
2645+
sc.return_type,
2646+
sc.parameters,
2647+
sc.parameter_types,
2648+
sc.function_type,
2649+
sc.description,
2650+
sc.syntax_example
2651+
FROM (
2652+
SELECT
2653+
p.function_name,
2654+
p.return_type,
2655+
p.parameters,
2656+
p.parameter_types,
2657+
r.function_type function_type,
2658+
r.description description,
2659+
r.syntax_example syntax_example
2660+
FROM (
26432661
SELECT
2644-
i.specific_name function_name,
2662+
o.specific_name function_name,
26452663
o.data_type return_type,
26462664
array_agg(i.parameter_name ORDER BY i.ordinal_position ASC) parameters,
26472665
array_agg(i.data_type ORDER BY i.ordinal_position ASC) parameter_types
@@ -2657,9 +2675,9 @@ FROM
26572675
FROM
26582676
information_schema.parameters
26592677
WHERE
2660-
parameter_mode = 'IN'
2661-
) i
2662-
JOIN
2678+
parameter_mode = 'OUT'
2679+
) o
2680+
LEFT JOIN
26632681
(
26642682
SELECT
26652683
specific_catalog,
@@ -2672,16 +2690,32 @@ FROM
26722690
FROM
26732691
information_schema.parameters
26742692
WHERE
2675-
parameter_mode = 'OUT'
2676-
) o
2693+
parameter_mode = 'IN'
2694+
) i
26772695
ON i.specific_catalog = o.specific_catalog
26782696
AND i.specific_schema = o.specific_schema
26792697
AND i.specific_name = o.specific_name
26802698
AND i.rid = o.rid
2681-
GROUP BY 1, 2, i.rid
2699+
GROUP BY 1, 2, o.rid
26822700
) as p
2683-
JOIN information_schema.routines r
2684-
ON p.function_name = r.routine_name
2701+
JOIN information_schema.routines r
2702+
ON p.function_name = r.routine_name
2703+
AND r.function_type <> 'TABLE'
2704+
2705+
UNION ALL
2706+
2707+
SELECT
2708+
routine_name function_name,
2709+
data_type return_type,
2710+
array_agg(NULL) FILTER (WHERE FALSE) parameters,
2711+
array_agg(NULL) FILTER (WHERE FALSE) parameter_types,
2712+
function_type,
2713+
description,
2714+
syntax_example
2715+
FROM information_schema.routines
2716+
WHERE function_type = 'TABLE'
2717+
GROUP BY routine_name, data_type, function_type, description, syntax_example
2718+
) sc
26852719
{where_clause}
26862720
"#
26872721
);

‎datafusion/sqllogictest/test_files/information_schema.slt‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -897,6 +897,17 @@ date_trunc Time(ns) [precision, expression] [String, Time(ns)] SCALAR Truncates
897897
date_trunc Timestamp(ns) [precision, expression] [String, Timestamp(ns)] SCALAR Truncates a timestamp or time value to a specified precision. date_trunc(precision, expression)
898898
date_trunc Timestamp(ns, "+TZ") [precision, expression] [String, Timestamp(ns, "+TZ")] SCALAR Truncates a timestamp or time value to a specified precision. date_trunc(precision, expression)
899899

900+
# Table functions (UDTFs) appear in information_schema.routines with
901+
# function_type = TABLE and data_type = TABLE.
902+
# Note: built-in `generate_series` and `range` are registered as BOTH a
903+
# scalar UDF and a UDTF, so this test filters to the TABLE rows to make
904+
# a stable assertion.
905+
query TTT rowsort
906+
select routine_name, data_type, function_type from information_schema.routines where function_type = 'TABLE' order by routine_name;
907+
----
908+
generate_series TABLE TABLE
909+
range TABLE TABLE
910+
900911
statement ok
901912
show functions
902913

0 commit comments

Comments
 (0)