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:
reversingthe 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
mustbe 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
