>>>>> "Matt" == Matt Hoskins <matt@stripped> writes:
Matt> On Mon, 3 Jan 2000, Michael Widenius wrote:
mysql> select * from test t1 LEFT JOIN test t2
Matt> ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
Matt> +------+------+---------+------+------+---------+
Matt> | id | pid | rep_del | id | pid | rep_del |
Matt> +------+------+---------+------+------+---------+
Matt> | 2 | 1 | NULL | NULL | NULL | NULL |
Matt> +------+------+---------+------+------+---------+
Matt> 1 row in set (0.01 sec)
>>
>> Could you please send me a mysqldump of both tables so that I can
>> check this on my side?
Matt> D'oh, I forgot to say what was in the table. Actually there are only two
Matt> entries in the test table ;). I've included two dumps, where the only
Matt> difference is an index on the rep_del column existing in one, and not in
Matt> the other.
Matt> Matt
Matt> CREATE TABLE test (
Matt> id int(11),
Matt> pid int(11),
Matt> rep_del tinyint(4),
Matt> KEY id (id),
Matt> KEY pid (pid),
Matt> KEY rep_del (rep_del)
Matt> );
Matt> INSERT INTO test VALUES (1,NULL,NULL);
Matt> INSERT INTO test VALUES (2,1,NULL);
Matt> CREATE TABLE test (
Matt> id int(11),
Matt> pid int(11),
Matt> rep_del tinyint(4),
Matt> KEY id (id),
Matt> KEY pid (pid)
Matt> );
Matt> INSERT INTO test VALUES (1,NULL,NULL);
Matt> INSERT INTO test VALUES (2,1,NULL);
Thanks. This was a bug in the relative new LEFT JOIN optimization
that didn't take into account that a key_column could have NULL
values.
Here is a patch for this:
*** /my/monty/master/mysql-3.23.8-alpha/sql/sql_select.cc Tue Dec 28 05:41:30 1999
--- ./sql_select.cc Tue Jan 4 01:34:11 2000
***************
*** 1221,1227 ****
}
}
/* Mark that we can optimize LEFT JOIN */
! if (key_field->val->type() == Item::NULL_ITEM)
key_field->field->table->reginfo.not_exists_optimize=1;
}
--- 1221,1228 ----
}
}
/* Mark that we can optimize LEFT JOIN */
! if (key_field->val->type() == Item::NULL_ITEM &&
! !key_field->field->real_maybe_null())
key_field->field->table->reginfo.not_exists_optimize=1;
}
Regards,
Monty
