BUG #18173: ERROR: could not identify a comparison function for type unknown

On Mon, 2023-10-30 at 13:24 +0000, PG Bug reporting form wrote:

PostgreSQL version: 16.0

postgres=# CREATE TABLE test_v(id int,name varchar(30));
CREATE TABLE
postgres=# insert into test_v values(9,'abc'),(9,'def'),(9,'gh'),
(9,'gh');
INSERT 0 4
postgres=# explain (costs off) select distinct
(id,name,'D3Q84xpymM',123,'123') from test_v;
QUERY PLAN
-------------------------------------------------------------
Unique
-> Sort
Sort Key: (ROW(id, name, 'D3Q84xpymM', 123, '123'))
-> Seq Scan on test_v
(4 rows)

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.

Yours,
Laurenz Albe

Thank you very much for your reply.

when remove the parentheses, it is OK.
because plan is diffrent:

postgres=# explain select distinct id,name,'D3Q84xpymM',123,'123' from test_v;
                            QUERY PLAN                            
------------------------------------------------------------------
 HashAggregate  (cost=28.75..30.75 rows=200 width=105)
   Group Key: id, name
   ->  Seq Scan on test_v  (cost=0.00..22.50 rows=1250 width=105)
(3 rows)

postgres=# explain select distinct (id,name,'D3Q84xpymM',123,'123') from test_v;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Unique  (cost=86.80..93.05 rows=200 width=32)
   ->  Sort  (cost=86.80..89.92 rows=1250 width=32)
         Sort Key: (ROW(id, name, 'D3Q84xpymM', 123, '123'))
         ->  Seq Scan on test_v  (cost=0.00..22.50 rows=1250 width=32)
(4 rows)

Sometimes users just want to use parentheses, because  they were used on Oracle like this  before.

Thanks!

------------------ 原始邮件 ------------------
发件人: "Laurenz Albe" <laurenz.albe@cybertec.at&gt;;
发送时间:&nbsp;2023年10月31日(星期二) 晚上7:10
收件人:&nbsp;"下雨天"<409800246@qq.com&gt;;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org&gt;;

主题:&nbsp;Re: BUG #18173: ERROR: could not identify a comparison function for type unknown

On Mon, 2023-10-30 at 13:24 +0000, PG Bug reporting form wrote:
&gt; PostgreSQL version: 16.0
&gt;
&gt; postgres=# CREATE TABLE test_v(id int,name varchar(30));
&gt; CREATE TABLE
&gt; postgres=# insert into test_v values(9,'abc'),(9,'def'),(9,'gh'),
&gt; (9,'gh');
&gt; INSERT 0 4
&gt; postgres=# explain (costs off) select distinct
&gt; (id,name,'D3Q84xpymM',123,'123') from test_v;
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&gt; -------------------------------------------------------------
&gt;&nbsp; Unique
&gt;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Sort
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: (ROW(id, name, 'D3Q84xpymM', 123, '123'))
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on test_v
&gt; (4 rows)
&gt;
&gt; postgres=# select distinct (id,name,'D3Q84xpymM',123,'123') from test_v;
&gt; ERROR:&nbsp; could not identify a comparison function for type unknown

This is not a bug.&nbsp; You probably want to remove the parentheses.

Yours,
Laurenz Albe

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

Perhaps you're right.&nbsp;
Different databases have their unique characteristics, and there is indeed no need to be compatible with Oracle.
At least in the specific case of DISTINCT, it indeed increases the workload for users migrating from Oracle to PostgreSQL, which seems&nbsp;inevitable at present.

Thanks!

------------------&nbsp;Original&nbsp;------------------
From: "Tom Lane" <tgl@sss.pgh.pa.us&gt;;
Date:&nbsp;Tue, Oct 31, 2023 10:51 PM
To:&nbsp;"Laurenz Albe"<laurenz.albe@cybertec.at&gt;;
Cc:&nbsp;"下雨天"<409800246@qq.com&gt;;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org&gt;;
Subject:&nbsp;Re: BUG #18173: ERROR: could not identify a comparison function for type unknown

Laurenz Albe <laurenz.albe@cybertec.at&gt; writes:
&gt; On Mon, 2023-10-30 at 13:24 +0000, PG Bug reporting form wrote:
&gt;&gt; postgres=# select distinct (id,name,'D3Q84xpymM',123,'123') from test_v;
&gt;&gt; ERROR:&nbsp; could not identify a comparison function for type unknown

&gt; This is not a bug.&nbsp; 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.&nbsp; Poking around, I discovered a related failure:

select ((id,name,'D3Q84xpymM',123,'123')).f3 from test_v;
ERROR:&nbsp; 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.&nbsp; You could argue that a RowExpr is
morally the same thing as a SELECT list, so this would have
some consistency to it.&nbsp; Nonetheless, it's a rather big
semantics change and I wonder what it might break.&nbsp; 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.&nbsp; ("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