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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
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-17 06:44:50
Message-ID: 20468.977035490@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> (p142, after a discussion of other cases)
> "One very counterintuitive consequence of this unorthodox scoping rule
> is illustrated by the following example: The result of the expression

>   select distinct sp.* from sp natural join s;

> will include columns PNO and QTY but *not* column SNO, because --
> believe it or not -- there is no column "SP.SNO" in the result of the
> join expression (indeed specifying SP.SNO in the SELECT clause would be
> a syntax error)."

> The emphasis is D&D's, not mine ;)

Hm.  After further digging in the spec, it seems that their
interpretation rests on SQL92's section 6.4 <column reference> syntax
rule 2.b.  Rule 2 in full is:

         2) If CR contains a <qualifier> Q, then CR shall appear within the
            scope of one or more <table name>s or <correlation name>s that
            are equal to Q. If there is more than one such <table name> or
            <correlation name>, then the one with the most local scope is
            specified. Let T be the table associated with Q.

            a) T shall include a column whose <column name> is CN.

            b) If T is a <table reference> in a <joined table> J, then CN
              shall not be a common column name in J.

              Note: Common column name is defined in Subclause 7.5, "<joined
              table>".

2.b strikes me as a completely unnecessary and counterintuitive
restriction.  Do D&D provide any justification for it?  I'm not
especially inclined to make our implementation substantially more
complex in order to enforce what seems a bogus restriction.

What's even more interesting is that I can find no equivalent
text in SQL99.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Thomas LockhartDate: 2000-12-17 07:49:13
Subject: Re: Table name scope (was Re: Outer joins aren't working with views)
Previous:From: Thomas LockhartDate: 2000-12-17 06:32:09
Subject: Re: Table name scope (was Re: Outer joins aren't working with views)

pgsql-bugs by date

Next:From: Thomas LockhartDate: 2000-12-17 07:49:13
Subject: Re: Table name scope (was Re: Outer joins aren't working with views)
Previous:From: Thomas LockhartDate: 2000-12-17 06:32:09
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