Determining the best fit

There might be more than one function with the same name that is a candidate for execution. In that case, the data types of the parameters, the position of the schema in the SQL path, and the total number of parameters of each function in the set of candidate functions are used to determine if the function meets the best fit requirements. Note that the data type of the result of the function or the type of function (aggregate, scalar, or table) under consideration does not enter into this determination.

Start of change

If the set of candidate functions contains more than one function and named arguments are used in the function invocation, the ordinal position of the parameter corresponding to a named argument must be the same for all candidate functions.

The term set of parameters is used to refer to all of the parameters at the same position in the parameter lists (where such a parameter exists) for the set of candidate functions. The corresponding argument of a parameter is determined based on how the arguments are specified in the function invocation. For positional arguments, the corresponding argument to a parameter is the argument in the same position in the function invocation as the position of the parameter in the parameter list of the candidate function. For named arguments, the corresponding argument to a parameter is the argument with the same name as the parameter. In this case, the order of the arguments in the function invocation is not considered while determining the best fit. If the number of parameters in a candidate function is greater than the number of arguments in the function invocation, each parameter that does not have a corresponding argument is processed as if it does have a corresponding argument that has the DEFAULT keyword as the value.

End of change

Start of changeThe following steps are used to determine the function that is the best fit:End of change

Start of change
  • Step 1: Considering arguments that are typed expressions

    The database manager determines the function, or set of functions, that meet the best fit requirements for the invocation by comparing the data type of each parameter with the data type of the corresponding argument.

    When determining whether the data type of a parameter is the same as the data type of its corresponding argument:
    • Synonyms of data types match. For example, DOUBLE and FLOAT are considered to be the same.
    • Attributes of a data type such as length, precision, scale, and CCSID are ignored. Therefore, CHAR(8) and CHAR(35) are considered to be the same, as are DECIMAL(11,2), and DECIMAL(4,3).
    • The character and graphic types are considered to be the same. For example, the following are considered to be the same type: CHAR and GRAPHIC, VARCHAR and VARGRAPHIC, and CLOB and DBCLOB. CHAR(13) and GRAPHIC(8) are considered to be the same type.

    A subset of the candidate functions is obtained by considering only those functions for which the data type of each argument of the function invocation that is not an untyped expression matches or is promotable to the data type of the corresponding parameter of the function instance. If the argument of the function invocation is an untyped expression, the data type of the corresponding parameter can be any data type. The precedence list for the promotion of data types in Promotion of data types shows the data types that fit (considering promotion) for each data type in best-to-worst order. If this subset is not empty, then the best fit is determined using the Promotable process on this subset of candidate functions. If this subset is empty, then the best fit is determined using the Castable process on the original set of candidate functions.

    Promotable process
    This process determines the best fit only considering whether arguments in the function invocation match or can be promoted to the data type of the corresponding parameter of the function definition. For the subset of candidate functions, the parameter lists are processed from left to right, processing the set of parameters in the first position from the subset of candidate functions before moving on to the set of parameters in the second position, and so on. The following steps are used to eliminate candidate functions from the subset of candidate functions (only considering promotion):
    1. If one candidate function has a parameter where the data type of the corresponding argument fits (only considering promotion) the data type of the parameter better than other candidate functions, those candidate functions that do not fit the function invocation equally well are eliminated. The precedence list for the promotion of data types in Promotion of data types shows the data types that fit (considering promotion) for each data type in best-to-worst order.
    2. If the data type of the corresponding argument is an untyped expression, no candidate functions are eliminated.
    3. These steps are repeated for the next set of parameters from the remaining candidate functions until there are no more sets of parameters.
    Castable process
    This process determines the best fit first considering, for each parameter, if the data type of the corresponding argument in the function invocation matches or can be promoted to the data type of the parameter of the function definition. Then, for each set of parameters where no corresponding argument has a data type that was promotable, the database manager considers, for each parameter, if the data type of the corresponding argument can be implicitly cast for function resolution to the data type of the parameter.
    For the set of candidate functions, the parameters in the parameter lists are processed from left to right, processing the set of parameters in the first position from all the candidate functions before moving on to the set of parameters in the second position, and so on. The following steps are used to eliminate candidate functions from the set of candidate functions (only considering promotion):
    1. If one candidate function has a parameter where the data type of the corresponding argument fits (only considering promotion) the data type of the parameter better than other candidate functions, those candidate functions that do not fit the function invocation equally well are eliminated. The precedence list for the promotion of data types in Promotion of data types shows the data types that fit (considering promotion) for each data type in best-to-worst order.
    2. If the data type for the corresponding argument is not promotable (which includes the case when the corresponding argument is an untyped expression) to the data type of the parameter of any candidate function, no candidate functions are eliminated.
    3. These steps are repeated for the next set of parameters from the remaining candidate functions until there are no more sets of parameters.
    If at least one set of parameters has no corresponding argument that fit (only considering promotion) and the corresponding argument for the set of parameters has a data type, the database manager compares each such set of parameters from left to right. The following steps are used to eliminate candidate functions from the set of candidate functions (considering implicit casting).
    1. If all the data types of the set of parameters for all remaining candidate functions do not belong to the same data type precedence list, as specified in Promotion of data types, an error is returned.
    2. If the data type of the corresponding arguments cannot be implicitly cast to the data type of the parameters, as specified in Implicit casting for function resolution, an error is returned.
    3. If one candidate function has a parameter where the data type of the corresponding argument fits (considering implicit casting) the data type of the parameter better than other candidate functions, those candidate functions that do not fit the function invocation equally well are eliminated. The data type list in Implicit casting for function resolution shows the data type that fits (considering implicit casting) better.
    4. These steps are repeated for the next set of parameters which has no corresponding argument that fits (only considering promotion) and the corresponding argument for the set of parameters has a data type until there are no more such sets of parameters or an error occurs.
  • Step 2: Considering SQL path

    If more than one candidate function remains, the database manager selects those candidate functions whose schema is earliest in the SQL path.

  • Step 3: Considering number of arguments in the function invocation

    If more than one candidate function remains and if one candidate function has a number of parameters that is less than or equal to the number of parameters of the other candidate functions, those candidate functions that have a greater number of parameters are eliminated.

  • Step 4: Considering arguments that are untyped expressions
    If more than one candidate function remains and at least one set of parameters has a corresponding argument that is an untyped expression, the database manager compares each such set of parameters from left to right. The following steps are used to eliminate candidate functions from the set of candidate functions:
    1. If all the data types of the set of parameters for all remaining candidate functions do not belong to the same data type precedence list, as specified in Promotion of data types, an error is returned.
    2. If the data type of the parameter of one candidate function is further left in the data type ordering for implicit casting than other candidate functions, those candidate functions where the data type of the parameter is further right in the data type ordering are eliminated. The data type list in Implicit casting for function resolution shows the data type ordering for implicit casting.
    If there are still multiple candidate functions, an error is returned.
