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

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

From: "Adam Buchbinder" <adam(at)support(dot)uroweb(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3384: Subselects on joins show columns not in the join query.
Date: 2007-06-13 16:51:22
Message-ID: 200706131651.l5DGpMmP002076@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3384
Logged by:          Adam Buchbinder
Email address:      adam(at)support(dot)uroweb(dot)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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-06-13 18:18:20
Subject: Re: BUG #3384: Subselects on joins show columns not in the join query.
Previous:From: Heikki LinnakangasDate: 2007-06-13 08:01:42
Subject: Re: BUG #3383: Postmaster Service Problem

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