SPI_prepare() doesn't work well ?

Hello all,

It seems that SPI_prepare() doesn't work well in some cases.

Pawel Pierscionek [pawel(at)astercity(dot)net] reported about the
following case 1([SQL] drop table in pgsql).
Michael Contzen [mcontzen(at)dohle(dot)com] reported about the
following case 2(PL/PGSQL bug using aggregates).
You can find it from pgsql-hackers archive.

1. PL/pgSQL can't execute UTILITY commands.
SPI_prepare() doesn't copy(save) the utilityStmt member of
Query type nodes,because copyObject() is not implemented
for nodes of (Create/Destroy etc)Stmt type.

2. Aggregates in PL/pgSQL cause wrong results.

create table t1 (i int, a int, b int);
create table t2 (i int, x int, y int);

insert into t1 values(1, 1,10);
insert into t1 values(1, 2,10);
insert into t1 values(2, 3,10);
insert into t1 values(2, 4,10);

create function func1()
returns int
as '
declare
begin
insert into t2
select i,
sum(a) as x,
sum(b) as y
from t1
group by i;
return 1;
end;
' language 'plpgsql';

select func1();

select * from t2;

The result must be the following.

i| x| y
- -+--+--
1| 3|20
2| 7|20
(2 rows)

But the result is as follows.

i| x| y
- -+--+--
1|20|20
2|20|20
(2 rows)

The result of x's are overwritten by y's.

There is a patch for this case at the end of this mail.
After I applied it,I got a correct result.
But I'm not sure this patch is right for all aggregate cases.

SPI_prepare() doesn't copy(save) nodes of Agg type
correctly.
The node of Agg type has a member named aggs.
It's a list including Aggreg type nodes which exist in
TargetList(i.e Aggreg type nodes are common to aggs
member list and TargetList).
AFAIC the common pointer is not copied to the same
pointer by copyObject() function.
In my patch I reconstruct aggs member node from
new(copied) Agg type node.
Is it proper to use set_agg_tlist_references() function to
reconstruct aggs member node for Agg type nodes ?

Thanks.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp

*** backend/nodes/copyfuncs.c.orig Tue Jan 19 09:07:48 1999
--- backend/nodes/copyfuncs.c Wed Jan 20 14:42:37 1999
***************
*** 506,512 ****

CopyPlanFields((Plan *) from, (Plan *) newnode);

! Node_Copy(from, newnode, aggs);
Node_Copy(from, newnode, aggstate);

return newnode;
--- 506,512 ----

CopyPlanFields((Plan *) from, (Plan *) newnode);

! newnode->aggs = set_agg_tlist_references(newnode);
Node_Copy(from, newnode, aggstate);

return newnode;