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

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: pgsql-bugs(at)postgresql(dot)org
Subject: 'natural join' and 'join ... using' giving different results
Date: 2002-01-09 17:29:28
Message-ID: 20020109172928.GA21771@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(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'
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(at)wolff bruno]$ exit
exit

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2002-01-09 19:37:58 Re: case-sensitivity inconsistency in quoted column aliases in FROM subselects
Previous Message Mike Hoolehan 2002-01-09 16:42:45 case-sensitivity inconsistency in quoted column aliases in FROM subselects