ITPub博客

首页 > 数据库 > Oracle > 诡异的insert as select与select结果集不一致问题

诡异的insert as select与select结果集不一致问题

原创 Oracle 作者:wxjzqym 时间:2012-03-19 09:33:45 0 删除 编辑
环境描述:
新库——OS:linux-64bit DB:10.2.0.4 CHARSET:ZHS16GBK
环境变量:NLS_LANG=american_america.ZHS16GBK
旧库——OS:AIX-64bit DB:10.2.0.3 CHARSET:ZHS16GBK
环境变量:NLS_LANG=american_america.ZHS16GBK(两边的dblink配置一致)
问题如下:以下这条SQL 新旧两个库跑出的结果不同 

在旧库中运行结果:插入表中的businesstype 都是正确的两位字符如2030这样;

在新库中运行结果:插入表中的businesstype 都是xx 但是只执行select 部分是没问题的,加上insert 插到表中 就有问题了。
原始SQL如下:

 insert into d_agent_t2

      (businesstype, agentcode, agentname, businesstypename)

      select case
       when t1.agenttype = '3' then  --这句话如果去点 点引号 直接写 agenttype = 3 就与原来的库运行结果一致。
                decode(t2.businesstype,
                       '1',
                       '41',
                       '2',
                       '42',
                       '3',
                       '43',
                       '4',
                       '44',
                       '45')
               when t1.agenttype in ('1', '6') then
                '20'
               when t1.agenttype = '2' then
                '30'
               when t1.agenttype in ('4', '5') then
                '50'
               else
                'xx'
             end as businesstype,
             t1.agentcode,
             t1.agentname,
             '其他' as businesstypename
        from PRPDAGENT@dbLNK_238 t1, sales.prpdagentunit@dblnk_238 t2
       where t1.agentcode = t2.agentcode(+) and rownum<=10;
表结构描述:

SQL> desc d_agent_t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AGENTCODE                                 NOT NULL VARCHAR2(12)
 AGENTNAME                                          VARCHAR2(100)
 BUSINESSTYPE                                       VARCHAR2(2)
 BUSINESSTYPENAME                                   VARCHAR2(40)

SQL> desc PRPDAGENT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AGENTCODE                                 NOT NULL VARCHAR2(12)
 AGENTNAME                                          VARCHAR2(120)
 AGENTTYPE                                          VARCHAR2(1)
 
SQL> desc prpdagentunit
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AGENTCODE                                 NOT NULL VARCHAR2(12)
 BUSINESSTYPE                                       VARCHAR2(2)
操作过程描述:

在旧库上的操作:

1.先单独执行select语句,结果如下:

BU AGENTCODE  AGENTNAME                                BUSI
-- ---------- ---------------------------------------- ----
45 113000034  北京朱山人汽车俱乐部有限公司             其他
30 112000016  北京中佳保险代理有限公司                 其他
30 112000017  北京普順保险代理有限公司                 其他
30 322000020  苏州金鹰保险代理有限公司                 其他
45 113000046  北京天之龙汽车销售服务有限公司           其他
45 113000047  北京圆明园东路汽车维修有限公司           其他
45 113000048  北京鑫广宝汽车服务有限公司               其他
45 113000049  北京德利亚汽车修理服务中心               其他
45 333000011  杭州东星票务有限公司                     其他
30 322000021  江苏环球保险代理有限公司南通分公司       其他

2.执行insert as select语句,结果如下:

10 rows created.

3.查询目标表验证结果:

SQL> select * from d_agent_t2;

AGENTCODE  AGENTNAME                                BU BUSINESSTY
---------- ---------------------------------------- -- ----------
113000034  北京朱山人汽车俱乐部有限公司             45 其他
112000016  北京中佳保险代理有限公司                 30 其他
112000017  北京普順保险代理有限公司                 30 其他
322000020  苏州金鹰保险代理有限公司                 30 其他
113000046  北京天之龙汽车销售服务有限公司           45 其他
113000047  北京圆明园东路汽车维修有限公司           45 其他
113000048  北京鑫广宝汽车服务有限公司               45 其他
113000049  北京德利亚汽车修理服务中心               45 其他
333000011  杭州东星票务有限公司                     45 其他
322000021  江苏环球保险代理有限公司南通分公司       30 其他
该sql在旧库上执行一切OK。

在新库上的操作:

1.先单独执行select语句,结果与旧库结果一致。

2.执行insert as select语句,语句也提示插入10条成功。

3.查询目标表验证结果,此时结果与旧库不一致。

AGENTCODE    AGENTNAME                                BU BUSINESSTY
------------ ---------------------------------------- -- ----------
113000034    北京朱山人汽车俱乐部有限公司             xx 其他
112000016    北京中佳保险代理有限公司                 xx 其他
112000017    北京普順保险代理有限公司                 xx 其他
322000020    苏州金鹰保险代理有限公司                 xx 其他
113000046    北京天之龙汽车销售服务有限公司           xx 其他
113000047    北京圆明园东路汽车维修有限公司           xx 其他
113000048    北京鑫广宝汽车服务有限公司               xx 其他
113000049    北京德利亚汽车修理服务中心               xx 其他
333000011    杭州东星票务有限公司                     xx 其他
322000021    江苏环球保险代理有限公司南通分公司       xx 其他

 4.将原始sql中 t1.agenttype = '3' 改为t1.agenttype = 3,执行新sql然后查询目标表,结果如下:

SQL> select * from d_agent_t2;

AGENTCODE    AGENTNAME                                BU BUSINESSTY
------------ ---------------------------------------- -- ----------
112000016    北京中佳保险代理有限公司                 30 其他
112000017    北京普順保险代理有限公司                 30 其他
322000020    苏州金鹰保险代理有限公司                 30 其他
113000046    北京天之龙汽车销售服务有限公司           45 其他
113000047    北京圆明园东路汽车维修有限公司           45 其他
113000048    北京鑫广宝汽车服务有限公司               45 其他
113000049    北京德利亚汽车修理服务中心               45 其他
333000011    杭州东星票务有限公司                     45 其他
322000021    江苏环球保险代理有限公司南通分公司       30 其他
发现新插入的10行是以正确的格式显示。

结论:当时自己认为最有可能的是Oracle bug,也怀疑过字符集和dblink的问题,不过还是一无所获。最后还请教了老熊,崔华等大牛最终定位问题。此问题确实是Oracle的一个bug,当sql语句中包含dblink和case子句时会触发这个bug——Bug 6952233:WRONG RESULTS WHEN CASE IS USED IN INSERT~SELECT VIA DBLINK”。
        解决方法:1.升级db到10.2.0.4

             2.使用convert函数封装case子句或者将decode替代case,问题解决。

这里最后要感谢老熊,崔华这样的大牛,他们这样的大忙人还肯一封一封的邮件回复我的问题,非常感谢!
 

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-15

  • 博文量
    100
  • 访问量
    457524