End of change
Start of change

Implicit casting for function resolution

Implicit casting for function resolution is not supported for arguments with a user-defined type or an XML, ROWID, or DATALINK data type. It is also not supported for built-in or user-defined cast functions. It is supported for the following cases:
  • Any supported cast between built-in types, except for DATE, TIME, and TIMESTAMP to numeric data types. See Table 2.
  • An untyped argument can be cast to any data type.
Similar to the data type precedence list for promotion, for implicit casting there is an order to the data types that are in the group of related data types. This order is used when performing function resolution that considers implicit casting. Table 1 shows the data type ordering for implicit casting for function resolution. The data types are listed in best-to-worst order (note that this is different than the ordering in the data type precedence list for promotion).
Table 1. Data type ordering for implicit casting for function resolution
Data type group Data type list for implicit casting for function resolution (in best-to-worst order)
Numeric data types DECFLOAT, double, real, decimal, BIGINT, INTEGER, SMALLINT
Character and graphic string data types VARCHAR or VARGRAPHIC, CHAR or GRAPHIC, CLOB or DBCLOB
Binary data types VARBINARY, BINARY, BLOB, VARCHAR FOR BIT DATA, CHAR FOR BIT DATA
Datetime data types TIMESTAMP, DATE, TIME
Note:
The lower case types above are defined as follows:
decimal
= DECIMAL(p,s) or NUMERIC(p,s)
real
= REAL or FLOAT(n) where n is a specification for single precision floating point
double
= DOUBLE, DOUBLE PRECISION, FLOAT or FLOAT(n) where n is a specification for double precision floating point
End of change
Start of change

