Hi,
On Thu, Mar 7, 2024 at 9:06 PM Amit Langote <amitlangote09@gmail.com> wrote:
> This boils down to the difference in the cast expression chosen to
> convert the source value to int in the two cases.
>
> The case where the source value has no quotes, the chosen cast
> expression is a FuncExpr for function numeric_int4(), which has no way
> to suppress errors. When the source value has quotes, the cast
> expression is a CoerceViaIO expression, which can suppress the error.
> The default behavior is to suppress the error and return NULL, so the
> correct behavior is when the source value has quotes.
>
> I think we'll need either:
>
> * fix the code in 0001 to avoid getting numeric_int4() in this case,
> and generally cast functions that don't have soft-error handling
> support, in favor of using IO coercion.
> * fix FuncExpr (like CoerceViaIO) to respect SQL/JSON's request to
> suppress errors and fix downstream functions like numeric_int4() to
> comply by handling errors softly.
>
> I'm inclined to go with the 1st option as we already have the
> infrastructure in place -- input functions can all handle errors
> softly.
I've adjusted the coercion-handling code to deal with this and similar
cases to use coercion by calling the target type's input function in
more cases. The resulting refactoring allowed me to drop a bunch of
code and node structs, notably, the JsonCoercion and JsonItemCoercion
nodes. Going with input function based coercion as opposed to using
casts means the coercion may fail in more cases than before but I
think that's acceptable. For example, the following case did not fail
before because they'd use numeric_int() cast function to convert 1.234
to an integer:
select json_value('{"a": 1.234}', '$.a' returning int error on error);
ERROR: invalid input syntax for type integer: "1.234"
It is same error as this case, where the source numerical value is
specified as a string:
select json_value('{"a": "1.234"}', '$.a' returning int error on error);
ERROR: invalid input syntax for type integer: "1.234"
I had hoped to get rid of all instances of using casts and standardize
on coercion at runtime using input functions and json_populate_type(),
but there are a few cases where casts produce saner results and also
harmless (error-safe), such as cases where the target types are
domains or are types with typmod.
I've also tried to address most of Jian He's comments and a bunch of
cleanups of my own. Attaching 0002 as the delta over v42 containing
all of those changes.
I intend to commit 0001+0002 after a bit more polishing.
--
Thanks, Amit Langote