Skip site navigation (1) Skip section navigation (2)

Re: BUG #2237: SELECT optimizer drops everything improperly

From: alexis(at)m2osw(dot)com
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2237: SELECT optimizer drops everything improperly
Date: 2006-02-05 19:57:56
Message-ID: Pine.LNX.4.44.0602051149450.20114-100000@substitute.m2osw.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
> 


In response to

pgsql-bugs by date

Next:From: Olleg SamoylovDate: 2006-02-06 11:50:12
Subject: BUG #2239: "vacuumdb -a" remove freeze
Previous:From: Tom LaneDate: 2006-02-05 19:49:56
Subject: Re: incorrect collation order in at least some non-C locales

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group