Why does this MERGE statement cause the session to be killed?

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

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:

OPTION (QUERYTRACEON 8692);

-
Change the ON clause to:

ON s."ObjectId" = t."ObjectId" + 0

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

StackExchange Database Administrators Q#140880, answer score: 22