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

Okay, I've looked into this a little more and found that the rewrite
engine converts UNION INTERSECT and EXCEPT queries to semantiacally
equivalent queries that use IN and NOT IN subselects.
See: backend/rewrite/rewriteHandler.c, line 2821.

So, my hope that the intersection problem will be easier to solve than
the sub select problem is incorrect. I'm still confused by the error
message about "views" with the IN clause. I'll look into that some more.

On Sun, Nov 07, 1999 at 12:54:37PM -0600, Brian Hirt wrote:
> 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
>
> ************

--
The world's most ambitious and comprehensive PC game database project.

http://www.mobygames.com