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
