Functions
NULL handling functions
7 NULL handling functions in NYXDB: names, aliases, signatures and behaviour.
7 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.
| Function | Summary |
|---|---|
assume_not_null | Removes a value’s nullable annotation after enforcing non-NULL validity. |
coalesce | Returns the earliest available non-NULL value from a compatible argument list. |
if_null | Substitutes a default only when the primary expression is SQL NULL. |
is_not_null | Recognizes present SQL values, including zero and empty text. |
is_null | Detects SQL absence without conflating it with JSON null or empty values. |
is_zero_or_null | Accepts either numeric zero or SQL NULL as a true predicate result. |
null_if | Converts a matching first value into SQL NULL while preserving unequal inputs. |
assume_not_null
Removes a value’s nullable annotation after enforcing non-NULL validity.
assume_not_null(value:nullable(T)) -> T| Kind | scalar |
| Signature class | scalar/unary+generic |
| Returns | T |
| Aliases | assumeNotNull |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL is an error; otherwise strip nullability |
Returns the original value through a nonnullable result descriptor when a proof or runtime check establishes its presence. An actual NULL raises INVALID_ARGUMENT rather than producing an arbitrary default. The operation changes nullability metadata without converting the underlying logical type.
Example
assume_not_null(7)
-- → 7coalesce
Returns the earliest available non-NULL value from a compatible argument list.
coalesce(x1:T,x2:T,...) -> common_supertype(T...) nullable| Kind | scalar |
| Signature class | scalar/variadic+generic |
| Returns | common_supertype(T...) nullable |
| Aliases | if_null |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | first_non_NULL_argument;all_NULL_returns_NULL |
Checks two through sixteen arguments in order and stops at the first value that is not SQL NULL. All-NULL input returns a typed NULL, and later expressions are masked after a value is found. Incompatible arity raises INVALID_ARGUMENT and a missing lossless common type raises TYPE_MISMATCH.
Example
coalesce(NULL, 7, 9)
-- → 7if_null
Substitutes a default only when the primary expression is SQL NULL.
if_null(x:T,default:T) -> common_supertype(T,T)| Kind | scalar |
| Signature class | scalar/binary+generic |
| Returns | common_supertype(T,T) |
| Aliases | ifNull |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL_x_selects_default;otherwise_x |
Returns the first argument whenever it is present, evaluating the default only for NULL rows. A NULL default remains NULL if selected, and incompatible result types raise TYPE_MISMATCH. This binary operation preserves zero, false and empty strings as ordinary primary values.
Example
if_null(NULL, 12)
-- → 12is_not_null
Recognizes present SQL values, including zero and empty text.
is_not_null(x:T) -> bool| Kind | scalar |
| Signature class | scalar/unary+generic |
| Returns | bool |
| Aliases | isNotNull |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | returns_false_exactly_for_SQL_NULL |
Returns false exactly for SQL NULL and true for every non-NULL value of a supported ledger type. It never returns NULL, and JSON null remains a present JSON value rather than SQL absence. Binding a nonvalue expression instead of one typed argument raises TYPE_MISMATCH.
Example
is_not_null('')
-- → trueis_null
Detects SQL absence without conflating it with JSON null or empty values.
is_null(x:T) -> bool| Kind | scalar |
| Signature class | scalar/unary+generic |
| Returns | bool |
| Aliases | isNull |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | returns_true_exactly_for_SQL_NULL |
Tests the SQL nullability flag independently of the value’s payload or logical type. The Boolean result is always present, including when the input is NULL. Empty arrays, empty strings and native JSON null return false; incompatible call shapes raise TYPE_MISMATCH.
Example
is_null(NULL)
-- → trueis_zero_or_null
Accepts either numeric zero or SQL NULL as a true predicate result.
is_zero_or_null(value:nullable(number)) -> bool| Kind | scalar |
| Signature class | scalar/unary |
| Returns | bool |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | true for NULL or numeric zero; false otherwise |
Returns true for a missing numeric value or for a value equal to zero, including floating negative zero. Every other number returns false and the predicate itself is never NULL. Nonnumeric input raises TYPE_MISMATCH; a floating NaN is not equal to zero.
Example
is_zero_or_null(0)
-- → truenull_if
Converts a matching first value into SQL NULL while preserving unequal inputs.
nullif(x:T,y:T) -> T nullable| Kind | scalar |
| Signature class | scalar/binary+generic |
| Returns | T nullable |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | equal_non_NULL_inputs_return_NULL;otherwise_x |
Compares two values with SQL equality and returns NULL only when that comparison is true. If the second argument is NULL, a present first argument survives because the comparison is unknown. The input type must support equality or raise TYPE_MISMATCH; a NULL first argument remains NULL.
Example
nullif(8, 8)
-- → NULL