【ORACLE】记录一些ORACLE的merge into语句的BUG
一、自相矛盾-DML重启动行为差异,违反acid原则
发现版本：10g ~ 23ai
这个用例在我之前的文章里有提过，ORACLE和PG系关于并发事务行为有一个非常大的差异，就是ORACLE在某些并发冲突的场景下会进行DML重启动，但是对比下面两个例子，可以发现无论采取何种事务一致性实现逻辑，在read commit下，最终的结果无外乎得到(1,‘Alice’)、(10,‘Alice’)、(1,‘Tom’)三种其一，但ORACLE的merge into 却得到了(10,‘Tom’)这一理论不应该存在的记录。
用例一：
drop table test_dml_restart;
create table test_dml_restart(id number ,name varchar2(10));
insert into test_dml_restart values (1,'Alice');
commit;
--会话一，执行后不提交
update test_dml_restart set id=10 where name='Alice';
--会话二执行，被锁
update test_dml_restart set name='Tom' where id=1;
--会话一提交，会话二自动解锁，更新0行，会话2根据id=1进行了重启动,找不到id=1的记录
commit;
--会话二查询
select * from test_dml_restart;
10,Alice
用例二：
drop table test_dml_restart;
create table test_dml_restart(id number ,name varchar2(10));
insert into test_dml_restart values (1,'Alice');
commit;
--会话一，执行后不提交
update test_dml_restart set id=10 where name='Alice';
--会话二执行，被锁
merge into test_dml_restart t1
using (select * from dual) t2
on (t1.id=1)
when matched then
update set t1.name='Tom';
--会话一提交，会话二自动解锁，更新1行，会话2仍然按旧的id找到了数据进行更新
commit;
--会话二查询
select * from test_dml_restart;
10,Tom
在asktom上也有人发现过，但从10g到目前最新的23ai，这个问题一直未修复
https://asktom.oracle.com/ords/f?p=100:11:::NO::P11_QUESTION_ID:61865893444475
merge and write consistency
Andrey N. Edemsky, May 04, 2006 - 3:51 pm UTC
其实这个问题在ORACLE里通过改写SQL规避，就是把on里的条件，复制一份到update后面的where里
merge into test_dml_restart t1
using (select * from dual) t2
on (t1.id=1)
when matched then
update set t1.name='Tom' where t1.id=1;
二、百密一疏-触发ORA600的外关联
发现版本：11g 12c 18c 19c
create table test1 (col1 number,col2 number);
create table test2 (col1 number,col2 number);
insert into test1 values (1,1);
merge into test2
using test1 on (test1.col1=test2.col1(+))
when matched then
update set test2.col2=test1.col2;
ORA-00600: 内部错误代码, 参数: [25027], [3], [3], [0], [0], [0], [1], [1], [], [], [], []
虽然这个sql本身的确不对，但是ORACLE报错却是ORA-600这个ORACLE自己没有估计到的报错。
19.20及最新的23ai版本已修复这个问题，在23ai中该SQL执行结果是merge 0行。
三、自欺欺人-不能更新on中引用的列
版本：9i ~ 23ai
详见这篇 【ORACLE】你以为的真的是你以为的么?–ORA-38104: Columns referenced in the ON Clause cannot be updated
这个是个ORACLE故意做的限制，本身可以说不算BUG，但是通过特殊方式能绕过这个限制，就可以算个BUG了。所以ORACLE要么承认这个设计是BUG，要么承认开发遗漏了场景也还是BUG，总归有一边是BUG。
merge into test_merge_a a
using test_merge_b b
on (a.id=b.id)
when matched then
update set id=2;
ORA-38104: 无法更新 ON 子句中引用的列: "A"."ID"
--改写
merge into test_merge_a a
using test_merge_b b
on ((a.id=b.id or 1=2) and (a.id=b.id or 1=2 ))
when matched then
update set a.id=2;
Plan Hash Value : 4101543598
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 13 | 4 | 00:00:01 |
| 1 | MERGE | TEST_MERGE_A | | | | |
| 2 | VIEW | | | | | |
| * 3 | HASH JOIN | | 1 | 76 | 4 | 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST_MERGE_B | 1 | 32 | 2 | 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST_MERGE_A | 1 | 44 | 2 | 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("A"."ID"="B"."ID")
以上是较新版本中需要注意一些问题。
在早期版本中已经修复过的就不全部列举了，MOS上搜merge into的BUG大把大把的，翻了N页没见底，什么更新视图、分区表、并行、并发、行归档、dblink、压缩表等等有一堆功能和merge into组合使用有问题的，这里只写几个简单的merge into语句有问题的场景
四、无中生有-源数据为空的结果集也能匹配上
版本：11.2.0.2.0
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:9537112000346374938
https://asktom.oracle.com/ords/f?p=100:11:::NO::P11_QUESTION_ID:61865893444475
Same guy - Correction
GPU, August 26, 2013 - 5:11 pm UTC
create table test_merge_null(col1 varchar2(20) not null);
MERGE INTO test_merge_null
USING (SELECT dummy
FROM DUAL
WHERE 1 = 2) s
ON (1 = 2)
WHEN NOT MATCHED
THEN
INSERT (col1)
VALUES (s.dummy);
这是11.2.0.2.0 中的 bug，已在 11.2.0.3.0 中修复。还好，11.2.0.2.0这个版本用得少。
五、一山能容二虎-突破主键唯一限制
发现版本：10.2.0.1.0
通过direct path write，能突破主键限制进行插入，使表中数据违反有效的唯一约束，案例来自：
https://blog.csdn.net/jackpk/article/details/3788143
create table KL_TEST
(
a NUMBER(15) not null,
b NUMBER(15) not null,
c NUMBER(15) not null,
d NUMBER(15) not null,
e NUMBER(15) not null,
f NUMBER(15) not null
)
;
10 ;
Table created.
alter table KL_TEST
add constraint KL_TEST_PK primary key (A, B, C, D)
3 ;
Table altered.
CREATE TABLE KL_TEST_1 NOLOGGING PCTFREE 0 AS
SELECT * FROM KL_TEST
3 WHERE 1=2;
Table created.
SYS@ora10g> INSERT INTO KL_TEST_1 VALUES (1,1,1,1,1,1);
1 row created.
SYS@ora10g> INSERT INTO KL_TEST_1 VALUES (1,2,1,2,1,2);
1 row created.
SYS@ora10g> INSERT INTO KL_TEST_1 VALUES (1,1,1,1,1,1);
1 row created.
SYS@ora10g> commit;
Commit complete.
MERGE /*+ APPEND */ INTO KL_TEST trgt
USING
(select fct.a,fct.b,fct.c,fct.d,fct.e,fct.f from KL_TEST_1 fct) tmp
ON
(tmp.a = trgt.a
and tmp.b = trgt.b
and tmp.c = trgt.c
and tmp.d = trgt.d
and tmp.e = trgt.e
and tmp.f = trgt.f)
WHEN NOT MATCHED THEN
INSERT (a,b,c,d,e,f)
13 VALUES (tmp.a,tmp.b,tmp.c,tmp.d,tmp.e,tmp.f);
3 rows merged.
SYS@ora10g> commit;
Commit complete.
SYS@ora10g> SELECT COUNT(*) FROM KL_TEST;
COUNT(*)
----------
0
SYS@ora10g> SELECT /*+ FULL (KL_TEST)*/ COUNT(*) FROM KL_TEST;
COUNT(*)
----------
3
SYS@ora10g> select * from kl_test;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1
1 1 1 1 1 1
1 2 1 2 1 2
SYS@ora10g> select CONSTRAINT_NAME,CONSTRAINT_TYPE, STATUS from dba_constraints where table_name ='KL_TEST';
CONSTRAINT_NAME C STATUS
------------------------------ - --------
SYS_C005398 C ENABLED
SYS_C005397 C ENABLED
SYS_C005396 C ENABLED
SYS_C005395 C ENABLED
SYS_C005394 C ENABLED
SYS_C005393 C ENABLED
KL_TEST_PK P ENABLED
7 rows selected.
SYS@ora10g> insert into kl_test values (1,1,1,1,1,1);
1 row created.
SYS@ora10g> select * from kl_test;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1
1 1 1 1 1 1
1 2 1 2 1 2
1 1 1 1 1 1
在10.2.0.5.0和11.2.0.4版本中已经没这个问题了
总结
ORACLE从很早的版本就增加了merge into这一语法，虽然给开发人员带来了很大的便利性，但是其BUG数之多非常恐怖，很多BUG从发现到修复跨越了数十年的好几个大版本，甚至还有些BUG一直延续到了最新的23ai版本。国产数据库的开发人员远没有ORACLE那么多，ORACLE都做成这样了，国产数据库要是说merge into里没有BUG我是不相信的。
- 本文作者： DarkAthena
- 本文链接： https://www.darkathena.top/archives/oracle-merge-into-some-bug
- 版权声明： 本博客所有文章除特别声明外，均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处
