the manual indicates that "mysql follows ansi sql, and a comparison with null is always null".
after years of server work, i still sometimes forget that, at least in all the servers that i've used, null and nothing are not the same thing. > >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.sock --with-mysqld-user=mysql > Perl: This is perl, version 5.005_03 built for i386-linux > -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ --------------------------------------------------------------------- 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