On 22.09.23 02:38, Andy Fan wrote:
> create table tb(a jsonb);
>
> 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
>
> the call stack is:
> 0 in errstart of elog.c:351
> 1 in errstart_cold of elog.c:333
> 2 in cannotCastJsonbValue of jsonb.c:2033
> 3 in jsonb_numeric of jsonb.c:2063
> 4 in ExecInterpExpr of execExprInterp.c:758
>
> select cast(a->'b' as int2) from tb;
> NUMERIC_VALUE_OUT_OF_RANGE smallint out of range
>
> the call stack is:
> 1 in errstart_cold of elog.c:333
> 2 in numeric_int2 of numeric.c:4503
> 3 in DirectFunctionCall1Coll of fmgr.c:785
> 4 in jsonb_int2 of jsonb.c:2086
>
> There are 2 different errcode involved here and there are two different
> functions that play part in it (jsonb_numeric and numeric_int2). and
> the error code jsonb_numeric used is improper as well.
This looks like an undesirable inconsistency.
You asked about the SQL standard. The error code
NUMERIC_VALUE_OUT_OF_RANGE appears as part of a failure of the <cast
specification>. The error code ERRCODE_INVALID_PARAMETER_VALUE appears
only as part of processing host parameters in <externally-invoked
procedure>. Of course, in PostgreSQL, function calls and casts are
related under the hood, so you could maybe make arguments for both. But
I think we already use ERRCODE_INVALID_PARAMETER_VALUE more broadly than
the standard, so I would tend to prefer going in the direction of
NUMERIC_VALUE_OUT_OF_RANGE when in doubt.
We could also consider these operators a special case of JSON_VALUE, in
which case the following would apply:
"""
If IDT cannot be cast to target type DT according to the Syntax Rules of
Subclause 6.13, “<cast specification>”, then let TEMPST be data
exception — SQL/JSON item cannot be cast to target type (2203G).
"""
We do have a definition of this in errcodes.txt but don't use it
anywhere. Maybe the patches for SQL/JSON currently being reviewed will
use it.