Null Call Clause - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
This optional clause specifies how the function handles null results. The clause has two options:
  • CALLED ON NULL INPUT
  • RETURNS NULL ON NULL INPUT

The behavior of this clause is correlated with the specification you make for the parameter style:

IF you specify this parameter style … THEN nulls …
SQL can be specified for both the input and the result.

This is accomplished by providing an indicator value for each parameter as well as for the result parameter.

TD_GENERAL raise an exception condition.
JAVA behave differently depending on whether the function uses an object map or a simple map (see Data Type Mapping Between SQL and Java for details), as follows.
  • If an object map type is used, nulls can be specified for both the input and the result.
  • If a simple map type is used, nulls raise an exception condition.

A null can be explicitly returned from a scalar or aggregate Java UDF only when the return type of its Java method is defined as an object-mapped data type.

If the return type of the Java method is defined as simple-mapped, then you cannot return a null at runtime because simple-mapped data types cannot represent nulls.

You can pass a null as a parameter at runtime to a scalar or aggregate Java UDF only under the following conditions.
  • If a parameter is object-mapped, then you can specify either RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT.

    If you specify RETURNS NULL ON NULL INPUT, Vantage detects a null for the parameter and returns null as the result of the UDF.

    The function does not evaluate the null parameter.

    If you specify CALLED ON NULL INPUT, Vantage passes the null for the parameter to the UDF to be evaluated appropriately.

  • If a parameter is simple-mapped, you can only specify RETURNS NULL ON NULL INPUT.

    In this case, the system detects the null for the parameter and returns it as the result of the UDF. The function does not evaluate the null parameter.

    While you can specify CALLED ON NULL INPUT for CREATE FUNCTION and REPLACE FUNCTION definitions, invocation of the UDF with a null always fails.

For C and C++ procedures, if any of the passed arguments is null, then the action taken depends on the option you specify for handling nulls.

IF you specify … THEN the function is …
CALLED ON NULL INPUT called and evaluated.
RETURNS NULL ON NULL INPUT

and any of the parameter values passed to the function are null

not called. Instead, it always returns a null.

This option is useful to avoid generating an exception condition for functions defined with the TD_GENERAL parameter style, which does not handle nulls.

You cannot specify RETURNS NULL ON NULL INPUT for aggregate functions because they must always be called even if they are passed a null.