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

Can anyone comment on this?

> Hello,
> 
> I was writing a query using intersect and came across a strang error.  
> Independently, the two queries work fine but fail to compile when
> intersected.  My first instinct was to rewrite the query with an
> in clause, and that too failed in even a stranger way.  I've stripped
> down the queries to the most basic case of failure.  I'm running 6.5.3
> on a RedHat 6.0 PII.  I've included a little snippet of code to reproduce 
> the problem.  I'm expecting to hear that you can't have aggregates in 
> IN clauses until the rewrite engine gets fixed -- discussed in previous
> posts.  I'm more hopefull that the intersection problem will be easy to
> solve.
> 
> /* 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);
> 
> -- 
> The world's most ambitious and comprehensive PC game database project.
> 
>                       http://www.mobygames.com
> 
> ************
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026