Left join expression and amount of rows returned by Oracle

Question

A query with left join is not returning records, although the where clause from the left table should find a single record. In this case, it should return a record with the fields from the left table containing values and from the right table null, since there is no match between them.

Apparently there is a problem with the use of case that references the right table on the join expression.

In SQL Server the same query worked as expected.

select 
  t1.Description, t2.Description
from 
  A t1 
left join 
  B t2 
on
  t1.Id = t2.Id and
  1 = case when (
    t2.Id = t2.Id and 
    (select t3.Flag from C t3 where t3.ID_B = t2.Id) = 'S'
  ) then 1 else 0 
  end
where t1.Id = 1

Result: no rows returned.

Then I moved the expression t2.Id = t2.Id (that is here only to demonstrate the problem and should always return true, apparently) out of the case expression.

select 
  t1.Description, t2.Description
from 
  A t1 
left join 
  B t2 
on
  t1.Id = t2.Id and
  t2.Id = t2.Id and 
  1 = case when ( 
    (select t3.Flag from C t3 where t3.ID_B = t2.Id) = 'S') then 1 else 0 
  end
where t1.Id = 1

Result: one row returned.

The queries above only serve to demonstrate the problem, are not useful in a real situation and not optimized.

I want to know if anyone knows any limitation of Oracle related to this case. So far we believe it is a bug.

Data used:

• A: Id=1, Description=Item A1;
• B: Id=1, Description=Item B1;
• C: Id=1, Id_B=2, Flag=S.

Answer 1

CREATE TABLE t1 AS (SELECT 1 ID FROM dual);
CREATE TABLE t2 AS (SELECT 2 ID FROM dual);
CREATE TABLE t3 AS (SELECT 2 id_b, 's' flag FROM dual);

SELECT t1.* 
FROM t1 LEFT JOIN t2 
      ON t1.ID = t2.ID
         AND 1 = CASE WHEN t2.id = t2.id and (SELECT flag FROM t3 WHERE t3.id_b = t2.ID) = 's' THEN 1 ELSE 0 END
where t1.id = 1;

The output: no rows selected

The result looks strange, I suppose it can be a bug.

Oracle documentation only states
https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52337

You cannot compare a column with a subquery in the WHERE clause of any outer join, regardless which form you specify.

By looking on the plan of the above query I can see that this condition:

 AND 1 = CASE WHEN t2.id = t2.id and (SELECT flag FROM t3 WHERE t3.id_b = t2.ID) = 's' THEN 1 ELSE 0 END

Is interpreted as:

 CASE WHEN (T2.ID(+)=T2.ID(+) AND (SELECT FLAG FROM T3 T3 WHERE T3.ID_B=:B1)='s') THEN 1 ELSE 0 END =1

and is calculated after the join.

I suppose that Oracle cannot calcuate the CASE until the join is performed (because of T2.ID(+)=T2.ID(+))