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(at)candle(dot)pha(dot)pa(dot)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)