pg.ddx.io  pgsql-testers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
5+ messages / 2 participants
[nested] [flat]

* Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
@ 2010-03-03 04:07  Noel Proffitt <noelp@calpacs.org>
  0 siblings, 2 replies; 5+ messages in thread

From: Noel Proffitt @ 2010-03-03 04:07 UTC (permalink / raw)
  To: pgsql-testers

[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.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
@ 2010-03-07 01:10  Josh Berkus <josh@agliodbs.com>
  parent: Noel Proffitt <noelp@calpacs.org>
  1 sibling, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2010-03-07 01:10 UTC (permalink / raw)
  To: Noel Proffitt <noelp@calpacs.org>; +Cc: pgsql-testers

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
@ 2010-03-07 23:08  Josh Berkus <josh@agliodbs.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2010-03-07 23:08 UTC (permalink / raw)
  To: Noel Proffitt <noelp@calpacs.org>; +Cc: pgsql-testers

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
@ 2010-03-08 06:45  Noel Proffitt <noelp@calpacs.org>
  parent: Noel Proffitt <noelp@calpacs.org>
  1 sibling, 1 reply; 5+ messages in thread

From: Noel Proffitt @ 2010-03-08 06:45 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-testers

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






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function
@ 2010-03-08 18:10  Josh Berkus <josh@agliodbs.com>
  parent: Noel Proffitt <noelp@calpacs.org>
  0 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2010-03-08 18:10 UTC (permalink / raw)
  To: Noel Proffitt <noelp@calpacs.org>; +Cc: pgsql-testers

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2010-03-08 18:10 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-03 04:07 Numerics of diffrent scales Raises Type Mismatch Error in a Set Returning Function Noel Proffitt <noelp@calpacs.org>
2010-03-07 01:10 ` Josh Berkus <josh@agliodbs.com>
2010-03-07 23:08   ` Josh Berkus <josh@agliodbs.com>
2010-03-08 06:45 ` Noel Proffitt <noelp@calpacs.org>
2010-03-08 18:10   ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox