Re: remaining sql/json patches

On 3/6/24 12:58, Himanshu Upadhyaya wrote:
> 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.
>

I'm pretty sure this is the correct & expected behavior. The second
query treats the value as string (because that's what should happen for
values in double quotes).

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company