Skip site navigation (1) Skip section navigation (2)

Re: Table name scope (was Re: Outer joins aren't working with views)

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Table name scope (was Re: Outer joins aren't working with views)
Date: 2000-12-18 02:18:09
Message-ID: 3A3D73E1.8C579EDD@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
> > So there are two issues here which I hope to clarify: scoping
> > on joins, and NATURAL and USING join column sets.
> Two issues?  I thought we were only arguing about the latter one.

Well, I prefer to consider it "discussing" ;)

And there are two issues. I'll bet lunch and dinner that SQL99 did *not*
make radical changes in the scoping rules for join syntax vis a vis
SQL92. Certainly something compatible with SQL92 should have a shot at
being also compatible under SQL99, and scoping rules would fall into
that category.

On the second topic, NATURAL and USING join column sets, I believe that
it *must* be true that the set of columns available in a natural join
result (e.g. the result of

  A NATURAL JOIN B

) is the complete set of columns available to a SELECT target list, to a
WHERE qualification, etc. D&D's description of the effects of this
"interpretation" are consistant and clear (where the spec is not). I'm
not sure how we can allow our interpretation to be at odds with the
SQL92 spec or with a reading of the SQL99 draft I have available. In
particular, the rules for forming join results seem to cover the cases
we are discussing, and I read them as being consistant with D&D's SQL92
discussion. btw, their appendix on the upcoming "SQL3" does not bring up
join results or join scoping as among the changes in the upcoming
standard, though of course that is not a definitive point.

Date and Darwen have imho a very clear description of the scoping
allowed in join syntax. That scoping discussion says very clearly that a
"range variable" (SQL9x "correlation name") becomes the only allowed
qualification to a column name in SELECT target lists, WHERE clauses,
etc etc. They have very specific examples to clarify the point. And they
deem that necessary because the spec is a PITA to wade through. I'd
rather leave it to them to do the wading ;)

Let's look for counterexamples in our other texts if you are really
uncomfortable with the SQL92 (and SQL99?) result in D&D. I have another
book or two, and will look through them tonight. Does anyone else want
to jump in, esp. if you have experience with the SQL9x conventions or
have access to a db which already implements it?

                     - Thomas

In response to

Responses

pgsql-hackers by date

Next:From: Michael RichardsDate: 2000-12-18 02:22:32
Subject: Re: Tuple data
Previous:From: Peter BiermanDate: 2000-12-18 02:11:09
Subject: Re: [HACKERS] 7.1 features list

pgsql-bugs by date

Next:From: Karel ZakDate: 2000-12-18 07:54:25
Subject: Re: Bug report
Previous:From: Tom LaneDate: 2000-12-17 18:34:42
Subject: Re: Table name scope (was Re: Outer joins aren't working with views)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group