Home Dashboard Directory Help
Search

MERGE statement bug when INSERT/DELETE used and filtered index by Vladimir Moldovanenko


Status: 

Active


23
0
Sign in
to vote
Type: Bug
ID: 596086
Opened: 9/8/2010 6:29:49 AM
Access Restriction: Public
0
Workaround(s)
view
5
User(s) can reproduce this bug

Description

MERGE statement will produce
Msg 2601, Level 14, State 1, Line 77
Cannot insert duplicate key row in object 'dbo.Test' with unique index 'UIXF_Test'.
The statement has been terminated.

use tempdb
go
/*
IF object_id('dbo.Test', 'U') IS NOT NULL
DROP TABLE dbo.Test
go
IF object_id('dbo.Src', 'U') IS NOT NULL
DROP TABLE dbo.Src
go
*/
IF object_id('dbo.Test', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Test
    (
        intID int NOT NULL IDENTITY PRIMARY KEY
        ,sysID int NOT NULL
        ,ioID int NOT NULL
        ,Code nvarchar(10) NOT NULL
    )
    INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')

    CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]
    (
         [sysID] ASC,
         [ioID] ASC
    )
    WHERE ([ioID] IN ((1)))
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [Primary]

    CREATE TABLE dbo.Src
    (
        intID int NOT NULL IDENTITY PRIMARY KEY
        ,sysID int NOT NULL
        ,ioID int NOT NULL
        ,Code nvarchar(10) NOT NULL
    )
    INSERT dbo.Src(sysID, ioID, Code) VALUES (10, 1, 'B')

END

DECLARE @sysID int = 1
    ,@ioID int = 1


BEGIN TRAN;

/*
WITH d AS
(
    SELECT *
    FROM dbo.Test d
    WHERE d.sysID = @sysID
    AND d.ioID = @ioID
)
MERGE INTO d
USING
(
    SELECT
         @sysID AS sysID
        ,@ioID AS ioID
        ,'B' as Code
) s
ON d.sysID = s.sysID
    AND d.ioID = s.ioID
    AND d.Code = s.Code
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT
    (sysID
    ,ioID
    ,Code)
VALUES
    (s.sysID
    ,s.ioID
    ,s.Code)
OUTPUT $action, inserted.*, deleted.*
;
*/

WITH d AS
(
    SELECT *
    FROM dbo.Test d
    WHERE d.sysID = @sysID
    AND d.ioID = @ioID
)
MERGE INTO d
USING
(
    SELECT
         @sysID AS sysID
        ,@ioID AS ioID
        ,Code
    FROM dbo.Src
) s
ON d.sysID = s.sysID
    AND d.ioID = s.ioID
    AND d.Code = s.Code
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT
    (sysID
    ,ioID
    ,Code)
VALUES
    (s.sysID
    ,s.ioID
    ,s.Code)
OUTPUT $action, inserted.*, deleted.*
;

IF @@TRANCOUNT > 0 ROLLBACK

If unique index is removed, then MERGE performs modification correctly

Forum discussion:

http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/32727b47-fd03-4033-8140-510876ccada2

Details
Sign in to post a comment.
Posted by Microsoft on 8/3/2011 at 10:54 AM
Hello Vladimir,

Sorry it took so long to respond on this item! I acknowledge that this is a bug in our code.

The fix for this bug requires changes that can introduce potential performance and correctness regressions so it will take some time before you will see this item as fixed.

Thank you and best regards,
Boris Baryshnikov.
SQL Server Engine
Posted by Vladimir Moldovanenko on 4/11/2011 at 7:49 AM
Are you still looking into this? Any feedback?
Posted by Microsoft on 12/6/2010 at 10:50 AM
Hi
thanks for reporting this issue with the Merge statement/Filtered index. We are currently looking into this and will provide updates when we have more information here.

SQL Server.
Sign in to post a workaround.