| 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: | Whole Thread | Raw Message | 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
| 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 |