BUG #2237: SELECT optimizer drops everything improperly

The following bug has been logged online:

Bug reference: 2237
Logged by: Alexis Wilke
Email address: alexis(at)m2osw(dot)com
PostgreSQL version: 8.0.1
Operating system: Linux (RH9)
Description: SELECT optimizer drops everything improperly
Details:

Hi guys,

It looks like it could have been fixed by Tom in 8.1.1

* Fix incorrect optimizations of outer-join conditions (Tom)

But I cannot upgrade just right now to test whether it would work.

The following is a very simple set of SQL commands.

Create a new database, then do:

psql -f <name>.sql <database name>

where <name>.sql is the following SQL saved in a file named <name>.sql and
<database name> is that empty database.

Note that it is written so you can run it multiple times, which means I DROP
tables and the first time you get some warnings which is fine.

Hope this helps.

Best regards,
Alexis

--
-- Sample script to create several tables and show that
-- a select does not behave as it should.
--

-- Assuming a database named empty_table_bug exists
-- and we are connected to it somehow.

-- In case you want to try several times, generates warnings...
DROP TABLE phpbb_users;
DROP TABLE phpbb_topics;
DROP TABLE phpbb_forums_watch;
DROP TABLE phpbb_topics_watch;

-- Found the bug hacking phpBB so tables are in link
-- with what phpBB uses.

CREATE TABLE phpbb_users (user_id int, user_name text);

CREATE TABLE phpbb_topics (topic_id int, user_id int, forum_id int,
topic_title text);

CREATE TABLE phpbb_forums_watch (forum_id int, user_id int);

CREATE TABLE phpbb_topics_watch (topic_id int, user_id int);

INSERT INTO phpbb_users (user_id, user_name) VALUES (1, 'alexis');
INSERT INTO phpbb_users (user_id, user_name) VALUES (2, 'john');

INSERT INTO phpbb_topics (topic_id, user_id, forum_id, topic_title)
VALUES (1, 1, 1, 'Misc');

INSERT INTO phpbb_forums_watch (forum_id, user_id)
VALUES (1, 1);

-- This did not happen yet and therefore phpbb_topics_watch is empty
-- INSERT INTO phpbb_topics_watch (topic_id, user_id) VALUES (1, 1);

-- This is what I need to work, it works with this simple SELECT
SELECT 'The next SELECT finds 1 row. Perfect!' AS message;
SELECT u.user_id, u.user_name, t.topic_title
FROM phpbb_users u, phpbb_forums_watch fw, phpbb_topics t
WHERE
t.topic_id = 1 -- some variable id
AND fw.forum_id = t.forum_id
AND fw.user_id = u.user_id;

-- 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);

-- insert a dummy value...
INSERT INTO phpbb_topics_watch (topic_id, user_id)
VALUES (0, 0);

-- now it works!
SELECT 'Make the phpbb_topics_watch table ''non-empty'' and it works' 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);