Thread: 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 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 isdefined 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
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
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
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