Bug #5 left join bug

Description:
> 
>>Description:
> 
> Hello sirs,
> 
> Mr. Benjamin Pflugmann told me to send this problem to
> bugs@lists.mysql.com.
> 
> I have a database structure as follows (simplyfied) :
> 
> CREATE TABLE masterTABLE (
>   pid int(11) unsigned NOT NULL default '0',
>   c1id int(11) unsigned default NULL,
>   c2id int(11) unsigned default NULL,
>   value int(11) unsigned NOT NULL default '0',
>   UNIQUE KEY pid2 (pid,c1id,c2id),
>   UNIQUE KEY pid (pid,value)
> ) TYPE=MyISAM;
> 
> INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
> INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
> INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
> INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
> INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
> 
> CREATE TABLE childTABLE1 (
>   id int(11) unsigned NOT NULL default '0',
>   active enum('Yes','No') NOT NULL default 'Yes',
>   PRIMARY KEY  (id)
> ) TYPE=MyISAM;
> 
> INSERT INTO childTABLE1 VALUES (1, 'Yes');
> INSERT INTO childTABLE1 VALUES (2, 'No');
> INSERT INTO childTABLE1 VALUES (4, 'Yes');
> INSERT INTO childTABLE1 VALUES (5, 'No');
> 
> CREATE TABLE childTABLE2 (
>   id int(11) unsigned NOT NULL default '0',
>   active enum('Yes','No') NOT NULL default 'Yes',
>   PRIMARY KEY  (id)
> ) TYPE=MyISAM;
> 
> INSERT INTO childTABLE2 VALUES (3, 'Yes');
> 
> If I do this query:
> 
> SELECT MAX( value ) 
> FROM masterTABLE AS m
> LEFT JOIN childTABLE1 AS c1
> ON m.c1id = c1.id AND
> c1.active = 'Yes'
> LEFT JOIN childTABLE2 AS c2
> ON m.c2id = c2.id
> AND c2.active = 'Yes'
> WHERE m.pid=1
> AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
> 
> the Result will be "5" which is probably wrong.
> The expected Result ist "4".
> 
> The correct Result will be returned if you remove
> both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
> 
> This behaviour ist tested on MySQL 3.23.51 and now even
> on MySQL 3.23.53.
> 

I will just add 2 more results to make things more clear:

mysql> select max(value) from masterTABLE AS m LEFT JOIN childTABLE1 AS c1 ON 
m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN childTABLE2 AS c2 ON m.c2id = 
c2.id AND c2.active = 'Yes' WHERE m.pid=1  AND (c1.id IS NOT NULL OR c2.id IS 
NOT NULL);
+------------+
| max(value) |
+------------+
|          5 |
+------------+

mysql> select * from masterTABLE AS m LEFT JOIN childTABLE1 AS c1 ON m.c1id = 
c1.id AND c1.active = 'Yes' LEFT JOIN childTABLE2 AS c2 ON m.c2id = c2.id AND 
c2.active = 'Yes' WHERE m.pid=1  AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
+-----+------+------+-------+------+--------+------+--------+
| pid | c1id | c2id | value | id   | active | id   | active |
+-----+------+------+-------+------+--------+------+--------+
|   1 |    1 | NULL |     1 |    1 | Yes    | NULL | NULL   |
|   1 | NULL |    3 |     3 | NULL | NULL   |    3 | Yes    |
|   1 |    4 | NULL |     4 |    4 | Yes    | NULL | NULL   |
+-----+------+------+-------+------+--------+------+--------+

Exactly the same WHERE clause.

ysql> explain select max(value) from masterTABLE AS m LEFT JOIN childTABLE1 AS 
c1 ON m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN childTABLE2 AS c2 ON m.c2id 
= c2.id AND c2.active = 'Yes' WHERE m.pid=1  AND (c1.id IS NOT NULL OR c2.id IS 
NOT NULL);
+-------+--------+---------------+---------+---------+--------+------+------------+
| table | type   | possible_keys | key     | key_len | ref    | rows | Extra      |
+-------+--------+---------------+---------+---------+--------+------+------------+
| m     | ALL    | pid,pid2      | NULL    |    NULL | NULL   |    5 | where used |
| c1    | eq_ref | PRIMARY       | PRIMARY |       4 | m.c1id |    1 |            |
| c2    | ALL    | PRIMARY       | NULL    |    NULL | NULL   |    1 | where used |
+-------+--------+---------------+---------+---------+--------+------+----------

Note key: NULL for table m

mysql> select * from masterTABLE AS m LEFT JOIN childTABLE1 AS c1 ON m.c1id = 
c1.id AND c1.active = 'Yes' LEFT JOIN childTABLE2 AS c2 ON m.c2id = c2.id AND 
c2.active = 'Yes' WHERE m.pid=1;
+-----+------+------+-------+------+--------+------+--------+
| pid | c1id | c2id | value | id   | active | id   | active |
+-----+------+------+-------+------+--------+------+--------+
|   1 |    1 | NULL |     1 |    1 | Yes    | NULL | NULL   |
|   1 |    2 | NULL |     2 | NULL | NULL   | NULL | NULL   |
|   1 | NULL |    3 |     3 | NULL | NULL   |    3 | Yes    |
|   1 |    4 | NULL |     4 |    4 | Yes    | NULL | NULL   |
|   1 |    5 | NULL |     5 | NULL | NULL   | NULL | NULL   |
+-----+------+------+-------+------+--------+------+--------+

I have feeling that this is where wrong result came from.
I.e. during optimization of LEFT JOIN this condition was skipped somehow -> 
(c1.id IS NOT NULL OR c2.id IS NOT NULL)

How to repeat:

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Fixed in 3.23.54 and 4.0.5