Thread: IN clause and INTERSECT not behaving as expected
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
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
Brian Hirt <bhirt@mobygames.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
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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can anyone comment on this?
The given cases seem to work in current sources...
regards, tom lane
>> /* 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);