Epic: Simplify functions signature with LogicalType

[jayzhan211]

Is your feature request related to a problem or challenge?

Some functions signature is quite verbose, like

impl RegexpLikeFunc {
    pub fn new() -> Self {
        Self {
            signature: Signature::one_of(
                vec![
                    TypeSignature::Exact(vec![Utf8View, Utf8]),
                    TypeSignature::Exact(vec![Utf8View, Utf8View]),
                    TypeSignature::Exact(vec![Utf8View, LargeUtf8]),
                    TypeSignature::Exact(vec![Utf8, Utf8]),
                    TypeSignature::Exact(vec![Utf8, Utf8View]),
                    TypeSignature::Exact(vec![Utf8, LargeUtf8]),
                    TypeSignature::Exact(vec![LargeUtf8, Utf8]),
                    TypeSignature::Exact(vec![LargeUtf8, Utf8View]),
                    TypeSignature::Exact(vec![LargeUtf8, LargeUtf8]),
                    TypeSignature::Exact(vec![Utf8View, Utf8, Utf8]),
                    TypeSignature::Exact(vec![Utf8View, Utf8View, Utf8]),
                    TypeSignature::Exact(vec![Utf8View, LargeUtf8, Utf8]),
                    TypeSignature::Exact(vec![Utf8, Utf8, Utf8]),
                    TypeSignature::Exact(vec![Utf8, Utf8View, Utf8]),
                    TypeSignature::Exact(vec![Utf8, LargeUtf8, Utf8]),
                    TypeSignature::Exact(vec![LargeUtf8, Utf8, Utf8]),
                    TypeSignature::Exact(vec![LargeUtf8, Utf8View, Utf8]),
                    TypeSignature::Exact(vec![LargeUtf8, LargeUtf8, Utf8]),
                ],
                Volatility::Immutable,
            ),
        }
    }
}

Can replace it with `Signature::string(2, Volatility::Immutable)`

impl LPadFunc {
    pub fn new() -> Self {
        use DataType::*;
        Self {
            signature: Signature::one_of(
                vec![
                    Exact(vec![Utf8View, Int64]),
                    Exact(vec![Utf8View, Int64, Utf8View]),
                    Exact(vec![Utf8View, Int64, Utf8]),
                    Exact(vec![Utf8View, Int64, LargeUtf8]),
                    Exact(vec![Utf8, Int64]),
                    Exact(vec![Utf8, Int64, Utf8View]),
                    Exact(vec![Utf8, Int64, Utf8]),
                    Exact(vec![Utf8, Int64, LargeUtf8]),
                    Exact(vec![LargeUtf8, Int64]),
                    Exact(vec![LargeUtf8, Int64, Utf8View]),
                    Exact(vec![LargeUtf8, Int64, Utf8]),
                    Exact(vec![LargeUtf8, Int64, LargeUtf8]),
                ],
                Volatility::Immutable,
            ),
        }
    }
}

Can replace it with one of `Signature::coercible(string, int)` and `Signature::coercible(string, int, string)`

Describe the solution you'd like

#13240 starts an attempt to bring logical type to function signature

There are more functions to be cleanup.
The example above can be replaced with TypeSiganture::String, TypeSiganture::Numeric, TypeSiganture::Coercible.

We might also need time related signature for time function.

Improve test coverage with these functions especially with different kinds of types would be great 👍

The role of TypeSignature

TypeSignature used in function which is responsible for handling

1. length check
2. type checking and casting

The functions behaviour follows Postgres, DuckDB or other well-designed database. Can check whether the result and coercion is consistent with them.

If the result is consistent in both Postgres, DuckDB, we should follow them. Otherwise, we follow either of them.

For the casting rule, I think we can follow DuckDB's casting rule described here

TypeSignature should handle implicit casting

Implicit Casting
In many situations, the system will add casts by itself. This is called implicit casting. This happens for example when a function is called with an argument that does not match the type of the function, but can be casted to the desired type.

Consider the function sin(DOUBLE). This function takes as input argument a column of type DOUBLE, however, it can be called with an integer as well: sin(1). The integer is converted into a double before being passed to the sin function.

Implicit casts can only be added for a number of type combinations, and is generally only possible when the cast cannot fail. For example, an implicit cast can be added from INTEGER to DOUBLE – but not from DOUBLE to INTEGER.

Tasks

• Improve error messages when coercion fails #19004
• Avoid use of Signature::user_defined for normal functions #12725
• More ergnomic way to specify (named) paramters in signature #18685
• Return NativeType instead of DataType for get_example_types #14761
• [Proposal] String function data type handling requirements #13552
• Support numeric string argument in functions  #13285
• Simplify the returned type in TypeSignature::get_possible_types #13371
• Strengthen TypeSignature and Coercion rule. #10507
• New Signature concept #7580
• Support function signature of any type #7225
• Write Signature method for list datatypes. #6559
• Improve candidate functions suggestions #15817
• Refactor away usage of NUMERICS/INTEGERS in datafusion/expr-common/src/type_coercion/aggregates.rs #18092
• Replace TypeSignature::Numeric with TypeSignature::Coercible #14760
• TypeSignature::Coercible for math functions #14763
• Unified Function Signature type coercion handling for Nulls #12698

[solomonope]

take

[jayzhan211]

@jonathanc-n You can take a look on this if you are interested, there are tons of functions require the change, not able to resolved in single PR. Signature like Exact and Uniform are the targeted one to replace with

[jonathanc-n]

Thanks, i'll look into it!