"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
