MIME-Version: 1.0
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>
In-Reply-To: <562e5ad9-1357-4752-ad28-7e56740f74f8@enterprisedb.com>
From: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>
Date: Thu, 7 Mar 2024 10:48:02 +0530
Message-ID: <CAPF61jBm+_QCwDg32_K+BgNKf09isU9g3o8CLW2kUDLZ5=28Hw@mail.gmail.com>
Subject: Re: remaining sql/json patches
To: Tomas Vondra <tomas.vondra@enterprisedb.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>
Content-Type: multipart/alternative; boundary="0000000000003f424606130b35d4"
Archived-At: <https://www.postgresql.org/message-id/CAPF61jBm%2B_QCwDg32_K%2BBgNKf09isU9g3o8CLW2kUDLZ5%3D28Hw%40mail.gmail.com>
Precedence: bulk

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

On Wed, Mar 6, 2024 at 9:04=E2=80=AFPM Tomas Vondra <tomas.vondra@enterpris=
edb.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 i=
f
we enclose that in double quotes?
=E2=80=98postgres[102531]=3D#=E2=80=99SELECT * 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.
=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)

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')

=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)
)

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?

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

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Wed, Mar 6, 2024 at 9:04=E2=80=AFP=
M Tomas Vondra &lt;<a href=3D"mailto:tomas.vondra@enterprisedb.com">tomas.v=
ondra@enterprisedb.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><br><br>
I&#39;m pretty sure this is the correct &amp; expected behavior. The second=
<br>
query treats the value as string (because that&#39;s what should happen for=
<br>
values in double quotes).<br>
<br></blockquote><div>=C2=A0ok, Then why does the below=C2=A0query provide =
the correct conversion, even if we enclose=C2=A0that in double quotes?</div=
><div>=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;12345678=
90&quot;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&quot;FULL_NAME&quot; : &quo=
t;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 in=
t PATH &#39;lax $.id&#39;<br>=C2=A0 =C2=A0 =C2=A0 )<br>=C2=A0 =C2=A0)<br>;<=
br></div><div>=C2=A0 =C2=A0name =C2=A0 | =C2=A0 =C2=A0 id =C2=A0 =C2=A0 <br=
>----------+------------<br>=C2=A0JOHN DOE | 1234567890</div><div>(1 row)<b=
r></div><div><br></div><div>and for bigger input(string) it will leave as e=
mpty as below.</div><div>=E2=80=98postgres[102531]=3D#=E2=80=99SELECT * FRO=
M JSON_TABLE(jsonb &#39;{<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&quot;id&quo=
t; : &quot;12345678901&quot;,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&quot;FU=
LL_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)<br></div><div><br></div><div>seems i=
t is not something to do with data enclosed in double quotes but somehow re=
lated with internal casting it to integer and I think in case of bigger inp=
ut it is not able to cast it to integer(as defined under COLUMNS as id int =
PATH &#39;lax $.id&#39;)=C2=A0</div><div><br></div><div>=E2=80=98postgres[1=
02531]=3D#=E2=80=99SELECT * FROM JSON_TABLE(jsonb &#39;{</div>=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;}=
9;,<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=A0na=
me 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>=C2=A0 =C2=A0na=
me =C2=A0 | id <br>----------+----<br>=C2=A0JOHN DOE | =C2=A0 <br>(1 row)<b=
r><div>)</div><div><br></div><div>if it is not able to represent it to inte=
ger because of bigger input, it should error out with a similar error messa=
ge instead of leaving it empty.</div><div><br></div><div>Thoughts?</div></d=
iv><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div=
 dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Regards,<br>
<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>

--0000000000003f424606130b35d4--