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

BUG #4290: wrong double subselect with aggregate function

From: "Andreas" <postgresql(at)elbrief(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4290: wrong double subselect with aggregate function
Date: 2008-07-09 15:42:24
Message-ID: 200807091542.m69FgOeC088180@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4290
Logged by:          Andreas
Email address:      postgresql(at)elbrief(dot)de
PostgreSQL version: 8.3.3
Operating system:   Linux
Description:        wrong double subselect with aggregate function
Details: 

select version() ;
                                     version
----------------------------------------------------------------------------
------
 PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (Debian 4.3.1-1)
4.3.1

create table a (
  id serial primary key
) ;

create table b (
  id serial primary key
  , aid int not null references a
) ;

create table c (
  id serial primary key
  , aid int not null references a
) ;

create table d (
  id serial primary key
  , bid int not null references b
  , cid int references b
) ;

insert into a ( id ) values ( default ) , ( default ) ;
insert into b ( aid ) values ( 1 ) , ( 2 ) ;
insert into c ( aid ) values ( 1 ) , ( 1 ) , ( 2 ) , ( 2 ) ;
insert into d ( bid ) values ( 1 ) , ( 2 ) ;
select
 ( select min( c.id ) from c where c.aid = ( select b.aid from b where b.id
= d.bid ) ) as min_c_id
  , ( select b.aid from b where b.id = d.bid ) as b_aid
  , ( select min( c.id ) from c where c.aid = 1 ) as min_c_id_1
  , ( select min( c.id ) from c where c.aid = 2 ) as min_c_id_2
from d ;

 min_c_id | b_aid | min_c_id_1 | min_c_id_2
----------+-------+------------+------------
        1 |     1 |          1 |          3
        1 |     2 |          1 |          3

I expected for min_c_id in the second row 3.

Best Regards
Andreas

Responses

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2008-07-09 16:06:46
Subject: Re: BUG #4290: wrong double subselect with aggregatefunction
Previous:From: Tom LaneDate: 2008-07-09 15:40:09
Subject: Re: BUG #4289: drop owned by report ERROR: cache lookup failed for relation 16390

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