Re: remaining sql/json patches - Mailing list pgsql-hackers

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.