The COMMUTATOR modifier

The COMMUTATOR modifier allows you to specify an SPL function that is the commutator function of the SPL function you are creating. A commutator function accepts the same arguments as the SPL function you are creating, but in opposite order, and returns the same value. The commutator function might be more cost effective for the SQL optimizer to execute.

For example, the functions lessthan(a,b), which returns TRUE if a is less than b, and greaterthan(b,a), which returns TRUE if b is greater than or equal to a, are commutator functions. The following figure uses the WITH clause to define a commutator function.

Figure 1: Define a commutator function.

CREATE FUNCTION lessthan( a dtype1, b dtype2 )
   RETURNING BOOLEAN
   WITH ( COMMUTATOR = greaterthan );
. . .
END FUNCTION;

The optimizer might use greaterthan(b,a) if it is less expensive to execute than lessthan(a,b). To specify a commutator function, you must own both the commutator function and the SPL function you are writing. You must also grant the user of your SPL function the Execute privilege on both functions.

For a detailed description of granting privileges, see the description of the GRANT statement in the HCL OneDB™ Guide to SQL: Syntax.