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,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@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;