Query NULL comparison gives wrong result (bug)

        3 votes: ***       5,789 views      0 comments
by Allen Browne, 20 April 2005    (All versions of Access)

Flaws in Microsoft Access

Provided by Allen Browne, October 2004. Bug reported by Kai Langosch.


Comparison gives wrong result

The query engine in Access yields the wrong results if you use the inequality operator to compare anything to Null.

This flaw has is present in JET 4 (Access 2000 and later), JET 3 (Access 95 and 97), JET 2 (Access 2), and was not tested in Access 1 or 1.1. In earlier versions, the example query must be a little different (needs a source table), but the bug is still present.

Demonstrating the flaw

Compare anything to Null, and the result should be Null. Since Null effectively means Unknown, the result of the comparison is Unknown. To check for yourself, open the Immediate Window (Ctrl+G), and enter:
    ? (4 <> Null)
VBA correctly yields Null, but the query engine in Access gets it wrong.

To see the error in Access 2000 and later:

  1. Create a new query. No table is needed.
  2. Switch the query to SQL View (SQL View on View menu).
  3. Paste in this statement:
        SELECT (4 <> Null) AS Expr1;
  4. Run the query.

The query output is a single row, where the value of Expr1 is -1, i.e. True. It should be Null.

The query gives the correct result if you reverse the expression:
    SELECT (Null <> 4) AS Expr1;

Clearly, it is completely confused. The inequality operator is symetrical, i.e. comparing A to B should give the same results as comparing B to A.

The value 4 has no significance. Any field or literal shows the bug.

Where it goes wrong

If you switch the query to design view, the expression is displayed as (4 Is Not Null). Modify the query in design view, that expression becomes part of the SQL statement. It appears that JET is actually interpreting the expression wrongly.

In the query design GUI, it makes sense to replace
    [X] <> Null
with
    [X] Is Not Null
since that is probably what the user intended. But to interpret those expressions as the same at the engine level is just plain wrong.

On its own, the expression ([X] <> Null) is not a very useful piece of SQL. The real-life problems arise in more involved statements such as:
   SELECT COUNT(*) FROM T1,T2
   WHERE (T1.Fn<>T2.Fn) AND (T1.ID=T2.ID);

If the discussion about Nulls is new territory for you, see Common errors with Null.


Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Query NULL comparison gives wrong result (bug)'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.