BUG #3384: Subselects on joins show columns not in the join query.

The following bug has been logged online:

Bug reference: 3384
Logged by: Adam Buchbinder
Email address: adam@support.uroweb.net
PostgreSQL version: 7.4.16
Operating system: Debian GNU/Linux 4.0
Description: Subselects on joins show columns not in the join query.
Details:

Create the following test database:

CREATE TABLE firsttab (
name VARCHAR(80),
otherfield VARCHAR(80),
id INT,
join_id INT,
PRIMARY KEY(id)
);

CREATE TABLE secondtab (
name VARCHAR(80),
id INT,
PRIMARY KEY(id)
);

CREATE VIEW joinview AS SELECT firsttab.name AS fname, secondtab.name AS
sname FROM firsttab LEFT JOIN secondtab ON firsttab.join_id = secondtab.id;

Insert this data:

INSERT INTO secondtab VALUES ('Foo', 1);

INSERT INTO firsttab VALUES ('Qux', 'Secret1', 1, 1);
INSERT INTO firsttab VALUES ('Quux', 'Secret2', 2, 1);

Run these queries:

SELECT otherfield FROM joinview WHERE fname='Quux';

SELECT otherfield FROM firsttab WHERE otherfield IN (SELECT otherfield FROM
joinview WHERE fname='Quux');

SELECT otherfield FROM firsttab WHERE otherfield IN (SELECT otherfield FROM
joinview WHERE fname='Bar');

The first query returns an error, but the other two do not; they don't,
however, perform the query properly; in the second case, they return all the
values in 'Otherfield'; in the third, none.

If the view is properly created:

CREATE VIEW joinview AS SELECT firsttab.otherfield, firsttab.name AS fname,
secondtab.name AS sname FROM firsttab LEFT JOIN secondtab ON
firsttab.join_id = secondtab.id;

then each query returns what it should.

This error first appeared when a query stubbornly refused to use an index on
one of the fields in a join when that join was queried in a subselect; the
query plan was doing a seq scan instead of an index scan, even though an
index existed, until I fixed the join to include the field which was
indexed.

Adam Buchbinder