Re: remaining sql/json patches

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