Re: BUG #2237: SELECT optimizer drops everything improperly

Hi Tom,

Yes. It looks like that works properly. And I'm replying because I had a
hard time to write the actual SQL command! I put it below.

It looks like your page about the joins would need a few examples. I tried
different syntax and they didn't work (thought they are valid for MySQL or
Oracle).

For instance, the first three tables, I would think I can define them like
this: (phpbb_users u, phpbb_forums_watch fw, phpbb_topics t) LEFT JOIN ...
but postgres doesn't like it. I had to use CROSS JOIN instead.

One other thing, which is certainly a bit harder right now, when I forget
to put the ON clause, the parser gives me an error saying "I don't
understand that WHERE". It would be much more helpful to have a message
such as "I don't like the WHERE here because I expected an ON statement."
This is certainly trickier to fix however. 8-)


SELECT u.user_id, u.user_name, t.topic_title
        FROM
                phpbb_users u CROSS JOIN phpbb_forums_watch fw CROSS JOIN
phpbb_topics t LEFT JOIN phpbb_topics_watch tw
                ON u.user_id = tw.user_id
        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)
        ;


Thank you very much for your hints.

Alexis


On Sat, 4 Feb 2006, Tom Lane wrote:

> "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
>