It looks like a bug. I was able to repeat it and I had different results for the same join
depending on when index was added and values of actual data in tables.
-----Original Message-----
From: jfield@stripped [mailto:jfield@stripped]
Sent: Wednesday, October 09, 2002 6:33 PM
To: mysql@stripped
Subject: NULL sometimes joins to NULL
>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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <mysql-thread121784@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-Artem.Koltsov=wfinet.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Attention:
Any views expressed in this message are those of the individual sender, except where the
message states otherwise and the sender is authorized to state them to be the views of
any such entity.
The information contained in this message and or attachments is intended only for the
person or entity to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or taking of any
action in reliance upon, this information by persons or entities other than the intended
recipient is prohibited. If you received this in error, please contact the sender and
delete the material from any system and destroy any copies.
