At 5:21 +0000 11/2/02, Kevin McManus wrote:
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 ^
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
[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
