Thread: PLPGSQL bug in implicit SELECT

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
 

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 usinga junkfilter, the output tuple should NOT be stored back into thesame tuple slot
thatthe raw tuple came from, because that slot hasthe wrong tuple descriptor.  Store it into its own slot with
thecorrectdescriptor, instead.  This repairs problems with SPIfunctions seeing inappropriate tuple descriptors --- for
example,plpgsqlcode failing to cope with SELECT FOR UPDATE.
 
        regards, tom lane