Hi,

create table a (num integer)
create table b (num integer)

insert into a values (1)
insert into b values (2)

select * from a left join b
on b.num = 123
where a.num = 1 and b.num is null


MySQL & Oracle 10i show the following result:
1 null

Oracle 9.2.0.1 shows:
Empty result set


Is this a bug of Oracle 9,2.0.1?
Any workaround?

On Aug 3, 7:10 am, joe <fischauto...@yahoo.de> wrote:
> Hi,
>
> create table a (num integer)
> create table b (num integer)
>
> insert into a values (1)
> insert into b values (2)
>
> select * from a left join b
> on b.num = 123
> where a.num = 1 and b.num is null
>
> MySQL & Oracle 10i show the following result:
> 1 null
>
> Oracle 9.2.0.1 shows:
> Empty result set
>
> Is this a bug of Oracle 9,2.0.1?
> Any workaround?

An interesting test with Oracle 10.2.0.2:
CREATE TABLE T1 (C1 NUMBER(10));
CREATE TABLE T2 (C1 NUMBER(10));

INSERT INTO T1 VALUES (1);
INSERT INTO T2 VALUES (2);

This should be logically equivalent to the left outer join syntax that
you used:
SELECT
*
FROM
T1 A,
T2 B
WHERE
A.C1=1
AND B.C1(+) = 123
AND B.C1 IS NULL;

no rows selected

Note that you have a Cartesian join between the two tables. The DBMS
Xplan looks like this:
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN CARTESIAN| | 1 | 1 | 0 |
00:00:00.01 | 7 | | | |
|* 2 | TABLE ACCESS FULL | T2 | 1 | 1 | 0 |
00:00:00.01 | 7 | | | |
| 3 | BUFFER SORT | | 0 | 1 | 0 |
00:00:00.01 | 0 | 73728 | 73728 | |
|* 4 | TABLE ACCESS FULL | T1 | 0 | 1 | 0 |
00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("B"."C1" IS NULL AND "B"."C1"=123))
4 - filter("A"."C1"=1)

See the #2 predicate filter - can something be NULL and equal to 123
at the same time? A Cartesian join between 0 rows and 1 rows results
in 0 rows.

Here is your query:
SELECT
*
FROM
T1 A LEFT JOIN T2 B ON B.C1 = 123
WHERE
A.C1=1
AND B.C1 IS NULL;

C1 C1
---------- ----------
1

DBMS Xplan:
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 1 |
00:00:00.01 | 14 | | | |
| 2 | MERGE JOIN OUTER | | 1 | 1 | 1 |
00:00:00.01 | 14 | | | |
|* 3 | TABLE ACCESS FULL | T1 | 1 | 1 | 1 |
00:00:00.01 | 7 | | | |
| 4 | BUFFER SORT | | 1 | 1 | 0 |
00:00:00.01 | 7 | 1024 | 1024 | |
| 5 | VIEW | | 1 | 1 | 0 |
00:00:00.01 | 7 | | | |
|* 6 | TABLE ACCESS FULL| T2 | 1 | 1 | 0 |
00:00:00.01 | 7 | | | |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."C1" IS NULL)
3 - filter("A"."C1"=1)
6 - filter("B"."C1"=123)

The above shows an outer join taking place between the two tables.

Trying to force the same predicate filter in my original rewrite:
SELECT
*
FROM
T1 A LEFT JOIN T2 B ON B.C1 = 123 AND B.C1 IS NULL
WHERE
A.C1=1;

C1 C1
---------- ----------
1

The DBMS Xplan:
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN OUTER | | 1 | 1 | 1 |
00:00:00.01 | 14 | | | |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 1 |
00:00:00.01 | 7 | | | |
| 3 | BUFFER SORT | | 1 | 1 | 0 |
00:00:00.01 | 7 | 1024 | 1024 | |
| 4 | VIEW | | 1 | 1 | 0 |
00:00:00.01 | 7 | | | |
|* 5 | TABLE ACCESS FULL| T2 | 1 | 1 | 0 |
00:00:00.01 | 7 | | | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."C1"=1)
5 - filter(("B"."C1" IS NULL AND "B"."C1"=123))

Now, what if we remove the Cartesian join:
DROP TABLE T2;

CREATE TABLE T2 (T1_C1 NUMBER(10), C1 NUMBER(10));
INSERT INTO T2 VALUES (1,2);

SELECT
*
FROM
T1 A,
T2 B
WHERE
A.C1=1
AND A.C1=B.T1_C1(+)
AND B.C1(+) = 123
AND B.C1 IS NULL;

C1 T1_C1 C1
---------- ---------- ----------
1

The DBMS Xplan:
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 1 |
00:00:00.01 | 14 | | | |
|* 2 | HASH JOIN OUTER | | 1 | 1 | 1 |
00:00:00.01 | 14 | 1517K| 1517K| 333K (0)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |
00:00:00.01 | 7 | | | |
|* 4 | TABLE ACCESS FULL| T2 | 1 | 1 | 0 |
00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."C1" IS NULL)
2 - access("A"."C1"="B"."T1_C1")
3 - filter("A"."C1"=1)
4 - filter(("B"."C1"=123 AND "B"."T1_C1"=1))

