List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 2 2002 5:45am
Subject:Re: behaviour of WHERE statement with NULL fields
View as plain text  
At 5:21 +0000 11/2/02, Kevin McManus wrote:
>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 ^

That makes perfect sense.  That value of NULL is unknown, so it's
impossible to say whether or not it matches a pattern.  The only
reasonable result is NULL.  Even NULL LIKE NULL and NULL REGEXP NULL
return NULL, because you can't tell whether or not one unknown value
is the same as another.

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