public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Himanshu Upadhyaya <[email protected]>
Cc: 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: Thu, 7 Mar 2024 12:13:03 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPF61jBm+_QCwDg32_K+BgNKf09isU9g3o8CLW2kUDLZ5=28Hw@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CA+HiwqGtdVCg-KboUUU=svwukwXGT4EOFOk3eCZLz6h-d6YLNw@mail.gmail.com>
<CAPF61jAOJM6FqMsPGYHXbKxtX8DQ2a7kuOZ3AzR8oGXHy3fjFA@mail.gmail.com>
<[email protected]>
<CAPF61jBm+_QCwDg32_K+BgNKf09isU9g3o8CLW2kUDLZ5=28Hw@mail.gmail.com>
On 3/7/24 06:18, Himanshu Upadhyaya wrote:
> On Wed, Mar 6, 2024 at 9:04 PM Tomas Vondra <[email protected]>
> wrote:
>
>>
>>
>> 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).
>>
>> ok, Then why does the below query provide the correct conversion, even if
> we enclose that in double quotes?
> ‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{
> "id" : "1234567890",
> "FULL_NAME" : "JOHN DOE"}',
> '$'
> COLUMNS(
> name varchar(20) PATH 'lax $.FULL_NAME',
> id int PATH 'lax $.id'
> )
> )
> ;
> name | id
> ----------+------------
> JOHN DOE | 1234567890
> (1 row)
>
> and for bigger input(string) it will leave as empty as below.
> ‘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)
>
> seems it is not something to do with data enclosed in double quotes but
> somehow related with internal casting it to integer and I think in case of
> bigger input it is not able to cast it to integer(as defined under COLUMNS
> as id int PATH 'lax $.id')
>
> ‘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)
> )
>
> if it is not able to represent it to integer because of bigger input, it
> should error out with a similar error message instead of leaving it empty.
>
> Thoughts?
>
Ah, I see! Yes, that's a bit weird. Put slightly differently:
test=# SELECT * FROM JSON_TABLE(jsonb '{"id" : "2000000000"}',
'$' COLUMNS(id int PATH '$.id'));
id
------------
2000000000
(1 row)
Time: 0.248 ms
test=# SELECT * FROM JSON_TABLE(jsonb '{"id" : "3000000000"}',
'$' COLUMNS(id int PATH '$.id'));
id
----
(1 row)
Clearly, when converting the string literal into int value, there's some
sort of error handling that realizes 3B overflows, and returns NULL
instead. I'm not sure if this is intentional.
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