Re: [HACKERS] IN clause and INTERSECT not behaving as expected

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can anyone comment on this?

The given cases seem to work in current sources...
        regards, tom lane

>> /* create test tables and test data */
>> create table test1 (id int);
>> create table test2 (id int, fk int);
>> insert into test1 values (1);
>> insert into test1 values (2);
>> insert into test2 values (1,100);
>> insert into test2 values (1,102);
>> insert into test2 values (2,100);
>> insert into test2 values (3,101);
>> 
>> /* QUERY 1: this query works */
>> select id from test1;
>> 
>> /* QUERY 2: this query works */
>> select id from test2 group by id having count(fk) = 2;
>> 
>> /* QUERY 3: intersected, the queries fail with:
>> * ERROR:  SELECT/HAVING requires aggregates to be valid 
>> * NOTE: reversing the order of the intersection works */
>> select id from test1 
>> intersect 
>> select id from test2 group by id having count(fk) = 2;
>> 
>> 
>> /* QUERY 4: using "QUERY 2" as an in clause you get a more confusing error:
>> * ERROR:  rewrite: aggregate column of view must be at rigth side in qual */
>> select id from test1 where id in
>> (select id from test2 group by id having count(fk) = 2);