It does look like there are consistency issues with the two outer join
syntax methods when dealing with Cartesian joins between the tables.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Aug 3, 1:10 pm, joe <fischauto...@yahoo.de> wrote:
> Hi,
>
> create table a (num integer)
> create table b (num integer)
>
> insert into a values (1)
> insert into b values (2)
>
> select * from a left join b
> on b.num = 123
> where a.num = 1 and b.num is null
>
> MySQL & Oracle 10i show the following result:
> 1 null
>
> Oracle 9.2.0.1 shows:
> Empty result set
>
> Is this a bug of Oracle 9,2.0.1?
> Any workaround?

I think that is only a different behaviour on a condition not defined.
It seems to me that "b.num =123 " has no sense as join condition so
optimizer as changed behaviour but in my opinion it is not possible to
say this is a bug

regards
--
Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com

"joe" <fischauto333@yahoo.de> wrote in message
news:1186139409.491400.128680@d55g2000hsg.googlegr oups.com...
> Hi,
>
> create table a (num integer)
> create table b (num integer)
>
> insert into a values (1)
> insert into b values (2)
>
> select * from a left join b
> on b.num = 123
> where a.num = 1 and b.num is null
>
>
> MySQL & Oracle 10i show the following result:
> 1 null
>
> Oracle 9.2.0.1 shows:
> Empty result set
>
>
> Is this a bug of Oracle 9,2.0.1?

Yes it is a bug. See section 7.7 of the ANSI/ISO standard. Join conditions
behave exactly like regular search conditions - in fact they are defined as
such. The only difference is that the join condition is evaluated against a
cross join so as to determine the "inner" half of the query and the "outer"
part consists of those rows that don't meet the join condition. The end
result is a union:

SELECT * FROM TN
UNION ALL
SELECT * FROM XN1

where TN is the restriction of the cross-product and XN1 is the "outer" or
"preserved" part, extended with nulls. There is no special restriction on
the type of condition that can be used as a join condition, just the obvious
one that any column references are valid and within scope. It's had to
summarise the full definition in a small space but that's roughly what it
means.

This is one of those occassions when Oracle's (+) syntax is definitely not
equivalent to an ANSI outer join - despite some common misconceptions that
it is so.

--
David Portas

On Aug 3, 10:01 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> "joe" <fischauto...@yahoo.de> wrote in message
>
> news:1186139409.491400.128680@d55g2000hsg.googlegr oups.com...
>
>
>
> > Hi,
>
> > create table a (num integer)
> > create table b (num integer)
>
> > insert into a values (1)
> > insert into b values (2)
>
> > select * from a left join b
> > on b.num = 123
> > where a.num = 1 and b.num is null
>
> > MySQL & Oracle 10i show the following result:
> > 1 null
>
> > Oracle 9.2.0.1 shows:
> > Empty result set
>
> > Is this a bug of Oracle 9,2.0.1?
>
> Yes it is a bug. See section 7.7 of the ANSI/ISO standard. Join conditions
> behave exactly like regular search conditions - in fact they are defined as
> such. The only difference is that the join condition is evaluated against a
> cross join so as to determine the "inner" half of the query and the "outer"
> part consists of those rows that don't meet the join condition. The end
> result is a union:
>
> SELECT * FROM TN
> UNION ALL
> SELECT * FROM XN1
>
> where TN is the restriction of the cross-product and XN1 is the "outer" or
> "preserved" part, extended with nulls. There is no special restriction on
> the type of condition that can be used as a join condition, just the obvious
> one that any column references are valid and within scope. It's had to
> summarise the full definition in a small space but that's roughly what it
> means.
>
> This is one of those occassions when Oracle's (+) syntax is definitely not
> equivalent to an ANSI outer join - despite some common misconceptions that
> it is so.
>
> --
> David Portas

besades the fact that i don't know SQL standard i'm a bit confused:
On 10.2.0.2
i've :
SQL> select * from a full outer join b on (1=1);
NUM NUM
---------- ----------
1 2
SQL> select * from a full outer join b on (1=0);
NUM NUM
---------- ----------
1
2

SQL> select * from a,b;

NUM NUM
---------- ----------
1 2

Is this correct??

Regards
--
Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com

Cristian Cudizio wrote:
> besades the fact that i don't know SQL standard i'm a bit confused:
> On 10.2.0.2
> i've :
> SQL> select * from a full outer join b on (1=1);
> NUM NUM
> ---------- ----------
> 1 2
> SQL> select * from a full outer join b on (1=0);
> NUM NUM
> ---------- ----------
> 1
> 2
>
> SQL> select * from a,b;
>
> NUM NUM
> ---------- ----------
> 1 2
>
> Is this correct?
Yes

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab