Date: Wed, 26 Mar 2003 15:02:04 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <gsstark@mit.edu>,
	"Correia,     Carla" <Carla.Correia@logicacmg.com>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: Complex outer joins? 
In-Reply-To: <7039.1048718031@sss.pgh.pa.us>
Message-ID: <20030326145855.J66362-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 26 Mar 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.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.

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

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