Absence, emptiness and errors

A feature returns a result for every key it is asked about. That result is a value, an empty result, NULL, or an error. This page describes which one applies. Several of these rules differ from SQL habits.

Empty is not absent

A known parent with no children is an empty set. COUNT() of it is 0 and ARRAY_AGG is []. SUM, MIN and MAX return NULL.

FeatureQL

DEFINE
    customers := ENTITY(),
    orders := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    order_id := INPUT(BIGINT#orders),
    ord := INLINE_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        customer_id BIGINT#customers,
        amount DECIMAL(10,2)
        FROM JSON([
            {"order_id": 101, "customer_id": 1, "amount": 10.00},
            {"order_id": 102, "customer_id": 1, "amount": 5.00}
        ])
    ),
    n_orders := customer_id.RELATED(COUNT() GROUP BY (ord[customer_id])),
    revenue := customer_id.RELATED(SUM(ord[amount]) GROUP BY (ord[customer_id])),
    biggest := customer_id.RELATED(MAX(ord[amount]) GROUP BY (ord[customer_id])),
    amounts := customer_id.RELATED(
        ARRAY_AGG(ord[amount]) GROUP BY (ord[customer_id])
    )
RETURN
    customer_id,
    n_orders,
    revenue,
    biggest,
    amounts
FOR
    customer_id := BIND_VALUES(ARRAY(1, 2)),
    NESTED order_id := BIND_VALUES(ARRAY(101, 102))
ORDER BY customer_id
;

Result

CUSTOMER_ID BIGINT#CUSTOMERS	N_ORDERS BIGINT	REVENUE DECIMAL(38,2)	BIGGEST DECIMAL(10,2)	AMOUNTS ARRAY<DECIMAL(10,2)>
1	2	15.00	10.00	[5.00, 10.00]
2	0	NULL	NULL	[]

Customer 2 has no orders. COUNT() = 0 is the test for "has no children".

NULL marks absence: a NULL key or a lookup with no matching target.

FeatureQL

DEFINE
    customers := ENTITY(),
    orders := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    order_id := INPUT(BIGINT#orders),
    cust := INLINE_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        segment VARCHAR
        FROM JSON([{"customer_id": 1, "segment": "gold"}])
    ),
    ord := INLINE_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        customer_id BIGINT#customers
        FROM JSON([
            {"order_id": 101, "customer_id": 1},
            {"order_id": 102, "customer_id": 9},
            {"order_id": 103, "customer_id": null}
        ])
    ),
    order_segment := RELATED(cust[segment] VIA ord[customer_id])
RETURN
    order_id,
    order_segment
FOR
    order_id := BIND_VALUES(ARRAY(101, 102, 103))
ORDER BY order_id
;

Result

ORDER_ID BIGINT#ORDERS	ORDER_SEGMENT VARCHAR
101	gold
102	NULL
103	NULL

Situation	Result
Known key with no children	`COUNT()` is `0`, `ARRAY_AGG` is `[]`, `SUM`, `MIN` and `MAX` are `NULL`
Lookup with no matching target	`NULL`
`NULL` key	`NULL`
`NULL` in `GROUP BY`	One group
`NULL` in `COUNT(DISTINCT k)` and `PER k`	Excluded
`NULL` in `ARRAY_DISTINCT`	Kept, once

Counting

COUNT() takes no argument and counts rows. COUNT(*) is accepted as a permissive spelling. A count of non-NULL values is written COUNT_IF(x IS NOT NULL).

FeatureQL

DEFINE
    ARR := ARRAY(ROW(1 AS v), ROW(NULL::BIGINT AS v), ROW(3 AS v), ROW(3 AS v)),
