Thread: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function

[TEST REPORT]

[Release]: 9.0 Alpha 4

[Test Type]: feature

[Test]: NUMERICS OF DIFFERENT SCALE UNABLE TO CAST TO RESULTS IN SET
RETURNING FUNCTION

[Platform]: Linux RHEL/Fedora

[Parameters]:

[Failure]: Yes

[Results]: ERROR:  wrong record type supplied in RETURN NEXT
DETAIL:  Returned type numeric does not match expected type
numeric(14,2) in column 1.
CONTEXT:  PL/pgSQL function "check_numeric" line 5 at RETURN NEXT

-- Test case

CREATE TABLE a_table ( val NUMERIC );
INSERT INTO a_table VALUES (42);

CREATE TABLE b_table ( val NUMERIC(14,2) );

CREATE OR REPLACE FUNCTION check_numeric() RETURNS SETOF b_table AS
$$
DECLARE
  myrec RECORD;
BEGIN
  SELECT * INTO myrec FROM a_table;
  RETURN NEXT myrec;
  RETURN;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
SELECT * FROM check_numeric();

[Comments]: Works in Pg 8.3 and 8.4. Didn't see a change in the release
notes notifying of the behavior change.

On 3/2/10 8:07 PM, Noel Proffitt wrote:
> [TEST REPORT]
>
> [Release]: 9.0 Alpha 4
>
> [Test Type]: feature
>
> [Test]: NUMERICS OF DIFFERENT SCALE UNABLE TO CAST TO RESULTS IN SET
> RETURNING FUNCTION

Verified as a problem in 9.0.

--Josh Berkus

On 3/6/10 5:10 PM, Josh Berkus wrote:
> On 3/2/10 8:07 PM, Noel Proffitt wrote:
>> [TEST REPORT]
>>
>> [Release]: 9.0 Alpha 4
>>
>> [Test Type]: feature
>>
>> [Test]: NUMERICS OF DIFFERENT SCALE UNABLE TO CAST TO RESULTS IN SET
>> RETURNING FUNCTION
>
> Verified as a problem in 9.0.

Per pgsql-bugs, it's an expected compatibility issue due to unifying the
SQL and PLPGSQL parsers.  That is, the fact that plpgsql used to let you
do this was regarded as a bug, and was not consistent with the SQL
command line.

Worth noting in the release notes as part of a general class of
backwards-compatibility issues.

--Josh Berkus

On Sun, 2010-03-07 at 15:08 -0800, Josh Berkus wrote:
> Per pgsql-bugs, it's an expected compatibility issue due to unifying the
> SQL and PLPGSQL parsers.  That is, the fact that plpgsql used to let you
> do this was regarded as a bug, and was not consistent with the SQL
> command line.
>
> Worth noting in the release notes as part of a general class of
> backwards-compatibility issues.
>
> --Josh Berkus
>

Note that this behavior change affects seems to affect other types who's
size is different such as character varying.

I'm probably not understanding, but I'm not sure I follow what the
consistency issue is. It seems like in most other parts of PG, types are
cast sensibly without complaint.

In 9.0 and 8.4 we can do things like:

  CREATE TABLE foo (n NUMERIC(10,2));
  INSERT INTO foo values (42.777777::NUMERIC(12,2));
  INSERT INTO foo values (42.777777::NUMERIC(8,2));
  INSERT INTO foo values (42.777777::NUMERIC(14,8));
  SELECT * FROM foo
  JOIN (VALUES (42.78::NUMERIC)) AS bar(m) ON foo.n = bar.m;

The values are rounded and cast; Same with varchar of various sizes.

But when returning a setof things are now strictly checked. However,
when selecting into a record PL doesn't complain if the sizes don't
match the substructure. Also curious is that single value return types
are not too particular. For example:

CREATE OR REPLACE FUNCTION check_me() RETURNS VARCHAR(2) AS
$$
BEGIN
  RETURN 42.7777::NUMERIC(7,4);
END;
$$ LANGUAGE 'plpgsql';
SELECT *,pg_typeof(check_me) FROM check_me();
 check_me |     pg_typeof
----------+-------------------
 42.7777  | character varying
(1 row)

-Noel Proffitt

On 3/7/10 10:45 PM, Noel Proffitt wrote:
> On Sun, 2010-03-07 at 15:08 -0800, Josh Berkus wrote:
>> Per pgsql-bugs, it's an expected compatibility issue due to unifying the
>> SQL and PLPGSQL parsers.  That is, the fact that plpgsql used to let you
>> do this was regarded as a bug, and was not consistent with the SQL
>> command line.
>>
>> Worth noting in the release notes as part of a general class of
>> backwards-compatibility issues.
>>
>> --Josh Berkus
>>
>
> Note that this behavior change affects seems to affect other types who's
> size is different such as character varying.
>
> I'm probably not understanding, but I'm not sure I follow what the
> consistency issue is. It seems like in most other parts of PG, types are
> cast sensibly without complaint.

Please take this up on pgsql-hackers.

--Josh Berkus