With Clause vs View of the same name

Question

In my database, there is a view named VAL_DATES_V which is used in many other views in the same schema. For example, the SQL code in the view SECTOR_V uses VAL_DATES_V.

CREATE OR REPLACE VIEW schema1.sector_v AS
SELECT s.*
FROM   schema1.sector      s,
       schema1.val_dates_v d
WHERE  s.date_id   = d.date_id
AND    d.open_flag = 1;

In some use cases, we want to allow a user to override the VAL_DATES_V view with a "with clause" of the same name:

WITH val_dates_v AS (
    SELECT 1234 AS date_id,
           1    AS open_flag
    FROM   dual
) SELECT * FROM schema1.sector_v;

In our Dev database this works perfectly fine, and SECTOR_V evaluates VAL_DATES_V as what is defined in the "with clause". However, in our QA database, the optimizer uses the schema defined view SCHEMA1.VAL_DATES_V instead of the "with clause" view of the same name.

Even more confusing, if I copy the SQL code for SECTOR_V in place of "sector_v" it works just fine in QA.

Let me know if anyone can explain why the view is taking precedent over the "with clause" view of the same name.

Real World Dev Explain Plan (partially formatted):

WITH val_dates AS
  (SELECT /*+ materialize */
          val_date,
          id,
          period_month,
          period_year,
          '1' AS open_flag,
          val_date_type
   FROM   client_rep.val_dates_table a
   WHERE  a.period_year= 2014
   AND    a.period_month = 9
  )
SELECT * FROM client_rep.xamin_sleeve_attribution_v

OPERATION    OBJECT_NAME     CARDINALITY     COST 

SELECT STATEMENT         14      42 
 **TEMP TABLE TRANSFORMATION                
  LOAD AS SELECT    SYS_TEMP_0FD9D66E3_A916354E
   TABLE ACCESS VAL_DATES_TABLE 2   3 
    INDEX   VAL_DATES_IDX2   2   1 
     Access Predicates 
      AND 
       A.PERIOD_MONTH=9 
       A.PERIOD_YEAR=2014**

 SORT   14   39
 HASH JOIN  14   38
 Access Predicates 
 XSA.VARIABLE_COMP_FUND_CODE=F.VARIABLE_COMP_FUND_CODE 
 VIEW 
 index$_join$_005    105     2
 HASH JOIN
 Access Predicates
 ROWID=ROWID
 INDEX 
 FUND_CONTROL_PK     105     1 
 INDEX 
 FUND_CONTROL_UK1    105     1
 HASH JOIN 14    36 
 Access Predicates 
 XSA.BATCH_ID=B.ID 
 JOIN FILTER 
 :BF0000     93      29 
 NESTED LOOPS   93   29 
 NESTED LOOPS   190      29 
 VIEW 
 VW_NSO_1    2   2 
 HASH 2        
 VIEW   2    2 
 Filter Predicates 
 AND
 TO_NUMBER(V.OPEN_FLAG)=1 
 VAL_DATE_TYPE='Calendar End'
 **TABLE ACCESS 
 SYS_TEMP_0FD9D66E3_A916354E     2   2**
 INDEX 
 BATCHES_VAL_DATES_FK    95      1 
 Access Predicates
 B.VAL_DATE_ID=ID
 TABLE ACCESS
 BATCHES     47      13 
 Filter Predicates
 B.ACTIVE=1
 JOIN FILTER
 :BF0000     548     7 
 TABLE ACCESS
 XAMIN_SLEEVE_ATTRIBUTION    548     7 
 Access Predicates
 SYS_OP_BLOOM_FILTER(:BF0000,XSA.BATCH_ID)
 Filter Predicates
 SYS_OP_BLOOM_FILTER(:BF0000,XSA.BATCH_ID)

Create view statement:

CREATE OR REPLACE FORCE VIEW "CLIENT_REP"."XAMIN_SLEEVE_ATTRIBUTION_V" AS 
SELECT XSA.*
     FROM CLIENT_REP.XAMIN_SLEEVE_ATTRIBUTION XSA,
          CLIENT_REP.BATCHES B,
          CLIENT_REP.FUND_CONTROL F
    WHERE     XSA.BATCH_ID = B.ID
          AND B.ACTIVE = 1
          AND XSA.VARIABLE_COMP_FUND_CODE = F.VARIABLE_COMP_FUND_CODE
          AND B.VAL_DATE_ID IN
                 (SELECT v.id
                    FROM client_rep.val_dates v
                   WHERE v.open_flag = 1 AND VAL_DATE_TYPE = 'Calendar End')
 ORDER BY VAL_DATE,
          FIXED_COMP_FUND_CODE,
          TIME_PERIOD,
          SORT_KEY;

Just trust me, the QA explain plan does not even materialize the "with clause". It ignores it and just uses the "val_dates_v"

Answer 1

This is a bug, fixed in the July 2014 CPU - See Oracle Support document 1666884.1.

Easily reproduced with the following:

create view v1 as ( select 'view' as vvv from dual );

create view v2 as ( select * from v1 ); 

with v1 as ( select 'cte' as vvv from dual) select * from v2;