RETURN
    N_ROWS     := ARR.TRANSFORM(RETURN COUNT()).UNWRAP_ONE(),
    N_STAR     := ARR.TRANSFORM(RETURN COUNT(*)).UNWRAP_ONE(),
    N_NOT_NULL := ARR.TRANSFORM(RETURN COUNT_IF(v IS NOT NULL)).UNWRAP_ONE(),
    N_DISTINCT := ARR.TRANSFORM(RETURN COUNT(DISTINCT v)).UNWRAP_ONE(),
    TOTAL      := ARR.TRANSFORM(RETURN SUM(v)).UNWRAP_ONE(),
    DISTINCT_LEN := ARRAY_LENGTH(ARRAY_DISTINCT(ARR.TRANSFORM(RETURN v).UNWRAP()));

Result

N_ROWS BIGINT	N_STAR BIGINT	N_NOT_NULL BIGINT	N_DISTINCT BIGINT	TOTAL BIGINT	DISTINCT_LEN BIGINT
4	4	3	2	7	3

The array holds 1, NULL, 3, 3. COUNT(DISTINCT v) excludes NULL and returns 2. ARRAY_DISTINCT keeps one NULL and has 3 elements.

COUNT(x) is refused. In SQL it counts non-NULL values, which is easy to misread as a row count:

FeatureQL

DEFINE
    ARR := ARRAY(ROW(1 AS v), ROW(NULL::BIGINT AS v), ROW(3 AS v)),
    N := ARR.TRANSFORM(RETURN COUNT(v)).UNWRAP_ONE(),
RETURN N;

Result

ERROR UE/AGGREGATE-COUNT-NULLARY-ONLY COUNT() counts every row and takes no argument. Write COUNT() for a row count, or COUNT_IF(expr IS NOT NULL) or COUNT() FILTER (WHERE expr IS NOT NULL) to count non-NULL values.

Comparisons with NULL

= and < follow SQL three-valued logic. Null-safe equality is IS [NOT] DISTINCT FROM. GROUP BY and ORDER BY treat NULL as a key of its own, so they do not behave like =.

Errors

At compile time. A literal that can never be valid is refused when the query is compiled:

FeatureQL

RETURN
    x := 1 / 0
;

Result

ERROR UE/ARGUMENT-MUST-BE-NON-ZERO Division by zero is not allowed; DIVIDE() requires a non-zero divisor when defining feature X; got 0.

A non-finite constant is refused in the same way:

FeatureQL

RETURN
    x := "NAN"()
;

Result

ERROR UE/NON-FINITE-LITERAL A FeatureQL number must be finite. NAN() is not a finite number. Remove NAN() when defining feature X.

At run time. The compiler does not trace values that arrive through INPUT or from a source. A zero divisor in the data fails when the query runs, and the message names the feature:

FeatureQL

DEFINE
    a := INPUT(BIGINT),
    b := INPUT(BIGINT)
RETURN
    a,
    b,
    q := a / b
FOR
    (a, b) := BIND_VALUES(ARRAY(ROW(10, 2), ROW(10, 0)))
;

Result

ERROR SQL_EXECUTION_ERROR Error in sql_executor: Invalid Input Error: ARGUMENT-MUST-BE-NON-ZERO: Division by zero is not allowed; DIVIDE() requires a non-zero divisor at runtime when computing the feature Q. Please check your data or adapt the definition.

Guarding. There are two ways to avoid the failure. TRY before a function call turns that call's error into NULL. A guard inside the expression, such as NULLIF, avoids the error condition.

FeatureQL

DEFINE
    a := INPUT(BIGINT),
    b := INPUT(BIGINT)
RETURN
    a,
    b,
    safe := TRY DIVIDE(a, b),
    guarded := a / NULLIF(b, 0),
    logged := TRY LN(b::DOUBLE)
FOR
    (a, b) := BIND_VALUES(ARRAY(ROW(10, 2), ROW(10, 0)))
ORDER BY b
;

Result

A BIGINT	B BIGINT	SAFE DOUBLE	GUARDED DOUBLE	LOGGED DOUBLE
10	0	NULL	NULL	NULL
10	2	5	5	0.6931471805599453

TRY applies to a function call, so division is written TRY DIVIDE(a, b).

A feature that is not reachable from RETURN is never evaluated, so it cannot fail. See the overview .

Last update at: 2026/10/13 08:32:21

On this page

Empty is not absent Counting Comparisons with NULL Errors