> Thomas, what do you think is needed for outer joins?
Bruce and I have talked about it some already:
For outer joins, tables must be combined in a particular order. For
example, a left outer join requires that any entries in the left-side
table which do not have a corresponding entry in the right-side table
be expanded with nulls during the join. The information on the outer
join can't be carried by the rte since the same table can appear twice
in an outer join expression:
select * from t1 left join t2 using (i) left join t1 on (i = t1.j);
For a query like
select * from t1 left join t2 using (i) where t2.j = 3;
istm that the outer join must be done before the t2 qualification is
applied, and that another ordering may produce the wrong result.
>From what I understand Bruce to say, the planner/optimizer is allowed
to try all kinds of permutations of plans, choosing the one with the
lowest cost. But if the info for the join is carried in a
qualification node, then the planner/optimizer must know that it can't
reorder the query as freely as it does now.
I was thinking of having a new qualification node to carry this info,
and it could be transformed into a mergejoin node which has a couple
of new fields indicating left and/or right outer join behavior.
A hashjoin method may be possible for queries which are structured as
a left outer join; other outer joins will need to use the mergejoin
method. Also, some poorly-qualified outer joins reduce to inner joins,
and perhaps the optimizer can be smart enough to realize this.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California