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>
In-Reply-To: 
 <CA+HiwqEw96AwERGSXWLX52oO8p-=BRJUHT6Jc9R1GCty3unZyA@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Thu, 25 Jan 2024 22:39:30 +0800
Message-ID: 
 <CACJufxG5vymBu=4J0yzMQCtcj51j6FEo=CGQF9Pnp3tzQPp+nw@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: multipart/mixed; boundary="000000000000636c74060fc626c0"
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxG5vymBu%3D4J0yzMQCtcj51j6FEo%3DCGQF9Pnp3tzQPp%2Bnw%40mail.gmail.com>
Precedence: bulk

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

On Thu, Jan 25, 2024 at 7:54=E2=80=AFPM Amit Langote <amitlangote09@gmail.c=
om> 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 compariso=
n).
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);

I did some minor refactoring on the function coerceJsonFuncExprOutput.
it will make the following queries return null instead of error. NULL
is the return of json_value.

    SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int2);
    SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int4);
    SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int8);
    SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING bool);
    SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING numeric);
    SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING real);
    SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING float8);

--000000000000636c74060fc626c0
Content-Type: application/octet-stream;
	name="v1-0001-minor-refactor-coerceJsonFuncExprOutput.no-cfbot"
Content-Disposition: attachment;
	filename="v1-0001-minor-refactor-coerceJsonFuncExprOutput.no-cfbot"
Content-Transfer-Encoding: base64
Content-ID: <f_lrtbj35o0>
X-Attachment-Id: f_lrtbj35o0

RnJvbSBjZmYwZDk0NmRkNjViODFjMDIwZjJhMDZhNmIwMjljOGFlYjgxMDBlIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IFRodSwgMjUgSmFuIDIwMjQgMjI6MzU6NDUgKzA4MDAKU3ViamVjdDogW1BBVENIIHYx
IDEvMV0gbWlub3IgcmVmYWN0b3IgY29lcmNlSnNvbkZ1bmNFeHByT3V0cHV0CgpjYXN0IGZ1bmN0
aW9uIGRvIHRoZSBjb2VyY2lvbiBmb3IgdGhlIGZvbGxvd2luZyB0eXBlcyBjYW5ub3QgaGFuZGxl
IGVycm9yIHNvZnRseSwKc28gd2UgdXNlIGNvZXJjaW9uLgpzbyB0aGUgZm9sbG93aW5nIHF1ZXJp
ZXMgaW5zdGVhZCBvZiBnZW5lcmF0ZSBhbiBlcnJvciwgbnVsbCB3aWxsIGJlIHByb2R1Y2VkLgoK
U0VMRUNUIEpTT05fUVVFUlkoanNvbmIgJyIxMjMiJywgJyQnIFJFVFVSTklORyBpbnQyKTsKU0VM
RUNUIEpTT05fUVVFUlkoanNvbmIgJyIxMjMiJywgJyQnIFJFVFVSTklORyBpbnQ0KTsKU0VMRUNU
IEpTT05fUVVFUlkoanNvbmIgJyIxMjMiJywgJyQnIFJFVFVSTklORyBpbnQ4KTsKU0VMRUNUIEpT
T05fUVVFUlkoanNvbmIgJyIxMjMiJywgJyQnIFJFVFVSTklORyBib29sKTsKU0VMRUNUIEpTT05f
UVVFUlkoanNvbmIgJyIxMjMiJywgJyQnIFJFVFVSTklORyBudW1lcmljKTsKU0VMRUNUIEpTT05f
UVVFUlkoanNvbmIgJyIxMjMiJywgJyQnIFJFVFVSTklORyByZWFsKTsKU0VMRUNUIEpTT05fUVVF
UlkoanNvbmIgJyIxMjMiJywgJyQnIFJFVFVSTklORyBmbG9hdDgpOwotLS0KIHNyYy9iYWNrZW5k
L3BhcnNlci9wYXJzZV9leHByLmMgfCAyNyArKysrKysrKysrKysrKysrKysrKysrKysrKysKIDEg
ZmlsZSBjaGFuZ2VkLCAyNyBpbnNlcnRpb25zKCspCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQv
cGFyc2VyL3BhcnNlX2V4cHIuYyBiL3NyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9leHByLmMKaW5k
ZXggN2MzODcwYWMuLjI3ODU3NDRlIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9wYXJzZXIvcGFy
c2VfZXhwci5jCisrKyBiL3NyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9leHByLmMKQEAgLTQ1MDMs
NiArNDUwMywzMyBAQCBjb2VyY2VKc29uRnVuY0V4cHJPdXRwdXQoUGFyc2VTdGF0ZSAqcHN0YXRl
LCBKc29uRXhwciAqanNleHByKQogCiAJZGVmYXVsdF90eXBpZCA9IEpzb25GdW5jRXhwckRlZmF1
bHRSZXR1cm5UeXBlKGpzZXhwcik7CiAJZGVmYXVsdF90eXBtb2QgPSAtMTsKKworCS8qCisJKiBj
YXN0IGZ1bmN0aW9uIGRvIHRoZSBjb2VyY2lvbiBmb3IgdGhlIGZvbGxvd2luZyB0eXBlcyBjYW5u
b3QgaGFuZGxlIGVycm9yIHNvZnRseSwKKwkqIHNvIHdlIHVzZSBKc29uQ29lcmNpb24KKwkqLwor
CWlmKGpzZXhwci0+b3AgPT0gSlNPTl9RVUVSWV9PUCkKKwl7CisJCUpzb25Db2VyY2lvbiAqY29l
cmNpb24gPSBOVUxMOworCQlzd2l0Y2ggKHJldHVybmluZy0+dHlwaWQpCisJCXsKKwkJCWNhc2Ug
Qk9PTE9JRDoKKwkJCWNhc2UgTlVNRVJJQ09JRDoKKwkJCWNhc2UgSU5UMk9JRDoKKwkJCWNhc2Ug
SU5UNE9JRDoKKwkJCWNhc2UgSU5UOE9JRDoKKwkJCWNhc2UgRkxPQVQ0T0lEOgorCQkJY2FzZSBG
TE9BVDhPSUQ6CisJCQkJY29lcmNpb24gPSBtYWtlSnNvbkNvZXJjaW9uKHJldHVybmluZyk7CisJ
CQkJY29lcmNpb24tPm9taXRfcXVvdGVzID0ganNleHByLT5vbWl0X3F1b3RlczsKKwkJCQlicmVh
azsKKwkJCWRlZmF1bHQ6CisJCQkJYnJlYWs7CisJCX0KKwkJaWYoY29lcmNpb24pCisJCQlyZXR1
cm4gKE5vZGUgKikgY29lcmNpb247CisJfQorCiAJaWYgKHJldHVybmluZy0+dHlwaWQgIT0gZGVm
YXVsdF90eXBpZCB8fAogCQlyZXR1cm5pbmctPnR5cG1vZCAhPSBkZWZhdWx0X3R5cG1vZCkKIAl7
Ci0tIAoyLjM0LjEKCg==
--000000000000636c74060fc626c0--