Search results matching tags 'Merge' and 'Bugs'

Incorrect Results with Indexed Views

Paul White — Tue, 05 Feb 2013 22:18:11 GMT

Summary: If you use MERGE, indexed views and foreign keys, your queries can return incorrect results.

Microsoft have released a fix for incorrect results returned when querying an indexed view. The problem applies to:

SQL Server 2012
SQL Server 2008 R2
SQL Server 2008

The Knowledge Base article does not go into much detail, or provide a reproduction script, but this blog entry does :)

The KB does say that reproducing the bug requires these features:

An indexed view on two tables that have a foreign key relationship
An update performed against the base tables
A query executed against the indexed view using a NOEXPAND hint

There are two important details I would like to add right away:

The NOEXPAND hint is not required to reproduce the bug on Enterprise Edition
The update must be performed by a MERGE statement

The fix is available in the following cumulative update packages:

Cumulative Update 2 for SQL Server 2012 SP1 [build 11.0.3339]
Cumulative Update 5 for SQL Server 2012 RTM [build 11.0.2395]
Cumulative Update 4 for SQL Server 2008 R2 SP2 [build 10.50.4270]
Cumulative Update 10 for SQL Server 2008 R2 SP1 [build 10.50.2868]
Cumulative Update 8 for SQL Server 2008 SP3 [build 10.00.5828]

No service pack contains this fix, you must apply one of the hotfix packages above.

Steps to Reproduce

The first thing we will need is two tables:

     CREATE TABLE dbo.Parent 

    (

        parent_id integer IDENTITY NOT NULL,

        value varchar(20) NOT NULL,

        CONSTRAINT PK_Parent_id 

            PRIMARY KEY CLUSTERED (parent_id)

    );

    GO

    CREATE TABLE dbo.Child

    (

        child_id integer IDENTITY NOT NULL,

        parent_id integer NOT NULL,

        CONSTRAINT PK_Child_id 

            PRIMARY KEY CLUSTERED (child_id)

    );

And a few rows of data:

    INSERT dbo.Child (parent_id)

    SELECT New.parent_id 

    FROM 

        (

        INSERT Parent 

        OUTPUT inserted.parent_id 

        VALUES 

            ('Apple'), 

            ('Banana'), 

            ('Cherry')

        ) AS New;

The tables now look like this (parent first):

We can now add the required FOREIGN KEY relationship:

    ALTER TABLE dbo.Child

    ADD CONSTRAINT FK_Child_Parent

    FOREIGN KEY (parent_id)

    REFERENCES dbo.Parent (parent_id);

Next, a very simple indexed view that joins the two tables (the view could contain more complex features like aggregates):

    CREATE VIEW dbo.ParentsAndChildren

    WITH SCHEMABINDING 

    AS

    SELECT 

        p.parent_id, 

        p.value, 

        c.child_id

    FROM dbo.Parent AS p

    JOIN dbo.Child AS c ON 

        c.parent_id = p.parent_id;

    GO

    CREATE UNIQUE CLUSTERED INDEX cuq 

    ON dbo.ParentsAndChildren (child_id);

The final step is to use a MERGE statement to make some changes to the Parent table:

    DECLARE @ParentMerge AS TABLE

    (

        parent_id integer PRIMARY KEY, 

        value varchar(20) NOT NULL

    );

    INSERT @ParentMerge

        (parent_id, value)

    VALUES

        (1, 'Kiwi Fruit'),

        (4, 'Dragon Fruit');

    MERGE dbo.Parent AS p

    USING @ParentMerge AS s ON 

        s.parent_id = p.parent_id

    WHEN MATCHED THEN 

        UPDATE SET value = s.value

    WHEN NOT MATCHED THEN 

        INSERT (value) VALUES (s.value)

    OUTPUT 

        $action, 

        inserted.parent_id, 

        deleted.value AS old_value, 

        inserted.value AS new_value;

This MERGE performs two actions:

