Re: PL/PGSQL bug in handling composite types

From: Boszormenyi Zoltan <zb(at)cybertec(dot)at>
To: pgsql-hackers(at)postgresql(dot)org
Cc: hs(at)cybertec(dot)at, michael(dot)krueger(at)voipfuture(dot)com
Subject: Re: PL/PGSQL bug in handling composite types
Date: 2012-04-24 08:04:41
Message-ID: 4F965E99.6030300@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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/

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2012-04-24 08:08:36 Re: B-tree page deletion boundary cases
Previous Message Boszormenyi Zoltan 2012-04-24 08:01:58 Re: PL/PGSQL bug in handling composite types