Re: View prevents index

On Wed, Jul 11, 2001 at 11:00:18PM -0700, Eric G. Miller wrote:
> 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 ??

What's wrong with that query. Looks fine to me.

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

Seems to me that you need to go through the entire union clause and realias
all the output fields to their final names, and check that they don't clash.

I think that as long as you don't have any aggregates, you should be
fine. I certainly can't think of a counter example.

Or put it another way, is:

cat a.txt b.txt | grep pattern

the same as:

cat a.txt | grep pattern ; cat b.txt | grep pattern
--
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.