I have a number of views that I'm bringing over from DB2 which
have UNIONs in them. Some of the UNIONs have joins.
The views are not working as expected (I'm running 7.1RC2).
It appears that the where clause being applied to the view
by the user is not being distributed properly to the selects.
It's probably easiest to give an example:
There are two tables, itinerary and conjunctive. A conjunctive
is for an itinerary which has > 4 stops (this is imposed by the
source data, so we have to preserve the relationships).
create table itinerary as (
ticket_nbr,
origin,
dest,
flight_nbr );
create conjunctive as (
ticket_nbr, -- the original ticket number
conj_ticket_nbr -- the ticket which extends it
);
I've trimmed them a bit for clarity.
What the view does is this:
create view ticket_conj as (
select ticket_nbr, origin, dest, flight_nbr
from itinerary
union
select c.ticket_nbr i.origin, i.dest, i.flight_nbr
from itinerary i inner join conjunctive c on i.ticket_nbr = c.conj_ticket_nbr
);
Then we issue queries of this form:
select * from ticket_conj where ticket_nbr = '9483';
Sadly, PostgreSQL goes off and munches for a *long* time,
whereas DB2 and SQL Server return the desired results
promptly.
If I write a query like the view, but giving each select
a where clause, it works (and faster than the other DBs).
It really looks to me (and I am *not* a backend guru) that
the where clause is not being bound to the desired value
(c.ticket_nbr) in the second select.
Does anyone have any ideas on this?
Thanks,
Gordon.
--
It doesn't get any easier, you just go faster. -- Greg LeMond