Date: Sat, 2 Nov 2002 05:09:44 +0000 From: Kevin McManus <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] - 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 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php