使用merge语句，合并远程数据库的数据时，得到非期望结果
适用于：
Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later [Release: 11.1 and later ]
在这个文件中的信息适用于任何平台。
症状
使用merge语句，合并远程数据库的数据时，得到错误的结果。
Test case :
-------------
--on SITEA:
create table test1 (name varchar2(10), id number, price number);
insert into test1 values ( 'aa', 1, 100);
insert into test1 values ( 'bb', 2, 200);
--On SITEB
create table test (name varchar2(10), id number, price number);
insert into test values ( 'xx', 2, 800);
merge into test1@sitea a using test b on (a.id=b.id)
when matched then
update set a.name = b.name where a.price != b.price
when not matched then
insert (name, id, price) values (b.name, b.id, b.price); 2 3 4 5
0 rows merged.
SQL> select * from test1@sitea;
NAME ID PRICE
---------- ---------- ----------
aa 1 100
bb 2 200
SQL> rollback;
Rollback complete.
SQL> select * from test1@rep11a;
NAME ID PRICE
---------- ---------- ----------
aa 1 100
bb 2 200
SQL> select * from test;
NAME ID PRICE
---------- ---------- ----------
xx 2 800
SQL> merge into test1@sitea a using test b on (a.id=b.id)
when matched then
update set a.name = b.name where a.price = b.price
when not matched then
insert (name, id, price) values (b.name, b.id, b.price); 2 3 4 5
1 row merged.
SQL> select * from test1@sitea;
NAME ID PRICE
---------- ---------- ----------
aa 1 100
xx 2 200
SQL> rollback;
Rollback complete.
--on SITEA:
select * from test1;
NAME ID PRICE
---------- ---------- ----------
aa 1 100
bb 2 200
SQL> merge into test1 a using test@siteb b on (a.id=b.id)
when matched then
update set a.name = b.name where a.price != b.price
when not matched then
insert (name, id, price) values (b.name, b.id, b.price); 2 3 4 5
1 row merged.
SQL> select * from test1;
NAME ID PRICE
---------- ---------- ----------
aa 1 100
xx 2 200
SQL> rollback;
Rollback complete.
SQL> select * from test@siteb;
NAME ID PRICE
---------- ---------- ----------
xx 2 800
SQL> merge into test1 a using test@siteb b on (a.id=b.id)
when matched then
update set a.name = b.name where a.price = b.price
when not matched then
insert (name, id, price) values (b.name, b.id, b.price); 2 3 4 5
0 rows merged.
SQL> select * from test1;
NAME ID PRICE
---------- ---------- ----------
aa 1 100
bb 2 200
原因
问题在于msq产生的远程的merge语句包含一个不正确的update set子句
对于上面的测试案例
在远程merge子句中的update set 子句，"where a.price != b.price"被判定生成为"where a2.price != a2.price”
如果你看到错误的别名在where子句中，你最有可能碰到这个bug：
BUG:9816678 - MERGE WITH CONDITION ON REMOTE DATABASE GOT DIFFERENT RESULT
解决方案
Issue is fixed in 12.1 .
Apply patch for
BUG:9816678 - MERGE WITH CONDITION ON REMOTE DATABASE GOT DIFFERENT RESULT
Workaround:
---------------
Avoid referencing remote objects in the merge condition.
References
BUG:9816678 - MERGE WITH CONDITION ON REMOTE DATABASE GOT DIFFERENT RESULT
相关信息显示
产品
* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-16 9:29:33 |
赞(
操作
