MIME-Version: 1.0
References: <202401181711.qxjxpnl3ohnw@alvherre.pgsql> <202401221619.ragf2vnbyopl@alvherre.pgsql>
 <CA+HiwqGN6DUKAVabqEhQ8e=0593emvJ6P4rQ_8x8N84k=mWq1Q@mail.gmail.com>
 <CA+HiwqFctBg7--Om7fFdYUwwfJ5xb1M3L4E3d=-HZ=nXK_ETtA@mail.gmail.com>
 <CA+HiwqGpaB7Oyea0qJiAQ_V6-qtfLdCTExcFNDChL_M-80-4uw@mail.gmail.com>
 <CA+HiwqEw96AwERGSXWLX52oO8p-=BRJUHT6Jc9R1GCty3unZyA@mail.gmail.com> <CACJufxG5vymBu=4J0yzMQCtcj51j6FEo=CGQF9Pnp3tzQPp+nw@mail.gmail.com>
In-Reply-To: <CACJufxG5vymBu=4J0yzMQCtcj51j6FEo=CGQF9Pnp3tzQPp+nw@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Tue, 6 Feb 2024 12:55:51 +0800
Message-ID: <CACJufxFm8dKXkeYK7eOmrxwCtbzRVncQ_kOUYKP52fA34wuj2w@mail.gmail.com>
Subject: Re: remaining sql/json patches
To: Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Andres Freund <andres@anarazel.de>, 
	Erik Rijkers <er@xs4all.nl>, Andrew Dunstan <andrew@dunslane.net>, 
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACJufxFm8dKXkeYK7eOmrxwCtbzRVncQ_kOUYKP52fA34wuj2w%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 25, 2024 at 10:39=E2=80=AFPM jian he <jian.universality@gmail.c=
om> wrote:
>
> On Thu, Jan 25, 2024 at 7:54=E2=80=AFPM Amit Langote <amitlangote09@gmail=
.com> wrote:
> >
> > >
> > > The problem with returning comp_domain_with_typmod from json_value()
> > > seems to be that it's using a text-to-record CoerceViaIO expression
> > > picked from JsonExpr.item_coercions, which behaves differently than
> > > the expression tree that the following uses:
> > >
> > > select ('abcd', 42)::comp_domain_with_typmod;
> > >    row
> > > ----------
> > >  (abc,42)
> > > (1 row)
> >
> > Oh, it hadn't occurred to me to check what trying to coerce a "string"
> > containing the record literal would do:
> >
> > select '(''abcd'', 42)'::comp_domain_with_typmod;
> > ERROR:  value too long for type character(3)
> > LINE 1: select '(''abcd'', 42)'::comp_domain_with_typmod;
> >
> > which is the same thing as what the JSON_QUERY() and JSON_VALUE() are
> > running into.  So, it might be fair to think that the error is not a
> > limitation of the SQL/JSON patch but an underlying behavior that it
> > has to accept as is.
> >
>
> Hi, I reconciled with these cases.
> What bugs me now is the first query of the following 4 cases (for compari=
son).
> SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3) omit quotes);
> SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3) keep quotes);
> SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text omit quotes);
> SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text keep quotes);
>

based on v39.
in ExecEvalJsonCoercion
coercion->targettypmod related function calls:
json_populate_type calls populate_record_field, then populate_scalar,
later will eventually call InputFunctionCallSafe.

so I make the following change:
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4533,7 +4533,7 @@ ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *=
op,
         * deed ourselves by calling the input function, that is, after rem=
oving
         * the quotes.
         */
-       if (jb && JB_ROOT_IS_SCALAR(jb) && coercion->omit_quotes)
+       if ((jb && JB_ROOT_IS_SCALAR(jb) && coercion->omit_quotes) ||
coercion->targettypmod !=3D -1)

now the following two return the same result:  `[1,`
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3) omit quotes);
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3) keep quotes);