Recognising source-defined sentinel values

[marioapiphani]

Opened at @ceubank's suggestion, following the discussion on #50. Posting the use case in detail so others can add requirements.

The gap

Three different things can leave a value absent, or apparently absent, in a result:

• A join introduced it. [core-spec] Add Relational Query Interface specification #354 §7.6 and §8.1 handle this for measure aggregation: rows carrying no row identity for a measure are excluded from that measure's aggregation input.
• The source stored NULL. Ordinary SQL semantics.
• The source stored a real value that it defines as carrying a special meaning — most often "not set". [core-spec] Add Relational Query Interface specification #354 does not define source-sentinel recognition, and I haven't found it addressed elsewhere, though I'd welcome a pointer if it is.

The third case is the subject here. Row identity cannot recognise it: the row is real and the value is real, and only the interpretation differs.

Why this matters for systems of record

Many SAP fields use type-dependent initial values rather than NULL:

Type	Initial value	Example
CHAR	blank	LAND1 (country key, CHAR 3) — blank where not maintained
DATS	'00000000'	a planned delivery date not yet set
NUMC	zeros	a zero-padded numeric-text field, initial as all zeros
DEC / CURR / QUAN	0	a credit limit of zero

ABAP treats this as first-class — IS INITIAL is a language construct — so the convention is pervasive rather than incidental.

SAP documents mapping '00000000' to NULL, which can be appropriate. Two questions remain. First, without recognition, a non-NULL sentinel can be counted as a populated field or treated as a candidate join key. Second, conversion to NULL alone does not preserve whether the original value was a sentinel or a source NULL. If downstream behavior needs that distinction, additional information must survive the conversion. #354's row-identity rules address join-generated rows for measure aggregation, but do not by themselves preserve this field-level distinction.

Two things that separate cleanly

The pattern is derivable; the meaning is not. What an initial value looks like follows from the type, so an emitter can propose candidates mechanically without anyone curating a list by hand. Whether initial means "not set" is a per-field decision. LIFSD (delivery block, CHAR 2) is blank when a customer isn't blocked — that blank is a real state, not an absence. So the recognition rule needs to be declarable per field even though the candidate values come from the type.

Recognition and behaviour are different questions. Recognising that '00000000' meant "not set" requires source-specific knowledge, and that knowledge may be lost unless something carries it forward. How the result then behaves in a calculation is a separate question, and the one @ceubank pointed at with Power BI's Blank.

Adjacent cases that may not want the same treatment

Similar-looking conventions exist outside ERP, and I don't think they all reduce to absence. SCD Type 2 dimensions commonly use '9999-12-31' as an open-ended validity end — that encodes "no specified validity end", a business state rather than a missing value. -1 as an unknown-member key encodes "known to be unknown", which is different again from "never set". I raise them as cases that may require distinct behaviour, not as evidence that one absence category covers everything.

What I'm not proposing

Deliberately, a design. In particular I don't know whether one missing-value type covers this. The numeric case is the one I'd want examined: a credit limit of 0 might mean "no credit granted" or "never maintained", and those are the same bytes. Recognising that needs a companion field or a business rule rather than a value pattern, so a type alone may not be sufficient.

Open questions

• Is this a type in the expression language, a property of a field, or both?
• Ordering: must recognition happen before type conversion and trimming? '00000000' can fail date conversion, and trailing spaces can become '', before any rule gets a chance to apply.
• Does the resulting state propagate through expressions, or is it resolved at the boundary and gone afterwards?
• Aggregation: does a declared sentinel participate in COUNT? SUM? How does that relate to the empty-input rules in [core-spec] Add Relational Query Interface specification #354 §8.1 / §9?
• Can an identifying field carry a declared sentinel? In ERP an initial key component in an incomplete record is common.
• Round-tripping: if a consumer materialises a blank and re-emits the model, does the source's sentinel come back?
• Is a per-type model-level default with per-field override useful, or is per-field the only safe granularity?

Happy to write up further SAP cases in whatever shape is useful, and to contribute fixtures once there's a direction.