Re: Inconsistency between try_mergejoin_path and create_mergejoin_plan

Hello Richard and Tom,

04.09.2024 06:50, Richard Guo wrote:
> I pushed this patch with the test case remaining, as it adds only a
> minimal number of test cycles. I explained in the commit message why
> the test case is included in equivclass.sql rather than in join.sql.

While playing with the equivclass test, I've discovered that the next step
to define a complete set of operators in the test:
@@ -65,6 +65,7 @@
     procedure = int8alias1eq,
     leftarg = int8, rightarg = int8alias1,
     restrict = eqsel, join = eqjoinsel,
+commutator = =,
     merges
 );

produces an internal error:
ERROR:  XX000: operator 32312 is not a member of opfamily 1976
LOCATION:  get_op_opfamily_properties, lsyscache.c:149

pg_regress/equivclass BACKTRACE:
get_op_opfamily_properties at lsyscache.c:149:3
MJExamineQuals at nodeMergejoin.c:228:19
ExecInitMergeJoin at nodeMergejoin.c:1608:25
ExecInitNode at execProcnode.c:303:27
InitPlan at execMain.c:964:14

Maybe the error itself is not that unexpected, but I'm confused by a
comment above the function:
 * Caller should already have verified that opno is a member of opfamily,
 * therefore we raise an error if the tuple is not found.
 */
void
get_op_opfamily_properties(Oid opno, Oid opfamily, bool ordering_op,
                           int *strategy,
                           Oid *lefttype,
                           Oid *righttype)
{
    HeapTuple   tp;
    Form_pg_amop amop_tup;

    tp = SearchSysCache3(AMOPOPID,
                         ObjectIdGetDatum(opno),
                         CharGetDatum(ordering_op ? AMOP_ORDER : AMOP_SEARCH),
                         ObjectIdGetDatum(opfamily));
    if (!HeapTupleIsValid(tp))
        elog(ERROR, "operator %u is not a member of opfamily %u",
             opno, opfamily);

This behavior reproduced on commit a33cf1041, dated 2007-01-23, which
added "ALTER OPERATOR FAMILY", but I think it also can be reproduced on
previous commits with manual catalog editing. (The comment was added by
a78fcfb51 from 2006-12-23, which introduced operator families.)

Best regards,
Alexander