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
