PL/PGSQL bug in handling composite types

2012-04-24 09:59 keltezéssel, Boszormenyi Zoltan írta:

Hi,

we have found a way to make pl/pgsql throw an error for
a legitimate use case that works in plain SQL.

Minimal test case:

create table x1 (id serial primary key, d timestamptz);
create table x2 (id serial primary key, d timestamptz);
insert into x2 (d) values ('now');
create type mytype as (id bigint, d timestamptz);

Casting a set of values to "mytype" works in SQL:

=# select (max(id),min(d))::mytype from x2;
row
-------------------------------------
(1,"2012-04-24 09:04:07.475315+02")
(1 row)

=# select (max(id),min(d))::mytype from x1;
row
-----
(,)
(1 row)

Even empty data in plain text casted to "mytype" works
and individual fields can be tested for IS NULL:

=# select '(,)'::mytype;
mytype
--------
(,)
(1 row)

=# select ('(,)'::mytype).id is null;
?column?
----------
t
(1 row)

Now, try this from PL/PGSQL:

create or replace function testfunc1() returns mytype as $$declare
v_sql text;
mt mytype;
begin
v_sql := 'select (max(id),min(d))::mytype from x1';
execute v_sql into mt;
return mt;
end;$$ language plpgsql;

=# select testfunc1();
ERROR: invalid input syntax for integer: "(,)"
CONTEXT: PL/pgSQL function "testfunc1" line 6 at EXECUTE statement

The same error happens with table "x2" with data in it:

create or replace function testfunc2() returns mytype as $$declare
v_sql text;
mt mytype;
begin
v_sql := 'select (max(id),min(d))::mytype from x2';
execute v_sql into mt;
return mt;
end;$$ language plpgsql;

=# select testfunc2();
ERROR: invalid input syntax for integer: "(1,"2012-04-24 09:04:07.475315+02")"
CONTEXT: PL/pgSQL function "testfunc2" line 6 at EXECUTE statement

Same happens also with non-dynamic queries:

create or replace function testfunc1a() returns mytype as $$declare
mt mytype;
begin
select (max(id),min(d))::mytype into mt from x1;
return mt;
end;$$ language plpgsql;

=# select testfunc1a();
ERROR: invalid input syntax for integer: "(,)"
CONTEXT: PL/pgSQL function "testfunc1a" line 4 at SQL statement

create or replace function testfunc2a() returns mytype as $$declare
mt mytype;
begin
select (max(id),min(d))::mytype into mt from x2;
return mt;
end;$$ language plpgsql;

=# select testfunc2a();
ERROR: invalid input syntax for integer: "(1,"2012-04-24 09:04:07.475315+02")"
CONTEXT: PL/pgSQL function "testfunc2a" line 4 at SQL statement

Best regards,
Zoltán Böszörményi

Sorry, I didn't mention the version tested: 9.0.6 and 9.1.3.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig& Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

2012-04-24 10:01 keltezéssel, Boszormenyi Zoltan írta:

2012-04-24 09:59 keltezéssel, Boszormenyi Zoltan írta:

Hi,

we have found a way to make pl/pgsql throw an error for
a legitimate use case that works in plain SQL.

Minimal test case:

create table x1 (id serial primary key, d timestamptz);
create table x2 (id serial primary key, d timestamptz);
insert into x2 (d) values ('now');
create type mytype as (id bigint, d timestamptz);

Casting a set of values to "mytype" works in SQL:

=# select (max(id),min(d))::mytype from x2;
row
-------------------------------------
(1,"2012-04-24 09:04:07.475315+02")
(1 row)

=# select (max(id),min(d))::mytype from x1;
row
-----
(,)
(1 row)

Even empty data in plain text casted to "mytype" works
and individual fields can be tested for IS NULL:

=# select '(,)'::mytype;
mytype
--------
(,)
(1 row)

=# select ('(,)'::mytype).id is null;
?column?
----------
t
(1 row)

Now, try this from PL/PGSQL:

create or replace function testfunc1() returns mytype as $$declare
v_sql text;
mt mytype;
begin
v_sql := 'select (max(id),min(d))::mytype from x1';
execute v_sql into mt;
return mt;
end;$$ language plpgsql;

=# select testfunc1();
ERROR: invalid input syntax for integer: "(,)"
CONTEXT: PL/pgSQL function "testfunc1" line 6 at EXECUTE statement

The same error happens with table "x2" with data in it:

create or replace function testfunc2() returns mytype as $$declare
v_sql text;
mt mytype;
begin
v_sql := 'select (max(id),min(d))::mytype from x2';
execute v_sql into mt;
return mt;
end;$$ language plpgsql;

=# select testfunc2();
ERROR: invalid input syntax for integer: "(1,"2012-04-24 09:04:07.475315+02")"
CONTEXT: PL/pgSQL function "testfunc2" line 6 at EXECUTE statement

Same happens also with non-dynamic queries:

create or replace function testfunc1a() returns mytype as $$declare
mt mytype;
begin
select (max(id),min(d))::mytype into mt from x1;
return mt;
end;$$ language plpgsql;

=# select testfunc1a();
ERROR: invalid input syntax for integer: "(,)"
CONTEXT: PL/pgSQL function "testfunc1a" line 4 at SQL statement

create or replace function testfunc2a() returns mytype as $$declare
mt mytype;
begin
select (max(id),min(d))::mytype into mt from x2;
return mt;
end;$$ language plpgsql;

=# select testfunc2a();
ERROR: invalid input syntax for integer: "(1,"2012-04-24 09:04:07.475315+02")"
CONTEXT: PL/pgSQL function "testfunc2a" line 4 at SQL statement

Best regards,
Zoltán Böszörményi

Sorry, I didn't mention the version tested: 9.0.6 and 9.1.3.

The same also happens with current 9.2 GIT.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig& Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

Boszormenyi Zoltan <zb@cybertec.at> writes:

we have found a way to make pl/pgsql throw an error for
a legitimate use case that works in plain SQL.

The fact that it doesn't work the way you thought doesn't make it a bug.

plpgsql expects an assignment "INTO row_variable" to be supplied from
one select column per field in the composite variable. So instead of
executing 'select (max(id),min(d))::mytype from x1', try executing
'select max(id),min(d) from x1'.

If we were to change this, we would break a lot of existing plpgsql
code; or, if we tried to support both usages, we would create nasty
semantic ambiguities for single-column composites. Possibly the
documentation in "39.5.3. Executing a Query with a Single-row Result"
could be improved though. To my eyes, it does say that this is what
happens, but it doesn't really emphasize the point that the SELECT's
output has to be "exploded" not delivered as a single composite column.

regards, tom lane