Applied.
---------------------------------------------------------------------------
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;
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
