Merge Bug 진짜 당했다..

http://sqlblog.com/blogs/paul_white/archive/2010/08/04/another-interesting-merge-bug.aspx
http://www.sqlperformance.com/2013/02/t-sql-queries/another-merge-bug

Tip on using caution with MERGE - Aaron Bertrand
http://www.mssqltips.com/sqlservertip/3074/tip-on-using-caution-with-merge/ 

Bug는 언제나 존재한다... but 나에게 올줄이야~ ㅡ.ㅡ

-- ===================================================================
-- Merge Test
-- Update : 
-- Update Date : 2013.09.02
-- Desc : Merge를 사용하는 SQL에 이상 현상 발견 
--
--		http://www.sqlperformance.com/2013/02/t-sql-queries/another-merge-bug
		http://sqlblog.com/blogs/paul_white/archive/2010/08/04/another-interesting-merge-bug.aspx

	

-- ===================================================================
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

DECLARE @Target
TABLE   (
        row_id  INTEGER NOT NULL PRIMARY KEY,
        value   INTEGER NOT NULL
        );
 
DECLARE @Delta
TABLE   (
        row_id  INTEGER NOT NULL PRIMARY KEY,
        delta   INTEGER NOT NULL
        );
 
-- Existing records (1 & 3)
INSERT  @Target
        (row_id, value)
VALUES  (1, 1),
        (3, 3),
		(5, 3);

 
 -- 비정상 수행
MERGE   @Target T
USING  ( SELECT * FROM @Target WHERE row_id = 2  ) AS D (  row_id, delta )	-- ##. ROW COUNT가 0일 경우(NULL) 
        ON  D.row_id = T.row_id
WHEN    MATCHED THEN
        UPDATE SET T.value += D.delta
WHEN    NOT MATCHED THEN 
        INSERT VALUES (D.row_id, D.delta)
OUTPUT  D.row_id,
        $action,
        DELETED.value AS old_value,
        INSERTED.value AS new_value;

SELECT * FROM @Target;

-- 정상 수행
MERGE   @Target T
USING  ( SELECT 2, 3   ) AS D (  row_id, delta ) 
        ON  D.row_id = T.row_id
WHEN    MATCHED THEN
        UPDATE SET T.value += D.delta
WHEN    NOT MATCHED THEN 
        INSERT VALUES (D.row_id, D.delta)
OUTPUT  D.row_id,
        $action,
        DELETED.value AS old_value,
        INSERTED.value AS new_value;

SELECT * FROM @Target;