Wrong Results

It is interesting how a combination of technologies in Oracle can play in a way which produce a seemingly bizarre outcomes.

Consider the following query:

SQL> with v as
(
        select 20 n from dual
) select distinct v.n
                from v, t
                where v.n=t.n(+);

no rows selected

Note that I'm doing a left outer join, however, the query somehow managed to loose a single row I have in the subquery factoring (and just in case you're wondering I've used subquery factoring for simplicity and replacing it with a real table makes no difference).

The first reaction is how could something as simple as this go so terribly wrong?

Let's take a look at the plan:

SQL> with v as
(
        select 20 n from dual
) select distinct v.n
                from v, t
                where v.n=t.n(+);  2    3    4    5    6

Execution Plan
----------------------------------------------------------
Plan hash value: 627307704

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |     9 |     6  (17)| 00:00:01 |
|   1 |  HASH UNIQUE                    |      |     1 |     9 |     6  (17)| 00:00:01 |
|*  2 |   FILTER                        |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER              |      |     1 |     9 |     5   (0)| 00:00:01 |
|   4 |     VIEW                        |      |     1 |     3 |     2   (0)| 00:00:01 |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|*  6 |     MAT_VIEW REWRITE ACCESS FULL| MV_T |    10 |    60 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter("MV_T"."GID"=0)
   3 - access("V"."N"="MV_T"."N"(+))
   6 - filter("MV_T"."N"(+) IS NOT NULL)

As it turns out the query rewrite is in play but the real oddity lies in the Predicate Information section. Line #2 reads filter("MV_T"."GID"=0) which took me by surprise (where did it come from?) as well as explained why the row went missing. A predicate like that essentially turned our query into an inner join!

The answer lies in how this materialized view was created and the cool trick the optimizer tried to do which didn't quite work out. Indeed, I have created the table and the materialized view in the following way:

SQL> create table t as
  2   select mod(level, 10) n, level m
  3    from dual
  4    connect by level <= 1000;
Table created

SQL> create materialized view mv_t enable query rewrite as
  2   select n, sum(m), grouping_id(n) gid
  3    from t
  4    group by rollup(n);
Materialized view created

The materialized view has two levels of aggregation due to the use of a rollup. I'm also using a grouping_id function to identify each grouping level. So what the optimizer tried to do is apply the GID = 0 predicate in order to eliminate the rollup data but, unfortunately, it also turned our query into an inner join equivalent as a result (final query taken from the optimizer trace):

SELECT DISTINCT "V"."N" "N"
 FROM (SELECT 20 "N" FROM "SYS"."DUAL" "DUAL") "V", "ROOT"."MV_T" "MV_T"
 WHERE "MV_T"."GID" = 0
  AND "V"."N" = "MV_T"."N"(+)

Of course the correct predicate in this case should be "MV_T"."GID" (+) = 0.

I have tried the above test case on both 11.2.0.3 and 12.1.0.2.0 with both versions producing the same wrong results.