ON NULL INPUT characteristic
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
This characteristic both determines a contract for use by optimisers, as well as influences the behaviour of a function.
Most built-in SQL functions return NULL as soon as any of the arguments are NULL. For example SUBSTRING:
create.select(
substring("abc" , 2) .as("s1"),
substring("abc" , val(null, INTEGER)).as("s2"),
substring(val(null, VARCHAR), 2) .as("s3"),
substring(val(null, VARCHAR), val(null, INTEGER)).as("s4"),
).fetchOne();
Yielding:
+----+--------+--------+--------+
| s1 | s2 | s3 | s4 |
+----+--------+--------+--------+
| bc | {null} | {null} | {null} |
+----+--------+--------+--------+
While it is easy to implement this manually, it is both convenient, and helpful for optimisers, to just use the characteristic to achieve this standard behaviour, and possibly even to prevent calling the function, preventing the overhead from the context switches, etc.
If not natively supported by your dialect, jOOQ will simply wrap your function body in a IF statement checking for argument value nullability.
Parameter<Integer> i1 = in("i1", INTEGER);
Parameter<Integer> i2 = in("i2", INTEGER);
// The function always returns NULL if any argument value is NULL
create.createFunction("my_sum")
.parameters(i1, i2)
.returns(INTEGER)
.returnsNullOnNullInput()
.as(return_(i1.plus(i2)))
.execute();
// The function may not return NULL if any argument value is NULL
create.createFunction("my_null_safe_sum")
.parameters(i1, i2)
.returns(INTEGER)
.calledOnNullInput()
.as(return_(coalesce(i1, 0).plus(coalesce(i2, 0))))
.execute();
Feedback
Do you have any feedback about this page? We'd love to hear it!