Re: Questioning an errcode and message in jsonb.c

On 2023-09-21 20:38, Andy Fan wrote:
> insert into tb select '{"a": "foo", "b": 100000000}';
> ...
> select cast(a->'a' as numeric) from tb;
> ERRCODE_INVALID_PARAMETER_VALUE  cannot cast jsonb string to type 
> numeric
> ...
> select cast(a->'b' as int2) from tb;
> NUMERIC_VALUE_OUT_OF_RANGE smallint out of range

... and perhaps driving home the point:

insert into tb select '{"a": "1", "b": 100000000}';
select cast(a->'a' as int2) from tb;
ERRCODE_INVALID_PARAMETER_VALUE  cannot cast jsonb string to type 
smallint

which illustrates that:

1) it is of no consequence whether the non-numeric JSON type of
the cast source is something that does or doesn't look castable to
numeric: in the first-step test that produces this message, the
only thing tested is whether the JSON type of the source is JSON
numeric. If it is not, there will be no attempt to cast it.

2) it is immaterial what the SQL target type of the cast is;
the message will misleadingly say "to smallint" if you are
casting to smallint, or "to double precision" if you are casting
to that, but the only thing that has been tested is whether the
source has JSON type numeric.

The message in this case only really means "JSON type string
where JSON type numeric needed".

The issue is fully general:

insert into tb select '{"a": 1}';
select cast(a->'a' as boolean) from tb;
ERRCODE_INVALID_PARAMETER_VALUE  cannot cast jsonb numeric to type 
boolean

Again, all that has been tested is whether the JSON type is
JSON boolean. If it is not, no effort is made to cast it, and
the message really only means "JSON type numeric where
JSON type boolean needed".

The most annoying cases are the ones where JSON type numeric
is needed, because of the several different SQL types that one
might want as the ultimate target type, so extra machinations
are needed to get this message to misleadingly mention that
ultimate type.

As I mentioned in my earlier message, the behavior here
differs from the exactly analogous specified behavior for
XMLCAST in SQL/XML. I am not saying the behavior here is
wrong; perhaps SQL/JSON has chosen to specify it differently
(I haven't got a copy). But I pointed out the difference as
it may help to pinpoint the relevant part of the spec.

In the SQL/XML XMLCAST, the same two-step process exists:
a first step that is only concerned with the XML Schema
type (say, is it xs:string or xs:decimal?), and a second
step where the right xs type is then cast to the wanted SQL type.

The difference is, XMLCAST in the first step will try to
cast a different xs type to the right xs type. By contrast
our JSON casting simply requires the JSON type to be the
right JSON type, or fails. And for all I know, that different
approach may be as specified in SQL/JSON.

But I would not have it use ERRCODE_INVALID_PARAMETER_VALUE,
or issue a message talking about the ultimate SQL type when the
only thing checked in that step is the JSON type ... unless
the spec really says to do so.

Regards,
-Chap