MIME-Version: 1.0
References: <1cee81f3-312f-30e3-0282-c4df7ee72f93@xs4all.nl>
 <202403041503.5momp6knupgo@alvherre.pgsql> <CA+HiwqGtdVCg-KboUUU=svwukwXGT4EOFOk3eCZLz6h-d6YLNw@mail.gmail.com>
In-Reply-To: <CA+HiwqGtdVCg-KboUUU=svwukwXGT4EOFOk3eCZLz6h-d6YLNw@mail.gmail.com>
From: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>
Date: Wed, 6 Mar 2024 17:28:06 +0530
Message-ID: <CAPF61jAOJM6FqMsPGYHXbKxtX8DQ2a7kuOZ3AzR8oGXHy3fjFA@mail.gmail.com>
Subject: Re: remaining sql/json patches
To: Amit Langote <amitlangote09@gmail.com>
Cc: 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>
Content-Type: multipart/alternative; boundary="000000000000338f0d0612fcae22"
Archived-At: <https://www.postgresql.org/message-id/CAPF61jAOJM6FqMsPGYHXbKxtX8DQ2a7kuOZ3AzR8oGXHy3fjFA%40mail.gmail.com>
Precedence: bulk

--000000000000338f0d0612fcae22
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 5, 2024 at 6:52=E2=80=AFAM Amit Langote <amitlangote09@gmail.co=
m> wrote:

Hi,

I am doing some random testing with the latest patch and found one scenario
that I wanted to share.
consider a below case.

=E2=80=98postgres[102531]=3D#=E2=80=99SELECT * 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
=E2=80=98postgres[102531]=3D#=E2=80=99SELECT * 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.


--=20
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com

--000000000000338f0d0612fcae22
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Tue, Mar 5, 2024 at 6:52=E2=80=AFAM Am=
it Langote &lt;<a href=3D"mailto:amitlangote09@gmail.com">amitlangote09@gma=
il.com</a>&gt; wrote:</div><div dir=3D"ltr" class=3D"gmail_attr"><br></div>=
<div dir=3D"ltr" class=3D"gmail_attr"><div id=3D"gmail-:4b6" class=3D"gmail=
-Ar gmail-Au gmail-Ao" style=3D"display:block"><div id=3D"gmail-:4b2" class=
=3D"gmail-Am gmail-aiL gmail-Al editable gmail-LW-avf gmail-tS-tW gmail-tS-=
tY" aria-label=3D"Message Body" role=3D"textbox" aria-multiline=3D"true" st=
yle=3D"direction:ltr;min-height:85px" tabindex=3D"1" aria-controls=3D":4du"=
 aria-expanded=3D"false"><div dir=3D"ltr"><div>Hi,</div><div><br></div><div=
>I am doing some random testing with the latest patch and found one scenari=
o that I wanted to share.</div><div>consider a below case.</div><div><br></=
div><div>=E2=80=98postgres[102531]=3D#=E2=80=99SELECT * FROM JSON_TABLE(jso=
nb &#39;{<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&quot;id&quot; : 12345678901=
,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&quot;FULL_NAME&quot; : &quot;JOHN D=
OE&quot;}&#39;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
&#39;$&#39;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 COLU=
MNS(<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0name varchar(20) PATH &#39;lax $.FULL_NAME&#39;,<br>=C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0id int PATH =
&#39;lax $.id&#39;<br>=C2=A0 =C2=A0 =C2=A0 )<br>=C2=A0 =C2=A0)<br>;<br>ERRO=
R: =C2=A022003: integer out of range<br>LOCATION: =C2=A0numeric_int4_opt_er=
ror, numeric.c:4385<br>=E2=80=98postgres[102531]=3D#=E2=80=99SELECT * FROM =
JSON_TABLE(jsonb &#39;{<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&quot;id&quot;=
 : &quot;12345678901&quot;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&quot;FULL=
_NAME&quot; : &quot;JOHN DOE&quot;}&#39;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 &#39;$&#39;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 COLUMNS(<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0name varchar(20) PATH &#39;lax $.FULL=
_NAME&#39;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0id int PATH &#39;lax $.id&#39;<br>=C2=A0 =C2=A0 =C2=A0 )<b=
r>=C2=A0 =C2=A0)<br>;<br>=C2=A0 =C2=A0name =C2=A0 | id <br>----------+----<=
br>=C2=A0JOHN DOE | =C2=A0 <br>(1 row)</div><div><br></div><div>The first q=
uery throws an error that the integer is &quot;out of range&quot; and is qu=
ite expected but in the second case(when the value is enclosed with &quot;)=
 it is able to process the JSON object but does not return any relevant err=
or(in fact processes the JSON but returns it with empty data for &quot;id&q=
uot; field). I think second query should fail with a similar error.<br></di=
v><div><br></div></div><br><span class=3D"gmail_signature_prefix">-- </span=
><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Regards,<b=
r>
<span>Himanshu Upadhyaya</span><br>
EnterpriseDB: <a href=3D"http://www.enterprisedb.com" rel=3D"noreferrer" ta=
rget=3D"_blank">http://www.enterprisedb.com</a></div></div></div></div></di=
v></div></div>

--000000000000338f0d0612fcae22--