Re: View prevents index

On Thu, Jul 12, 2001 at 01:39:07PM +0900, Tatsuo Ishii wrote:
> > Christopher Masto <chris+pg-general(at)netmonger(dot)net> writes:
> > > I guess maybe I'm expecting too much magic optimization.
> >
> > You're expecting the system to transform
> >
> > (SELECT foo UNION SELECT bar) WHERE condition
> >
> > into
> >
> > (SELECT foo WHERE condition) UNION (SELECT bar WHERE condition)
> >
> > It's not immediately obvious to me that these are equivalent, or
> > perhaps I should say it's not clear under what conditions is the
> > transformation legitimate.
>
> Could you tell me in what cases two of above are not equivalent? Or
> any specification in the standard that refers to this kind of
> transformation for views?

The first clearly specifies the WHERE filtering should occur after the
UNION, whereas the second is just the opposite. I think transforming
the first to the second would be problematic due to the column name
matching for the where clause (i.e. the user thinks they're matching on
the column names that the result set from the union would generate but
if it is transformed behind their back, it could fail).

Consider:

foo ::= { id::int, blurb::varchar }
bar ::= { bar_id::int, data::varchar }

Are these equivalent?

1. (SELECT foo.id As "Id", foo.blurb As "Stuff"
UNION
SELECT bar.bar_id, bar.data)
WHERE character_length("Stuff") > 80;

2. (SELECT foo.id As "Id", foo.blurb As "Stuff"
WHERE character_length ("Stuff") > 80)
UNION
(SELECT bar.bar_id As "Id", bar.data As "Stuff"
WHERE character_length("Stuff") > 80);

I guess the second would fail on the alias, but the first should
succeed ??

I can't say anything about the specs, but the parser/planner/optimizer
would have to be able to fall back to applying the WHERE after the UNION
if it couldn't match up column names by splitting the UNION(s) out. I
suppose you could try some magic to alias names by position in the
select phrase, but that seems difficult.

--
Eric G. Miller <egm2(at)jps(dot)net>