6

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.

naasking
  • 2,514
  • 1
  • 27
  • 32
  • 1
    The weird part is Status get `not null`, probably depend on the data type. How did you create the view? I recomend work with two test tables, with all datatype as null and not null and see if can reproduce this behaviour – Juan Carlos Oropeza May 31 '16 at 17:00
  • 1
    How are you using the view? WHERE clause? Aggregates? Depending on your query, it's possible you have a record in one table that doesn't exist in the 2nd table, resulting in a null being used. Under the hood a UNION does a sort and merge to eliminate duplicates. If you want ALL the records, do a UNION ALL If you don't care about duplicates, UNION ALL is much faster. – JVC May 31 '16 at 17:37
  • @JuanCarlosOropeza, I'm guessing Type, Id, PartTypeId and Status are all key fields? Quantity, FiscalYearId, DateTransacted and Source aren't. – Clay May 31 '16 at 18:37
  • Thanks for the suggestions. UNION ALL has no effect. This schema has been in use for years and it just did this when I upgraded to SQL 2014 from SQL 2008 R2. IncomingTransactions and OutgoingTransactions are views over disjoint tables, so they have little in common. The Id field is the PK of the underlying tables, Type is a FK that both underlying tables have in common. Status is a string built from a StatusId FK which is different for each underlying table. The only nullable columns in underlying tables are FiscalYearId, DateTransacted. Everything else is non-null as reflected in the views. – naasking May 31 '16 at 19:04
  • @naasking: New clue there! I've seen union where two different types of columns result in the first one governing the result column...only to actually error when selecting from the composed view. Is there any possibility that either Ingoing or Outgoing are themselves composed from data that *might* be nullable (say...if/when a nullable-referring table is listed first)? I'm pretty sure SQL will go back to the sources underlying those views. – Clay May 31 '16 at 20:17
  • Differences in version are interesting, too...might set a back-level compatibility on the new server and see if you get back-level behavior. Never know if you're seeing a bug fixed or a new bug introduced :-) – Clay May 31 '16 at 20:18
  • Ok, 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. – naasking Jun 01 '16 at 13:45
  • Try to specify explicitly type for `0`: `CASE PIN.StatusId WHEN 6 THEN PIN.QuantityReceived WHEN 7 THEN CAST(0 As decimal(7,2)) ELSE PIN.QuantityRevised END` – Y.B. Jun 01 '16 at 14:25
  • @naasking, Something about the cast...I bet SQL can't determine the nullability. Just for fun, try taking the cast out of the expression. – Clay Jun 01 '16 at 16:07
  • Column didn't have a cast originally, but it's still nullable with or without the cast for the 0, as described in my reply to YB's detailed analysis below. – naasking Jun 01 '16 at 16:20
  • I added an edit with a minimal test case that reproduces the problem. It seems to be a problem with CASE statements specifically, not the UNION as I originally thought. – naasking Jun 01 '16 at 16:27
  • 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. – Martin Smith Jun 01 '16 at 16:29

1 Answers1

2

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • That seems to be the case Martin, but it's not [documented as such](https://msdn.microsoft.com/en-us/library/ms181765.aspx?f=255&MSPPError=-2147217396). Quote: "If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified." This is the same for simple and searched case statements. The ELSE clause is clearly not null in my examples. – naasking Jun 01 '16 at 16:35
  • @naasking yes, in reality your `case` expression can't return null. But there is little or no logic to analyse this. Pretty much any computed column will be treated as nullable except ones wrapped in isnull. – Martin Smith Jun 01 '16 at 16:38
  • 1
    OK, weird that it didn't infer NULL in 2008 R2 but is doing it in 2014. We double checked this just now, so it seems MSSQL 2014 is less precise than 2008. This answer will have to do, thanks. – naasking Jun 01 '16 at 16:40