Inferring the possible types from the TypeSignature

[goldmedal]

Is your feature request related to a problem or challenge?

#13255 introduced the information_schema.routines table. I'm working on another table, inoformation_schema.parameters. Both need to list all possible combinations of the argument types and return types from a function signature.

Describe the solution you'd like

I implemented the method TypeSignature::get_possible_types to initial this framework( #13255 (comment)). Currently, I only implement TypeSignature::Exact and TypeSignature::OneOf. I think we can invoke the functions in type_coercion/functions.rs to do it.

datafusion/datafusion/expr/src/type_coercion/functions.rs

Lines 301 to 305 in 32266c2

	/// Returns a Vec of all possible valid argument types for the given signature.
	fn get_valid_types(
	signature: &TypeSignature,
	current_types: &[DataType],
	) -> Result<Vec<Vec<DataType>>> {

Given all types as the current type, and then get all possible argument combinations.

Describe alternatives you've considered

No response

Additional context

• Introduce INFORMATION_SCHEMA.ROUTINES table #13255 (comment)
• List available functions (SHOW FUNCTIONS) #12144 (comment)

[alamb]

I think this would be a good first issue as it is well written, the need is clear, and doesn't require broad knowledge of the codebase.

Someone could just write some unit tests for the code and fill in the implementation and I think it would be 👌 very nice

[delamarch3]

take

[goldmedal]

Thanks @delamarch3
This issue follows up on #13255. If you want to start working, you can use #13255 to work and rebase after it is merged.

[delamarch3]

Hi @goldmedal, I have a few thoughts/questions about this:

• I'm wondering how the type signature for something like Variadic and VariadicAny can be represented? In get_valid_types it returns what is passed in.
• The list of possible signatures for Coercible and Any could be quite large if we're looking to list all possible combinations
• I'm not sure how to handle UserDefined, it's doc suggests calling ScalarUDFImpl::coerce_types but we don't have a suitable list of types to pass into it to figure out the possible combinations, unless I'm missing something

[goldmedal]

• I'm wondering how the type signature for something like Variadic and VariadicAny can be represented? In get_valid_types it returns what is passed in.

This behavior makes sense to me. Variadic means the user can input the One or more arguments with the specific type. So, we can just return the result.

• The list of possible signatures for Coercible and Any could be quite large if we're looking to list all possible combinations

For the VariadicAny and Any, one choice is just listing all the data types but it is exactly too large to show. I think we can introduce a new logical type, ANY, for it. That's how DuckDB handles the kind of signatures.

D select return_type, parameters, parameter_types  from duckdb_functions() where function_name = 'concat';
┌─────────────┬────────────┬─────────────────┐
│ return_type │ parameters │ parameter_types │
│   varchar   │ varchar[]  │    varchar[]    │
├─────────────┼────────────┼─────────────────┤
│ VARCHAR     │ [col0]     │ [ANY]           │
└─────────────┴────────────┴─────────────────┘

However, it isn't a good first issue. Let's ignore VariadicAny and Any in this issue. I might raise another issue for them.

For Coercible, I think we can input all the data types. However, I think we can limit the scope. Currently, I think we don't need to handle the nested type. You can refer to what has been presented by the logical type in

datafusion/datafusion/common/src/types/builtin.rs

Line 34 in f190fc6

singleton!(LOGICAL_NULL, logical_null, Null);

Let's focus on them.

• I'm not sure how to handle UserDefined, it's doc suggests calling ScalarUDFImpl::coerce_types but we don't have a suitable list of types to pass into it to figure out the possible combinations, unless I'm missing something

Ok, I think it should be another issue that isn't a good first issue. I will raise another ticket for it. Let's ignore it here.

Thanks @delamarch3 , I think we can reorganize the scope of this issue. We can focus on the following signatures first:

            TypeSignature::Uniform(_, _)
            | TypeSignature::Coercible(_)
            | TypeSignature::Variadic(_)
            | TypeSignature::Numeric(_)
            | TypeSignature::String(_) 

We can handle others in the follow-up PRs.