none
Is this a bug in MERGE statement with DELETE/INSERT? RRS feed

  • Question

  • This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2.

    Note that the first commneted statement works properly but the second does not.

    Is there any explanation why not, other than a bug?

    Duplication script below.

    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

     

     

    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.

    Wednesday, September 8, 2010 12:52 PM

All replies

  • Hi,

    Please refer this:

    http://sqlblog.com/blogs/paul_white/archive/2010/08/04/another-interesting-merge-bug.aspx

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Wednesday, September 8, 2010 1:20 PM
  • Vladimir,

    At first look, it looks like a bug, at least to me. But I have a question for you, why are you joining [d] and [s] by the three columns (sysID, ioID, Code) when there is no unique or primary key constraint in either tables by these columns?

    If you apply any of these changes, then it succed.

    1 - Add column [Code] to the filtered index.

    2 - Exclude column [Code] from the join expression.

     


    AMB

    Some guidelines for posting questions...

    Wednesday, September 8, 2010 1:58 PM
    Moderator
  • Thanks for your reply.

    Actual table where thsi problem was discovered does contain 2 unique filtered indexes and identity primary key. Unique indexes enforce ceratain conditions that differ based on ioID  value. Underlining data is mapping data where code value can be mapped for import and export (ioID mode). Requirements differ for import from export.

    In my situation, adding code to indexes is not approapriate.

    Excluding comun does work for me too, THANKS. Maybe that is what I would do instead of DELETE followed by INSERT.

    Yet, this MERGE should work the same with literal set and data set from other table.

    Wednesday, September 8, 2010 2:23 PM
  • correction. Excluding column does NOT work. It does not produce error but does not do modifications. OUTPUT is empty
    Wednesday, September 8, 2010 2:27 PM
  •  Reddy Balaji

    I think this case is slighly different. According to this post, this happens...

    "If the row did exist, the PRIMARY KEY on @Target will cause a duplicate key violation to occur.  The relational engine suppresses that error, and execution continues on down to the Clustered Index Merge iterator..."

    but in my case, it looks like a violation of UNIQUE FILTERED INDEX that is not suppressed.

    Also, using OPTION clause to avoid nested loop does not work.

    Wednesday, September 8, 2010 3:19 PM
  • correction. Excluding column does NOT work. It does not produce error but does not do modifications. OUTPUT is empty


    I am having trouble following what you are trying to do. What about if you have more than one row in table [Src], the derived table you are using as the source will yield duplicated rows by (sysID, ioID) if those rows match ioID = 1.

    INSERT dbo.Src(sysID, ioID, Code) VALUES (10, 1, 'B');
    INSERT dbo.Src(sysID, ioID, Code) VALUES (20, 1, 'C');

    declare @sysID int = 1, @ioID int = 1; 

    select @sysID as sysID, @ioID as ioID, Code
    from dbo.Src;
    GO

    Result:

    1, 1, 'B'
    1, 1, 'C'

    If (1, 1) matches a row in [Test] then which one do we want to survive form [Src] 'B' or 'C'. MERGE will yield an error if you try to delete or update same row more than once.

    If we have just one row in [Src], then this works. I am excluding column [Code] from the join, but I added "when matched and d.Code <> s.Code" to the merge.

    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
    CREATE TABLE dbo.Test
    (
    intID int NOT NULL IDENTITY PRIMARY KEY
    ,sysID int NOT NULL
    ,ioID int NOT NULL
    ,Code nvarchar(10) NOT NULL
    );
    GO
    INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A');
    GO
    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]
    GO
    CREATE TABLE dbo.Src
    (
    intID int NOT NULL IDENTITY PRIMARY KEY
    ,sysID int NOT NULL
    ,ioID int NOT NULL
    ,Code nvarchar(10) NOT NULL
    );
    GO
    INSERT dbo.Src(sysID, ioID, Code) VALUES (10, 1, 'B');
    --INSERT dbo.Src(sysID, ioID, Code) VALUES (20, 1, 'C');
    GO
    DECLARE @sysID int = 1, @ioID int = 1;
    
    WITH d AS
    (
    SELECT intID, sysID, ioID, Code
    FROM dbo.Test
    WHERE sysID = @sysID AND 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
    WHEN NOT MATCHED BY SOURCE THEN
      DELETE
      
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (sysID, ioID, Code)
      VALUES (s.sysID, s.ioID, s.Code)
      
    WHEN MATCHED AND d.Code <> s.Code THEN
      UPDATE SET d.Code = s.Code
      
    OUTPUT $action, inserted.*, deleted.*;
    GO
    SELECT * FROM dbo.Test;
    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
    
    

    Now uncomment insert statement that will insert another row into [Src], and run the script again. You will get an error because the derived table referencing [Src] now yields two rows with same values for (sysID, ioID).

    Anyway, it looks to me that you are trying to delete any row in [Test] matching the value of the variables, and then inserting the new row from [Src] but using the values from the variables for [sysID] and [ioID]. Can you elaborate more about what you are trying to accomplish and if possible, post more sample data.

     


    AMB

    Some guidelines for posting questions...

    Wednesday, September 8, 2010 3:38 PM
    Moderator
  • OK, my code duplicated an error and it is a test code. In reality, my code is more complicated.

    For a given sysID and ioID, there is a sorce data set and target data set. I want source data set to override target in one statement.

    I can do DELETE FROM dbo.test WHERE sysID = @sysID and ioID = @ioID then INSERT ... SELECT ... FROM dbo.Src WHERE sysID = @sysID and ioID = @ioID

    Yet, all this is irrelevent. Comment out unique filtered index and you will see it working, with no error. if index is back it is not working but it should.

    MERGE should act on data sets. I am using CTE to extablish update scope. My ON condition is on all relevent fields.  My source is some source, restricted or not. I don't need to update anything: DELETE and INSERT in one statement is what I am trying to accomplish.

    Your reduction of ON conditions does cause "more than one row" affected condition. But with full ON, it should not.

    See below.

    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

    CREATE TABLE dbo.Test

    (

    intID int NOT NULL IDENTITY PRIMARY KEY

    ,sysID int NOT NULL

    ,ioID int NOT NULL

    ,Code nvarchar(10) NOT NULL

    );

    GO

    INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A');

    GO

    --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]

    --GO

    CREATE TABLE dbo.Src

    (

    intID int NOT NULL IDENTITY PRIMARY KEY

    ,sysID int NOT NULL

    ,ioID int NOT NULL

    ,Code nvarchar(10) NOT NULL

    );

    GO

    INSERT dbo.Src(sysID, ioID, Code) VALUES (10, 1, 'B');

    INSERT dbo.Src(sysID, ioID, Code) VALUES (20, 1, 'C');

    GO

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

     

    WITH d AS

    (

    SELECT intID, sysID, ioID, Code

    FROM dbo.Test

    WHERE sysID = @sysID AND 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)

     

    --WHEN MATCHED AND d.Code <> s.Code THEN

    --  UPDATE SET d.Code = s.Code

     

    OUTPUT $action, inserted.*, deleted.*;

    GO

    SELECT * FROM dbo.Test;

    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

     

     

    Wednesday, September 8, 2010 4:18 PM