Re: Questioning an errcode and message in jsonb.c

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 .  The error code ERRCODE_INVALID_PARAMETER_VALUE appears 
only as part of processing host parameters in .  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, “”, 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.