Introduce `INFORMATION_SCHEMA.ROUTINES` table by goldmedal · Pull Request #13255 · apache/datafusion

goldmedal · 2024-11-05T02:59:50Z

Which issue does this PR close?

Part of #12144 .

Rationale for this change

As per the discussed in #12144 (comment), to support listing available functions, we should implement corresponding tables in the information schema.

What changes are included in this PR?

Refer to Postgres, MySQL, and BigQuery, I implemented the following columns for the ROUTINES table:

specific_catalog: Same as the session default catalog
specific_schema: Same as the session default schema
specific_name: function name
routine_catalog: Same as the session default catalog
routine_schema: Same as the session default schema
routine_name: function name
routine_type: always FUNCTION (this field could be PROCEDURE in other databases)
is_deterministic: if the volatility of the function signature is Immutable
data_type: the return type of the function
function_type: DataFusion custom field. It could be scalar, aggregate or window
description: DataFusion custom field. The description of the function document.

Are these changes tested?

yes

Are there any user-facing changes?

A new table of information_schema

goldmedal · 2024-11-05T03:34:53Z

There is another useful column I wanted to add, is_null_call. Its description in the Postgres document is

If the function automatically returns null if any of its arguments are null, then YES, else NO. Null for a procedure.

However, I think we don't have enough information in the function signature to do it 🤔 .

goldmedal · 2024-11-05T03:37:23Z

datafusion/sqllogictest/test_files/information_schema.slt

+query TTTTTTTBTTT rowsort
+select * from information_schema.routines where routine_name = 'date_trunc' OR routine_name = 'string_agg' OR routine_name = 'rank';
+----
+datafusion public date_trunc datafusion public date_trunc FUNCTION true Timestamp(Microsecond, None) SCALAR Truncates a timestamp value to a specified precision.


The type name is an ArrowType name, e.g., Timestamp (Microsecond, None). I prefer to return a SQL type. I think it would be improved if we finished the logical type epic.

I agree -- makes sense 👍

goldmedal · 2024-11-05T03:38:50Z

datafusion/expr-common/src/signature.rs

+            // TODO: Implement for other types
+            TypeSignature::Uniform(_, _)
+            | TypeSignature::Coercible(_)


There is a follow-up issue for inferring the types from other TypeSignatures.

I think we should file a ticket to track adding the feature (I bet some other contributors would enjoy helping out now that you have setup the framework)

Sounds great. I filed #13271 for it.

goldmedal · 2024-11-05T04:01:00Z

datafusion/core/src/catalog_common/information_schema.rs

+                // only handle the function which implemented [`ScalarUDFImpl::return_type`] method
+                let return_type = udf.return_type(&arg_types).ok().map(|t| t.to_string());


Some functions only implement return_type_from_exprs, e.g., arrow_cast. It's hard to get the return type here. Return null for this case.

alamb

Looks like a great start to me -- thank you @goldmedal

I think it would be really nice to file tickets for the follow on work you have identified so we don't forget (and so that others can help out)

alamb · 2024-11-05T18:14:39Z

datafusion/expr-common/src/signature.rs

+            // TODO: Implement for other types
+            TypeSignature::Uniform(_, _)
+            | TypeSignature::Coercible(_)


I think we should file a ticket to track adding the feature (I bet some other contributors would enjoy helping out now that you have setup the framework)

alamb · 2024-11-05T18:16:04Z

datafusion/sqllogictest/test_files/information_schema.slt

+query TTTTTTTBTTT rowsort
+select * from information_schema.routines where routine_name = 'date_trunc' OR routine_name = 'string_agg' OR routine_name = 'rank';
+----
+datafusion public date_trunc datafusion public date_trunc FUNCTION true Timestamp(Microsecond, None) SCALAR Truncates a timestamp value to a specified precision.


I agree -- makes sense 👍

alamb · 2024-11-05T18:17:14Z

fyi @findepi

goldmedal · 2024-11-07T10:12:22Z

Thanks @alamb for reviewing

* tmp * introduce routines table * add is_deterministic field * cargo fmt * rollback the session_state changed

Omega359 · 2024-11-19T22:44:35Z

There is another useful column I wanted to add, is_null_call. Its description in the Postgres document is
If the function automatically returns null if any of its arguments are null, then YES, else NO. Null for a procedure.
However, I think we don't have enough information in the function signature to do it 🤔 .

You are correct, there isn't a way to do it currently without inspecting the code.

goldmedal added 3 commits November 4, 2024 10:46

tmp

cd0af0f

introduce routines table

f358431

add is_deterministic field

0ef6eb1

github-actions bot added logical-expr Logical plan and expressions core Core DataFusion crate sqllogictest SQL Logic Tests (.slt) substrait Changes to the substrait crate execution Related to the execution crate labels Nov 5, 2024

goldmedal added 3 commits November 5, 2024 11:02

cargo fmt

3f5412f

Merge branch 'main' into feature/information-schema-routine

d79db45

rollback the session_state changed

1a949db

github-actions bot removed the substrait Changes to the substrait crate label Nov 5, 2024

goldmedal commented Nov 5, 2024

View reviewed changes

goldmedal mentioned this pull request Nov 5, 2024

List available functions (SHOW FUNCTIONS) #12144

Closed

goldmedal marked this pull request as ready for review November 5, 2024 03:43

goldmedal commented Nov 5, 2024

View reviewed changes

alamb approved these changes Nov 5, 2024

View reviewed changes

alamb mentioned this pull request Nov 5, 2024

Nov 5. 2024: This week in DataFusion #13265

Closed

3 tasks

goldmedal mentioned this pull request Nov 6, 2024

Inferring the possible types from the TypeSignature #13271

Closed

goldmedal merged commit b0b6e44 into apache:main Nov 7, 2024

goldmedal deleted the feature/information-schema-routine branch November 7, 2024 10:12

adbmal pushed a commit to adbmal/datafusion that referenced this pull request Nov 7, 2024

Introduce INFORMATION_SCHEMA.ROUTINES table (apache#13255)

10da22e

* tmp * introduce routines table * add is_deterministic field * cargo fmt * rollback the session_state changed

alamb mentioned this pull request Nov 20, 2024

Nov 20. 2024: This week in DataFusion #13503

Closed

3 tasks

hiltontj mentioned this pull request May 21, 2025

chore: update to latest influxdb3_core influxdata/influxdb#26429

Merged

		// only handle the function which implemented [`ScalarUDFImpl::return_type`] method
		let return_type = udf.return_type(&arg_types).ok().map(\|t\| t.to_string());

Comments

Conversation

goldmedal commented Nov 5, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Which issue does this PR close?

Rationale for this change

What changes are included in this PR?

Are these changes tested?

Are there any user-facing changes?

Uh oh!

goldmedal commented Nov 5, 2024

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

alamb left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

alamb commented Nov 5, 2024

Uh oh!

goldmedal commented Nov 7, 2024

Uh oh!

Omega359 commented Nov 19, 2024

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

goldmedal commented Nov 5, 2024 •

edited

Loading