Attempting to set a non-NULL-able column's value to NULL

Question

I'm experiencing a strange issue on a clients database when using the ISNULL() function of SQL Server 2014. Searching online has lead me to other people experiencing the same error but when INSERTing. I'm getting the following error when running a simple SELECT:

Msg 681, Level 16, State 3, Line 1
Attempting to set a non-NULL-able column's value to NULL.

The following query produces that error:

SELECT
    ISNULL( [t2].[date_update], GETDATE() )
FROM
    [dbo].[t1]
INNER JOIN
    [dbo].[t2] ON [t1].[a] = [t2].[id];

I've stripped down the tables to simplify my explanation, but the affected columns are defined as follows:

CREATE TABLE [dbo].[t1] (
    [id]          INT           NOT NULL PRIMARY KEY,
    [a]           NVARCHAR(100) NOT NULL
);

CREATE TABLE [dbo].[t2] (
    [id]          BIGINT        NOT NULL PRIMARY KEY,
    [date_update] DATETIME      NULL
);

Any help or insight is greatly appreciated!

Edit:

In troubleshooting I was able to reproduce the same error message on the clients system with the following INSERT:

CREATE TABLE #t2 ([id] BIGINT NOT NULL PRIMARY KEY NONCLUSTERED, [date_update] DATETIME NULL);

INSERT INTO #t2 ([id], [date_update)
SELECT [id], [date_update]
FROM [dbo].[t2];

Edit2:

More troubleshooting has indicated that the index the SELECT is using is at fault. When trying to rebuild the index I get the same error!

Rebuild failed for Index xxx
Attempting to set a non-NULL-able column's value to NULL.

The only thing that I can see is that you are comparing a text based field with an integer type. Perhaps the error message is just wrong. Are you sure that every instance of [a] can be converted to a BIGINT? SELECT [a] FROM [dbo].[t1] WHERE TRY_PARSE([a] AS BIGINT) IS NULL — Martin Soles, Oct 28 '15 at 13:42
Yes I thought the same, but this isn't the cause of the issue. All values in the NVARCHAR column can be implicitly converted to BIGINTs. — Chris Pickford, Oct 28 '15 at 13:44
Using your stripped down tables, what data can you put in those tables that will cause this error to reproduce? I suspect the error is caused by some part of the code you have stripped out. — Tab Alleman, Oct 28 '15 at 13:45
I've been trying to reproduce the error locally on my laptop but I'm unable to do so given any combination of data. I suspect it's an environment issue. — Chris Pickford, Oct 28 '15 at 13:48
I was just able to reproduce the error on the clients server in an alternative method - I will update the question. — Chris Pickford, Oct 28 '15 at 13:49
It could be environmental if you are doing an insert or update in any part of the code you have stripped out, but a SELECT can never try to set the value of a column in any environment that I'm aware of. — Tab Alleman, Oct 28 '15 at 13:49
check the answer here: http://stackoverflow.com/questions/26955418/why-is-there-the-error-attempting-to-set-a-non-null-able-columns-value-to-null?rq=1 — Tab Alleman, Oct 28 '15 at 13:53
@TabAlleman - Thanks but it's running SQL 2014 (12.0.2000.8) - Presuming all 2012 hotfixes would have been included in 2014. — Chris Pickford, Oct 28 '15 at 13:56
@ChrisPickford Prepare http://sqlfiddle.com I can't recreate your case — Lukasz Szozda, Oct 28 '15 at 13:58
I don't see a programming error here. If this is an environmental issue, you'd be more likely to get help with it on dba.stackexchange.com — Tab Alleman, Oct 28 '15 at 13:58
Is the error replicated when using `COALESCE()` instead of `ISNULL()`? — BeaglesEnd, Oct 28 '15 at 13:59
@TabAlleman I think you're right, see edit2 above, I think there may be corruption within one or more of the indexes. — Chris Pickford, Oct 28 '15 at 14:18
@TabAlleman Unfortunately it's the clustered index that is corrupted, somehow there exists a row containing nothing but NULLs, including in the NOT NULL columns. I suspect a disk error may have caused this or maybe corruption in backup transport. I'll be notifying the client that they have corruption in their database and back away slowly :) — Chris Pickford, Oct 28 '15 at 14:59
Anyone who found this while searching for the error text may be interested in a link explaining why this error can show up when merging data into a system-versioned temporal table that has a nonclustered index on the history table: https://stackoverflow.com/questions/70734060/why-does-a-merge-into-a-temporal-table-with-a-nonclustered-index-in-the-history — AHiggins, Oct 24 '22 at 11:52

score 4 · Accepted Answer · answered Oct 28 '15 at 15:04

The error message was being displayed due to the existence of a completely NULL row in a table containing NOT NULL column definitions.

The cause of the corruption is unknown in this case but the error can be explained where the SELECT query is creating a hash table in order to facilitate the join, it's trying to insert NULL into the primary key column.

This can be tested for by creating a table with the same definition but with all columns NULLable, inserting into it from the corrupted table, then querying for NULLs in the supposed NOT NULL columns.

score -1 · Answer 2 · answered Dec 30 '20 at 16:16

-1

sometimes when I call select * from sys.dm_exec_query_stats I get this error and I have no idea how to catch it. TRY CATCH doesn't catch this error.

answered Dec 30 '20 at 16:16

Germo Görtz

19
3

Welcome to Stack Overflow. This does not anwer the question. Consider opening a new question if you have a problem.To improve your answers, please read the [tour](https://stackoverflow.com/tour), and [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer). – ChristianB Dec 30 '20 at 16:37

Attempting to set a non-NULL-able column's value to NULL

2 Answers2