Null arguments in overloaded routines

The database server might return an error message when you call a UDR and both of the following conditions are true:

The argument list of the UDR contains a null value.
The UDR invoked is an overloaded routine.

Suppose you create the following user-defined functions:

CREATE FUNCTION func1(arg1 INT, arg2 INT) RETURNS BOOLEAN...
CREATE FUNCTION func1(arg1 MONEY, arg2 INT) 
   RETURNS BOOLEAN...
CREATE FUNCTION func1(arg1 REAL, arg2 INT) RETURNS BOOLEAN...

The following statement creates a table, new_tab:

CREATE TABLE new_tab (col_int INT);

The following query is successful because the database server locates only one func1() function that matches the function argument in the expression:

SELECT *
FROM new_tab
WHERE func1(col_int, NULL) = "t";

The null value acts as a wildcard for the second argument and matches the second parameter type for each function func1() defined. The only func1() function with a leftmost parameter of type INT qualifies as the function to invoke.

If more than one qualifying routine exists, the database server returns an error. The following query returns an error because the database server cannot determine which func1() function to invoke. The null value in the first argument matches the first parameter of each function; all three func1() functions expect a second argument of type INTEGER.

SELECT *
FROM new_tab
WHERE func1(NULL, col_int) = "t";

To avoid ambiguity, use null values as arguments carefully.