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:
Best Answer
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:
Workarounds
There are several ways to defeat this optimization, and so avoid the bug.
Use an undocumented trace flag to force explicit Halloween Protection:
Change the
ON
clause to:Change the table type
PointTable
to replace the primary key with: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
.