there may be something I'm missing in the executor side of things.
"For those curious, I found the issue: in ExecEvalJsonExprPath, I
had misplaced the check for jsexpr->on_mismatch.
I wasn't setting jsestate->escontext.details_wanted = true early enough.
Without this, the soft error context wasn't capturing the specific SQL error code needed to identify the mismatch.
Attaching a v2 that implements ON MISMATCH for JSON_QUERY and JSON_TABLE too.
That said, the semantics of ON ERROR / MISMATCH / EMPTY are complex.
I expect someone with access to and detailed knowledge of the standard might be able to poke holes in certain edge cases, particularly regarding precedence.
2 amendments to the commit message:
- This is isn't in the SQL/JSON standard
But aligns with the semantics of Oracle that already provides such a clause [0]:
- A TYPE ERROR is defined as "A JSON scalar value has a data type that is incompatible with the corresponding return SQL scalar data type."
- Precedence logic: ON MISMATCH applies only when neither of the clauses ON EMPTY and ON ERROR applies.
- Fallback Behavior: if the user doesn't specify ON MISMATCH, the coercion error is caught by ON ERROR.
