public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomas Vondra <[email protected]>
To: Himanshu Upadhyaya <[email protected]>
To: Amit Langote <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: jian he <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: remaining sql/json patches
Date: Wed, 6 Mar 2024 16:34:41 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPF61jAOJM6FqMsPGYHXbKxtX8DQ2a7kuOZ3AzR8oGXHy3fjFA@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CA+HiwqGtdVCg-KboUUU=svwukwXGT4EOFOk3eCZLz6h-d6YLNw@mail.gmail.com>
	<CAPF61jAOJM6FqMsPGYHXbKxtX8DQ2a7kuOZ3AzR8oGXHy3fjFA@mail.gmail.com>



On 3/6/24 12:58, Himanshu Upadhyaya wrote:
> On Tue, Mar 5, 2024 at 6:52 AM Amit Langote <[email protected]> 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

view thread (24+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: remaining sql/json patches
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox