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

'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 (view raw or flat)
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

pgsql-bugs by date

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

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