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.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