`ExecuteUpdateAsync` JSON partial updates translate CLR null to SQL NULL instead of JSON null

[equals03]

When using ExecuteUpdateAsync to clear nullable properties inside a JSON/jsonb-mapped complex type, EF Core + Npgsql generate SQL that passes SQL NULL into jsonb_set / to_jsonb, instead of the JSON literal 'null'::jsonb.

That leads to failures such as:

• 42804: could not determine polymorphic type because input has type unknown
• 23502: null value in column "<jsonb column>" violates not-null constraint - because jsonb_set(..., NULL) returns SQL NULL and can wipe the entire JSON document

So atomic bulk updates cannot reliably null-clear JSON leaves or subtrees.

Where we hit it

We use ExecuteUpdateAsync for partial updates against a JSON-mapped configuration object on a PostgreSQL jsonb column - for example clearing optional scalar fields like rateLimitPerMinute / expiresAt, or nullable nested objects like basic / oauth.

Typical pattern:

await query.ExecuteUpdateAsync(setters => setters
    .SetProperty(e => e.Configuration.RateLimitPerMinute, _ => (int?)null));

Environment

• Npgsql.EntityFrameworkCore.PostgreSQL 10.0.1
• EF Core 10.x
• PostgreSQL 16
• JSON mapping via ComplexProperty(...).ToJson(...)

Cause

The issue appears to be in Npgsql’s JSON partial-update translation, mainly:

• NpgsqlQueryableMethodTranslatingExpressionVisitor.TrySerializeScalarToJson
• NpgsqlQueryableMethodTranslatingExpressionVisitor.GenerateJsonPartialUpdateSetter

For null values, generated SQL looks like:

-- scalar clear
jsonb_set(configuration, '{rateLimitPerMinute}', to_jsonb(NULL::int))

-- structural clear (parameterised null)
jsonb_set(configuration, '{basic}', NULL)

In PostgreSQL, to_jsonb(NULL::int) is SQL NULL, not JSON null. Likewise, jsonb_set's third argument must be jsonb; SQL NULL there nulls the whole expression.

Expected shape for a null-clear is effectively:

jsonb_set(configuration, '{rateLimitPerMinute}', 'null'::jsonb)

Experimental Workaround

We replaced IQueryableMethodTranslatingExpressionVisitorFactory with a subclass of NpgsqlQueryableMethodTranslatingExpressionVisitor that, for null JSON update values:

1. Emits 'null'::jsonb for null constants
2. For parameters, casts to the target type and wraps with COALESCE(..., 'null'::jsonb) so runtime null becomes JSON null, not SQL NULL

As a "proof of concept" this seems to work but it uses internal API's and is therefore nowhere near to a sustainable solution. It did, however, serve to illustrate the point and fix this particular problem.

DISCLAIMER: I couldn't seem to find anyone else with a similar issue and it is entirely possible that I have missed something obvious and have gone and boiled the ocean on this one 😄

[roji]

Thanks, this looks like a real provider bug.

The current JSON partial update pipeline does two things that are problematic for CLR null values:

• scalar leaves go through TrySerializeScalarToJson(), which wraps non-JSON scalar expressions in to_jsonb(...)/to_json(...); since those functions are strict, to_jsonb(NULL::int) produces SQL NULL, not the JSON value null.
• structural nested updates go through GenerateJsonPartialUpdateSetter() and pass the translated JSON value directly as the third jsonb_set argument; if that value is SQL NULL, jsonb_set(..., NULL) returns SQL NULL for the whole document.

So the expected behavior for a partial JSON update which assigns CLR null is indeed to generate a JSON null value for the third argument, e.g. 'null'::jsonb/'null'::json, rather than allowing SQL NULL to flow into jsonb_set/json_set.

A fix should likely cover both constant and parameter/runtime-null cases for scalar and structural partial updates, with tests for nullable scalar properties and nullable nested complex/owned JSON members. For parameterized scalar values, this probably means preserving normal non-null serialization while coalescing the JSON conversion result to JSON null, rather than only special-casing compile-time null constants.