Home Dashboard Directory Help

SQL2008 R2 Merge statement with only table variables fails. by ALZDBA


 as Fixed Help for as Fixed

Sign in
to vote
Type: Bug
ID: 581548
Opened: 7/30/2010 4:25:45 AM
Access Restriction: Public
User(s) can reproduce this bug


A merge statement that merges rows from two table variables fails.
(Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.)

The same statement using a #temp table or regular table will succeed.

All my databases are on dblevel 100.

Tested with:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1720.0 (Intel X86) Jun 12 2010 01:43:49 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1702.0 (Intel X86) May 7 2010 15:24:21 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1720.0 (X64) Jun 12 2010 01:34:59 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

Sign in to post a comment.
Posted by Paul White NZ on 11/5/2010 at 3:54 AM

Will the bug fix allow the optimization for table variables, or block it?

Posted by Microsoft on 11/4/2010 at 1:17 PM
The problem is that we were trying to apply a particular optimization that doesn't work with table variables. The bug has been fixed for the next release.

You can work around the issue by disabling the unique key constraint on your table variable; that will disable the faulty optimization. If that is not acceptable, and you need the full fix now, please contact customer support.

Andrew Richardson
Developer, SQL Server Query Optimizer.
Posted by Paul White NZ on 9/3/2010 at 5:44 AM
Blogged: http://sqlblog.com/blogs/paul_white/archive/2010/08/04/another-interesting-merge-bug.aspx
Posted by Microsoft on 8/27/2010 at 12:29 PM
Thanks for reporting the issue. You have basically hit an access violation in the server for your example. This error condition also generates a dump file in the LOG directory of the instance. We will investigate the issue and let you know what we find.

Umachandar, SQL Programmability Team
Posted by ALZDBA on 8/6/2010 at 1:40 AM
Fellow SSC member Paul White (NZ) blogged about it and pointed to a much worse issue if Merge is used with the OUTPUT clause, the output clause may provide WRONG data.

You can find his analysis at http://sqlblog.com/blogs/paul_white/archive/2010/08/04/another-interesting-merge-bug.aspx
Sign in to post a workaround.
Posted by Paul White NZ on 11/5/2010 at 3:53 AM
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.

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, but that could also negatively affect other plans that benefit from the optimisation.