[20150715]Wrong Results.txt

[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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1735923/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1735923/