[20150715]Wrong Results.txt
http://afatkulin.blogspot.com/2015/07/wrong-results.html
--重复测试：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t as select mod(level, 10) n, level m from dual connect by level <= 1000;
create materialized view mv_t enable query rewrite as select n, sum(m), grouping_id(n) gid from t group by rollup(n);
with v as ( select 20 n from dual) select distinct v.n from v, t where v.n=t.n(+);
SCOTT@test> with v as ( select 20 n from dual) select distinct v.n from v, t where v.n=t.n(+);
no rows selected
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4nhtt9tat8cws, child number 0
-------------------------------------
with v as ( select 20 n from dual) select distinct v.n from v, t where v.n=t.n(+)
Plan hash value: 29516041
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | SORT UNIQUE NOSORT | | 1 | 28 | 6 (17)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 28 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
|* 5 | MAT_VIEW REWRITE ACCESS FULL| MV_T | 1 | 26 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9EB47AD3
4 - SEL$9EB47AD3 / DUAL@SEL$1
5 - SEL$9EB47AD3 / MV_T@SEL$EE2C87C0
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MV_T"."GID"=0)
5 - filter("MV_T"."N"=CASE WHEN (ROWID IS NOT NULL) THEN 20 ELSE 20 END )
--仔细观察可以发现2 - filter("MV_T"."GID"=0)，导致没有结果输出。
SCOTT@test> select * from MV_T;
N SUM(M) GID
---------- ---------- ----------
0 50500 0
1 49600 0
2 49700 0
3 49800 0
4 49900 0
5 50000 0
6 50100 0
7 50200 0
8 50300 0
9 50400 0
500500 1
11 rows selected.
--查询使用物化视图，导致结果出现异常。
SCOTT@test> drop materialized view mv_t;
Materialized view dropped.
SCOTT@test> with v as ( select 20 n from dual) select distinct v.n from v, t where v.n=t.n(+);
N
----------
20
