Re: BUG #2869: COALESCE in criteria and multiple joins behavior

"Pablo Giancarelli" <pgiancarelli(at)gmail(dot)com> writes:
> select rh.*,rd.iddet from rel_head rh
> left join rel_det rd on rh.id = rd.id
> left join rel_null rn on rd.idnull = rn.idnull
> where COALESCE(rn.nullfield,'S') = 'S';
> -- Result with : set join_collapse_limit to 8;
> id;description;iddet
> 10;"one"; (null)
> (1 row)

I can't reproduce this in CVS HEAD; I think it is already fixed by this
change:

2006-12-07 14:33 tgl

* src/backend/optimizer/plan/: initsplan.c (REL8_2_STABLE),
initsplan.c: Repair incorrect placement of WHERE clauses when there
are multiple, rearrangeable outer joins and the WHERE clause is
non-strict and mentions only nullable-side relations. New bug in
8.2, caused by new logic to allow rearranging outer joins. Per bug
#2807 from Ross Cohen; thanks to Jeff Davis for producing a usable
test case.

regards, tom lane