From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: 409800246@qq.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18173: ERROR: could not identify a comparison function for type unknown
Date: Tue, 31 Oct 2023 10:51:52 -0400
Message-ID: <2583135.1698763912@sss.pgh.pa.us> (raw)
In-Reply-To: <52e52bddd9d5396ca07f103f799518f82e2c8aad.camel@cybertec.at>
References: <18173-c971c32eb961e810@postgresql.org>
<52e52bddd9d5396ca07f103f799518f82e2c8aad.camel@cybertec.at>
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Mon, 2023-10-30 at 13:24 +0000, PG Bug reporting form wrote:
>> postgres=# select distinct (id,name,'D3Q84xpymM',123,'123') from test_v;
>> ERROR: could not identify a comparison function for type unknown
> This is not a bug. You probably want to remove the parentheses.
It is a bit inconsistent that we allow
select distinct id,name,'D3Q84xpymM',123,'123' from test_v;
and
select (id,name,'D3Q84xpymM',123,'123') from test_v;
but not the above. Poking around, I discovered a related failure:
select ((id,name,'D3Q84xpymM',123,'123')).f3 from test_v;
ERROR: failed to find conversion function from unknown to text
That happens because at the end of parsing we apply
resolveTargetListUnknowns, which sees that there's an UNKNOWN
output column and tries to coerce it to text, but can't since
it's not a plain literal.
So maybe there is a case for forcing unknowns to text in the
input list of a RowExpr. You could argue that a RowExpr is
morally the same thing as a SELECT list, so this would have
some consistency to it. Nonetheless, it's a rather big
semantics change and I wonder what it might break. Notably,
in the context
row(...,'foo',...)::somerowtype
currently the cast to somerowtype will see an unknown literal
and do the right thing, whereas maybe with text input it might
do something surprising (and in any case we'd lose some
efficiency from the extra type coercion).
On the whole, given the lack of complaints for the last couple
of decades, I'm content to say that it's operating as intended.
At best we'd be switching from one nonstandard behavior to
another. ("Oracle does it" doesn't make it standard.)
In any case, I don't like the proposed patch one bit.
Kluging this up in the specific case of DISTINCT is just
adding a wart.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, 409800246@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18173: ERROR: could not identify a comparison function for type unknown
In-Reply-To: <2583135.1698763912@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox