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

Brian Hirt <bhirt(at)mobygames(dot)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