PostgreSQL: filtering on null returns zero rows instead of IS NULL / IS NOT NULL

[rrrodzilla]

Summary

On the PostgreSQL backend, a filter comparing a field to null matches zero rows and returns total_count: 0 with a 200. It does not error, and it does not fall back to IS NULL. Both eq null and ne null return zero for the same field, so the two answers sum to less than the table.

Callers that use a null filter to partition a table get a silently wrong count. There is nothing in the response to distinguish "no rows are null" from "this predicate cannot be expressed".

Reproduction

Against a PostgreSQL-backed instance with a Journalist schema whose work_email is a nullable text, holding 13,554 rows, every one of which has a non-empty work_email:

POST /api/v1/forge/schemas/Journalist/entities/query
{"limit": 1}                                                          -> total_count 13554
{"limit": 1, "filter": {"op":"ne","field":"work_email","value":null}} -> total_count 0
{"limit": 1, "filter": {"op":"eq","field":"work_email","value":null}} -> total_count 0

The correct answers are 13,554 and 0. The same shape reproduces on an enum column (Outlet.email_format), where ne null returns 0 while a full page-through finds rows with a value set.

Root cause

crates/schema-forge-postgres/src/query.rs:129 and :133:

Filter::Eq { path, value } => {
    params.push(value.clone());
    format!("{} = ${}", field_path_to_sql(path), params.len())
}
Filter::Ne { path, value } => {
    params.push(value.clone());
    format!("{} != ${}", field_path_to_sql(path), params.len())
}

When value is DynamicValue::Null, this emits col = $1 / col != $1 with $1 bound to NULL. Under SQL three-valued logic both predicates evaluate to UNKNOWN for every row, so the WHERE clause excludes the whole table. Postgres is behaving correctly; the SQL is wrong.

Suggested fix

Special-case the null value before pushing a parameter:

Filter::Eq { path, value } => {
    if matches!(value, DynamicValue::Null) {
        return format!("{} IS NULL", field_path_to_sql(path));
    }
    params.push(value.clone());
    format!("{} = ${}", field_path_to_sql(path), params.len())
}
Filter::Ne { path, value } => {
    if matches!(value, DynamicValue::Null) {
        return format!("{} IS NOT NULL", field_path_to_sql(path));
    }
    params.push(value.clone());
    format!("{} != ${}", field_path_to_sql(path), params.len())
}

Ne against a non-null value has a related asymmetry worth deciding deliberately: col != $1 also drops rows where col is NULL, so eq x and ne x do not partition a nullable column. If the intended semantic is a partition, that arm wants (col IS NULL OR col != $1).

Cross-backend inconsistency

The three backends do not agree on this today, so a null filter is not portable:

• schema-forge-postgres/src/query.rs:129 emits = $n with a NULL bind. Matches nothing.
• schema-forge-surrealdb/src/query.rs:109 emits field = <value> into SurrealQL, where NONE = NONE is true. Matches.
• schema-forge-mssql/src/backend.rs:396 compares in memory with field_value(entity, path) == Some(value), a third semantic again.

Whatever the fix, it is worth pinning the intended semantic in a shared test so the backends cannot drift.

Impact

Found while generating a proposal figure for a live bid. The script counted "journalist records with a work email" with ne null and would have written 0 into the submitted document. A predicate that answers a question wrong with a 200 is worse than one that refuses, because there is no place to notice it.