SPI_prepare() doesn't work well ? - Mailing list pgsql-hackers

Hello all,

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

Pawel Pierscionek [pawel@astercity.net] reported about the 
following case 1([SQL] drop table in pgsql).
Michael Contzen [mcontzen@dohle.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,becausecopyObject() 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
surethis 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
listincluding 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
reconstructaggs 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@tpf.co.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;