Bradley Baetz writes:
> On Wed, 17 Oct 2001, Michael Widenius wrote:
>
> > bbaetz> SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON (bugs.bug_id =
> > bbaetz> cc.bug_id AND cc.who = 2) WHERE (bugs.reporter = 2 OR cc.who =
> > bbaetz> 2);
> >
>
> <snip>
>
> > 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..
>
> I realise this, and want the behaviour, since I still want bugs with no
> people cc'd to match if the reporter matches.
>
> However, the above query _does_ have the restriction in the WHERE part (as
> well as the on part - this is a simplified version of a more complicated,
> partly autogenerated, query). And removing the index changes the result,
> which seems really strange. Also, if you change the index on the cc table
> to be unique(bug_id,who), then the result changes based on whether or not
> the who column is NOT NULL.
>
> Postgresql gives my expected result.
>
> >
> > Regards,
> > Monty
> >
>
> Thanks,
>
> Bradley
>
Hi!
You could try to run CHECK TABLE to be sure that indices are OK.
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <sinisa@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
