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

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


20
0
Sign in
to vote
ID 581548 Comments
Status Closed Workarounds
Type Bug Repros 8
Opened 7/30/2010 4:25:45 AM
Access Restriction Public

Description

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 Newfangled Old-fashioned Stuff on 4/24/2015 at 9:57 AM
Confirmed to still be a bug in SQL 2008 (SP3) build 5869 (X64), as expected. (note that it closes the connection)

Confirmed to still be a bug in SQL 2008 R2 build 4321 (X64). (note that it closes the connection)

Confirmed to be FIXED in SQL 2012 build 5522 (X64)

Confirmed to be FIXED in SQL 2014 build 2480 (X64)
Posted by Paul White NZ on 11/5/2010 at 3:54 AM
Andrew,

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

Paul
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
Hi,
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