检查 Null 值时的结果不一致(Jet DAO 与 ACE DAO) [英] Inconsistent results when checking for Null values (Jet DAO vs. ACE DAO)

问题描述

在访问 MDB 文件的 VB6 程序中，正在执行以下 SQL 查询:

In a VB6 program accessing an MDB file, the following SQL query is being executed:

> Select * FROM [table1] WHERE ([type] = 1 OR [type] = 2 OR [type] = 6)
> AND ([notes] = Null OR [notes] = '0') AND [date] >= 
> cvdate('09/03/2013') ORDER BY [date], [column2]

如果我在程序中引用 Microsoft Access 14.0 对象库，则返回的记录集有 0 行.

If I reference Microsoft Access 14.0 Object Library in the program the returned recordset has 0 rows.

如果我引用 Microsoft DAO 3.51 对象库，则返回的记录集有超过 100 行.

If I reference Microsoft DAO 3.51 Object Library the returned recordset has over 100 rows.

造成这种差异的原因是什么?两个提供者处理 Null 测试的方式有区别吗?这是 ACE DAO 访问旧 MDB 文件的重大更改吗?

What is the reason for this difference? Is there a difference between the way the two providers handles the test for Null? Is this a breaking change for ACE DAO accessing older MDB files?

推荐答案

WHERE ... [notes] = Null 是非标准 SQL.Null 传播 可能会强制任何涉及 Null 返回 Null.因此，表达式 [notes] = Null(您希望它是一个布尔表达式)很可能返回 Null，它既不是 True 也不是错误.

WHERE ... [notes] = Null is non-standard SQL. Null propagation can potentially force any expression involving Null to return Null. Therefore the expression [notes] = Null (which you intended to be a boolean expression) could very well return Null, which is neither True nor False.

查询处理器处理 Null 值的方式确实可能因一个数据库引擎而异:它可以将 Null 解释为 False，或者它可能会忽略结果，也可能会触发错误.另请注意，null 传播可能会将您的整个 WHERE 子句折叠为 Null if...

How the query processor handles that Null value may indeed differ from one database engine to another: it could interpret Null as False, or it could just ignore the result, or it could trigger an error. Note also that null propagation could collapse your entire WHERE clause to Null if...

(一些其他条件)AND(Null)

...评估为 Null.

标准 SQL 为 ([notes] IS NULL)，Jet/ACE 等效为 IsNull([notes]).这两个都将始终返回 True 或 False.

Standard SQL would be ([notes] IS NULL) and a Jet/ACE equivalent would be IsNull([notes]). Both of these will always return either True or False.

这篇关于检查 Null 值时的结果不一致(Jet DAO 与 ACE DAO)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！