List:General Discussion« Previous MessageNext Message »
From:Giuseppe Maxia Date:March 16 2005 6:40am
Subject:subquery fails when a NOT IN operator tests a subset with NULL valu
View as plain text  
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:	<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:
	<machine, os, target, libraries (multiple lines)>
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'


-- 
Giuseppe Maxia
CTO
http://www.StarData.it
MySQL Certified Professional
        __              __     __
   ___ / /____ ________/ /__ _/ /____ _
  (_-</ __/ _ `/ __/ _  / _ `/ __/ _ `/
/___/\__/\_,_/_/  \_,_/\_,_/\__/\_,_/
        Database is our business
Thread
subquery fails when a NOT IN operator tests a subset with NULL valuGiuseppe Maxia16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULL valuDan Nelson16 Mar
    • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluGiuseppe Maxia16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULL valuHarald Fuchs16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluPeter Brawley16 Mar
    • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluGiuseppe Maxia16 Mar