Wrong query results on column ORA_ROWSCN with bind variables and NVL (Oracle 12c)

Question

In the process of migrating our DB from Oracle 11 to Oracle 12c (12.2.0.1.0), we discovered that the same query gives different results on the two instances.

The query is:

VARIABLE myId NUMBER;
BEGIN :myId := 1325; END;
/

SELECT
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(:myId, rid);

On Oracle 11, we have:

      RID ORA_ROWSCN
--------- ----------
     1325 1.3439E+13
     1325 1.3439E+13

while on Oracle 12c we have:

      RID ORA_ROWSCN
--------- ----------
     1325
     1325

and the following query

SELECT
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(1325, rid);

gives:

      RID ORA_ROWSCN
--------- ----------
     1325    2549788
     1325    2549788

(Clearly the different values of the ORA_ROWSCN column are expected.)

MY_TABLE.RID is NUMBER(38) NOT NULL and there is a non-unique index on it.

I strongly suspect that the wrong results are the unintended consequences of the optimizer, but, as I have a very limited experience with DBs, I have no clues on how to fix this strange behavior.

The execution plans of the two queries are:

Plan hash value: 709374914

---------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |    52 |   676 |     2   (0)| 00:00:01 |
|   1 |  VIEW               | VW_ORE_55EECC8D |    52 |   676 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL         |                 |       |       |            |          |
|*  3 |    FILTER           |                 |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| MY_TABLE_INDEX1 |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    FILTER           |                 |       |       |            |          |
|   6 |     INDEX FULL SCAN | MY_TABLE_INDEX1 |    51 |   663 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:MYID IS NOT NULL)
   4 - access("RID"=:MYID)
   5 - filter(:MYID IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

and

Plan hash value: 460232730

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     2 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE        |     2 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | MY_TABLE_INDEX1 |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RID"=1325)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

My question is: how can I obtain on Oracle 12c the same results that I obtained so far on Oracle 11?

Answer 1

The plan clearly shows the OR-expansion query transformation. It is not the first time it produced wrong results due to various bugs.

Try running your query as:

SELECT /*+ NO_EXPAND */
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(:myId, rid);

Or set the below parameters before executing it:

alter session set "_no_or_expansion"=true;
alter session set "_or_expand_nvl_predicate"=false;

Then run your query.