Re: Merge Joins and Views

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

Chris Mayfield <cmayfiel(at)cs(dot)purdue(dot)edu> writes:
>>> So the long and the short of it is that the COALESCE acts as an
>>> optimization fence in the presence of outer joins.  We've seen this
>>> before and there are some rough ideas about fixing it.

> You may already have this rough idea somewhere, but it seems to me that 
> the view could be flattened into the upper query as long as the join 
> predicates don't depend on coalesced columns.  In the examples I sent, 
> even if the COALESCE is evaluated at the very end of the query, the 
> merge join (on the id columns) would still be correct.

But the output would not be: the join column would fail to go to null
when it was supposed to.  See the example that made us put in that
restriction in the first place:
http://archives.postgresql.org/pgsql-bugs/2001-04/msg00223.php

			regards, tom lane