SQL 视图从非空表推断可为空列？

发布于 2024-11-07 19:34:15 字数 395 浏览 1 评论 0原文

我有一个包含非空“数量”（十进制）和“状态”（整数）列的 Product 表，并使用以下 case 表达式在此表上创建了一个视图：

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

ProductTypeId 被正确推断为非空。但是，此视图的 Quantity 列被推断为可为空，即使基础列不可为空。这对我来说没有任何意义。

在这种情况下，我可以使用 ISNULL/COALESCE 提供默认值并强制不可为空，但没有有意义的默认值，根据我的理解，这首先不应该发生。有什么想法吗？

原文

I have a Product table with non-null "quantity" (decimal) and "status" (int) columns, and I created a view on 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 inferred as non-null. However, the Quantity column of this view is inferred as nullable, even though the underlying columns are not nullable. This doesn't make any sense to me.

I could use ISNULL/COALESCE to provide a default value in this case and force non-nullability, but there is no meaningful default value, and this shouldn't happen in the first place from what I understand. Any ideas what's going on?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

千年*琉璃梦 2024-11-14 19:34:15

以下说明适用于表中的计算列。我想这同样适用于视图中的计算列。

数据库引擎自动
确定计算的可空性
基于所使用的表达式的列。
大多数表达式的结果是
即使只有
存在不可为空的列，
因为可能出现下溢或
溢出将产生空结果，如下所示
出色地。使用 COLUMNPROPERTY 函数
与AllowsNull属性
研究任何的可空性
表中的计算列。一个
可为空的表达式可以是
变成一个不可空的
指定 ISNULL(check_expression,
常数），其中常数是
非空值替换任何空值
结果。

表达式可能返回 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

The below explanation is for computed columns in a table. I imagine the same applies to computed columns in a view.

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,
because possible underflows or
overflows will produce null results as
well. Use the COLUMNPROPERTY function
with the AllowsNull property to
investigate the nullability of any
computed column in a table. 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.

An example where your expression could return NULL is

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

回复收藏 0 原文

~没有更多了~