'natural join' and 'join ... using' giving different results

Started by Bruno Wolff IIIover 24 years ago3 messagesbugs
Jump to latest
#1Bruno Wolff III
bruno@wolff.to

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.

Script started on Wed Jan 9 11:17:06 2002
[bruno@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'
Index: crate_pkey
Constraints: (rate >= 0)
             (frq >= 0)
             (opp >= 0)
             (rmp >= 0)
             (trn >= 0)

area=> select * from crate natural join games where areaid = '53217.01';
gameid | touched | areaid | rate | frq | opp | rmp | trn | title
--------+------------------------+----------+------+-----+-----+-----+-----+-----------
WRS | 2000-06-01 00:00:00-05 | 53217.01 | 5000 | 0 | 0 | 0 | 0 | Wrasslin'
(1 row)

area=> select * from crate join games using (gameid) where areaid = '53217.01';
gameid | areaid | rate | frq | opp | rmp | trn | touched | title | touched
--------+----------+------+-----+-----+-----+-----+------------------------+-------------------------+------------------------
GR | 53217.01 | 4969 | 2 | 49 | 0 | 2 | 1999-08-02 00:00:00-05 | Greed | 2001-11-17 00:00:00-06
GXY | 53217.01 | 4975 | 1 | 4 | 0 | 1 | 2001-01-13 00:00:00-06 | Galaxy | 2001-09-09 00:00:00-05
MRA | 53217.01 | 4966 | 1 | 3 | 0 | 1 | 1999-08-02 00:00:00-05 | Monsters Ravage America | 2000-06-01 00:00:00-05
RBN | 53217.01 | 5143 | 4 | 15 | 0 | 2 | 1993-08-02 00:00:00-05 | Rail Baron | 2001-08-10 00:00:00-05
SLS | 53217.01 | 4986 | 1 | 7 | 0 | 1 | 1999-08-02 00:00:00-05 | Slapshot | 2000-06-01 00:00:00-05
TTA | 53217.01 | 5103 | 1 | 4 | 0 | 1 | 1999-08-02 00:00:00-05 | Titan: The Arena | 2001-09-09 00:00:00-05
TTN | 53217.01 | 5554 | 28 | 38 | 0 | 11 | 2001-07-01 00:00:00-05 | Titan | 2001-09-21 00:00:00-05
VIP | 53217.01 | 5300 | 15 | 15 | 0 | 3 | 1999-03-07 00:00:00-06 | Victory In The Pacific | 2002-01-03 00:00:00-06
WRS | 53217.01 | 5000 | 0 | 0 | 0 | 0 | 2000-06-01 00:00:00-05 | Wrasslin' | 2000-06-01 00:00:00-05
(9 rows)

area=> \q
[bruno@wolff bruno]$ exit
exit

Script done on Wed Jan 9 11:18:48 2002

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bruno Wolff III (#1)
Re: 'natural join' and 'join ... using' giving different results

Natural join of those two tables will be on (gameid, touched)
not only (gameid). You should've noticed that the natural
join was only emitting one copy of the "touched" column ...

regards, tom lane

#3Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Bruno Wolff III (#1)
Re: 'natural join' and 'join ... using' giving different

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?

Show quoted text

Script started on Wed Jan 9 11:17:06 2002
[bruno@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'