MERGE Stmt : Search condition on a non-key column and an ORDER BY in source dervied table breaks MERGE completely - by Meiyalagan

Status : 

 


4
0
Sign in
to vote
ID 539084 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 3/4/2010 10:58:40 AM
Access Restriction Public

Description

Team,

I struggled for quite some time to troubleshoot this issue when i had a ORDER BY clause in my source table (Derived table) and the column i used in search condition (ON condition) doesn't have a unique constriant/index the MERGE simply breaks. I have to say that because i have observed really strange behaviours For example : "Attempting to set a non-NULL-able column's value to NULL.", some times it throws a similar error message with a column which is nullable, it runs fine if i remove the ORDER BY , it runs fine if reduce the length of a nvarchar field in my source table ! etc.,

But the good news is i have managed to create a simple repro so that you can re produce it in-house and see what's going wrong.

The re-pro i have created for you produces the error only when the source table doesn't have a PK but in my actual scneario i do have a PK but it was failing with the same error and could fix it by creating unique index on the column i used on the search condition.

Thanks
Mei
meiyab@microsoft.com
Sign in to post a comment.
Posted by Newfangled Old-fashioned Stuff on 4/24/2015 at 10:04 AM
Confirmed to be FIXED in SQL 2008 build 5869 (X64)

Confirmed to be FIXED in SQL 2008 R2 build 4321 (X64)

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

Confirmed to be FIXED in SQL 2014 build 2480 (X64)
Posted by Microsoft on 3/30/2010 at 6:24 PM
Mei,

We had a recent QFE (409769) which effectively fixes this problem. (We mark all computed columns in sorts and other work tables as nullable, even if the compiler thinks that they aren’t nullable.

Please see if this addresses your concern

Tha ks
Sunil
Posted by Meiyalagan on 3/11/2010 at 10:33 PM
Glad to hear that it has been fixed already and avaialble in next release. I'm going ahead with the workaround as of now just that it was driving me crazy for quite somtime on troubleshooting.

Thanks a lot !

Mei
Posted by Microsoft on 3/10/2010 at 10:07 AM
Thanks for submitting this issue Mei. We had also stumbled on this issue and this has been fixed in the next release for SQL Server. Since you have a work around, I assume this is not a blocking issue for you for now?

thanks
Sunil