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

Re: 'natural join' and 'join ... using' giving different

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: 'natural join' and 'join ... using' giving different
Date: 2002-01-09 21:13:26
Message-ID: 20020109131049.Q73577-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Wed, 9 Jan 2002, Bruno Wolff III wrote:

> I am seeing different results when using 'natural join' as opposed to
> 'join ... using' on what I think the equivalent columns should be.
> The 'join ... using' version of the query gives the expected answer.
> I have tried this on 7.1.3 (built locally) and 7.2b4 with a patch
> to how foreign keys are checked. The machines where both running
> Redhat linux 6.1 with a 2.2.16 kernel.
>
> Below is a sample psql session that I think illustrates the problem.

Wouldn't games natural join crate be on both gameid and touched
as opposed to only gameid?  I don't remember exactly but natural
join joins on columns of the same name right?

>
> Script started on Wed Jan  9 11:17:06 2002
> [bruno(at)wolff bruno]$ psql
> Welcome to psql, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> area=> \d games
>                          Table "games"
>  Attribute |           Type           |        Modifier
> -----------+--------------------------+------------------------
>  gameid    | text                     | not null
>  title     | text                     | not null
>  touched   | timestamp with time zone | not null default 'now'
> Indices: games_pkey,
>          title_idx
> Constraints: (gameid ~ '^[A-Z0-9]+$'::text)
>              (title ~ '^[!-~]+( [!-~]+)*$'::text)
>
> area=> \d crate
>                          Table "crate"
>  Attribute |           Type           |        Modifier
> -----------+--------------------------+------------------------
>  areaid    | text                     | not null
>  gameid    | text                     | not null
>  rate      | integer                  | not null default 5000
>  frq       | integer                  | not null default 0
>  opp       | integer                  | not null default 0
>  rmp       | integer                  | not null default 0
>  trn       | integer                  | not null default 0
>  touched   | timestamp with time zone | not null default 'now'


In response to

pgsql-bugs by date

Next:From: Robby DermodyDate: 2002-01-09 22:01:14
Subject: arrays broken in 7.1.3? also, support for embedded tables?
Previous:From: Tom LaneDate: 2002-01-09 20:26:12
Subject: Re: 'natural join' and 'join ... using' giving different results

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