Hi all,

Seems that I found a bug on HAVING clause, see attached file.

-Jose'-
Thu, 3 Dec 1998 15:01:51 +0100
Sferacarta Software <sferac@bo.nettuno.it>
BUG ON HAVING CLAUSE
Sferacarta Software wrote:
> 
> Hi all,
> 
> Seems that I found a bug on HAVING clause, see attached file.
> 

Could you post me data for 8342 rows ?

Vadim

Fri, 04 Dec 1998 09:53:39 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] BUG ON HAVING CLAUSE
Hello Vadim,

venerdï¿½, 4 dicembre 98, you wrote:

VM> Sferacarta Software wrote:
>> 
>> Hi all,
>> 
>> Seems that I found a bug on HAVING clause, see attached file.
>> 

VM> Could you post me data for 8342 rows ?

I think this bug is not on HAVING but on IN/ANY/ALL, I tried all these
statements and no one returns right values.
I compared it with Informix, take a look at attachment.

BTW. Thomas said me that you know something about the following
problem:

SELECT esito1,esito2
    FROM brogliacci
    WHERE NOT esito1 IS NULL AND NOT esito2 IS NULL;
esito1|esito2
------+------
N     |N     
(1 row)


SELECT esito1,esito2
    FROM brogliacci
    WHERE NOT esito1 IS NULL OR NOT esito2 IS NULL;

pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally before or while pr
ocessing the request.
We have lost the connection to the backend, so further processing is impossible.
  Terminating.

  May you help me?

-Jose'-
Fri, 4 Dec 1998 14:36:19 +0100
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: [HACKERS] BUG ON HAVING CLAUSE
Sferacarta Software wrote:
> 
> >> Seems that I found a bug on HAVING clause, see attached file.
> >>
> 
> VM> Could you post me data for 8342 rows ?
> 
> I think this bug is not on HAVING but on IN/ANY/ALL, I tried all these

This is bug on handling HAVING in subqueries.
There was no HAVING when I was implementing subqueries and
so I didn't care... The bug is in optimizer:

vac=> explain select * from test where x in (select * from test group by x having 1 < count(x));
NOTICE:  QUERY PLAN:

Seq Scan on test  (cost=0.00 size=0 width=4)
  SubPlan
    ->  Aggregate  (cost=0.00 size=0 width=0)
!          InitPlan
!            ->  Aggregate  (cost=0.00 size=0 width=0)
!              ->  Seq Scan on test  (cost=0.00 size=0 width=4)

There must be no InitPlan here...

          ->  Group  (cost=0.00 size=0 width=0)
                ->  Sort  (cost=0.00 size=0 width=0)
                      ->  Seq Scan on test  (cost=0.00 size=0 width=4)

Currently, my local copy of dev-tree is broken and so I can't
fix this. I'll return to this bug latter if no one else...

Vadim

Sat, 05 Dec 1998 18:32:20 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] BUG ON HAVING CLAUSE
Hello Vadim,

sabato, 5 dicembre 98, you wrote:

VM> Sferacarta Software wrote:
>> 
>> >> Seems that I found a bug on HAVING clause, see attached file.
>> >>
>> 
>> VM> Could you post me data for 8342 rows ?
>> 
>> I think this bug is not on HAVING but on IN/ANY/ALL, I tried all these

VM> This is bug on handling HAVING in subqueries.
VM> There was no HAVING when I was implementing subqueries and
VM> so I didn't care... The bug is in optimizer:

Well in such case I can send you my 8342 rows if you want it still.

-Jose'-



Wed, 9 Dec 1998 15:05:49 +0100
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: [HACKERS] BUG ON HAVING CLAUSE
> Hi all,
> 
> Seems that I found a bug on HAVING clause, see attached file.
> 
> -Jose'-

Can you summarize what was the result of your several bug reports on IN
and HAVING.  Is there a problem that needs to be added to the TODO list?

-- 
  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

Sat, 12 Dec 1998 23:20:28 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] BUG ON HAVING CLAUSE
Added to TODO:

	* subqueries containing HAVING return incorrect results


> Sferacarta Software wrote:
> > 
> > >> Seems that I found a bug on HAVING clause, see attached file.
> > >>
> > 
> > VM> Could you post me data for 8342 rows ?
> > 
> > I think this bug is not on HAVING but on IN/ANY/ALL, I tried all these
> 
> This is bug on handling HAVING in subqueries.
> There was no HAVING when I was implementing subqueries and
> so I didn't care... The bug is in optimizer:
> 
> vac=> explain select * from test where x in (select * from test group by x having 1 < count(x));
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on test  (cost=0.00 size=0 width=4)
>   SubPlan
>     ->  Aggregate  (cost=0.00 size=0 width=0)
> !          InitPlan
> !            ->  Aggregate  (cost=0.00 size=0 width=0)
> !              ->  Seq Scan on test  (cost=0.00 size=0 width=4)
> 
> There must be no InitPlan here...
> 
>           ->  Group  (cost=0.00 size=0 width=0)
>                 ->  Sort  (cost=0.00 size=0 width=0)
>                       ->  Seq Scan on test  (cost=0.00 size=0 width=4)
> 
> Currently, my local copy of dev-tree is broken and so I can't
> fix this. I'll return to this bug latter if no one else...
> 
> Vadim
> 
> 


-- 
  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

Sat, 12 Dec 1998 23:27:11 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] BUG ON HAVING CLAUSE
Hello Bruce,

domenica, 13 dicembre 98, you wrote:

>> Hi all,
>> 
>> Seems that I found a bug on HAVING clause, see attached file.
>> 
>> -Jose'-

BM> Can you summarize what was the result of your several bug reports on IN
BM> and HAVING.  Is there a problem that needs to be added to the TODO list?

Well, I tried IN-ANY-ALL on a subselect containing an HAVING clause
like:

select * from emp where ename in (
     select ename from emp group by ename having 1 < count(ename)
     );

and I was thought that IN-ANY-ALL were buggy, then I tried this:

select * from emp where ename IN (
     select ename from emp where deptno=20);

and now I know the bug is only in the HAVING clause.

-Jose'-



Mon, 14 Dec 1998 15:21:41 +0100
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: [HACKERS] BUG ON HAVING CLAUSE
> Well, I tried IN-ANY-ALL on a subselect containing an HAVING clause
> like:
> 
> select * from emp where ename in (
>      select ename from emp group by ename having 1 < count(ename)
>      );
> 
> and I was thought that IN-ANY-ALL were buggy, then I tried this:
> 
> select * from emp where ename IN (
>      select ename from emp where deptno=20);
> 
> and now I know the bug is only in the HAVING clause.

OK, that's good.  We now have TODO item:

	* subqueries containing HAVING return incorrect results


-- 
  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, 14 Dec 1998 10:09:40 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: Re[2]: [HACKERS] BUG ON HAVING CLAUSE