Questioning an errcode and message in jsonb.c

Hi,

This came up in [0] and opinions besides my own would be welcome.

There is a function cannotCastJsonbValue in jsonb.c, and it throws
errors
of this form:

ERRCODE_INVALID_PARAMETER_VALUE "cannot cast jsonb %1$s to type %2$s"

where %1 is one of the possible JsonbValue types (null, string, numeric,
boolean, array, object, or "array or object" for jbvBinary). %2 is the
name
of some SQL type.

I question the errcode because I do not see a lot of precedent for
ERRCODE_INVALID_PARAMETER_VALUE in this sort of context; it seems more
often used for a weird value of some behavioral parameter passed to
a function.

The bigger deal is I question the wording, because although calls to
this function are made from various jsonb_foo cast functions, the
conditions for calling it don't involve the SQL type foo. This message
only means that you don't have the type of JsonbValue you thought
you were going to cast to the SQL type. I think that's what it should
say.

Let me lay out a little more of the picture, by contrasting the way
these
jsonb casts work (which may be as specified in SQL/JSON, I don't have a
copy) with the way XMLCAST works in SQL/XML.

When you XMLCAST some XML value to some target SQL type TD, then there
is a corresponding XML Schema type XMLT chosen based on TD. For example,
if you are casting to SQL's SMALLINT, XMLT will be chosen as xs:integer.
There are then two things that happen in sequence:
1) whatever XML type you have is hit with the XQuery expression
"cast as xs:integer", and then
2) the xs:integer is cast to SQL's SMALLINT and returned.

What our jsonb_foo casts do starts out the same way: based on
the target SQL type, there's a corresponding JsonbValue type
chosen. Target SQL type SMALLINT => jbvNumeric, for example.

But step 2 is not like the SQL/XML case: there is no attempt
to cast any other kind of JsonbValue to jbvNumeric. If the value
isn't already of that JSON type, it's an error. (It's like an
alternate-universe version of the SQL/XML rules, where the
XQuery "cast as" in step 1 is "treat as" instead.)

And then step 3 is unchanged: the JsonbValue of the expected
type (which it had to already be) is cast to the wanted SQL
type.

Consider these two examples:

select '"32768"'::jsonb::smallint;
INVALID_PARAMETER_VALUE cannot cast jsonb string to type smallint

select '32768'::jsonb::smallint;
NUMERIC_VALUE_OUT_OF_RANGE smallint out of range

The second message is clearly from step 3, the actual attempt
to cast a value to smallint, and is what you would expect.

The first message is from step 2, and it really only means
"jsonb string where jsonb numeric expected", but for whatever SQL
type you ask for that corresponds to jsonb numeric in step 2,
you get a custom version of the message phrased as "can't cast to"
your target SQL type instead. To me, that just disguises what is
really happening. (It's not a matter of "can't" cast "32768" to
32768, after all; it's a matter of "won't" do any casting in
step 2.)

It matters because the patch being discussed in [0] is
complexified by trying to produce a matching message; it
actually requires passing the ultimate wanted SQL type as an
extra argument to a function that has no other reason to
need it, and could easily produce a message like "jsonb string
where jsonb numeric expected" without it.

To me, when a situation like that crops up, it suggests that the
message is kind of misrepresenting the logic.

It would make me happy if the message could be changed, and maybe
ERRCODE_INVALID_PARAMETER_VALUE also changed, perhaps to one of
the JSON-specific ones in the 2203x range.

By the same token, the message and the errcode are established
current behavior, so there can be sound arguments against changing
them (even though that means weird logic in rewriting the expression).

Thoughts?

Regards,
-Chap

[0]
https://www.postgresql.org/message-id/43a988594ac91a63dc4bb49a94303a42%40anastigmatix.net