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
