Re: Outer join returns incorrect results when using sub-selects

chris.greening_at_actix.com (Chris Greening) wrote in message news:<57c2930a.0309260545.3eef1732_at_posting.google.com>...
> I'm seeing a very strange problem with outer joins.
>
> The example below replicates the problem:
>
> create table data1 (dim1 integer, stat1 float);
> create table data2 (dim1 integer, stat2 float);
>
> insert into data1 values (1,1);
> insert into data1 values (1,2);
> insert into data1 values (2,2);
> insert into data1 values (2,2);
> insert into data1 values (3,3);
> insert into data1 values (6,3);
> insert into data1 values (7,3);
>
> insert into data2 values (2,1);
> insert into data2 values (2,2);
> insert into data2 values (3,2);
> insert into data2 values (4,3);
> insert into data2 values (5,3);
>
> I want to be able to run a query that generates the resultset:
>
> dim1, sum(stat1), sum(stat2)
> 1,3,NULL
> 2,4,3
> 3,3,2
> 4,NULL,3
> 5,NULL,3
> 6,3,NULL
> 7,3,NULL
>
> I was planning to use the following sql:
 

>
> select nvl(s1.dim1, s2.dim1) dim1, stat1, stat2 from
> (
> select dim1, sum(stat1) stat1 from data1 group by dim1
> ) s1
> full outer join
> (
> select dim1, sum(stat2) stat2 from data2 group by dim1
> ) s2 on s1.dim1=s2.dim1
> order by dim1;
>
> When I run this. Instead of the 4 rows I was expecting I get back:
>
> dim1,stat1,stat2
> 1,3,NULL
> 2,4,3
> 3,3,2
> 4,NULL,NULL
> 5,NULL,NULL
> 6,3,NULL
> 7,3,NULL
>
> The values for stat2 seem to be getting lost when there is no value for stat1.
>
> Anyone have any ideas as to what is going on? Am I doing something stupid?

What's RDBMS version? I had similar problem with 8.1.6, which disappeared after upgrade to 8.1.7. There was a workaround too, forcing hash outer join fixed the query. Received on Fri Sep 26 2003 - 20:59:22 CEST