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: <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'
-- 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
