【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 thenupdate 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 thenupdate 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 thenupdate 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_TESTadd constraint KL_TEST_PK primary key (A, B, C, D)3 ;Table altered.CREATE TABLE KL_TEST_1 NOLOGGING PCTFREE 0 ASSELECT * FROM KL_TEST3 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.aand tmp.b = trgt.band tmp.c = trgt.cand tmp.d = trgt.dand tmp.e = trgt.eand tmp.f = trgt.f)WHEN NOT MATCHED THENINSERT (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(*)
----------0SYS@ora10g> SELECT /*+ FULL (KL_TEST)*/ COUNT(*) FROM KL_TEST;COUNT(*)
----------3SYS@ora10g> select * from kl_test;A B C D E F
---------- ---------- ---------- ---------- ---------- ----------1 1 1 1 1 11 1 1 1 1 11 2 1 2 1 2SYS@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 ENABLED7 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 11 1 1 1 1 11 2 1 2 1 21 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 許可協議。轉載請注明出處
)
