jfield,
Thursday, October 10, 2002, 1:33:10 AM, you wrote:
jazdc> >Description:
jazdc> After adding a key to a nullable column, null values will
jazdc> successfully join through to null values in other tables.
jazdc> This only seems to happen when the index is added after
jazdc> the row contains null values. This affects both MyISAM
jazdc> and InnoDB table types and both binary versions 3.23.42
jazdc> and 4.0.4.
jazdc> >How-To-Repeat:
jazdc> Run the following queries. The first select will return 0
jazdc> rows, which is correct. The second select will return a
jazdc> match - but all that was changed was the adding of an index.
jazdc> mysql> create table foo (id int);
jazdc> Query OK, 0 rows affected (0.01 sec)
jazdc> mysql> insert into foo values (null), (0);
jazdc> Query OK, 2 rows affected (0.01 sec)
jazdc> Records: 2 Duplicates: 0 Warnings: 0
jazdc> mysql> create table bar (id int);
jazdc> Query OK, 0 rows affected (0.00 sec)
jazdc> mysql> insert into bar values (null);
jazdc> Query OK, 1 row affected (0.00 sec)
jazdc> mysql> select * from foo, bar where foo.id = bar.id;
jazdc> Empty set (0.00 sec)
jazdc> mysql> alter table foo add key id (id);
jazdc> Query OK, 2 rows affected (0.01 sec)
jazdc> Records: 2 Duplicates: 0 Warnings: 0
jazdc> mysql> select * from foo, bar where foo.id = bar.id;
jazdc> +------+------+
jazdc> | id | id |
jazdc> +------+------+
jazdc> | NULL | NULL |
jazdc> +------+------+
jazdc> 1 row in set (0.00 sec)
jazdc> mysql>
Hi!
Thank you for bug report!
This bug is already fixed in latest 4.0 BK tree.
--
For technical support contracts, goto https://order.mysql.com/?ref=ma02-010c
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ Victoria.Reznichenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
