PLPGSQL bug in implicit SELECT

Started by Kovacs Zoltanover 24 years ago2 messages
#1Kovacs Zoltan
kovacsz@pc10.radnoti-szeged.sulinet.hu

After more than 3 months of hard testing I found a small bug in
PLPGSQL. (It works _too_ fine due to your excellent work... :-) Thanks!)

Consider this function:

CREATE FUNCTION testfunc () RETURNS int4 AS '
declare
ret int4;
begin
ret := column1 FROM table WHERE column2 LIKE ''%anything%''
ORDER BY column3 LIMIT 1;
return ret;
end;
' LANGUAGE 'PLPGSQL';

Unfortunately I'm getting

testdb=# select testfunc();
ERROR: query "SELECT column1 FROM table WHERE column2 LIKE '%anything%'
ORDER BY column3 LIMIT 1" returned more than one column

In psql there is no such problem. My PostgreSQL version is "PostgreSQL
7.1.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66" patched with
four small patches (in fact I almost have a 7.1.2).

My workaround for the test function is:

CREATE FUNCTION testfunc () RETURNS int4 AS '
declare
ret int4;
begin
SELECT column1 into ret FROM table WHERE column2 LIKE ''%anything%''
ORDER BY column3 LIMIT 1;
return ret;
end;
' LANGUAGE 'PLPGSQL';

Is this bug a reported one?

Regards,
Zoltan

--
Kov\'acs, Zolt\'an
kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Kovacs Zoltan (#1)
Re: PLPGSQL bug in implicit SELECT

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

Unfortunately I'm getting
testdb=# select testfunc();
ERROR: query "SELECT column1 FROM table WHERE column2 LIKE '%anything%'
ORDER BY column3 LIMIT 1" returned more than one column

This appears fixed in current sources. I believe the relevant bugfix is:

2001-05-27 16:48 tgl

* src/: backend/executor/execJunk.c, backend/executor/execMain.c,
include/executor/executor.h, include/nodes/execnodes.h: When using
a junkfilter, the output tuple should NOT be stored back into the
same tuple slot that the raw tuple came from, because that slot has
the wrong tuple descriptor. Store it into its own slot with the
correct descriptor, instead. This repairs problems with SPI
functions seeing inappropriate tuple descriptors --- for example,
plpgsql code failing to cope with SELECT FOR UPDATE.

regards, tom lane