23

I have the below MERGE statement which is issued against the database:

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName"
       , "LocationId" = s."LocationId"
       , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

However, this causes the session to be terminated with the following error:

Msg 0, Level 11, State 0, Line 67 A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 67 A severe error occurred on the current command. The results, if any, should be discarded.

I have put a short test script together which produces the error:

USE master;
GO
IF DB_ID('TEST') IS NOT NULL
DROP DATABASE "TEST";
GO
CREATE DATABASE "TEST";
GO
USE "TEST";
GO

SET NOCOUNT ON;

IF SCHEMA_ID('MySchema') IS NULL
EXECUTE('CREATE SCHEMA "MySchema"');
GO

IF OBJECT_ID('MySchema.Region', 'U') IS NULL
CREATE TABLE "MySchema"."Region" (
"Id" TINYINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Region" PRIMARY KEY,
"Name" VARCHAR(8) NOT NULL CONSTRAINT "UK_MySchema_Region" UNIQUE
);
GO

INSERT [MySchema].[Region] ([Name]) 
VALUES (N'A'), (N'B'), (N'C'), (N'D'), (N'E'), ( N'F'), (N'G');

IF OBJECT_ID('MySchema.Location', 'U') IS NULL
CREATE TABLE "MySchema"."Location" (
"Id" SMALLINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Location" PRIMARY KEY,
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Location_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
"Name" VARCHAR(128) NOT NULL,
CONSTRAINT "UK_MySchema_Location" UNIQUE ("Region", "Name") 
);
GO

IF OBJECT_ID('MySchema.Point', 'U') IS NULL
CREATE TABLE "MySchema"."Point" (
"ObjectId" BIGINT NOT NULL CONSTRAINT "PK_MySchema_Point" PRIMARY KEY,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL CONSTRAINT "FK_MySchema_Point_Location" FOREIGN KEY REFERENCES "MySchema"."Location"("Id"),
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Point_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
CONSTRAINT "UK_MySchema_Point" UNIQUE ("Name", "Region", "LocationId")
);
GO

-- CONTAINS HISTORIC Point DATA
IF OBJECT_ID('MySchema.PointHistory', 'U') IS NULL
CREATE TABLE "MySchema"."PointHistory" (
"Id" BIGINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_PointHistory" PRIMARY KEY,
"ObjectId" BIGINT NOT NULL,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL,
"Region" TINYINT NOT NULL
);
GO

CREATE TYPE "MySchema"."PointTable" AS TABLE (
"ObjectId"      BIGINT          NOT NULL PRIMARY KEY,
"PointName"     VARCHAR(64)     NOT NULL,
"Location"      VARCHAR(16)     NULL,
"Region"        VARCHAR(8)      NOT NULL,
UNIQUE ("PointName", "Region", "Location")
);
GO

DECLARE @p1 "MySchema"."PointTable";

insert into @p1 values(10001769996,N'ABCDEFGH',N'N/A',N'E')

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName"
       , "LocationId" = s."LocationId"
       , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

If I remove the OUTPUT clause then the error does not occur. Also, if I remove the deleted reference then the error does not occur. So I looked at the MSDN documents for the OUTPUT clause which state:

DELETED cannot be used with the OUTPUT clause in the INSERT statement.

Which makes sense to me, however the entire point of MERGE is that you may not know in advance.

Additionally, the below script works perfectly fine regardless of the action that is taken:

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO 

Also, I have other queries that use the OUTPUT in the same fashion as the one that is throwing an error and they work perfectly fine - the only difference between them is the tables that take part in the MERGE.

This is causing major problems in production for us. I have reproduced this error in SQL2014 and SQL2016 on both VM and Physical with 128GB RAM, 12 x 2.2GHZ Cores, Windows Server 2012 R2.

The estimated execution plan generated from the query can be found here:

Estimated Execution Plan

  • 1
    Can the query generate an estimated plan? (Also, this won't shock a lot of people, but I recommend the old upsert methodology anyway - your MERGE doesn't have HOLDLOCK, for one, so it isn't immune from race conditions, and there are still other bugs to consider even after you resolve - or report - whatever is causing this issue.) – Aaron Bertrand Jun 9 '16 at 19:30
  • 1
    It gives a stack dump with an access violation. As far as I can see when unwinding the stack here i.stack.imgur.com/f9aWa.png You should raise this with Microsoft PSS if this is causing major problems for you. Specifically it seems to be deleted.ObjectId that is causing the problem. OUTPUT $action, inserted.*, deleted.Name, deleted.LocationId, deleted.Region works fine. – Martin Smith Jun 9 '16 at 23:29
  • 1
    Concur with Martin. In the meantime, see if you can avoid the issue by not using the MySchema.PointTable type, and just using a naked VALUES() clause, or #temp table, or table variable, inside the USING. Might help isolate contributing factors. – Aaron Bertrand Jun 10 '16 at 0:43
  • Thanks for your help guys, I tried using a temp table and the same error occurred. I will raise it with product support - in the meantime I rewrote the query to not use merge so that we could keep prod running. – Mr.Brownstone Jun 10 '16 at 2:19
20

This is a bug.

It is related to MERGE-specific hole-filling optimizations used to avoid explicit Halloween Protection and to eliminate a join, and how these interact with other update plan features.

There are details about those optimizations in my article, The Halloween Problem – Part 3.

The giveaway is the Insert followed by a Merge on the same table:

Plan fragment

Workarounds

There are several ways to defeat this optimization, and so avoid the bug.

  1. Use an undocumented trace flag to force explicit Halloween Protection:

    OPTION (QUERYTRACEON 8692);
    
  2. Change the ON clause to:

    ON s."ObjectId" = t."ObjectId" + 0
    
  3. Change the table type PointTable to replace the primary key with:

    ObjectID bigint NULL UNIQUE CLUSTERED CHECK (ObjectId IS NOT NULL)
    

    The CHECK constraint part is optional, included to preserve the original null-rejecting property of a primary key.

'Simple' update query processing (foreign key checks, unique index maintenance, and output columns) is complex enough to begin with. Using MERGE adds several additional layers to that. Combine that with the specific optimization mentioned above, and you have a great way to encounter edge-case bugs like this.

One more to add to the long line of bugs that have been reported with MERGE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.