JSON_VALUE: a RETURNING type mismatch bypasses ON ERROR and raises a raw cast failure (upstream Calcite) #120

This is an upstream Apache Calcite issue, not a defect in this project — filing here so it is recorded where the binding lives, and so the decision a consumer has to make about it is written down. It reproduces through Apache.Calcite.Data with no adapter or data source involved.

SQL:2016 makes a RETURNING type mismatch in JSON_VALUE an error condition, governed by the ON ERROR clause whose default is NULL. Calcite raises a raw cast failure instead, and the clause is not consulted.

The clause is implemented and honoured — that is the point

Measured against Calcite 1.42.0, over a literal JSON string:

DEFAULT 'x' ON ERROR answering x shows the clause is parsed, understood and acted on. An object is not a scalar, so the extraction fails, the error path runs, and the requested behaviour is applied.

The type mismatch bypasses it

The DEFAULT 0 ON ERROR row is the clearest statement of the defect: the same clause that returns x for an object does nothing here.

What it looks like

RETURNING appears to assert the runtime type rather than convert to it. The ON ERROR behaviour is applied to the extraction step, and the cast to the returning type happens afterwards and outside that handling — so a scalar of the wrong type never reaches the error path, while a non-scalar does.

Note the exception type: InvalidCastException is the CLR surfacing of a hard cast in the generated code, not a JSON-specific error. On the JVM this would presumably be a ClassCastException.

Why it matters to a consumer

An adapter pushing a predicate through JSON_VALUE(… RETURNING INTEGER) > 10 can restrict the scan to documents whose value is a number. Doing so excludes exactly the documents that would otherwise throw — which is what the standard says should have happened, those values yielding NULL and therefore failing the comparison.

That leaves the consumer choosing between reproducing the engine's crash and being closer to the standard than the engine. calcite-cosmos is choosing the latter and recording it as a deliberate divergence; this issue is the reference for why.

Reproduction

CalciteJsonValueMeasurementTests.ReturningAssertsTheTypeAndOnErrorDoesNotGovernIt in ikvmnet/calcite-cosmos pins all of the above, and needs no service — a CalciteConnection over a literal JSON string.