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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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);