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

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: mucher(at)tigana(dot)pl, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Outer joins aren't working with views
Date: 2000-12-16 05:16:22
Message-ID: 3A3AFAA6.B28A60D6@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
> It works for me:
> regression=# select t1.*, t2.* from t1 natural left outer join t2;
>  id | id2 | id
> ----+-----+----
>   1 |   1 |  1

My recollection is that SQL9x requires that the join result lose the
link to the original table names. That is,

  select id, id2 from t1 natural left outer join t2;

is legal, but

  select t1.id, ...

is not.

If one needs to label the join product, then one uses aliases, as

  select tx.* from (t1 natural left outer join t2) as tx;

or

  select tx.* from (t1 natural left outer join t2) as tx (c1, c2);

I could see allowing in the target list explicit reference to the
underlying tables as an extension when there is no ambiguity.

However, in this case should the natural join in the original example do
the join on the column "id", and not have two columns of name "id"
available after the join?

How do you read the spec and this example? My original reading was from
the Date and Darwen book, and the SQL99 spec we have is (to put it
nicely) a bit harder to follow. I'll write some of this up for the
syntax section of the user's guide once I'm clear on it...

ref:
ansi-iso-9075-2-1999.txt from the draft documents we found on the web
last year.

ISO/IEC 9075-2:1999 SQL - Part 2: SQL/Foundation-
September 23, 1999
[Compiled using SQL3_ISO option]
section 7.7 rule 7

                         - Thomas

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-12-16 06:38:33
Subject: Table name scope (was Re: Outer joins aren't working with views)
Previous:From: Patrick DunfordDate: 2000-12-16 04:43:29
Subject: Digest subscription

pgsql-bugs by date

Next:From: Thomas LockhartDate: 2000-12-16 05:30:12
Subject: Re: Bug in CAST() with time data types
Previous:From: pgsql-bugsDate: 2000-12-16 04:04:38
Subject: JDBC Driver Authentication Bug

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