List:General Discussion« Previous MessageNext Message »
From:Kevin McManus Date:November 2 2002 5:21am
Subject:behaviour of WHERE statement with NULL fields
View as plain text  
Date: Sat, 2 Nov 2002 05:09:44 +0000
From: Kevin McManus <k.mcmanus@stripped>
To: mysql@stripped
Subject: behaviour of WHERE statement with NULL fields
User-Agent: Mutt/1.4i

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

>How-To-Repeat:
	Please see the SQL at the foot of this email for the bugtest table
        SELECT * FROM bugtest WHERE grp IN ('A','B')
           Correctly returns rows where grp is 'A' or 'B'
        SELECT * FROM bugtest WHERE grp NOT IN ('A','B')
	   Returns rows where grp is 'C' but fails to return rows where
	   grp is NULL (but clearly not 'A' or 'B')
	SELECT * FROM bugtest WHERE grp NOT LIKE 'A'
	   Returns only rows where grp is 'B' and 'C' but not NULL
	SELECT * FROM bugtest WHERE grp NOT LIKE '_'
	   Returns no rows but NULL is not like a single character
	SELECT * FROM bugtest WHERE grp REGEXP '[AB]'
	   Returns rows where grp matches 'A' or 'B'
	SELECT * FROM bugtest WHERE grp REGEXP '[^AB]'
	   Returns rows where grp is 'C' but fails to return rows where
           grp is NULL

>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

>Submitter-Id:  <submitter ID>
>Originator:    Kevin McManus
>Organization:
	School of Computing & Math Science
	The University of Greenwich
	Park Row, Greenwich
	London SE10 9LS  UK
>
>MySQL support: none
>Synopsis:      behaviour of WHERE statement with NULL fields
>Severity:      medium
>Priority:      low
>Category:      mysql
>Class:         sw-bug
>Release:       mysql-3.23.41 (Source distribution)

>Environment:
        <machine, os, target, libraries (multiple lines)>
System: Linux raq566.uk2net.com 2.2.16C28_III #1 Mon Jul 30 22:07:58 PDT 2001 i586 unknown
Architecture: i586

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
LIBC:
lrwxrwxrwx   1 root     root           13 Jan 20  2001 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x   1 root     root      4101836 Sep  5  2000 /lib/libc-2.1.3.so
-rw-r--r--   1 root     root     20273284 Sep  5  2000 /usr/lib/libc.a
-rw-r--r--   1 root     root          178 Sep  5  2000 /usr/lib/libc.so
lrwxrwxrwx   1 root     root           19 Sep 26  2001 /usr/lib/libc-client.a ->
/usr/lib/c-client.a
Configure command: ./configure  --prefix=/usr/local/mysql
--with-unix-socket-path=/tmp/mysql.sock --with-mysqld-user=mysql
Perl: This is perl, version 5.005_03 built for i386-linux

CREATE TABLE bugtest (
  id int(11) NOT NULL auto_increment,
  name varchar(16) default NULL,
  grp char(1) binary default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

#
# Dumping data for table 'bugtest'
#

INSERT INTO bugtest VALUES (1,'fred','A');
INSERT INTO bugtest VALUES (2,'joe','B');
INSERT INTO bugtest VALUES (3,'bert','C');
INSERT INTO bugtest VALUES (4,'sally',NULL);
INSERT INTO bugtest VALUES (5,'sue','B');
INSERT INTO bugtest VALUES (6,'alan',NULL);
INSERT INTO bugtest VALUES (7,'cathy','A');
INSERT INTO bugtest VALUES (8,'mary','C');

PS An earlier attempt to post this report appears to have failed
   Apologies if this is a repeat posting

k.mcmanus@stripped - http://staffweb.cms.gre.ac.uk/~k.mcmanus
--------------------------------------------------------------
Dr Kevin McManus                     ||Queen Mary 413
School of Computing & Math Science   ||
The University of Greenwich          ||
Park Row, Greenwich                  ||Tel +44 (0)208 331 8719 
London SE10 9LS  UK                  ||Fax +44 (0)208 331 8665
Thread
behaviour of WHERE statement with NULL fieldsKevin McManus2 Nov
  • Re: behaviour of WHERE statement with NULL fieldsPaul DuBois2 Nov
  • Re: behaviour of WHERE statement with NULL fieldsDan Nelson2 Nov
Re: behaviour of WHERE statement with NULL fieldsMichael T. Babcock5 Nov