Message-ID: <562e5ad9-1357-4752-ad28-7e56740f74f8@enterprisedb.com>
Date: Wed, 6 Mar 2024 16:34:41 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: remaining sql/json patches
Content-Language: en-US
To: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>,
 Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Erik Rijkers <er@xs4all.nl>,
 jian he <jian.universality@gmail.com>, Andres Freund <andres@anarazel.de>,
 Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <1cee81f3-312f-30e3-0282-c4df7ee72f93@xs4all.nl>
 <202403041503.5momp6knupgo@alvherre.pgsql>
 <CA+HiwqGtdVCg-KboUUU=svwukwXGT4EOFOk3eCZLz6h-d6YLNw@mail.gmail.com>
 <CAPF61jAOJM6FqMsPGYHXbKxtX8DQ2a7kuOZ3AzR8oGXHy3fjFA@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <CAPF61jAOJM6FqMsPGYHXbKxtX8DQ2a7kuOZ3AzR8oGXHy3fjFA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/562e5ad9-1357-4752-ad28-7e56740f74f8%40enterprisedb.com>
Precedence: bulk


On 3/6/24 12:58, Himanshu Upadhyaya wrote:
> On Tue, Mar 5, 2024 at 6:52 AM Amit Langote <amitlangote09@gmail.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