Search code examples
sqlsql-serversql-server-2014-express

UNION of non-nullable columns is nullable

Running MS SQL server 2014 express. Suddenly one of the columns in a view became nullable, despite being a union of two non-nullable columns. Please help me understand what's going on here:

enter image description here

and the UNION:

enter image description here

What am I missing?

Edit with more information: when I resave IncomingTransactions view, its column now becomes null, but it shouldn't be! Here's the definition of the Quantity column: (CASE PIN.StatusId WHEN 6 THEN PIN.QuantityReceived WHEN 7 THEN 0 ELSE PIN.QuantityRevised END) AS Quantity. Each of the quantity fields are non-null, and the case statement is exhaustive. The rest of the query is a simple join on the StatusId field, which is a non-null FK, so I'm still lost here.

Edit 2: based on YB's suggestions below, I created a minimal test case that reproduces this behaviour:

Create Table ybTest1 (Q1 decimal (7,2) not null, X int not null);
GO
Create View ybTestNSB As
Select (CASE X WHEN 0 THEN Q1 ELSE CAST(0 as decimal(7,2)) END) AS Q From ybTest1
GO

The Q column in the ybTest1 view is null, even though the case statement is exhaustive. Even if I wrap the 0 in the ELSE branch with CAST(0 as decimal(7,2)), as YB suggested, it's still null. Either CASE doesn't have the semantics I thought, or this is a bug.

Solution

  • Just about every column that is computed as a result of an expression is regarded as nullable in SQL Server. The workaround is to use ISNULL as you are doing. This is mentioned in the computed columns section here

    The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present ... An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

    but it applies wherever a column is derived as a result of a calculation, including in a view definition.

    There is little or no logic to analyse whether nulls are actually possible (sometimes more difficult than it seems as various deprecated set options can induce null rather than overflow errors so even 1 + X could produce a null in your example) and it errs on the side of caution. I don't see any way your case expression can output null in reality but in my experience pretty much any computed column will be treated as nullable except ones wrapped in isnull.

    So in your test case you could replace 

    Create View ybTestNSB As
Select (CASE X WHEN 0 THEN Q1 ELSE CAST(0 as decimal(7,2)) END) AS Q From ybTest1

    With 

    Create View ybTestNSB As
Select ISNULL(CASE X WHEN 0 THEN Q1 END, 0) AS Q From ybTest1

    To avoid having to put an annoying entirely redundant expression there.