Re: Proposal for supporting outer joins in 7.1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Proposal for supporting outer joins in 7.1
Date: 2000-08-26 16:05:28
Message-ID: 3109.967305928@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> I've already got the merge-join code walking left, right, and full joins
> (look for #ifdef code in the appropriate routines).

OK, will look.

> Just didn't implment
> the "null column filling", and didn't figure out how to push the
> left/right/full flags into the executor.

I can deal with the null tuple insertion. As for the flags, we just
need to add those to the Plan nodes for joins ... but first the info
has to get to the planner, thus we need to fix the parser output.

> I haven't looked at the other
> join techniques to see how easy those would be.

Offhand it seems that left join is trivial in all three join types.
Right join can be handled by swapping the two tables (make the inner
outer), but full join only seems possible with a merge join. I am also
concerned about whether even a merge join can do right/full joins
correctly, if the merge qual (ordering expression) does not include
*all* the relevant WHERE conditions.

> The current code does not do the scoping rules quite right, but it isn't
> very far wrong either. I think that the scoping and aliasing can be
> contained within the parser code, swallowing the intermediate scopes by
> resolving back to the original names. However, if we continue to do it
> this way (losing intermediate alias names) then we will be making it
> harder for the planner/optimizer to jiggle up the plans, since the query
> tree will have already assumed some references.

How so? The planner only sees Var nodes, which are unambiguous by
definition: rangetable index and attribute number don't have any
dependency on aliases. I think the only real problem here is whether
analyze.c resolves column names correctly, ie searching the right set
of aliases, in each part of the query text. (If I understand the SQL
spec correctly, the set of aliases visible in a JOIN's ON/USING clauses
is different from what's visible elsewhere in the query --- do we do
that right, or not?)

> but the parser may already recast it as
> select a.i, a..., b.x, b..., c.j, c... from a, b, c
> where (a.i = b.i) and (a.i = c.j);

That is what the parser currently does, but I think it's actually easier
all round if we leave the ON/USING conditions attached to the relevant
JoinExpr node, instead of pushing them over to the main WHERE clause.

> (Also, you'll remember that it currently barfs on three-way joins; I
> haven't had a chance to look at that yet).

IIRC the barf is directly related to the lack of suitable parsetree
representation for this stuff, so I suspect that we need not worry too
much about fixing that problem. It should go away by itself once we
nail down a better representation.

> Getting outer joins for 7.1 would be great. Count me in to help...

OK, good. I think I have enough of a handle on the issues downstream
from the parser. Are you interested in dealing with the column lookup/
aliasing questions?

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Chris 2000-08-26 16:17:56 Re: AW: How Do You Pronounce "PostgreSQL"?
Previous Message Tom Lane 2000-08-26 15:45:48 Re: Performance on inserts