List:General Discussion« Previous MessageNext Message »
From:jfield Date:October 9 2002 10:33pm
Subject:NULL sometimes joins to NULL
View as plain text  
>Description:
After adding a key to a nullable column, null values will
successfully join through to null values in other tables.
This only seems to happen when the index is added after
the row contains null values.  This affects both MyISAM
and InnoDB table types and both binary versions 3.23.42
and 4.0.4.

>How-To-Repeat:
Run the following queries.  The first select will return 0
rows, which is correct.  The second select will return a
match - but all that was changed was the adding of an index.

  mysql> create table foo (id int);
  Query OK, 0 rows affected (0.01 sec)

  mysql> insert into foo values (null), (0);
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0

  mysql> create table bar (id int);
  Query OK, 0 rows affected (0.00 sec)

  mysql> insert into bar values (null);
  Query OK, 1 row affected (0.00 sec)

  mysql> select * from foo, bar where foo.id = bar.id;
  Empty set (0.00 sec)

  mysql> alter table foo add key id (id);
  Query OK, 2 rows affected (0.01 sec)
  Records: 2  Duplicates: 0  Warnings: 0

  mysql> select * from foo, bar where foo.id = bar.id;
  +------+------+
  | id   | id   |
  +------+------+
  | NULL | NULL |
  +------+------+
  1 row in set (0.00 sec)

  mysql>

>Fix:
	

>Submitter-Id:	<submitter ID>
>Originator:	
>Organization:
 
>MySQL support: none
>Synopsis:	adding key makes null columns join
>Severity:	
>Priority:	
>Category:	mysql
>Class:		
>Release:	mysql-4.0.4-beta (Source distribution)

>Environment:
	
System: Linux db2 2.4.18-64GB-SMP #1 SMP Wed Mar 27 13:58:12 UTC 2002 i686 unknown
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-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc'  CFLAGS='-O6 -mpentiumpro -fomit-frame-pointer'  CXX='gcc' 
CXXFLAGS='-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions
-fno-rtti'  LDFLAGS=''
LIBC: 
-rwxr-xr-x    1 root     root      1394238 Jul 12 08:29 /lib/libc.so.6
-rw-r--r--    1 root     root     25361424 Jul 12 06:58 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 Mar 23  2002 /usr/lib/libc.so
Configure command: ./configure --prefix=/opt/mysql --enable-assembler
--with-mysqld-ldflags=-all-static --disable-shared -with-extra-charsets=none 'CFLAGS=-O6
-mpentiumpro -fomit-frame-pointer' 'CXXFLAGS=-O6 -mpentiumpro -fomit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti' CXX=gcc

Thread
NULL sometimes joins to NULLjfield10 Oct
  • re: NULL sometimes joins to NULLVictoria Reznichenko11 Oct
  • re: NULL sometimes joins to NULLVictoria Reznichenko17 Oct
Re: NULL sometimes joins to NULLJan Steinman10 Oct
RE: NULL sometimes joins to NULLJianliang Zhao11 Oct
RE: NULL sometimes joins to NULLArtem Koltsov11 Oct