Re: [HACKERS] 6.4 Aggregate Bug - Mailing list pgsql-hackers

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