Updates the value column of parent row 1 from Apple to Kiwi Fruit
Adds a new parent row 4 for Dragon Fruit

The statement includes an OUTPUT clause to show the changes it makes (this is not required for the repro):

This confirms that the changes have been made as we requested: parent row 1 has changed; and row 4 has been added. The changes are reflected in the base tables:

    SELECT * FROM dbo.Parent AS p;

    SELECT * FROM dbo.Child AS c;

As highlighted, row 1 has changed from Apple to Kiwi Fruit and row 4 has been added.

We do not expect to see row 4 in the indexed view because there are no child records for that row, and the indexed view uses an inner join. Checking the indexed view using the NOEXPAND table hint (required in non-Enterprise SKUs to use indexes on a view):

    SELECT *

    FROM dbo.ParentsAndChildren 

    WITH (NOEXPAND);

The results are incorrect. They show the old value of the data for parent row 1.

Now we try using the EXPAND VIEWS query hint to force SQL Server to access the base tables rather than reading view indexes:

    SELECT *

    FROM dbo.ParentsAndChildren

    OPTION (EXPAND VIEWS);

This query produces correct results.

On SQL Server Enterprise Edition, the optimizer chooses whether to access the indexed view or the base tables. For following query, without any hints, the optimizer chooses not to expand the view. It reads the view index and produces incorrect results:

    -- Enterprise Edition ONLY

    SELECT * 

    FROM dbo.ParentsAndChildren;

Perhaps adding a child row to match the new parent row 4 will somehow fix things up?

    INSERT dbo.Child (parent_id) VALUES (4);

    GO

    SELECT * FROM dbo.ParentsAndChildren WITH (NOEXPAND);

    SELECT * FROM dbo.ParentsAndChildren OPTION (EXPAND VIEWS);

No. The query plan that accesses the view index still returns an incorrect value for row 1. It seems MERGE has corrupted our indexed view.

Analysis using DBCC CHECKTABLE

Checking the view with DBCC CHECKTABLE returns no errors:

    DBCC CHECKTABLE (ParentsAndChildren);

Unless we use the EXTENDED_LOGICAL_CHECKS option:

    DBCC CHECKTABLE (ParentsAndChildren) WITH EXTENDED_LOGICAL_CHECKS;

