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" <[EMAIL PROTECTED]>
To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Cc: <pgsql-bugs@postgresql.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" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
