Bug #10162 Optimizer: Problem with Indexes

Description:
Mysql 5.0.4 behaves buggy when using (multiple)indexes and joins,whereas mysql 4.1.10 behaves as expected.
I don't exactely know where the problem is, but in the 'how to repeat' part, the bug is described detailed enough, I hope.

How to repeat:
CREATE TABLE `table_a` (
  `gid` smallint(5) unsigned NOT NULL default '0',
  `x` int(11) NOT NULL default '0',
  `y` int(11) NOT NULL default '0',
  `art` int(11) NOT NULL default '0',
  PRIMARY KEY  (`gid`,`x`,`y`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table_a` VALUES (1, -5, -8, 2);
INSERT INTO `table_a` VALUES (1, 2, 2, 1);
INSERT INTO `table_a` VALUES (1, 1, 1, 1);

CREATE TABLE `table_b` (
  `gid` smallint(5) unsigned NOT NULL default '0',
  `x` int(11) NOT NULL default '0',
  `y` int(11) NOT NULL default '0',
  `id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`gid`,`id`,`x`,`y`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table_b` VALUES (1, -5, -8, 1);
INSERT INTO `table_b` VALUES (1, 1, 1, 1);
INSERT INTO `table_b` VALUES (1, 2, 2, 1);

CREATE TABLE `table_c` (
  `set_id` smallint(5) unsigned NOT NULL default '0',
  `id` tinyint(4) unsigned NOT NULL auto_increment,
  `name` char(12) NOT NULL default '',
  PRIMARY KEY  (`id`,`set_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `table_c` VALUES (0, 1, 'a');
INSERT INTO `table_c` VALUES (1, 1, 'b');
INSERT INTO `table_c` VALUES (0, 2, 'c');
INSERT INTO `table_c` VALUES (1, 2, 'd');
INSERT INTO `table_c` VALUES (1, 3, 'e');
INSERT INTO `table_c` VALUES (1, 4, 'f');
INSERT INTO `table_c` VALUES (1, 5, 'g');
INSERT INTO `table_c` VALUES (1, 6, 'h');
        
#########################################

EXPLAIN SELECT name
FROM table_a a
LEFT JOIN table_b av ON a.x = av.x
AND a.y = av.y
LEFT JOIN table_c al ON a.art = al.id
WHERE av.id =1
AND av.x = -5
AND av.y =-8
AND a.gid =1
AND av.gid =1
AND al.set_id =1

mysql 5.0.4==>
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+
|  1 | SIMPLE      | a     | ref    | PRIMARY       | PRIMARY | 2       | const            |    2 |                          |
|  1 | SIMPLE      | av    | index  | NULL          | PRIMARY | 14      | NULL             |    3 | Using where; Using index |
|  1 | SIMPLE      | al    | eq_ref | PRIMARY       | PRIMARY | 3       | test.a.art,const |    1 | Using where              |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------+
3 rows in set (0,00 sec)

mysql4.1.10 =>

+----+-------------+-------+-------+---------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref                     | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY |      10 | const,const,const       |    1 |             |
|  1 | SIMPLE      | av    | const | PRIMARY,id    | PRIMARY |      14 | const,const,const,const |    1 | Using index |
|  1 | SIMPLE      | al    | const | PRIMARY       | PRIMARY |       3 | const,const             |    1 |             |
+----+-------------+-------+-------+---------------+---------+---------+-------------------------+------+-------------+
3 rows in set (0.00 sec)

When I have discovered the bug I had about 160000 rows in table_a and 450 rows in table_b, table_c is the same like above.
After a querytime of 5 minutes I killed the query,
the Explain there was the same like above in mysql 4.1.10 but mysl 5.1.4 was a bit different:

+----+-------------+-------+--------+---------------+---------+---------+------------
-------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref
       | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+------------
-------+------+--------------------------+
|  1 | SIMPLE      | a     | ALL    | PRIMARY       | NULL    | NULL    | NULL
       | 7651 | Using where              |
|  1 | SIMPLE      | al    | eq_ref | PRIMARY       | PRIMARY | 3       | test.a.art
,const |    1 | Using where              |
|  1 | SIMPLE      | av    | index  | NULL          | PRIMARY | 14      | NULL
       |  449 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------
-------+------+--------------------------+
3 rows in set (0,00 sec)

Fixed in 5.0.6

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24341

Noted in 5.0.6 changelog.