Message-ID: <f1084e8b-17f2-4e1f-9d75-fbb48eb75280@enterprisedb.com>
Date: Thu, 7 Mar 2024 12:13:03 +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>
Cc: Amit Langote <amitlangote09@gmail.com>,
 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>
 <562e5ad9-1357-4752-ad28-7e56740f74f8@enterprisedb.com>
 <CAPF61jBm+_QCwDg32_K+BgNKf09isU9g3o8CLW2kUDLZ5=28Hw@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <CAPF61jBm+_QCwDg32_K+BgNKf09isU9g3o8CLW2kUDLZ5=28Hw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/f1084e8b-17f2-4e1f-9d75-fbb48eb75280%40enterprisedb.com>
Precedence: bulk


On 3/7/24 06:18, Himanshu Upadhyaya wrote:
> On Wed, Mar 6, 2024 at 9:04 PM Tomas Vondra <tomas.vondra@enterprisedb.com>
> 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