The damage is repairable:

    ALTER DATABASE Sandpit 

    SET SINGLE_USER 

    WITH ROLLBACK IMMEDIATE;

    GO

    DBCC CHECKTABLE (ParentsAndChildren, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS;

    GO

    DBCC CHECKTABLE (ParentsAndChildren) WITH EXTENDED_LOGICAL_CHECKS;

    GO

    ALTER DATABASE Sandpit SET MULTI_USER;

You probably do not want to set your database to SINGLE_USER mode and run a DBCC repair after every MERGE statement, however. We could also rebuild the indexed view’s clustered index manually, of course.

Cause

For the MERGE statement above, the query optimizer builds a plan that does not update the indexed view (click to enlarge):

In a version of SQL Server with the fix applied, the same MERGE statement produces a plan that does maintain the indexed view:

The plan operators used to keep the view index in step with the base tables are highlighted. Without these operators, the changes to the base table are not correctly written to any indexes defined on the view. The root cause is related to the same simplification that allows the optimizer to remove the reference to the Parent table in this query:

    SELECT 

        COUNT_BIG(*)

    FROM dbo.Parent AS p 

    JOIN dbo.Child AS c ON 

        c.parent_id = p.parent_id;

The FOREIGN KEY relationship and NOT NULL constraints on the referencing column together mean that the join to Parent cannot affect the result of the query, so the join is simplified away. In SQL Server 2012, we can see when this simplification is performed because the following message appears when undocumented trace flags 8619 and 3604 are enabled during compilation:

The same message is emitted when a MERGE statement contains a WHEN MATCHED THEN UPDATE clause and either a WHEN NOT MATCHED THEN INSERT or WHEN MATCHED … THEN DELETE clause. These conditions combine such that the optimizer incorrectly concludes that a table reference can be removed, when in fact it is needed later on when the update side of the plan is built.

Other details of the query and database can affect whether the simplification can be misapplied. For example, if the FOREIGN KEY constraint contains an ON DELETE CASCADE clause, and the MERGE contains a DELETE clause, the simplification is not performed, the TF 8619 message does not appear, and the bug does not manifest.

The key to determining whether a particular query is vulnerable to this bug (TF 8619 aside) is to check whether the query plan includes operators to maintain the indexed view. At a minimum, you should see a update operator for the view:

SQL Sentry Plan Explorer identifies the operator as applying to a view explicitly, in SSMS you need to click on the graphical operator and inspect the Properties window.

Summary

The updated conditions for incorrect results are:

An indexed view that joins tables
Two of the tables have a single-column FOREIGN KEY constraint
A MERGE statement contains an UPDATE action that affects one of the tables
The MERGE statement also contains an INSERT or DELETE action (or both)
The optimizer applies a simplification that removes a table reference based on the FK relationship and other metadata
As a result, the MERGE execution plan does not contain the operators necessary to correctly maintain the indexed view
A subsequent query plan accesses an index on the view, either explicitly or via indexed-view matching (Enterprise Edition)

Note:

The simplification is not applied in tempdb
The simplification is not applied to multi-column foreign key constraints

Under these conditions, the indexes on the view do not reflect the state of the base tables and incorrect results are returned. Once the hot fix is applied, the optimizer does not misapply the simplification so the correct indexed view maintenance features are built into execution plans.

Update: I am adding this based on Ian Yates’ great question in the comments: my expectation is that applying this hotfix will not remove any existing indexed view corruption. You would need to test the hotfix as usual, apply it, and then either rebuild all affected indexed views manually, or run DBCC CHECKDB with the EXTENDED_LOGICAL_CHECKS option (which could take a long time).

Twitter: @SQL_Kiwi
Email: SQLKiwi@gmail.com

MERGE Bug with Filtered Indexes

Paul White — Sun, 09 Dec 2012 17:54:00 GMT

A MERGE statement can fail, and incorrectly report a unique key violation when:

The target table uses a unique filtered index; and
No key column of the filtered index is updated; and
A column from the filtering condition is updated; and
Transient key violations are possible

Example Tables

Say we have two tables, one that is the target of a MERGE statement, and another that contains updates to be applied to the target. The target table contains three columns, an integer primary key, a single character alternate key, and a status code column. A filtered unique index exists on the alternate key, but is only enforced where the status code is ‘a’:

     CREATE TABLE #Target 

    (

        pk          integer NOT NULL, 

        ak          character(1) NOT NULL,

        status_code character(1) NOT NULL,

        PRIMARY KEY (pk)

    );

    CREATE UNIQUE INDEX uq1

    ON #Target (ak)

    INCLUDE (status_code)

    WHERE status_code = 'a';

The changes table contains just an integer primary key (to identify the target row to change) and the new status code:

    CREATE TABLE #Changes

    (

        pk          integer NOT NULL,

        status_code character(1) NOT NULL,

        PRIMARY KEY (pk)

    );

Sample Data

The sample data for the example is:

    INSERT #Target 

        (pk, ak, status_code)

    VALUES 

        (1, 'A', 'a'),

        (2, 'B', 'a'),

        (3, 'C', 'a'),

        (4, 'A', 'd');

    INSERT #Changes

        (pk, status_code)

    VALUES

        (1, 'd'),

        (4, 'a');

         Target                     Changes
╔════╦════╦═════════════╗    ╔════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣
║ 1 ║ A ║ a           ║    ║ 1 ║ d           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ a           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝
║ 4 ║ A ║ d           ║
╚════╩════╩═════════════╝

The target table’s alternate key (ak) column is unique, for rows where status_code = ‘a’. Applying the changes to the target will change row 1 from status ‘a’ to status ‘d’, and row 4 from status ‘d’ to status ‘a’. The result of applying all the changes will still satisfy the filtered unique index, because the ‘A’ in row 1 will be deleted from the index and the ‘A’ in row 4 will be added.

Merge Test One

Let’s now execute a MERGE statement to apply the changes:

    MERGE #Target AS t

    USING #Changes AS c ON 

        c.pk = t.pk

    WHEN MATCHED 

        AND c.status_code <> t.status_code 

        THEN UPDATE SET status_code = c.status_code;

The MERGE changes the two target rows as expected. The updated target table now contains:

╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣
║ 1 ║ A ║ d           ║ <—changed from ‘a’
║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║
║ 4 ║ A ║ a           ║ <—changed from ‘d’
╚════╩════╩═════════════╝

Merge Test Two

Now let’s repopulate the changes table to reverse the updates we just performed:

    TRUNCATE TABLE #Changes;

    INSERT #Changes

        (pk, status_code)

    VALUES

        (1, 'a'),

        (4, 'd');

This will change row 1 back to status ‘a’ and row 4 back to status ‘d’. As a reminder, the current state of the tables is:

         Target                        Changes
╔════╦════╦═════════════╗    ╔════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣
║ 1 ║ A ║ d           ║    ║ 1 ║ a           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ d           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝
║ 4 ║ A ║ a           ║
╚════╩════╩═════════════╝

We execute the same MERGE statement:

    MERGE #Target AS t

    USING #Changes AS c ON 

        c.pk = t.pk

    WHEN MATCHED 

        AND c.status_code <> t.status_code 

        THEN UPDATE SET status_code = c.status_code;

However this time we receive the following message:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#Target' with unique index 'uq1'. The duplicate key value is (A).
The statement has been terminated.

Applying the changes using UPDATE

Let’s now rewrite the MERGE to use UPDATE instead:

    UPDATE t

    SET status_code = c.status_code

    FROM #Target AS t

    JOIN #Changes AS c ON

        t.pk = c.pk

    WHERE

        c.status_code <> t.status_code;

This query succeeds where the MERGE failed. The two rows are updated as expected:

╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣
║ 1 ║ A ║ a           ║ <—changed back to ‘a’
║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║
║ 4 ║ A ║ d           ║ <—changed back to ‘d’
╚════╩════╩═════════════╝

What went wrong with the MERGE?

In this test, the MERGE query execution happens to apply the changes in the order of the ‘pk’ column.

In test one, this was not a problem: row 1 is removed from the unique filtered index by changing status_code from ‘a’ to ‘d’ before row 4 is added. At no point does the table contain two rows where ak = ‘A’ and status_code = ‘a’.

In test two, however, the first change was to change row 1 from status ‘d’ to status ‘a’. This change means there would be two rows in the filtered unique index where ak = ‘A’ (both row 1 and row 4 meet the index filtering criteria ‘status_code = a’).

The storage engine does not allow the query processor to violate a unique key (unless IGNORE_DUP_KEY is ON, but that is a different story, and doesn’t apply to MERGE in any case). This strict rule applies regardless of the fact that if all changes were applied, there would be no unique key violation (row 4 would eventually be changed from ‘a’ to ‘d’, removing it from the filtered unique index, and resolving the key violation).

Why it went wrong

The query optimizer usually detects when this sort of temporary uniqueness violation could occur, and builds a plan that avoids the issue. I wrote about this a couple of years ago in my post Beware Sneaky Reads with Unique Indexes (you can read more about the details on pages 495-497 of Microsoft SQL Server 2008 Internals or in Craig Freedman’s blog post on maintaining unique indexes). To summarize though, the optimizer introduces Split, Filter, Sort, and Collapse operators into the query plan to:

Split each row update into delete followed by an inserts
Filter out rows that would not change the index (due to the filter on the index, or a non-updating update)
Sort the resulting stream by index key, with deletes before inserts
Collapse delete/insert pairs on the same index key back into an update

The effect of all this is that only net changes are applied to an index (as one or more insert, update, and/or delete operations). In this case, the net effect is a single update of the filtered unique index: changing the row for ak = ‘A’ from pk = 4 to pk = 1. In case that is less than 100% clear, let’s look at the operation in test two again:

         Target                     Changes                   Result
╔════╦════╦═════════════╗    ╔════╦═════════════╗    ╔════╦════╦═════════════╗
║ pk ║ ak ║ status_code ║    ║ pk ║ status_code ║    ║ pk ║ ak ║ status_code ║
╠════╬════╬═════════════╣    ╠════╬═════════════╣    ╠════╬════╬═════════════╣
║ 1 ║ A ║ d           ║    ║ 1 ║ d           ║    ║ 1 ║ A ║ a           ║
║ 2 ║ B ║ a           ║    ║ 4 ║ a           ║    ║ 2 ║ B ║ a           ║
║ 3 ║ C ║ a           ║    ╚════╩═════════════╝    ║ 3 ║ C ║ a           ║
║ 4 ║ A ║ a           ║                            ║ 4 ║ A ║ d           ║
╚════╩════╩═════════════╝                            ╚════╩════╩═════════════╝

From the filtered index’s point of view (filtered for status_code = ‘a’ and shown in nonclustered index key order) the overall effect of the query is:

Before           After
╔════╦════╗    ╔════╦════╗
║ pk ║ ak ║    ║ pk ║ ak ║
╠════╬════╣    ╠════╬════╣
║ 4 ║ A ║    ║ 1 ║ A ║
║ 2 ║ B ║    ║ 2 ║ B ║
║ 3 ║ C ║    ║ 3 ║ C ║
╚════╩════╝    ╚════╩════╝

The single net change there is a change of pk from 4 to 1 for the nonclustered index entry ak = ‘A’. This is the magic performed by the split, sort, and collapse. Notice in particular how the original changes to the index key (on the ‘ak’ column) have been transformed into an update of a non-key column (pk is included in the nonclustered index). By not updating any nonclustered index keys, we are guaranteed to avoid transient key violations.

The Execution Plans

The estimated MERGE execution plan that produces the incorrect key-violation error looks like this (click to enlarge in a new window):

The successful UPDATE execution plan is (click to enlarge in a new window):

The MERGE execution plan is a narrow (per-row) update. The single Clustered Index Merge operator maintains both the clustered index and the filtered nonclustered index. The UPDATE plan is a wide (per-index) update. The clustered index is maintained first, then the Split, Filter, Sort, Collapse sequence is applied before the nonclustered index is separately maintained.

There is always a wide update plan for any query that modifies the database. The narrow form is a performance optimization where the number of rows is expected to be relatively small, and is not available for all operations. One of the operations that should disallow a narrow plan is maintaining a unique index where intermediate key violations could occur.

Workarounds

The MERGE can be made to work (producing a wide update plan with split, sort, and collapse) by:

Adding all columns referenced in the filtered index’s WHERE clause to the index key (INCLUDE is not sufficient); or
Executing the query with trace flag 8790 set e.g. OPTION (QUERYTRACEON 8790).

Undocumented trace flag 8790 forces a wide update plan for any data-changing query (remember that a wide update plan is always possible). Either change will produce a successfully-executing wide update plan for the MERGE that failed previously.

Conclusion

The optimizer fails to spot the possibility of transient unique key violations with MERGE under the conditions listed at the start of this post. It incorrectly chooses a narrow plan for the MERGE, which cannot provide the protection of a split/sort/collapse sequence for the nonclustered index maintenance.

The MERGE plan may fail at execution time depending on the order in which rows are processed, and the distribution of data in the database. Worse, a previously solid MERGE query may suddenly start to fail unpredictably if a filtered unique index is added to the merge target table at any point.

Connect bug filed here

Bug reproduced on the following SQL Server versions (all x64 Developer Edition):

2012 SP1 CUI (build 11.0.3321 – November 2012)
2008 R2 SP2 CU3 (build 10.50.4266 – October 2012)
2008 SP3 CU8 (build 10.0.5828 – November 2012)

Twitter: @SQL_Kiwi
Email: SQLkiwi@gmail.com

An Interesting MERGE Bug

Paul White — Tue, 03 Aug 2010 17:30:00 GMT

Summary: Investigating an optimiser transformation that exposes a bug in SQL Server’s MERGE implementation.

I came across a Connect item today (by fellow SQL Server Central member ‘ALZDBA’) describing how using a combination of relatively new features can produce incorrect results or even an access violation inside SQL Server.

Reproducing the bug

We’ll look first at how MERGE can produce incorrect an output. We’ll need two tables: one that contains two rows of existing data; and a second that contains four rows of change information. The overall process looks like this:

This is a classic MERGE (or “upsert”) requirement. If the row already exists, the new value is added to it, otherwise a new row is inserted. Here’s the code to generate the sample tables and data:

DECLARE @Target
TABLE   (
        row_id  INTEGER NOT NULL PRIMARY KEY,
        value   INTEGER NOT NULL
        );
 
DECLARE @Delta
TABLE   (
        row_id  INTEGER NOT NULL PRIMARY KEY,
        delta   INTEGER NOT NULL
        );
 
-- Existing records (1 & 3)
INSERT  @Target
        (row_id, value)
VALUES  (1, 1),
        (3, 3);
 
-- Change table:
-- Updates rows 1 & 3
-- Inserts rows 2 & 4
INSERT  @Delta
        (row_id, delta)
VALUES  (1, 10),
        (2, 20),
        (3, 30),
        (4, 40);

We can easily write a MERGE query to perform the actions needed. Let’s also include an OUTPUT clause to show the effects on each row:

MERGE   @Target T
USING   @Delta D
        ON  D.row_id = T.row_id
WHEN    MATCHED THEN
        UPDATE SET T.value += D.delta
WHEN    NOT MATCHED BY TARGET THEN 
        INSERT VALUES (D.row_id, D.delta)
OUTPUT  D.row_id,
        $action,
        DELETED.value AS old_value,
        INSERTED.value AS new_value;

If we examine the @Target table after executing this query, we get exactly the results shown in the diagram. All good so far. The problem comes when we look at the rows produced by the OUTPUT clause:

When the action is ‘INSERT’, the new_value column contains an incorrect value. Instead of reporting the value actually inserted into the target table, it repeats the value associated with the last UPDATE operation. If we change the data so that multiple INSERTs are performed after the UPDATE, all the INSERTs get the same new_value associated with the prior UPDATE. The @Target table always contains the correct data – it’s just that the OUTPUT clause disagrees.

The Query Plan

The original Connect item states that this issue only affects table variables, but there’s more to see here. Let’s start by taking a look at the execution plan for the sample MERGE statement above (click to enlarge):

I have omitted several Compute Scalar iterators to make it easier to see what’s going on. There’s a scan of the @Delta table, followed by an Insert to the @Target table, and finally a Merge into the @Target table. If you are familiar with the sort of plans MERGE normally produces, you might find that arrangement a bit odd. Even if MERGE is new to you, you might think that an Insert followed by a Merge is surprising.

In a normal MERGE, we would expect to see an outer join between the source and target tables, followed by a Merge:

SQL Server performs an outer join so the Clustered Index Merge knows whether the current row already exists in the target table or not. If it exists, it is updated, otherwise a new row is inserted.

If you’ve been following my recent series on optimiser internals, you might suspect that the optimiser has applied a transformation to get the join-less plan, and the two plans are somehow logically identical. You would be right – the rule in question is LOJPrjGetToApply (Left Outer Join, Project Get to Apply).

The Optimisation

The idea behind the optimisation is to read a row from the @Delta table and immediately try to insert it into the @Target table. If that succeeds, the row didn’t already exist, and no further work is required.

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, which performs the necessary UPDATE.

This is a very useful (and perfectly safe) query optimisation if the conditions are right. There are rather a lot of conditions, the most important of which are:

The target of the merge must have a suitable clustered unique or primary key constraint
The changes (delta) table must have some sort of unique constraint (can be non-clustered) on the join column
The plan must be already using a nested loops join
The MERGE must include a NOT MATCHED condition plus at least one MATCHED clause
Equivalent columns in the two tables must have exactly the same type – no implicit conversions allowed
There can be no chance of a constraint violation – including NOT NULL constraints
The query must not update the clustered key

If any of the required conditions do not apply, the optimisation is not performed, and the query plan retains the outer join.

In order for the MERGE to return incorrect results:

The MERGE target must be a temporary table (the delta table can be anything)
The optimiser must produce a final plan that includes the transformation described above
The MERGE statement must include an OUTPUT clause that references the inserted pseudo table
The query has to perform an INSERT row action after an UPDATE (in clustered index order)

Importantly, it is only rows that result in an INSERT that cause problems. If only UPDATE or DELETE operations result from the MERGE, everything works perfectly – even with a table variable as the target.

Producing an Access Violation

The example data given for the @Delta table was carefully constructed to ensure that incorrect results were returned. With different sample data, we will get an Access Violation (AV) which terminates the connection and produces a stack dump on the SQL Server.

The simplest way (based on the test rig above) is to change the @Delta table contents to just include a single row that will result in an INSERT:

INSERT  @Delta
        (row_id, delta)
VALUES  (2, 2);

There is no row in @Target with row_id = 2, so this results in an INSERT.

To expand our test rig to encompass DELETE operations, we need to add an extra condition to the original MERGE statement. The new code will delete a record if its value is zero after applying the deltas:

MERGE   @Target T
USING   @Delta D
        ON  D.row_id = T.row_id
WHEN    MATCHED             -- New --
AND     T.value = -D.delta  -- New --
THEN    DELETE              -- New --
WHEN    MATCHED THEN
        UPDATE SET T.value += D.delta
WHEN    NOT MATCHED BY TARGET THEN 
        INSERT VALUES (D.row_id, D.delta)
OUTPUT  D.row_id,
        $action,
        DELETED.value AS old_value,
        INSERTED.value AS new_value;

With that modification in place, the following values in the @Delta table will update the row with row_id = 1, and delete the one with row_id = 3:

INSERT  @Delta
        (row_id, delta)
VALUES  (1, 10),
        (3, -3);

This combination works correctly because there is no INSERT operation. Any number of UPDATEs and DELETEs can be performed without issue, so long as no INSERTs occur.

Workarounds

The easiest workarounds involve preventing the query optimisation from happening in the first place. This is quite easy since there are so many conditions for it to apply. One way is to prevent the plan from using a nested loops join with an OPTION (HASH JOIN, MERGE JOIN) hint. There are many other alternatives in the same vein, of course.

A second workaround is to apply Trace Flag 8758 – unfortunately this disables a number of optimisations, not just the one above, so it’s not really recommended for long term use.

Third, we could turn off the LOJPrjGetToApply rule (again, see my previous optimiser posts) but that could also negatively affect other plans that benefit from the optimisation.

Final Thoughts

This isn’t a bug that will affect everyone, but it does show that your chances of turning up a bug increase as you push the limits of the optimiser and fairly new features like MERGE.

It will be interesting to see how the Connect item goes: will Microsoft fix this, or will they just say that MERGE was never intended for use with table variables? We’ll have to wait and see.

My thanks to ‘ALZDBA’ for reporting this problem. Personally, I see the prospect of returning incorrect data from the OUTPUT clause as more serious than the access violation. The OUTPUT clause is often used to record audit information, so producing incorrect data with no error seems undesirable. I would encourage you all to form your own view on this bug, and vote on it accordingly.