Re: Merge Joins and Views

"Chris Mayfield" <cmayfiel@cs.purdue.edu> writes:

> 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.

I don't remember the example but that's not true in general, consider
something like

select *
  from a
left outer join (
  select *, coalesce(b.x,'foo') from b
  ) as b
 using (a.y=b.y)

The coalesce() doesn't depend on any join predicates, but if it's evaluated
late it will be 'foo' for all the non-matching records instead of NULL.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!