Examples

The following are examples of function resolution. (Note that not all required keywords are shown.)

End of change
Start of change

Example 1: This is an example illustrating the SQL path considerations in function resolution. For this example, there are eight ACT functions, in three different schemas, registered as:

CREATE FUNCTION AUGUSTUS.ACT (CHAR(5), INT, DOUBLE) SPECIFIC ACT_1 ...
CREATE FUNCTION AUGUSTUS.ACT (INT, INT, DOUBLE) SPECIFIC ACT_2 ...
CREATE FUNCTION AUGUSTUS.ACT (INT, INT, DOUBLE, INT) SPECIFIC ACT_3 ...
CREATE FUNCTION JULIUS.ACT (INT, DOUBLE, DOUBLE) SPECIFIC ACT_4 ...
CREATE FUNCTION JULIUS.ACT (INT, INT, DOUBLE) SPECIFIC ACT_5 ...
CREATE FUNCTION JULIUS.ACT (SMALLINT, INT, DOUBLE) SPECIFIC ACT_6 ...
CREATE FUNCTION JULIUS.ACT (INT, INT, DECFLOAT) SPECIFIC ACT_7 ...
CREATE FUNCTION NERO.ACT (INT, INT, DEC(7,2)) SPECIFIC ACT_8 ...      

The function reference is as follows (where I1 and I2 are INTEGER columns, and D is a DECIMAL column):

SELECT ... ACT(I1, I2, D) ...

Assume that the application making this reference has an SQL path established as:

"JULIUS","AUGUSTUS","CAESAR"
Following the function resolution rules:
  • The function with specific name ACT_8 is eliminated as a candidate, because the schema NERO is not included in the SQL path.
  • The function with specific name ACT_3 is eliminated as a candidate, because it has the wrong number of parameters. ACT_1 and ACT_6 are eliminated because, in both cases, the first argument cannot be promoted to the data type of the first parameter.
  • Because there is more than one candidate remaining, the arguments are considered in order.
    • For the first argument, the remaining functions, ACT_2, ACT_4, ACT_5, and ACT_7 are an exact match with the argument type. No functions can be eliminated from consideration; therefore the next argument must be examined.
    • For this second argument, ACT_2, ACT_5, and ACT_7 are exact matches, but ACT_4 is not, so it is eliminated from consideration. The next argument is examined to determine some differentiation among ACT_2, ACT_5, and ACT_7.
    • For the third and last argument, neither ACT_2, ACT_5, nor ACT_7 match the argument type exactly. Although ACT_2 and ACT_5 are equally good, ACT_7 is not as good as the other two because the type DOUBLE is closer to DECIMAL than is DECFLOAT. ACT_7 is eliminated.
  • There are two functions remaining, ACT_2 and ACT_5, with identical parameter signatures. The final tie-breaker is to see which function's schema comes first in the SQL path, and on this basis, ACT_5 is the function chosen.
End of change
Start of change

Example 2: This is an example of a situation where function resolution will result in an error since more than one candidate function fits the invocation equally well, but the corresponding parameters for one of the arguments do not belong to the same type precedence list.

For this example, there are only three function in a single schema defined as follows:

CREATE FUNCTION CAESAR.ACT (INT, VARCHAR(5), VARCHAR(5)) SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DATE) SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE) SPECIFIC ACT_3 ...   

The function reference is as follows (where I1 and I2 are INTEGER columns, and VC is a VARCHAR column):

SELECT ... ACT(I1, I2, VC) ...

Assume that the application making this reference has an SQL path established as:

