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: