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(at)m2osw(dot)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
>