You cannot specify nondeterministic functions in the primary condition of the ON clause because even when the ON clause expression matches the primary index value in the INSERT specification, the evaluation of the UDF might be different when it is executed in the context of the ON clause than when it is executed in the context of the INSERT specification, leading to an unreasonable insert.
In this context, an unreasonable insert is an insert operation that causes a nonlocal AMP insert. In other words, the row might need to be redistributed to a different AMP before it could be inserted into the target table.
For example, the following MERGE request is not valid because it specifies a nondeterministic function as the primary condition in the ON clause, even though that condition matches the INSERT specification:
MERGE INTO t1
USING t2
ON a1=non_deterministic_udf(b2)
WHEN MATCHED THEN
UPDATE SET b1=b2
WHEN NOT MATCHED THEN
INSERT (non_deterministic_udf(b2), a2, c2);
A parallel example that specifies the RANDOM function as the primary condition in the ON clause and matches the INSERT specification is also nondeterministic and, therefore, not valid. For more information about the RANDOM function, see SQL Functions, Operators, Expressions, and Predicates.
MERGE INTO t1
USING t2
ON a1=RANDOM (1,100)
WHEN MATCHED THEN
UPDATE SET b1=b2
WHEN NOT MATCHED THEN
INSERT (RANDOM (1,100), b2, c2);
To avoid this problem, you should always specify the appropriate DETERMINISTIC or NOT DETERMINISTIC option for the CREATE FUNCTION statement when you create your external UDFs. For details, see “CREATE FUNCTION” and “CREATE FUNCTION (Table Form)” in SQL Data Definition Language. This assists the Parser to take the actions necessary to process the MERGE request properly. If the external UDF is specified as DETERMINISTIC even though its behavior is NOT DETERMINISTIC, its execution can cause an internal AMP error during processing of the request, causing it to abort.