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

chris.greening_at_actix.com (Chris Greening) wrote in message news:<57c2930a.0309290033.3978c229_at_posting.google.com>...
> Hi Ed/Alex, thanks for replying
>
> Ed, here's a simpler example that gives the same problem:
>
> create table test1 (dim1 integer, stat1 float);
> create table test2 (dim1 integer, stat2 float);
>
> insert into test1 values (1, 1);
> insert into test2 values (2, 1);
> commit;
>
> select * from
> (select dim1, sum(stat1) from test1 group by dim1) s1 full outer join
> (select dim1, sum(stat2) from test2 group by dim1) s2 on s1.dim1=s2.dim1;
>
> DIM1 SUM(STAT1) DIM1 SUM(STAT2)
> ---------- ---------- ---------- ----------
> 1 1
> 2
>
> As you can see the SUM(STAT2) column is always null.
>
> If I remove the group by (and the sum) from the subselect I get the correct results:
>

Looks like problem was fixed in 9.2.0.3:

SQL> create table test1 (dim1 integer, stat1 float); create table test2 (dim1 integer, stat2 float);

insert into test1 values (1, 1);
insert into test2 values (2, 1);
commit;

select * from
(select dim1, sum(stat1) from test1 group by dim1) s1 full outer join (select dim1, sum(stat2) from test2 group by dim1) s2 on s1.dim1=s2.dim1;

Table created.

SQL>
Table created.

SQL> SQL>
1 row created.

SQL>
1 row created.

SQL>
Commit complete.

SQL> SQL> 2 3
      DIM1 SUM(STAT1) DIM1 SUM(STAT2) ---------- ---------- ---------- ----------

	 1	    1
			       2	  1

You need to upgrade your server.

> select * from
> (select dim1, stat1 from test1) s1 full outer join
> (select dim1, stat2 from test2) s2 on s1.dim1=s2.dim1;
>
> DIM1 STAT1 DIM1 STAT2
> ---------- ---------- ---------- ----------
> 1 1
> 2 1
>
> Unfortunately we need the aggregation in our subselects.
>
> Alex, we are using:
>
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> Thanks
> Chris.
>
> afilonov_at_yahoo.com (Alex Filonov) wrote in message news:<336da121.0309261059.60a32c36_at_posting.google.com>...
> > 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);
> ...
Received on Fri Oct 03 2003 - 19:29:39 CEST