Submitted by: Omacht András (aomacht)

Attachments:
FB_TEST.ZIP

See the attached database.

When running

select t3.id,
       (select first 1 v1.id
          from v1
          where v1.vc1 = 'A' and v1.i2 = 1000 and v1.i1 = coalesce(t3.i1_1, t3.i1_2))
  from t3

no indicies were found for view V1:

Plan
PLAN (V1 T1 NATURAL)
PLAN (V1 T2 NATURAL)
PLAN (T3 NATURAL)

The engine should use T1_UK and T2_UK:

Plan
PLAN (V1 T1 INDEX (T1_UK1))
PLAN (V1 T2 INDEX (T2_UK1))
PLAN (T3 NATURAL)

Without coalesce in then where clause it works fine:

select t3.id,
       (select first 1 v1.id
          from v1
          where v1.vc1 = 'A' and v1.i2 = 1000 and v1.i1 = t3.i1_1)
  from t3

Plan
PLAN (V1 T1 INDEX (T1_UK1))
PLAN (V1 T2 INDEX (T2_UK1))
PLAN (T3 NATURAL)

and

select t3.id,
       (select first 1 v1.id
          from v1
          where v1.vc1 = 'A' and v1.i2 = 1000 and v1.i1 = t3.i1_2)
  from t3

Plan
PLAN (V1 T1 INDEX (T1_UK1))
PLAN (V1 T2 INDEX (T2_UK1))
PLAN (T3 NATURAL)

====== Test Details ======

Deferred until core-2975 will be fixed (currently 2.5.x produces still bad plan: PLAN MERGE (SORT (JOIN (T1 INDEX (T1_COL), T2 INDEX (T2_ID))), SORT (T3 NATURAL)); 3.0 & 4.0: PLAN HASH (JOIN (T1 INDEX (T1_COL), T2 INDEX (T2_ID)), T3 NATURAL) )