Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

merge in SQL2008 SP2 still suffers from "Attempting to set a non-NULL-able column's value to NULL"

A merge statement that only has a when not matched by target then insert ... can still fail in SQL2008 SP2 where as the equivalent insert statement with source left outer join target works

0 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 15

<=-=Apr 5 2011 10:28AM=-=>

Thanks for taking the time to file this issue.
It may be an occurrence of an issue that was fixed in SP1 CU7.

That update is described and can be requested here:
http://support.microsoft.com/kb/979065

The item is third from the bottom:
409769 981037 (http://support.microsoft.com/kb/981037/ ) FIX: “Attempting to set a non-NULL-able column’s value to NULL” error message when you run an INSERTSELECT statement that uses the ISNULL() function on a temp table in SQL Server 2008

Use of ISNULL.

Campbell, SQL Development

<=-=Apr 6 2011 6:40AM=-=>

Thanks for the reply.

We are running SP2, which by definition includes the fix from SP1 CU7.

Since this has been reproduced in SP2 there is a scenario in the fix for SQL 2008’s T-SQL “merge” statement which still fails.

<=-=Apr 7 2011 4:29PM=-=>

Oops yes, you said SP2. Is there an identity column in the target table?

Thank-you.

<=-=Apr 8 2011 10:09AM=-=>

Neither table has an identity column.

My guess would be that the query planner has decided to select the column target.datetime2_3_notnull as being the column used in the outer join test “is null” test since the column target.binary4null could be null.

<=-=Apr 11 2011 5:12PM=-=>

I am having difficulty reproducing the problem. If possible, could you add the data to the bug, or at least the sizes of the two tables and the proportion of rows satisfying the ON clause?

If this is not feasible, could you please attach the query plan that hits the error message?

Thank you.

Andrew Richardson
Developer, SQL Server Query Optimizer.

<=-=Apr 14 2011 6:18AM=-=>

Our situation occures intermitantly, it will be working fine the suddenly stop – one have a single auto increment ID field, which is the clustered key there are a few other index’s some covering. All fields ( approx 30) are nullable – but it has several forigne keys to other tables ( about 8 ) . which become a left smi join in the query plan to the right of an assert – i’m removing the foreign keys as a test,

The Merge On clause is only on the ID column, none of the rows in the source will exist, the same captured SQL that doesn’t work will eventually just start working.

I’m happy to supply any details i can.

<=-=Apr 21 2011 12:20PM=-=>

The next time the problem occurs, please run the following statements:

set showplan_xml on
go

[Your failing merge statement]

set showplan_xml off
go

Save the resulting XML into a file and attach it to the bug.

Given the intermittent nature of the bug, it probably has to do with plan choice. We may be including a sort or spool in the plan, and mislabelling one of the columns as non-nullable. As your data changes, we recompile the plan. The new plan may have no sort/spool at all, or it may place the sort/spool some place where the column is truly non-nullable, or it may not need to include the bad column in the sort/spool.

Assuming that this explanation is correct, you can also work around the problem by “freezing” the plan that doesn’t hit the error. See the documentation for sp_create_plan_guide_from_handle.

Andrew Richardson
Developer, SQL Server Query Optimizer.

<=-=Apr 24 2011 2:54PM=-=>

We have a re-run scheduled for the next agile sprint (May 2011)

Is there a direct (non-public) feedback mechanism for this data to forwarded to Microsoft?

<=-=Apr 25 2011 8:58AM=-=>

You can email it to me at andric@microsoft.com.

Andrew Richardson
Developer, SQL Server Query Optimizer.

<=-=May 5 2011 2:15PM=-=>

Has a solution been found for this? We are experiencing a similar issue on our installation as well.
Product: Microsoft SQL Server Web Edition (64-bit)
Version: 10.50.1600.1

The problem does not appear to be intermittent. It happens every time I execute a specific query. We have had to stop using merge queries as a result.

<=-=Aug 4 2011 2:38PM=-=>

I too am experiencing this issue from within a Merge Statement.
Product: Microsoft SQL Server Enterprise Edition (64-bit) (and Developer Edition)
Version: 10.50.1600.1

I also do not receive the error intermittently, but happens every time a specific query is run. We are using a UDF (user-defined function) in the VALUES list of the INSERT portion of the MERGE statement. If I gather the results of the function in a variable before running the MERGE statement, then the query runs successfully.

I can work around the issue in this instance, but I’m concerned about using MERGE in new code since it may still have bugs.

<=-=May 16 2012 12:19AM=-=>

I still have this issue in SQL server 2008 SP3, and if i disable the foreign key constraints it works without the error. Is the bug solved in a CU and then re introduced in SP3?

The issue only seem to appear when we don’t have that many records.

<=-=Feb 15 2013 2:06AM=-=>

What is the status of this error? We are still experiencing this issue in SQL Server 2008 SP3. Is there any workaround? The issue is getting critical for us in our production system.

/H�kan Winther

<=-=Feb 15 2013 2:09AM=-=>

We are using a CTE and/or temptable in the MERGE statements that sometimes fails, can this be a part of the problem?

<=-=Mar 19 2013 7:54AM=-=>

The table that I try to update with MERGE is having some non-clustered indexes that are partitioned, and if I remove the partitioning of the indexes, the MERGE statement succedes. Why?

<=-=Jul 31 2013 9:35AM=-=>

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. Thanks again for reporting the product issue and continued support in improving our product.

0 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base