"CAESAR"
Following the function resolution rules:
  • Each of the candidate functions is evaluated to determine if the data type of each input argument of the function invocation matches or is promotable to the data type of the corresponding parameter of the function instance:
    • For the first argument, all the candidate functions have an exact match with the parameter type.
    • For the second argument, ACT_1 is eliminated because INTEGER is not promotable to VARCHAR.
    • For the third argument, both ACT_2 and ACT_3 are eliminated since VARCHAR is not promotable to DATE or DOUBLE, so no candidate functions remain.
  • Since the subset of candidate functions from above is empty, the candidate functions are considered using the castable process:
    • For the first argument, all the candidate functions have an exact match with the parameter type.
    • For the second argument, ACT_1 is eliminated since INTEGER is not promotable to VARCHAR. ACT_2 and ACT_3 are better candidates.
    • For the third argument, the data type of the corresponding parameters of ACT_2 and ACT_3 do not belong to the same data type precedence list, so an error is returned.
End of change
Start of change

Example 3: This example illustrates a situation where function resolution will succeed using the castable process.

For this example, there are only three function in a single schema defined as follows:

CREATE FUNCTION CAESAR.ACT (INT, VARCHAR(5), VARCHAR(5)) SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DECFLOAT) SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE) SPECIFIC ACT_3 ...   

The function reference is as follows (where I1 and I2 are INTEGER columns, and VC is a VARCHAR column):

SELECT ... ACT(I1, I2, VC) ...

Assume that the application making this reference has an SQL path established as:

"CAESAR"
Following the function resolution rules:
  • Each of the candidate functions is evaluated to determine if the data type of each input argument of the function invocation matches or is promotable to the data type of the corresponding parameter of the function instance:
    • For the first argument, all the candidate functions have an exact match with the parameter type.
    • For the second argument, ACT_1 is eliminated because INTEGER is not promotable to VARCHAR.
    • For the third argument, both ACT_2 and ACT_3 are eliminated since VARCHAR is not promotable to DECFLOAT or DOUBLE, so no candidate functions remain.
  • Since the subset of candidate functions from above is empty, the candidate functions are considered using the castable process:
    • For the first argument, all the candidate functions have an exact match with the parameter type.
    • For the second argument, ACT_1 is eliminated since INTEGER is not promotable to VARCHAR. ACT_2 and ACT_3 are better candidates.
    • For the third argument, both DECFLOAT and DOUBLE are in the same data type precedence list and VARCHAR can be implicitly cast to both DECFLOAT and DOUBLE. Since DECFLOAT is a better fit for the purpose of implicit casting, ACT_2 is the best fit
End of change
Start of change

Example 4: This example illustrates that during function resolution using the castable process that promotion of later arguments takes precedence over implicit casting.

For this example, there are only three function in a single schema defined as follows:

CREATE FUNCTION CAESAR.ACT (INT, INT, VARCHAR(5)) SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DECFLOAT) SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE) SPECIFIC ACT_3 ...   

The function reference is as follows (where I1 is an INTEGER columns, VC1 is a VARCHAR column, and C1 is a CHAR column):

SELECT ... ACT(I1, VC1, C1) ...

Assume that the application making this reference has an SQL path established as:

"CAESAR"
Following the function resolution rules:
  • Each of the candidate functions is evaluated to determine if the data type of each input argument of the function invocation matches or is promotable to the data type of the corresponding parameter of the function instance:
    • For the first argument, all the candidate functions have an exact match with the parameter type.
    • For the second argument, all candidate functions are eliminated since VARCHAR is not promotable to INTEGER, so no candidate functions remain.
  • Since the subset of candidate functions from above is empty, the candidate functions are considered using the castable process:
    • For the first argument, all the candidate functions have an exact match with the parameter type.
    • For the second argument, none of the candidate functions have a parameter to which the corresponding argument can be promoted, so no candidate functions are eliminated.
    • Since the third argument can be promoted to the parameter of ACT_1, but not to the parameters of ACT_2 or ACT_3, ACT_1 is the best fit.
End of change