ms access - Inconsistent results when checking for Null values (Jet DAO vs. ACE DAO)

Question

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]

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

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

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?

Answer 1

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.

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...

(some other condition) AND (Null)

...evaluates to Null.

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.