Обсуждение: 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
> > > 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)
> 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