Conversion error of floating point numbers in pl/pgsql
Hello. I found that 9.5 has an undocumented difference from 9.4
in type cast in pl/pgsql and I think it might better be mentioned
as a change of behavior in release notes.
Whether do you think it is worth mentioning or not in release notes?
=====
9.4 and 9.5 has difference in casting between floating point
numbers.
CREATE OR REPLACE FUNCTION hoge () RETURNS text AS $$ DECLARE vr real; vf8 float8; BEGIN vr := 0.1; vf8 = vr; RETURN 'hoge = '|| vf8 ; END; $$ LANGUAGE plpgsql;
9.5=# select hoge();
hoge
--------------------------
hoge = 0.100000001490116
9.4=# select hoge();
hoge
------------
hoge = 0.1
This is stemming from the difference between '0.1'::real::float8
and '0.1'::real::text::float8, made in exec_cast_value().
=# select '0.1'::real::float8, '0.1'::real::text::float8;
float8 | float8
-------------------+--------
0.100000001490116 | 0.1
This example itself looks somewhat artifitial but it would be
rather common to load real values in a table into float8
variables in a function for further calculations.
This is a side effect of the commit
1345cc67bbb014209714af32b5681b1e11eaf964 and the relase notes has
the following discription corresponds to this commit in the
Migration section, with no mention of this.
Use assignment cast behavior for data type conversions in
PL/pgSQL assignments, rather than converting to and from text
(Tom Lane)This change causes conversions of Booleans to strings to produce
true or false, not t or f. Other type conversions may succeed in
more cases than before; for example, assigning a numeric value
3.9 to an integer variable will now assign 4 rather than
failing. If no assignment-grade cast is defined for the
particular source and destination types, PL/pgSQL will fall back
to its old I/O conversion behavior.
Whether do you think it is worth mentioning or not?
Though the attached patch adds a description for that, it should
be rewritten even if this is worth mentioning.
+ This change also may bring different results of type casts
+ between floating point numbers having different conversion
+ errors.regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
Attachments:
95_releasenotes_plpgsql_fp_error.difftext/x-patch; charset=us-asciiDownload
diff --git a/doc/src/sgml/release-9.5.sgml b/doc/src/sgml/release-9.5.sgml
index 64057c3..5c86222 100644
--- a/doc/src/sgml/release-9.5.sgml
+++ b/doc/src/sgml/release-9.5.sgml
@@ -108,7 +108,9 @@
an integer variable will now assign 4 rather than failing. If no
assignment-grade cast is defined for the particular source and
destination types, <application>PL/pgSQL</> will fall back to its old
- I/O conversion behavior.
+ I/O conversion behavior. This change also may bring different results of
+ type casts between floating point numbers having different conversion
+ errors.
</para>
</listitem>
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> writes:
Hello. I found that 9.5 has an undocumented difference from 9.4
in type cast in pl/pgsql and I think it might better be mentioned
as a change of behavior in release notes.
Whether do you think it is worth mentioning or not in release notes?
This seems unnecessarily alarmist to me. Anybody who's in the habit
of converting between float4 and float8 will already be used to this
behavior, because it is what has always happened everywhere else in
the system.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello,
At Mon, 16 Nov 2015 09:49:54 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote in <32508.1447685394@sss.pgh.pa.us>
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> writes:
Hello. I found that 9.5 has an undocumented difference from 9.4
in type cast in pl/pgsql and I think it might better be mentioned
as a change of behavior in release notes.Whether do you think it is worth mentioning or not in release notes?
This seems unnecessarily alarmist to me. Anybody who's in the habit
of converting between float4 and float8 will already be used to this
behavior, because it is what has always happened everywhere else in
the system.
I guess not a small number of users don't have an enough insight
to antcipate such influence, but I'll agree to ommit this if such
a kind of users are not in target of the release notes.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Nov 16, 2015 at 9:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> writes:
Hello. I found that 9.5 has an undocumented difference from 9.4
in type cast in pl/pgsql and I think it might better be mentioned
as a change of behavior in release notes.Whether do you think it is worth mentioning or not in release notes?
This seems unnecessarily alarmist to me. Anybody who's in the habit
of converting between float4 and float8 will already be used to this
behavior, because it is what has always happened everywhere else in
the system.
Sure, but that doesn't mean nobody's functions will start behaving
differently. It seems worth mentioning as a backward compatibility
issue to me, because if something breaks, it may not be immediately
obvious why it has gotten broken.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Nov 17, 2015 at 9:00 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Nov 16, 2015 at 9:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> writes:
Hello. I found that 9.5 has an undocumented difference from 9.4
in type cast in pl/pgsql and I think it might better be mentioned
as a change of behavior in release notes.Whether do you think it is worth mentioning or not in release notes?
This seems unnecessarily alarmist to me. Anybody who's in the habit
of converting between float4 and float8 will already be used to this
behavior, because it is what has always happened everywhere else in
the system.Sure, but that doesn't mean nobody's functions will start behaving
differently. It seems worth mentioning as a backward compatibility
issue to me, because if something breaks, it may not be immediately
obvious why it has gotten broken.
Agreed, but the note should be followed by another one warning against
any expectations of floating point behavior below the precision
threshold :-).
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers