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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
