Thread: BUG #1682: subselect generates multiple rows

The following bug has been logged online:

Bug reference:      1682
Logged by:          D.J. Kniep
Email address:      dick@kniep.nl
PostgreSQL version: 8.0.3
Operating system:   Linux
Description:        subselect generates multiple rows
Details:

table1:
id         content
1          John Doe
2          Peter Gabriel

view2
id         extra1        extra2
1          a1            b1
1          a1            b1
2          a1            b3
3          a1            b5

query:
select * from table1 t1 where t1.id in (select t2.id from view2 t2 where
extra1 = 'a1')

produces:

id         content
1          John Doe
1          John Doe
2          Peter Gabriel

which obviously should be:

id         content
1          John Doe
2          Peter Gabriel

I might not be reachable on my mailaddress because of US imposed
restrictions. A big range of the IP addresses of the provider I am using are
marked as spam generating (which in fact is NOT true at least not for my IP
address), and blacklisted and this makes it effectively impossible for me to
use the mailinglist.

"D.J. Kniep" <dick@kniep.nl> writes:
> query:
> select * from table1 t1 where t1.id in (select t2.id from view2 t2 where
> extra1 = 'a1')

> produces:

> id         content
> 1          John Doe
> 1          John Doe
> 2          Peter Gabriel

Not for me ---

regression=# create table table1(id int, context text);
CREATE TABLE
regression=# create table view2(id int, extra1 text, extra2 text);
CREATE TABLE
regression=# insert into table1 values(1,'John Doe');
INSERT 156512 1
regression=# insert into table1 values(2,'Peter Gabriel');
INSERT 156513 1
regression=# insert into view2 values(1,'a1','b1');
INSERT 156514 1
regression=# insert into view2 values(1,'a1','b1');
INSERT 156515 1
regression=# insert into view2 values(2,'a1','b3');
INSERT 156516 1
regression=# insert into view2 values(3,'a1','b5');
INSERT 156517 1
regression=# select * from table1 t1 where t1.id in (select t2.id from view2 t2
 where extra1 = 'a1');
 id |    context
----+---------------
  1 | John Doe
  2 | Peter Gabriel
(2 rows)

We'll need a more reproducible test case.

            regards, tom lane

On Fri, May 27, 2005 at 08:55:50AM +0100, D.J. Kniep wrote:
>
> id         content
> 1          John Doe
> 1          John Doe
> 2          Peter Gabriel

I couldn't reproduce these results with the test data and query you
provided, but my table and view definitions probably differed from
yours.  Could you post a complete example?  That is, all CREATE,
INSERT, and SELECT statements that somebody could execute in an
empty database to reproduce the results you get.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/