Re: BUG #2237: SELECT optimizer drops everything improperly

"Alexis Wilke" <alexis@m2osw.com> writes:
> -- In this select, it detects that the phpbb_topics_watch is
> -- empty and thus ignores the WHERE clause thinking since that
> -- table is empty the SELECT will be empty
> SELECT 'The next SELECT finds 0 row. It should find the same row!' AS
> message;
> SELECT u.user_id, u.user_name, t.topic_title
>     FROM phpbb_users u, phpbb_forums_watch fw, phpbb_topics t,
> phpbb_topics_watch tw
>     WHERE
>         (t.topic_id = 1 -- some variable id
>         AND fw.forum_id = t.forum_id
>         AND fw.user_id = u.user_id)
>     OR
>         (tw.topic_id = 1
>         AND u.user_id = tw.user_id
>         AND t.topic_id = 1);

I see no bug here.  This SELECT is defined to return the rows in the
cartesian product of the four FROM tables that satisfy the WHERE
condition.  Since one of the tables is empty, so is the cartesian
product.

Perhaps you meant to use a LEFT JOIN?

            regards, tom lane