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 database manager determines which function is the best fit for the invocation by comparing the argument and parameter data types. 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.

If the data types of all the parameters for a given function are the same as those of the arguments in the function invocation, that function is the best fit. If there is no exact match, the database manager compares the data types in the parameter lists from left to right, using the following method:

  1. Compare the data type of the first argument in the function invocation to the data type of the first parameter in each function. (Any length, precision, scale, and CCSID attributes of the data types are not considered in the comparison.)
  2. For this argument, if one function has a data type that fits the function invocation better than the data types in the other functions, that function is the best fit. The precedence list for the promotion of data types in Promotion of data types shows the data types that fit each data type in best-to-worst order.
  3. If the data type of the first parameter for more than one candidate function fits the function invocation equally well, repeat this process for the next argument of the function invocation. Continue for each argument until a best fit is found.

The following examples illustrate function resolution.

Example 1: Assume that MYSCHEMA contains two functions, both named FUNA, that were created with these partial CREATE FUNCTION statements.

  CREATE FUNCTION MYSCHEMA.FUNA (VARCHAR(10), INT, DOUBLE) ...
  CREATE FUNCTION MYSCHEMA.FUNA (VARCHAR(10), REAL, DOUBLE) ...  

Also assume that a function with three arguments of data types VARCHAR(10), SMALLINT, and DECIMAL is invoked with a qualified name:

   MYSCHEMA.FUNA( VARCHARCOL, SMALLINTCOL, DECIMALCOL ) ...

Both MYSCHEMA.FUNA functions are candidates for this function invocation because they meet the criteria specified in Function resolution. The data types of the first parameter for the two function instances in the schema, which are both VARCHAR, fit the data type of the first argument of the function invocation, which is VARCHAR, equally well. However, for the second parameter, the data type of the first function (INT) fits the data type of the second argument (SMALLINT) better than the data type of second function (REAL). Therefore, the database manager selects the first MYSCHEMA.FUNA function as the function instance to execute.

Example 2: Assume that functions were created with these partial CREATE FUNCTION statements:

   1. CREATE FUNCTION SMITH.ADDIT (CHAR(5), INT, DOUBLE) ...
   2. CREATE FUNCTION SMITH.ADDIT (INT, INT, DOUBLE) ...
   3. CREATE FUNCTION SMITH.ADDIT (INT, INT, DOUBLE, INT) ...
   4. CREATE FUNCTION JOHNSON.ADDIT (INT, DOUBLE, DOUBLE) ...
   5. CREATE FUNCTION JOHNSON.ADDIT (INT, INT, DOUBLE) ...
   6. CREATE FUNCTION TODD.ADDIT (REAL) ...
   7. CREATE FUNCTION TAYLOR.SUBIT (INT, INT, DECIMAL) ...        

Also assume that the SQL path at the time an application invokes a function is "TAYLOR", "JOHNSON", "SMITH". The function is invoked with three data types (INT, INT, DECIMAL) as follows:

   SELECT  ... ADDIT(INTCOL1, INTCOL2, DECIMALCOL) ...

Function 5 is chosen as the function instance to execute based on the following evaluation:

Example 3: Assume that functions were created with these partial CREATE FUNCTION statements:

   1. CREATE FUNCTION BESTGEN.MYFUNC (INT, DECIMAL(9,0)) ...
   2. CREATE FUNCTION KNAPP.MYFUNC (INT, NUMERIC(8,0))...
   3. CREATE FUNCTION ROMANO.MYFUNC (INT, FLOAT) ...          

Also assume that the SQL path at the time an application invokes a function is "ROMANO", "KNAPP", "BESTGEN". The function is invoked with two data types (SMALLINT, DECIMAL) as follows:

   SELECT  ... MYFUNC(SINTCOL1, DECIMALCOL) ...

Function 2 is chosen as the function instance to execute based on the following evaluation: