Re: remaining sql/json patches

On Tue, Mar 5, 2024 at 6:52 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:

Hi,

I am doing some random testing with the latest patch and found one scenario
that I wanted to share.
consider a below case.

‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{
"id" : 12345678901,
"FULL_NAME" : "JOHN DOE"}',
'$'
COLUMNS(
name varchar(20) PATH 'lax $.FULL_NAME',
id int PATH 'lax $.id'
)
)
;
ERROR: 22003: integer out of range
LOCATION: numeric_int4_opt_error, numeric.c:4385
‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{
"id" : "12345678901",
"FULL_NAME" : "JOHN DOE"}',
'$'
COLUMNS(
name varchar(20) PATH 'lax $.FULL_NAME',
id int PATH 'lax $.id'
)
)
;
name | id
----------+----
JOHN DOE |
(1 row)

The first query throws an error that the integer is "out of range" and is
quite expected but in the second case(when the value is enclosed with ") it
is able to process the JSON object but does not return any relevant
error(in fact processes the JSON but returns it with empty data for "id"
field). I think second query should fail with a similar error.

--
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com