Hi!
>>>>> "bbaetz" == bbaetz <bbaetz@stripped> writes:
>> Description:
bbaetz> When doing a query on a table LEFT JOINed to one with an index, mysql
bbaetz> gives the wrong answer.
bbaetz> Tested on mysql 3.23.36 (RH7.1 package) and 3.22.30.
>> How-To-Repeat:
bbaetz> Run the following script:
bbaetz> drop table if exists bugs, cc;
bbaetz> create table bugs (bug_id mediumint, reporter mediumint);
bbaetz> create table cc (bug_id mediumint, who mediumint, index(who));
bbaetz> insert into cc values (1,1);
bbaetz> insert into cc values (1,2);
bbaetz> insert into bugs values (1,1);
bbaetz> insert into bugs values (2,1);
bbaetz> SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON (bugs.bug_id = cc.bug_id AND
bbaetz> cc.who = 2) WHERE (bugs.reporter = 2 OR cc.who = 2);
<cut>
bbaetz> Actual result:
bbaetz> -------------
bbaetz> *************************** 1. row ***************************
bbaetz> bug_id: 1
bbaetz> *************************** 2. row ***************************
bbaetz> bug_id: 2
bbaetz> 2 rows in set (0.00 sec)
<cut>
The above result is correct.
From the MySQL manual:
If there is no matching record for the right table in the ON or
USING part in a LEFT JOIN, a row with all columns set to
NULL is used for the right table.
In other words:
The The cc.who = 2 restriction in the ON clause does NOT affect the
rows that is considered from the LEFT table. It only affects if the
rows should be considered from the RIGHT table or if we should
generate a NULL row for the right table.
Fix: Move all tests that only affects the 'cc' table from the ON clause to the
WHERE part..
Regards,
Monty
