469,080 Members | 1,731 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,080 developers. It's quick & easy.

Outer join returns incorrect results when using sub-selects

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?
Jul 19 '05 #1
9 6145
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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?


I don't see the problem, but I did notice the nvl() function. A NULL
shouldn't occur in the join column (at least not with the data you
presented) so you should just list the key:

select s1.dim1, stat1, stat2 from

I doubt that would fix the other columns, but this is going to be one
of those "intuitive obvious" solutions, i.e., once you know the
answer, you realize it was "intuitive obvious" 8^)
Jul 19 '05 #2
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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.
Jul 19 '05 #3
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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?


No, if you are getting that result with that SQL then you are correct
and Oracle has a serious bug. Which version?
DG
Jul 19 '05 #4
select dim1,sum(stat1) stat1,sum(stat2) stat2
from (select distinct dim1, stat1, stat2 from
data1 full outer join data2 using (dim1)
order by dim1)
group by dim1;

This way will work.

Lori

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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?

Jul 19 '05 #5
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:

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.

af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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);

....
Jul 19 '05 #6
Try this :

select dim1,sum(stat1) stat1,sum(stat2) stat2
from (select distinct dim1, stat1, stat2 from
data1 full outer join data2 using (dim1)
order by dim1)
group by dim1;
Seems to give the proper output with no nvl surprises.

Lori
Jul 19 '05 #7
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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.

af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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);

...

Jul 19 '05 #8
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:

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.

af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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);
....
Jun 27 '08 #9
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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:

SQLcreate 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.

SQLSQL>
1 row created.

SQL>
1 row created.

SQL>
Commit complete.

SQLSQL 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.

af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@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);
...
Jun 27 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Dave | last post: by
8 posts views Thread by Matt | last post: by
1 post views Thread by Rory Campbell-Lange | last post: by
3 posts views Thread by Martin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.