The OpenAccess SDK SQL engine supports an extension of the built-in set of scalar functions by implementing the functions in the IP. A user defined scalar function can be defined to take a variable or a fixed number of arguments and return a scalar value. A scalar function can be marked as constant to indicate that its output does not change based on the row.
An IP supports IP-specific scalar functions by implementing the scalar function and registering it with the OpenAccess SDK SQL engine. A C/C++ IP registers its scalar functions in the INIT_SCALAR function. In INIT_SCALAR, call dam_add_scalar to register each IP specific scalar function. A Java IP registers its scalar functions by implementing the ipGetScalarFunctions method to return a scalar_function array with one entry for each function the IP implementer wants to expose. The signature of the function that is implementing the scalar function is always the same. The actual arguments to the scalar function are retrieved using the OpenAccess SDK SQL engine API.
Each scalar function entry contains the following:
• the scalar function name as exposed to the SQL Engine (szName).
• whether the function has constant output or not – A value of 1 indicates that the function is a constant scalar function and is evaluated only once per query if all its arguments are literal values. If the inputs to a constant function are not literal values then the function is evaluated once per record during the dam_isTargetRow call. Also, a scalar function of constant type can be treated as other constant restrictions in expressions, as in:
my_funct () BETWEEN 10 AND 20
A value of 0 indicates that the function is a variable scalar function and therefore it should be evaluated once per record during the dam_isTargetRow call, regardless of the input.
• the function that implements it (pScalarFunc). In C/C++ this is a function pointer. In Java this is the name of the method.
• the number of arguments (args):
• >=0 to allow fixed argument list
• < 0 to allow variable argument list with a maximum of |args|
• the return type (xo_type)
Execution of the user defined scalar function is handled by the following sequence of steps (refer to the referenced function description for details):
1. The function in the IP that is associated with the scalar function is called with an expression list. The function, if marked as a constant function, is called once per query. Functions that are marked as returning non-constant are called for each record as part of the dam_isTargetRow call.
2. Call dam_getFirstValExp to get the first argument.
3. Call dam_getValueOfExp to get the value of the argument.
4. Call dam_getNextValExp to get the next argument.
Repeat steps 3 and 4 until dam_getNextValExp returns a NULL. If the function implements a fixed number of arguments, then you can stop as soon as you have retrieved the preset number of argument values.
5. Perform the processing using the input arguments and generate an output value, using dam_createVal.
6. Return the value object returned by dam_createVal or return NULL if there has been an error.
Constant scalar functions are evaluated before the IP is called for the query execution. This occurs even before IP EXECUTE is called for START_QUERY. Non-constant scalar functions are evaluated during query execution for each row in the result. This can happen either during dam_isTargetRow validation or when a row is added to the result using dam_addRowToTable. Based on where the non-constant scalar function is used, evaluation can occur at any of the following phases of query execution:
• Functions in a search expression are evaluated during row validation when dam_isTargetRow is called.
• Functions in the SELECT list are evaluated when a row is added to result using dam_addRowToTable. This applies to a single-table query. In a join query, scalar functions are evaluated when the innermost table record is processed.If Block Joins are enabled, scalar functions are evaluated once the IP returns from the query execution and the OpenAccess SDK SQL engine combines the outer table and inner table records. In the case of block joins, the evaluation would occur before IP Execute(END_QUERY) is called.
