Re: Complex outer joins?

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:

> On Wed, 26 Mar 2003, Tom Lane wrote:
>
> > Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > > On 26 Mar 2003, Greg Stark wrote:
> > >> Can you show an example where the join order would affect the result set? I
> > >> can't think of any.
> >
> > > I can think of a few somewhat degenerate cases.
> >
> > I don't think you need a degenerate case. Consider
> >
> > from (G left join L on (G.SELID = L.SELID))
> > right join C on (L.SELID = C.SELID)
> >
> > versus
> >
> > from G left join
> > (L right join C on (L.SELID = C.SELID))
> > on (G.SELID = L.SELID)
>
> I'd noticed that too, but I was trying to do it without changing the
> type of join since I wasn't sure whether he'd meant one using only left
> joins or outer joins in general.

Yeah, that's not a particularly enlightening case because if you convert the
right joins to left joins you see that these aren't actually similar queries
at all.

the first is
"C left join (G left join L)"
and the second is
"G left join (C left join L)"

They only look similar superficially when written using right joins but
they're actually totally different structures.

The other example using IS NULL on a column produced by the outer join is more
fundamental. I'll have to ponder that one. I don't remember how Oracle behaved
with cases like that because I rarely used that idiom. I think I rarely used
it because I found it too confusing with Oracle's (*) syntax which I suppose
begs the question.

> > The difference between this and the query we were discussing is just use
> > of "right" rather than "left" in the second JOIN. AIUI, in the Oracle
> > syntax this difference would be expressed by moving the (*) from one
> > side to the other of the L.SELID = C.SELID clause. Unless they have
> > strange restrictions on the combinations of clauses you can mark with
> > (*), I don't see how they can assume that join order is insignificant.

There were indeed some strange restrictions on the combinations of clauses you
can mark with (*). I remember running into some and being most annoyed. I
don't remember precisely how it worked but my vague recollection is that it
was something sort of similar to what you're describing.

> I'd guess that they might check that the conditions are in a specific set
> of limited conditions in order to allow the reordering and disallow the
> reordering otherwise (well, I'd hope that they do this if they ever
> allow reordering).

I don't think they do "reordering" I think they build an abstract graph of
join dependencies with constraints between tables and then optimize the
ordering unconstrained by the original query. It's the only way I could
imagine reaching the results I saw where semantically equivalent queries
written in completely different ways reliably produced the same plans.

--
greg