From: Dan Nelson
Date: November 2 2002 5:46am
Subject: Re: behaviour of WHERE statement with NULL fields
List-Archive: http://lists.mysql.com/mysql/123849
Message-Id: <20021102054603.GA84071@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Nov 02), Kevin McManus said:
> >Description:
>         The WHERE statement does not correctly return rows matching NULL
> 	fields when using NOT with IN, LIKE or REGEXP - or using REGEXP
> 	with negation ^

Please see http://www.mysql.com/doc/en/Working_with_NULL.html for a
description of how the SQL language treats NULLs.  MySQL is working as
designed.
 
> >Fix:
> 	Use IS NULL
>         SELECT * FROM bugtest WHERE grp IS NULL OR grp REGEXP '[^AB]'
> 	Returns what would be expected from the last How-To-Repeat example

Correct.  The only way to select based on a NULL in a field is with IS
NULL.

-- 
	Dan Nelson
	dnelson@stripped