

Bruce Momjian wrote:

> Did we fix this yet?
>
> > While testing  my 6.4 patch to allow functions/expressions to be
> > specified in the ORDER/GROUP BY  clause (and not in the target list)  I
> > came across a nasty little bug.     A segmentation fault gets thrown
> > somewhere in replace_agg_clause() when using aggregates, in combination
> > with a function or expression.   (I  am still tracking down the
> > offending lines of code.  Sorry, the Linux/GCC environment is still new
> > to me.)
> >
> > I backed out my patch, and discovered  the bug was still present.  The
> > bug does not exist in version 6.3.2.  Here is an example:
> >
> > -- This crashes the backend
> >     select upper(a) as x, count(k) from t group by x;
> >
> > --  This works fine
> >     select upper(a) as x, count(a) from t group by x;
> >
> > Notice how in the first query, (the one that does not work) upper() has
> > a different argument than count().  And  in the second query (the one
> > that works) upper() has the same argument as count().     When using
> > count(*) it will always fail.
> >
> > This is the the pattern that I have observed.   If the arguments in the
> > aggregate and non-aggregate functions are the same, it runs; if the
> > arguments in the aggregate and non-aggregate functions are different, it
> > crashes.
> >
> > I have attached a test script for anyone able to help with (or verify)
> > this problem.
> >
> >
> >
>
> > create table t  (
> >       j integer,
> >       k integer,
> >       a varchar
> > );
> > insert into t values (1, 1, 'a');
> > insert into t values (2, 2, 'b');
> > insert into t values (2, 3, 'c');
> > insert into t values (3, 4, 'A');
> > insert into t values (3, 5, 'B');
> > insert into t values (3, 6, 'C');
> > insert into t values (4, 7, 'a');
> > insert into t values (4, 8, 'b');
> > insert into t values (4, 9, 'c');
> > insert into t values (4, 0, 'a');
> >
> > -- OK
> > select upper(a) as x, count(a) from t group by x;
> >
> > -- OK
> > select k/2 as x, max(k) from t group by x;
> >
> > -- OK
> > -- select k as x, max(j) from t group by x;
> >
> > -- OK
> > select upper(a) as x, count(k), count(a) from t group by x;
> >
> > -- CRASH
> > select k/2 as x, max(j) from t group by x;
> >
> > -- CRASH
> > select upper(a) as x, count(k) from t group by x;
> >
> > -- CRASH
> > select upper(a) as x, count(xmin) from t group by x;
> >
> > -- CRASH
> > select upper(a) as x, count(oid) from t group by x;
> >
> > -- CRASH
> > select upper(a) as x, count(*) from t group by x;

It seems that the last case has corrected itself.

    select upper(a) as x, count(*) from t group by x
        WORKS

The other conditions are still a problem.   In general, as long as the
argument in the grouped function is used somewhere else in the target list
there is no problem.

    select func(x), x, aggfunc(y) group by func
        WORKS

    select func(x), aggfunc(y) group by func
        CRASHES


Mon, 24 Aug 1998 08:24:06 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] 6.4 Aggregate Bug
> 
> 
> Bruce Momjian wrote:
> 
> > Did we fix this yet?
> >
> > > While testing  my 6.4 patch to allow functions/expressions to be
> > > specified in the ORDER/GROUP BY  clause (and not in the target list)  I
> > > came across a nasty little bug.     A segmentation fault gets thrown
> > > somewhere in replace_agg_clause() when using aggregates, in combination
> > > with a function or expression.   (I  am still tracking down the
> > > offending lines of code.  Sorry, the Linux/GCC environment is still new
> > > to me.)
> > >
> > > I backed out my patch, and discovered  the bug was still present.  The
> > > bug does not exist in version 6.3.2.  Here is an example:
> > >
> > > -- This crashes the backend
> > >     select upper(a) as x, count(k) from t group by x;
> > >
> > > --  This works fine
> > >     select upper(a) as x, count(a) from t group by x;
> > >
> > > Notice how in the first query, (the one that does not work) upper() has
> > > a different argument than count().  And  in the second query (the one
> > > that works) upper() has the same argument as count().     When using
> > > count(*) it will always fail.
> > >
> > > This is the the pattern that I have observed.   If the arguments in the
> > > aggregate and non-aggregate functions are the same, it runs; if the
> > > arguments in the aggregate and non-aggregate functions are different, it
> > > crashes.
> > >
> > > I have attached a test script for anyone able to help with (or verify)
> > > this problem.
> > >
> > >
> > >
> >
> > > create table t  (
> > >       j integer,
> > >       k integer,
> > >       a varchar
> > > );
> > > insert into t values (1, 1, 'a');
> > > insert into t values (2, 2, 'b');
> > > insert into t values (2, 3, 'c');
> > > insert into t values (3, 4, 'A');
> > > insert into t values (3, 5, 'B');
> > > insert into t values (3, 6, 'C');
> > > insert into t values (4, 7, 'a');
> > > insert into t values (4, 8, 'b');
> > > insert into t values (4, 9, 'c');
> > > insert into t values (4, 0, 'a');
> > >
> > > -- OK
> > > select upper(a) as x, count(a) from t group by x;
> > >
> > > -- OK
> > > select k/2 as x, max(k) from t group by x;
> > >
> > > -- OK
> > > -- select k as x, max(j) from t group by x;
> > >
> > > -- OK
> > > select upper(a) as x, count(k), count(a) from t group by x;
> > >
> > > -- CRASH
> > > select k/2 as x, max(j) from t group by x;
> > >
> > > -- CRASH
> > > select upper(a) as x, count(k) from t group by x;
> > >
> > > -- CRASH
> > > select upper(a) as x, count(xmin) from t group by x;
> > >
> > > -- CRASH
> > > select upper(a) as x, count(oid) from t group by x;
> > >
> > > -- CRASH
> > > select upper(a) as x, count(*) from t group by x;
> 
> It seems that the last case has corrected itself.
> 
>     select upper(a) as x, count(*) from t group by x
>         WORKS
> 
> The other conditions are still a problem.   In general, as long as the
> argument in the grouped function is used somewhere else in the target list
> there is no problem.
> 
>     select func(x), x, aggfunc(y) group by func
>         WORKS
> 
>     select func(x), aggfunc(y) group by func
>         CRASHES
> 
> 

Added to TODO:

* select upper(usename), count(usesysid) from pg_shadow group by 1 fails

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 28 Aug 1998 23:48:48 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.4 Aggregate Bug
> Added to TODO:
> 
> * select upper(usename), count(usesysid) from pg_shadow group by 1 fails

Fixed. Was broken by new HAVING code...

I see another problems in HAVING:

1.
drop table x;
create table x (a int, b int, c int);
insert into x values (1,1,0);
insert into x values (1,1,1);
select a, min (b) from x group by a having min(b) > c;
-- a|min
-- -+---
-- 1|  1
-- (1 row)
delete from x;
vacuum x;
insert into x values (1,1,1);
insert into x values (1,1,0);
select a, min (b) from x group by a having min(b) > c;
-- a|min
-- -+---
-- (0 rows)

Using C-column is illegal!

2.
vac=> select a, min (b) from x group by a having min(b) > 0 or a = 0;
                                                            ^^^^^^^^
ERROR:  This could have been done in a where clause!!
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No, couldn't! Using GroupBy expr in having must be allowed.

I'm going to change HAVING behaviour...

Also, could someone test is HAVING without aggregates
disallowed or not:

select a, min (b) from x group by a having a = 0;

- ???

Vadim

Tue, 08 Sep 1998 14:40:16 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] 6.4 Aggregate Bug and HAVING problems...