On Mon, Jun 17, 2024 at 10:51 PM Alexander Pyhalov <a.pyha...@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
