From: Peter Brawley Date: March 16 2005 4:53pm Subject: Re: subquery fails when a NOT IN operator tests a subset with NULL valu List-Archive: http://lists.mysql.com/mysql/181363 Message-Id: <42386477.4060500@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Giuseppe, >mysql> select 2 not in (1,null,3); >+---------------------+ >| 2 not in (1,null,3) | >+---------------------+ >| NULL | >+---------------------+ >1 row in set (0.00 sec) ># NOT OK Isn't that standard SQL behaviour? NULL is not a value. NOT IN compares the values using '=' and correctly returns NULL if any value is NULL ie missing, eg for Oracle see http://builder.com.com/5100-6388_14-5319615.html PB ----- Giuseppe Maxia wrote: > Hi. > Here is a description of what looks like a serious bug. > This is related to bugs #7294 and #6247 > > Tested against mysql 4.1.9 and 4.1.10. > > Cheers > > Giuseppe Maxia > > >Description: > operator NOT IN fails when a subquery returns one or more NULL > values. > >How-To-Repeat: > simple proof of concept: > mysql> select 1 in (1,null,3); > +-----------------+ > | 1 in (1,null,3) | > +-----------------+ > | 1 | > +-----------------+ > 1 row in set (0.00 sec) > #OK > > mysql> select 2 not in (1,null,3); > +---------------------+ > | 2 not in (1,null,3) | > +---------------------+ > | NULL | > +---------------------+ > 1 row in set (0.00 sec) > > # NOT OK > > More complete proof: > > mysql> drop table if exists t1; > Query OK, 0 rows affected (0.00 sec) > > mysql> drop table if exists t2; > Query OK, 0 rows affected (0.06 sec) > > mysql> create table t1 (id int not null auto_increment primary key, c1 > int); > Query OK, 0 rows affected (0.01 sec) > > mysql> > mysql> create table t2 (id int not null auto_increment primary key, c2 > int); > Query OK, 0 rows affected (0.02 sec) > > mysql> insert into t1(c1) values (1),(2); > Query OK, 2 rows affected (0.01 sec) > Records: 2 Duplicates: 0 Warnings: 0 > > mysql> insert into t2(c2) values (2),(null),(3); > Query OK, 3 rows affected (0.00 sec) > Records: 3 Duplicates: 0 Warnings: 0 > > mysql> select * from t1; > +----+------+ > | id | c1 | > +----+------+ > | 1 | 1 | > | 2 | 2 | > +----+------+ > 2 rows in set (0.01 sec) > > mysql> select * from t2; > +----+------+ > | id | c2 | > +----+------+ > | 1 | 2 | > | 2 | NULL | > | 3 | 3 | > +----+------+ > 3 rows in set (0.00 sec) > > mysql> select t1.* from t1 left join t2 on (c1=c2) where t2.id is null; > +----+------+ > | id | c1 | > +----+------+ > | 1 | 1 | > +----+------+ > 1 row in set (0.01 sec) > > # OK. This is the normal way of checking for non-existence of records > in a > # related table > > mysql> select t1.* from t1 where c1 not in (select distinct c2 from t2); > Empty set (0.01 sec) > > # NOT OK. This query should have returned the same result as the > previous one > > mysql> select t1.* from t1 where c1 not in (select distinct c2 from t2 > where c2 is not null); > +----+------+ > | id | c1 | > +----+------+ > | 1 | 1 | > +----+------+ > 1 row in set (0.01 sec) > > # ugly workaround with an express filter > > mysql> select t1.* from t1 where c1 not in (select distinct > coalesce(c2,0) from t2 ); > +----+------+ > | id | c1 | > +----+------+ > | 1 | 1 | > +----+------+ > 1 row in set (0.01 sec) > > # yet another ugly workaround > > >Fix: > as a temporary workaround, filter off the NULLs with > a WHERE clause or a COALESCE function. > > > >Submitter-Id: > >Originator: Giuseppe Maxia > >Organization: > Stardata s.r.l > >MySQL support: Certified Consulting Partner > >Synopsis: subquery fails on test with NOT IN and NULL values > >Severity: serious > >Priority: high > >Category: mysql > >Class: sw-bug > >Release: mysql-4.1.10-standard (MySQL Community Edition - Standard > (GPL)) > >Server: /usr/local/mysql/bin/mysqladmin Ver 8.41 Distrib 4.1.10, for > pc-linux-gnu on i686 > Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB > This software comes with ABSOLUTELY NO WARRANTY. This is free software, > and you are welcome to modify and redistribute it under the GPL license > > Server version 4.1.10-standard-log > Protocol version 10 > Connection Localhost via UNIX socket > UNIX socket /tmp/mysql.sock > Uptime: 41 min 36 sec > > Threads: 2 Questions: 111 Slow queries: 0 Opens: 32 Flush tables: > 1 Open tables: 2 Queries per second avg: 0.044 > >C compiler: 2.95.3 > >C++ compiler: 2.95.3 > >Environment: > > System: Linux ltstardata 2.6.9-1.667 #1 Tue Nov 2 14:41:25 EST 2004 > i686 i686 i386 GNU/Linux > Architecture: i686 > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc > /usr/bin/cc > GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.2/specs > Configured with: ../configure --prefix=/usr --mandir=/usr/share/man > --infodir=/usr/share/info --enable-shared --enable-threads=posix > --disable-checking --with-system-zlib --enable-__cxa_atexit > --disable-libunwind-exceptions --enable-java-awt=gtk > --host=i386-redhat-linux > Thread model: posix > gcc version 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) > Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' > CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' > ASFLAGS='' > LIBC: > lrwxrwxrwx 1 root root 13 Feb 12 14:24 /lib/libc.so.6 -> libc-2.3.3.so > -rwxr-xr-x 1 root root 1504728 Oct 28 01:00 /lib/libc-2.3.3.so > -rw-r--r-- 1 root root 2404716 Oct 28 00:46 /usr/lib/libc.a > -rw-r--r-- 1 root root 204 Oct 28 00:08 /usr/lib/libc.so > Configure command: ./configure '--prefix=/usr/local/mysql' > '--localstatedir=/usr/local/mysql/data' > '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community > Edition - Standard (GPL)' '--with-extra-charsets=complex' > '--with-server-suffix=-standard' '--enable-thread-safe-client' > '--enable-local-infile' '--enable-assembler' '--disable-shared' > '--with-client-ldflags=-all-static' > '--with-mysqld-ldflags=-all-static' '--with-readline' > '--with-embedded-server' '--with-archive-storage-engine' > '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' > 'CPPFLAGS=-DDEFAULT_THREAD_STACK=126976' 'CXXFLAGS=-O2 > -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 3/15/2005