Re: Inconsistency between try_mergejoin_path and create_mergejoin_plan - Mailing list pgsql-hackers

On Mon, Jun 17, 2024 at 10:51 PM Alexander Pyhalov
<a.pyhalov@postgrespro.ru> wrote:
> There's the following inconsistency between try_mergejoin_path() and
> create_mergejoin_plan().
> When clause operator has no commutator, we can end up with mergejoin
> path.
> Later create_mergejoin_plan() will call get_switched_clauses(). This
> function can error out with
>
> ERROR:  could not find commutator for operator XXX

Interesting.  This error can be reproduced with table 'ec1' from
sql/equivclass.sql.

set enable_indexscan to off;

explain select * from ec1 t1 join ec1 t2 on t2.ff = t1.f1;
ERROR:  could not find commutator for operator 30450

The column ec1.f1 has a type of 'int8alias1', a new data type created in
this test file.  Additionally, there is also a newly created operator
'int8 = int8alias1' which is mergejoinable but lacks a valid commutator.
Therefore, there is no problem generating the mergejoin path, but when
we create the mergejoin plan, get_switched_clauses would notice the
absence of a valid commutator needed to commute the clause.

It seems to me that the new operator is somewhat artificial, since it is
designed to support a mergejoin but lacks a valid commutator.  So before
we proceed to discuss the fix, I'd like to know whether this is a valid
issue that needs fixing.

Any thoughts?

Thanks
Richard