Left join expression and amount of rows returned by Oracle

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 ENDwhere 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(+))

Your assumption that t2.id = t2.id is always true is wrong. If the value were NULL that would be treated as false. I don't believe that is relevant for this particular example, but just to clarify.

The question is how is a left join processed. The idea is simple. The on clause is processed. If there are no matches, then the row from the first table is kept. This is regardless of what is in the on clause. (This is a functional description; there are many possible implementations.)

Based on your sample data, Oracle is incorrect. One row should be returned. The SQL Server example should also return one row. I suspect that the data might be subtly different; I personally have never had issues with left joins in SQL Server (or Oracle).

Using SQLFiddle Oracle 11g R2 (thanks to Shannon Severance) your first query gives Record Count: 0 but by simply removing the CASE we get Record Count: 1. (Note the renaming of t2Description.)

create table A (ID number(38), Description varchar(10));create table B (ID number(38), Description varchar(10));create table C (ID number(38), ID_B number(38), Flag varchar(10));insert into A values(1, 'Item A1');insert into B values(2, 'Item B1');insert into C values(1, 2, 'S');select   t1.Description, t2.Description as t2dfrom   A t1 left join   B t2 on  t1.Id = t2.Id and    t2.Id = t2.Id and     (select t3.Flag from C t3 where t3.ID_B = t2.Id) = 'S'where t1.Id = 1

This suggests that it has something to do with CASE being miscalculated.

Note that in the ON t2.Id is at least sometimes (correctly) taken to be the value from the FROM cross product, not NULL which it is after the ON:

select   t1.Description, t2.Description as t2dfrom   A t1 left join   B t2 on    -- for above data t2.id should be 1 here    t2.id is nullwhere t1.Id = 1-- for above data t2.id should be null hereDESCRIPTION     T2DItem A1     (null)

I found this link: Outer Join Bug in Oracle 12c?