Обсуждение: BUG ON HAVING CLAUSE

Hi all,

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

-Jose'-

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

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

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

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

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

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

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

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