Spurious rows returned with left join?

From: Edmund Bacon <ebacon(at)onesystem(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Spurious rows returned with left join?
Date: 2006-04-04 16:39:35
Message-ID: 4432A147.6000207@onesystem.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I think I have stumbled on a bug, though I'm not entirely sure about
that. Things do seem to get a little fuzzy when using outer joins ....

Consider the following:

create table t1(t1_a int);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);

create table t2(t2_a int , t2_b int);
insert into t2 values (1, 1);
insert into t2 values (2, 1);

create table t3(t3_b int, t3_c int);
insert into t3 values (1, 9);
insert into t3 values (1, 10);

select * from t1
left join t2 on t1_a = t2_a
left join t3 on t2_b = t3_b
order by t1_a, t2_b;

t1_a | t2_a | t2_b | t3_b | t3_c
------+------+------+------+------
1 | 1 | 1 | 1 | 9
1 | 1 | 1 | 1 | 10
2 | 2 | 1 | 1 | 9
2 | 2 | 1 | 1 | 10
3 | | | |
3 | | | |

Note that I get 2 rows where t1_a = 3.

My pgsql version is:

PostgreSQL 8.1.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.4 20050721 (Red Hat 3.4.4-2)

(RedHat ES4 rpms from postgresql.org)

I have tried this same select on pgsql ver-7.4.8, CVS tip, as well as
SQL Server 2005, MySQL and Firebird, all of which return just one row
for t1_a = 3.

Since writing the original query I've realized that this is partly a
problem with the INNER JOINS before OUTER JOINS query writing principle
(law?) and the query should perhaps be better written as:

select t1.*, t2.*, t3.*
from t2 join t3 on t2_b = t3_b
right join t1 on t1_a = t2_a;

which does return just one row for t1_a =3 for all postgres versions I
have currently available.

Edmund

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2006-04-04 16:44:41 Re: PostgreSQL support on Redhat Advance Server 2.1
Previous Message MargaretGillon 2006-04-04 16:38:00 Re: PostgreSQL support on Redhat Advance Server 2.1