Does SQL view display a column with a null value from a non-null table?

Question

I have a product table with non-zero columns "quantity" (decimal) and "status" (int), and I created a view in this table with the following case expression:

SELECT P.ProductTypeId,
       (CASE WHEN P.StatusId IN (5, 8) THEN 0 ELSE -P.Quantity END) AS Quantity,
       ...
FROM Product P

ProductTypeId is correctly deduced as nonzero. However, the Quantity column of this view is displayed as nullable, although the base columns are not null. That makes no sense to me.

I could use ISNULL / COALESCE to provide a default value in this case and force non-emptiness, but there is no meaningful default value, and this should not come primarily from what I understand. Any ideas what is going on?

Answer 1

The following is a description of the calculated columns in the table. I assume the same applies to computed columns in a view.

. , , . COLUMNPROPERTY AllowsNull . , , ISNULL (check_expression, ), nonnull .

, NULL,

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

WITH Product(Quantity,StatusId) As
(
SELECT -2147483648,1
)
SELECT (CASE WHEN P.StatusId IN (5, 8) THEN 0 ELSE -P.Quantity END) AS Quantity  
FROM Product P