On Sun, Jan 25, 2026 at 3:15 AM Florents Tselai <[email protected]> wrote:
> > >> >> I've had trouble making JSON_QUERY work too, >> so I suspect that although my tests pass, >> 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. [0] https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/mismatch-clause-sql-json-query-functions.html#GUID-21FE887D-5D7B-43BB-A1A8-B26B8924B290
