Re: RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3

Thanks for the speedy fix.

I agree that this is not a typical query, in it Table2.t3id and Table3.t3id
would always join
(a foreing key constraint ensure that) but columns from Table3 should
sometimes be excluded which is taken care by "table1.extension in
(table2.replacement)".

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Sent: Tuesday, October 25, 2005 4:34 PM
Subject: Re: [BUGS] RIGHT JOIN is only supported with merge-joinable join
conditions, PostgreSQL 8.1 beta3

> "Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca> writes:
>> select
>> count(table3.*)
>> from
>> table1
>> inner join table2
>> on table1.t1id = table2.t1id
>> and table1.extension in (table2.original, table2.replacement)
>> left outer join table3
>> on table2.t3id = table3.t3id
>> and table1.extension in (table2.replacement);
>
> I've applied the attached patch (for 8.1, variants as needed for back
> branches) to fix this failure.
>
> BTW, I think the reason nobody saw this before is that using a condition
> on table1 vs table2 in the outer-join condition for table3 is a bit, er,
> weird. Are you sure that the original query will do what you really
> wanted?
>
> But anyway, many thanks for the test case!
>
> regards, tom lane
>
> Index: joinpath.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v
> retrieving revision 1.96
> diff -c -r1.96 joinpath.c
> *** joinpath.c 15 Oct 2005 02:49:20 -0000 1.96
> --- joinpath.c 25 Oct 2005 19:52:54 -0000
> ***************
> *** 795,800 ****
> --- 795,801 ----
> {
> List *result_list = NIL;
> bool isouterjoin = IS_OUTER_JOIN(jointype);
> + bool have_nonmergeable_joinclause = false;
> ListCell *l;
>
> foreach(l, restrictlist)
> ***************
> *** 803,844 ****
>
> /*
> * If processing an outer join, only use its own join clauses in the
> ! * merge. For inner joins we need not be so picky.
> ! *
> ! * Furthermore, if it is a right/full join then *all* the explicit join
> ! * clauses must be mergejoinable, else the executor will fail. If we
> ! * are asked for a right join then just return NIL to indicate no
> ! * mergejoin is possible (we can handle it as a left join instead). If
> ! * we are asked for a full join then emit an error, because there is
> ! * no fallback.
> */
> ! if (isouterjoin)
> ! {
> ! if (restrictinfo->is_pushed_down)
> ! continue;
> ! switch (jointype)
> ! {
> ! case JOIN_RIGHT:
> ! if (!restrictinfo->can_join ||
> ! restrictinfo->mergejoinoperator == InvalidOid)
> ! return NIL; /* not mergejoinable */
> ! break;
> ! case JOIN_FULL:
> ! if (!restrictinfo->can_join ||
> ! restrictinfo->mergejoinoperator == InvalidOid)
> ! ereport(ERROR,
> ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> ! errmsg("FULL JOIN is only supported with merge-joinable join
> conditions")));
> ! break;
> ! default:
> ! /* otherwise, it's OK to have nonmergeable join quals */
> ! break;
> ! }
> ! }
>
> if (!restrictinfo->can_join ||
> restrictinfo->mergejoinoperator == InvalidOid)
> continue; /* not mergejoinable */
>
> /*
> * Check if clause is usable with these input rels. All the vars
> --- 804,822 ----
>
> /*
> * If processing an outer join, only use its own join clauses in the
> ! * merge. For inner joins we can use pushed-down clauses too.
> ! * (Note: we don't set have_nonmergeable_joinclause here because
> ! * pushed-down clauses will become otherquals not joinquals.)
> */
> ! if (isouterjoin && restrictinfo->is_pushed_down)
> ! continue;
>
> if (!restrictinfo->can_join ||
> restrictinfo->mergejoinoperator == InvalidOid)
> + {
> + have_nonmergeable_joinclause = true;
> continue; /* not mergejoinable */
> + }
>
> /*
> * Check if clause is usable with these input rels. All the vars
> ***************
> *** 856,865 ****
> --- 834,870 ----
> /* lefthand side is inner */
> }
> else
> + {
> + have_nonmergeable_joinclause = true;
> continue; /* no good for these input relations */
> + }
>
> result_list = lcons(restrictinfo, result_list);
> }
>
> + /*
> + * If it is a right/full join then *all* the explicit join clauses must
> be
> + * mergejoinable, else the executor will fail. If we are asked for a
> right
> + * join then just return NIL to indicate no mergejoin is possible (we can
> + * handle it as a left join instead). If we are asked for a full join
> then
> + * emit an error, because there is no fallback.
> + */
> + if (have_nonmergeable_joinclause)
> + {
> + switch (jointype)
> + {
> + case JOIN_RIGHT:
> + return NIL; /* not mergejoinable */
> + case JOIN_FULL:
> + ereport(ERROR,
> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("FULL JOIN is only supported with merge-joinable join
> conditions")));
> + break;
> + default:
> + /* otherwise, it's OK to have nonmergeable join quals */
> + break;
> + }
> + }
> +
> return result_list;
> }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match