Hi,

I see different results in Oracle and postgres for same outer join queries.
Here are the details.

I have the following tables in our pg db

table: yuva_test1
yt1_id		yt1_name	yt1_descr
1		1-name1	1-desc1
2		1-name2	1-desc2
3		1-name3	1-desc3
4		1-name4	1-desc4
5		1-name5	1-desc5
6		1-name6	1-desc6

table: yuva_test2
yt2_id		yt2_name	yt2_descr
2		2-name2	2-desc2
3		2-name3	2-desc3
4		2-name4	2-desc4

When I run the query "select yt1_name, yt1_descr, yt2_name, yt2_descr from
yuva_test1 left outer join yuva_test2 on yt1_id=yt2_id and yt2_name =
'2-name2'" on postgres database I get the following results

yt1_name	yt1_descr	yt2_name	yt2_descr
1-name1	1-descr1
1-name2	1-descr2	2-name2	2-descr2
1-name3	1-descr3
1-name4	1-descr4
1-name5	1-descr5
1-name6	1-descr6

But when I tried the same on Oracle(8.1.7) (the query is "select yt1_name,
yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where
yt1_id=yt2_id(+) and yt2_name = '2-name2'') I get the following results

yt1_name	yt1_descr	yt2_name	yt2_descr
1-name2	1-descr2	2-name2	2-descr2

Why postgres is giving? which is standard? is it a bug? or is it the way
postgres is implemented? Could some one help me?

Note: at the end of my mail is script to create tables and data in postgres.

Thanks
Yuva
Sr. Java Developer
www.ebates.com

============================================================
Scripts:
CREATE TABLE "yuva_test1" (
  "yt1_id" numeric(16, 0), 
  "yt1_name" varchar(16) NOT NULL, 
  "yt1_descr" varchar(32)
) WITH OIDS;

CREATE TABLE "yuva_test2" (
  "yt2_id" numeric(16, 0), 
  "yt2_name" varchar(16) NOT NULL, 
  "yt2_descr" varchar(32)
) WITH OIDS;

insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (1, '1-name1',
'1-descr1');
insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (2, '1-name2',
'1-descr2');
insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (3, '1-name3',
'1-descr3');
insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (4, '1-name4',
'1-descr4');
insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (5, '1-name5',
'1-descr5');
insert into yuva_test1 (yt1_id, yt1_name, yt1_descr) values (6, '1-name6',
'1-descr6');

insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (2, '2-name2',
'2-descr2');
insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (3, '2-name3',
'2-descr3');
insert into yuva_test2 (yt2_id, yt2_name, yt2_descr) values (4, '2-name4',
'2-descr4');
============================================================

Tue, 30 Jul 2002 20:53:06 -0700
Yuva Chandolu <ychandolu@ebates.com>
Outer join differences
Yuva Chandolu <ychandolu@ebates.com> writes:
> I see different results in Oracle and postgres for same outer join queries.

I believe you are sending your bug report to the wrong database.

> When I run the query "select yt1_name, yt1_descr, yt2_name, yt2_descr from
> yuva_test1 left outer join yuva_test2 on yt1_id=yt2_id and yt2_name =
> '2-name2'" on postgres database I get the following results

> yt1_name	yt1_descr	yt2_name	yt2_descr
> 1-name1	1-descr1
> 1-name2	1-descr2	2-name2	2-descr2
> 1-name3	1-descr3
> 1-name4	1-descr4
> 1-name5	1-descr5
> 1-name6	1-descr6

> But when I tried the same on Oracle(8.1.7) (the query is "select yt1_name,
> yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where
> yt1_id=yt2_id(+) and yt2_name = '2-name2'') I get the following results

> yt1_name	yt1_descr	yt2_name	yt2_descr
> 1-name2	1-descr2	2-name2	2-descr2

According to the SQL spec, the output of a LEFT JOIN consists of those
joined rows where the join condition is true, plus those rows of the
left table for which no right-table row produced a true join condition
(substituting nulls for the right-table columns).  Our output clearly
conforms to the spec.

I do not know what Oracle thinks is the correct output when one
condition is marked with (+) and the other is not --- it's not very
obvious what that corresponds to in the spec's terminology.  But I
suggest you take it up with them, not us.

			regards, tom lane

Wed, 31 Jul 2002 00:14:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Outer join differences 

On Tue, 30 Jul 2002, Yuva Chandolu wrote:

> Hi,
>
> I see different results in Oracle and postgres for same outer join queries.
> Here are the details.

Those probably aren't the same outer join queries.

> When I run the query "select yt1_name, yt1_descr, yt2_name, yt2_descr from
> yuva_test1 left outer join yuva_test2 on yt1_id=yt2_id and yt2_name =
> '2-name2'" on postgres database I get the following results
>

Both conditions are part of the join condition for the outer join.

> But when I tried the same on Oracle(8.1.7) (the query is "select yt1_name,
> yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where
> yt1_id=yt2_id(+) and yt2_name = '2-name2'') I get the following result

One condition is the join condition and one is a general where condition I
would guess since only one has the (+)

I think the equivalent query is
select yt1_name, yt1_descr, yt2_name, yt2_descr from yuva_test1 left outer
join yuva_test2 on yt1_id=yt2_id where yt2_name='2-name2'.

Note of course that you're destroying the outer joinness by doing
that yt2_name='2-name2' since the rows with no matching yuva_test2
will not match that conditoin.


Tue, 30 Jul 2002 21:31:26 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Outer join differences
> > When I run the query "select yt1_name, yt1_descr, yt2_name,
> yt2_descr from
> > yuva_test1 left outer join yuva_test2 on yt1_id=yt2_id and yt2_name =
> > '2-name2'" on postgres database I get the following results

Probaly if you change your postgres query to this, it will give the same
answer as Oracle:

select yt1_name, yt1_descr, yt2_name,
yt2_descr from
yuva_test1 left outer join yuva_test2 on yt1_id=yt2_id where yt2_name =
'2-name2';

??

Chris


> > But when I tried the same on Oracle(8.1.7) (the query is
> "select yt1_name,
> > yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where
> > yt1_id=yt2_id(+) and yt2_name = '2-name2'') I get the following result

And maybe if you change the oracle query to this, it will give the same
answer as postgres:

select yt1_name,
yt1_descr, yt2_name, yt2_descr from yuva_test1, yuva_test2 where
yt1_id=yt2_id(+) and yt2_name = '2-name2'(+);

Just guessing tho.

Chris


Wed, 31 Jul 2002 12:35:13 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Outer join differences