Re: Merge Joins and Views

"Chris Mayfield" <cmayfiel(at)cs(dot)purdue(dot)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!