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