Re: [HACKERS] IN clause and INTERSECT not behaving as expected - Mailing list pgsql-hackers

Brian Hirt <bhirt@mobygames.com> writes:
> /* 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);

These are both bugs, I think.  I committed rewriter fixes that take care
of query 4 (the rewriter mistakenly thought that having count(*) inside
WHERE was a bad thing even if the aggregate function was inside a
subselect).  I am not seeing any failure from query 3 either in current
sources, though I am not sure if that was the same bug or a different one.
        regards, tom lane