Re: Left join bug?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 03 Aug 2007 04:44:56 -0700
Message-ID: <1186141496.879905.18200@x35g2000prf.googlegroups.com>

On Aug 3, 7:10 am, joe <fischauto..._at_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. Received on Fri Aug 03 2007 - 06:44:56 CDT