strange (numeric) casting behaviour

Started by Sven Weltealmost 20 years ago2 messagesbugs
Jump to latest
#1Sven Welte
Sven.Welte@gmxpro.net

I'm experience some strange behaviour when casting numeric values.

Given the following SQL-Statement:

SELECT
a_int,
a_num,
CAST (a_num AS NUMERIC(9,1)) AS CastTo9_1,
CAST (a_num AS NUMERIC(9,2)) AS CastTo9_2,
CAST (a_num AS NUMERIC(9,3)) AS CastTo9_3
FROM f_numtest();

Generated output is:
a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | 8.6317245901639344 | 8.632

Expected output was:
a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | --> 8.63 | 8.632

reproducable testcase:

CREATE TABLE numtest (
n NUMERIC(9,2),
i INTEGER
);
INSERT INTO numTest VALUES (13163.38000, 1525);

CREATE TABLE frslt_test (
a_int INTEGER,
a_num NUMERIC(9,2)
);

CREATE OR REPLACE FUNCTION f_numTest() RETURNS SETOF frslt_test AS '
SELECT CAST (SUM(i) AS INTEGER),
SUM(n)/SUM(i)
FROM Numtest
' LANGUAGE 'sql';

SELECT
a_int,
a_num,
CAST (a_num AS NUMERIC(9,1)) AS CastTo9_1,
CAST (a_num AS NUMERIC(9,2)) AS CastTo9_2,
CAST (a_num AS NUMERIC(9,3)) AS CastTo9_3
FROM f_numtest();

select version();

a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | 8.6317245901639344 | 8.632

version
------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu4)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sven Welte (#1)
Re: strange (numeric) casting behaviour

Sven Welte <Sven.Welte@gmxpro.net> writes:

I'm experience some strange behaviour when casting numeric values.

This doesn't really have anything to do with casting as such. The
system thinks it can throw away the cast to NUMERIC(9,2) because the
function result is declared as already NUMERIC(9,2) ... but the function
is not actually constraining its result that way. In general a function
returning NUMERIC is considered to return unconstrained NUMERIC (and
likewise for VARCHAR etc).

Possibly we should disallow the length decoration on columns used in a
function result typedef, because it fools people into thinking that such
a decoration will be enforced.

regards, tom lane