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

Sun, 7 Nov 1999 12:54:37 -0600
Brian Hirt <bhirt@mobygames.com>
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

Sun, 7 Nov 1999 13:39:13 -0600
Brian Hirt <bhirt@mobygames.com>
Re: [HACKERS] IN clause and INTERSECT not behaving as expected
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

Wed, 10 Nov 1999 21:56:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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, Pennsylvania 19026

Mon, 29 Nov 1999 21:40:55 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] IN clause and INTERSECT not behaving as expected
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);

Mon, 29 Nov 1999 23:16:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] IN clause and INTERSECT not behaving as expected 