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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026