I think this is because a NULL is not and empty string. They are different things. to match NULL you got to do: WHERE grp IS NULL or to exclude NULL: WHERE grp IS NOT NULL.
I think that NULL values are excluded from string comparisons. I might be wrong though!!!!! JFernando ** sql ** -----Original Message----- From: Gonzo McMuppet [mailto:gonzo@;raq566.uk2net.com] Sent: November 1, 2002 16:29 To: [EMAIL PROTECTED] Subject: behaviour of WHERE clause with empty fields >Description: Empty fields are not matched using where clause NOT IN NOT LIKE NOT REGEXP Also not matched using regular expression negation [^] >How-To-Repeat: SELECT * FROM bugtest WHERE grp REGEXP '[^AB]' Returns only grp = 'C' not grp undefined (presumably NULL) SELECT * FROM bugtest WHERE grp REGEXP '[AB]' Correctly returns rows where grp = 'A' or 'B' SELECT * FROM bugtest WHERE grp NOT REGEXP '[AB]' Returns only rows where grp = 'C' and not undefined Similar behaviour with LIKE and IN >Fix: Puzzled? Am I not understanding something? >Submitter-Id: <submitter ID> >Originator: Kevin McManus ([EMAIL PROTECTED]) >Organization: The University of Greenwich Park Row Greenwich London SE10 9LS > >MySQL support: none >Synopsis: behaviour of WHERE clause with empty fields >Severity: serious >Priority: low >Category: mysql >Class: sw-bug >Release: mysql-3.23.41 (Source distribution) >Environment: 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.so ck --with-mysqld-user=mysql Perl: This is perl, version 5.005_03 built for i386-linux --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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