Left join bug?
joe
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?
Charles Hooper
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.
Cristian Cudizio
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
David Portas
news:1186139409.4...@d55g2000hsg.googlegroups.com...
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
Cristian Cudizio
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> "joe" <fischauto...@yahoo.de> wrote in message
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
Serge Rielau
> 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?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab