List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 16 2005 4:53pm
Subject:Re: subquery fails when a NOT IN operator tests a subset with NULL
valu
View as plain text  
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

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