Re: BUG #2869: COALESCE in criteria and multiple joins behavior - Mailing list pgsql-bugs

"Pablo Giancarelli" <pgiancarelli@gmail.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