Oracle 10.2.0.4的bug？

lastwinner · 发表于 2008-9-20 22:07

Test Case：
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
CREATE TABLE TestCode
(
  ID VARCHAR2(5),
  VAL  NUMBER(1)
);

select * from (
select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
from dual connect by rownum<=100
)
where  val>0;

insert into testcode select * from (
select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
from dual connect by rownum<=100
)
where  val>0;

select * from testcode;
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

运行一下，偶这里的情况是：

SQL> select * from (
2 select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
3 from dual connect by rownum<=100
4 )
5 where val>0;
ID VAL
---------- ----------
30 1
40 1
54 1
75 1
91 1
SQL> /
ID VAL
---------- ----------
1 1
3 1
5 1
13 1
22 1
24 1
29 1
37 1
38 1
54 1
60 1
ID VAL
---------- ----------
62 1
93 1
96 1
已选择14行。
插入到表中
SQL> insert into testcode select * from (
2 select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
3 from dual connect by rownum<=100
4 )
5 where val>0;
已创建6行。
查询一下看看
SQL> select * from testcode;
ID VAL
----- ----------
18 0
21 0
22 0
33 0
60 0
94 0
已选择6行。

复制代码

VAL值有问题，插入到表中的数据，VAL值应该都是1才对

似乎问题和调用dbms_random有关
请看下面的测试例子

SQL> select * from (select rownum+100 id, decode(sign(12-rownum),-1,1,0) val fro
m dual connect by rownum<20) where val>0;
ID VAL
---------- ----------
113 1
114 1
115 1
116 1
117 1
118 1
119 1
已选择7行。
SQL> insert into testcode select * from (select rownum+100 id, decode(sign(12-r
ownum),-1,1,0) val from dual connect by rownum<20) where val>0;
已创建7行。
SQL> select * from testcode;
ID VAL
----- ----------
18 0
21 0
22 0
33 0
60 0
94 0
113 1
114 1
115 1
116 1
117 1
ID VAL
----- ----------
118 1
119 1
已选择13行。

复制代码

系统环境：Windows XP SP2/Windows 2003 Server SP2下的Oracle10.2.0.4

[ 本帖最后由 lastwinner 于 2008-9-21 18:52 编辑 ]

lastwinner · 发表于 2008-9-20 22:12

问题的表现很像 Oracle 9201下connect by的bug
http://lastwinner.itpub.net/post/7102/46601
http://lastwinner.itpub.net/post/7102/46603

论坛讨论帖子在此
http://www.itpub.net/viewthread. ... light=%2Blastwinner

jack198409 · 发表于 2008-9-21 10:18

关注！

lastwinner · 发表于 2008-9-21 19:06

要是用 create table as ..........
就没事了

SQL> create table testcode2 as
  2  select * from (
  3  select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
  4  from dual connect by rownum<=100
  5  )
  6  where  val>0;

表已创建。

SQL> select * from testcode2;

      ID       VAL
---------- ----------
      13       1
      15       1
      23       1
      24       1
      54       1
      71       1
      83       1
      92       1
      96       1
      99       1

已选择10行。

nyfor · 发表于 2008-9-21 21:49

自个电脑上暂时没装 10G, 明天到公司试试, 我觉得应该是 Oracle 优化器的BUG
对SQL的执行计划做了在某种极端条件下不等价的变换.

nyfor · 发表于 2008-9-22 09:01

测试发现 insert 语句的结果中依然会包括 VAL=1 的结果, 请看:
SQL> set serverout on
SQL>
SQL> declare
  2 ln number;
  3 ln_cnt number;
  4  begin
  5 delete testcode;
  6 commit;
  7 for i in 1..20 loop
  8    insert into testcode
  9       select *
10       from (select rownum id,
11                      decode(sign(dbms_random.value - 0.9), -1, 0, 1) val
12                from dual
13             connect by rownum <= 100)
14       where val > 0;
15    select count(0),sum(val) into ln_cnt,ln from testcode;
16    rollback;
17    if ln > 0 then
18       dbms_output.put_line('Total Lines:' || ln_cnt || '  Val=1: ' || ln || ' line(s)');
19    end if;
20 end loop;
21  end;
22  /

Total Lines:7  Val=1: 1 line(s)
Total Lines:11  Val=1: 2 line(s)
Total Lines:8  Val=1: 1 line(s)
Total Lines:10  Val=1: 1 line(s)
Total Lines:14  Val=1: 2 line(s)
Total Lines:14  Val=1: 1 line(s)
Total Lines:8  Val=1: 1 line(s)
Total Lines:11  Val=1: 2 line(s)
Total Lines:7  Val=1: 1 line(s)
Total Lines:10  Val=1: 1 line(s)
Total Lines:11  Val=1: 2 line(s)
Total Lines:10  Val=1: 1 line(s)

PL/SQL procedure successfully completed

nyfor · 发表于 2008-9-22 09:04

这很大程度上说明, 在 INSERT 中的 where val > 0 的执行, 被替换成了 decode(sign(dbms_random.value - 0.9), -1, 0, 1) > 0
但是尚无证据表明, 不会使用 SQL Trace, 不知道这个能不能 Trace 出什么东东来.

nyfor · 发表于 2008-9-22 09:08

根据INSERT的资料来看:
ID VAL
----- ---
22    0
29    0
36    0
57    0
82    1
91    0

6 rows selected

从这个结果看: 基本上可以说明, 是 INSERT 语句中的 WHERE 筛选被改写为 decode(sign(dbms_random.value - 0.9), -1, 0, 1) > 0
导致的.

nyfor · 发表于 2008-9-22 09:14

但是又不完全等价于将 where 条件直接改写. 直接改写后的执行结果中出现要么有100条记录, 要么一条也没有.

nyfor · 发表于 2008-9-22 09:18

insert into testcode
  select *
from (select level id,
               decode(sign(dbms_random.value - 0.9), -1, 0, 1) val
         from dual
      connect by rownum <= 100)
where val > 0;
此语句中的 where val > 0 被改写成了 decode(sign(dbms_random.value - 0.9), -1, 0, 1) > 0,
但是这个改写后的条件是以
(select level id,
               decode(sign(dbms_random.value - 0.9), -1, 0, 1) val
         from dual
      connect by rownum <= 100)
结果集中的每一行记录都会执行一次 decode(sign(dbms_random.value - 0.9), -1, 0, 1) > 0 筛选.
如果直接将 where val > 0 改写成 decode(sign(dbms_random.value - 0.9), -1, 0, 1) > 0 的话, 那么这个 where 语句将只会执行一次.
难以琢磨, 呵呵...

[笔记] Oracle 10.2.0.4的bug？

浏览过的版块