Oracle Exadata - LEFT OUTER JOIN acting as INNER when using AND on join clause
Background
My company recently upgraded from Oracle 11g to Exadata. All production facilities and data were successfully migrated to the new system and verified as being consistent between systems: 1: 1. Immediately after completing the first daily set of ETL processes on our new system, we found that our report tables were significantly smaller than expected. Upon further investigation, we found that the batch ID added to the LEFT OUTER joins caused an issue when they worked fine on 11g.
Question
Why would the following request be treated as a LEFT OUTER JOIN on 11g, but an INNER JOIN on Exadata?
SELECT
*
FROM DIM_CALL CALLS
LEFT OUTER JOIN FACT_ACTVY_MNGMT ACTVY_MNGMT
ON ACTVY_MNGMT.CALL_KEY = CALLS.CALL_KEY
AND ACTVY_MNGMT.BATCH_ID = 20141112
LEFT OUTER JOIN DIM_ACTVY ACTVY
ON ACTVY.ACTVY_KEY = ACTVY_MNGMT.ACTVY_KEY
AND ACTVY_MNGMT.BATCH_ID = 20141112
Update
It looks like there was either a typo in the query used in the ETL process, or the original developer just missed the case. If you look at the last connection, you will notice that the connection is in ACTVY_KEY and BATCH_ID. The problem is that the BATCH_ID it refers to is from the ACTVY_MNGMT table. The database basically treats this as a WHERE clause, so any case where CALL_KEY is NULL will result in an error.
source to share
Minor programming error here but also looks like an optimizer error. Think of it LEFT JOIN
as working in the previous set, not just the previous table. Adding a duplicate predicate to another LEFT JOIN
shouldn't make any difference.
I'm guessing there is an optimizer or parser bug with some fantastic Exadata feature like smart scan.
This code is similar to your problem. However, it doesn't reproduce the error for me, on 11.2.0.3 EE, on Solaris. And it might not even reproduce the error for you on Exadata. It simply means that the problem you described is a bug. Avoiding mistakes as you are is usually the best approach. But you can also create an Oracle supported service request to investigate the issue. This same error can affect other code in less obvious ways.
with table1 as (select '1' a, '1' b from dual),
table2 as (select '1' a, '2' b from dual),
table3 as (select '1' a, '2' b from dual)
select *
from table1
left join table2
on table1.a = table2.a
and table2.b = 3
left join table3
on table2.a = table3.a;
with table1 as (select '1' a, '1' b from dual),
table2 as (select '1' a, '2' b from dual),
table3 as (select '1' a, '2' b from dual)
select *
from table1
left join table2
on table1.a = table2.a
and table2.b = 3
left join table3
on table2.a = table3.a
--This predicate is logically redundant but does *not* change results.
and table2.b = 3;
Both queries return this result on Oracle 11.2.0.3, EE, on Solaris:
A B A B A B
- - - - - -
1 1
source to share
It looks like there was either a typo in the query used in the ETL process, or the original developer just missed the case. If you look at the last connection, you will notice that the connection is in ACTVY_KEY and BATCH_ID. The problem is that the BATCH_ID it refers to is from the ACTVY_MNGMT table. The database basically treats this as a WHERE clause, so any case where CALL_KEY is NULL will result in an error.
source to share