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.
