Обсуждение: BUG #14138: Inconsistent rounding behavior in float4 coercion
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDEzOApMb2dnZWQgYnk6ICAg ICAgICAgIENoYXJsZXMKRW1haWwgYWRkcmVzczogICAgICBjb29wZXIuY2hh cmxlcy5tQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS4zCk9w ZXJhdGluZyBzeXN0ZW06ICAgTGludXggMy4xNi4wLjcwLWdlbmVyaWMKRGVz Y3JpcHRpb246ICAgICAgICAKCkJhc2VkIG9uIG15IHJlYWRpbmcgb2YgdGhl IGRvY3VtZW50YXRpb24KKGh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvZG9j cy85LjUvc3RhdGljL2RhdGF0eXBlLW51bWVyaWMuaHRtbCksIGZsb2F0NApv bmx5IHN1cHBvcnRzIHVwIHRvIDYgZGVjaW1hbCBwbGFjZXMgb2YgcHJlY2lz aW9uIChldmVuIGlmIHRoZSBudW1iZXIgZml0cwpjb21mb3J0YWJseSBpbiBh IDQtYnl0ZSBmbG9hdCkuIFRoaXMgaXMgZmluZSBidXQgSSBhbSBmaW5kaW5n IGl0IGRpZmZpY3VsdAp0byBwcmVkaWN0IHdoaWNoIHdheSB0aGUgZmxvYXQg d2lsbCB0cnVuY2F0ZSB0bzoNCg0KYGBgDQplbHNlbmRiPT4gc2VsZWN0IDE1 NDIyLjU1IDo6IGZsb2F0NDsNCiBmbG9hdDQgIA0KLS0tLS0tLS0tDQogMTU0 MjIuNQ0KKDEgcm93KQ0KDQplbHNlbmRiPT4gc2VsZWN0IDExMTQzLjE1IDo6 IGZsb2F0NDsNCiBmbG9hdDQgIA0KLS0tLS0tLS0tDQogMTExNDMuMg0KKDEg cm93KQ0KDQpgYGANCg0KUmVnYXJkbGVzcyBvZiB0aGUgZGVmYXVsdCByb3Vu ZGluZyBtb2RlICh1cCAvIGRvd24gLyBoYWxmIGV2ZW4gLwp5b3UtbmFtZS1p dCkgSSB3b3VsZCBleHBlY3QgdGhlc2UgdHdvIHRvIHJvdW5kIGluIHRoZSBz YW1lIGRpcmVjdGlvbiAtLSBvcgphdCBsZWFzdCB0aGUgcm91bmRpbmcgYWxn b3JpdGhtIHRvIGJlIHNwZWNpZmllZCBhbmQgZG9jdW1lbnRlZC4NCg0KUGxh dGZvcm0gaW5mb3JtYXRpb246DQpjaGFybGVzQGZ1dHVyZWxhYnM6fiQgdW5h bWUgLXINCjMuMTYuMC03MC1nZW5lcmljDQoNCmNoYXJsZXNAZnV0dXJlbGFi czp+JCAvbGliL3g4Nl82NC1saW51eC1nbnUvbGliYy0yLjE5LnNvIA0KR05V IEMgTGlicmFyeSAoVWJ1bnR1IEVHTElCQyAyLjE5LTB1YnVudHU2LjcpIHN0 YWJsZSByZWxlYXNlIHZlcnNpb24gMi4xOSwKYnkgUm9sYW5kIE1jR3JhdGgg ZXQgYWwuDQpDb3B5cmlnaHQgKEMpIDIwMTQgRnJlZSBTb2Z0d2FyZSBGb3Vu ZGF0aW9uLCBJbmMuDQpUaGlzIGlzIGZyZWUgc29mdHdhcmU7IHNlZSB0aGUg c291cmNlIGZvciBjb3B5aW5nIGNvbmRpdGlvbnMuDQpUaGVyZSBpcyBOTyB3 YXJyYW50eTsgbm90IGV2ZW4gZm9yIE1FUkNIQU5UQUJJTElUWSBvciBGSVRO RVNTIEZPUiBBDQpQQVJUSUNVTEFSIFBVUlBPU0UuDQpDb21waWxlZCBieSBH TlUgQ0MgdmVyc2lvbiA0LjguMi4NCkNvbXBpbGVkIG9uIGEgTGludXggMy4x My4xMSBzeXN0ZW0gb24gMjAxNi0wMi0xNi4NCkF2YWlsYWJsZSBleHRlbnNp b25zOg0KCWNyeXB0IGFkZC1vbiB2ZXJzaW9uIDIuMSBieSBNaWNoYWVsIEds YWQgYW5kIG90aGVycw0KCUdOVSBMaWJpZG4gYnkgU2ltb24gSm9zZWZzc29u DQoJTmF0aXZlIFBPU0lYIFRocmVhZHMgTGlicmFyeSBieSBVbHJpY2ggRHJl cHBlciBldCBhbA0KCUJJTkQtOC4yLjMtVDVCDQpsaWJjIEFCSXM6IFVOSVFV RSBJRlVOQw0KRm9yIGJ1ZyByZXBvcnRpbmcgaW5zdHJ1Y3Rpb25zLCBwbGVh c2Ugc2VlOg0KPGh0dHBzOi8vYnVncy5sYXVuY2hwYWQubmV0L3VidW50dS8r c291cmNlL2VnbGliYy8rYnVncz4uDQoNCkJlc3QsDQpDaGFybGVzCgo=
cooper.charles.m@gmail.com writes: > Based on my reading of the documentation > (http://www.postgresql.org/docs/9.5/static/datatype-numeric.html), float4 > only supports up to 6 decimal places of precision (even if the number fits > comfortably in a 4-byte float). This is fine but I am finding it difficult > to predict which way the float will truncate to: I think you misunderstand floating-point arithmetic entirely. It's rounding to the nearest so-many-digits binary value, which makes the change in the last decimal digit hard to predict. The business about "6 decimal digits" is only meant to indicate that you can expect that many decimal digits to be reproduced reliably; whether and how digits after the 6th change is a complicated business. You can see more nearly what's going on by increasing extra_float_digits so that more digits are printed. For example, at extra_float_digits = 3 it'll print a 9-decimal-digit rather than 6-decimal-digit approximation to the underlying binary value, so: regression=# select 15422.55 :: float4; float4 ------------ 15422.5498 (1 row) regression=# select 15422.5501 :: float4; float4 ------------ 15422.5498 (1 row) regression=# select 15422.5502 :: float4; float4 ------------ 15422.5498 (1 row) regression=# select 15422.5503 :: float4; float4 ------------ 15422.5508 (1 row) ... regression=# select 15422.5512 :: float4; float4 ------------ 15422.5508 (1 row) regression=# select 15422.5513 :: float4; float4 ------------ 15422.5518 (1 row) So adjacent binary values are about .001 apart in this range of values, but they don't correspond exactly to multiples of .001. Near the other value you cited: regression=# select 11143.15 :: float4; float4 ------------ 11143.1504 (1 row) ... regression=# select 11143.1508 :: float4; float4 ------------ 11143.1504 (1 row) regression=# select 11143.1509 :: float4; float4 ------------ 11143.1514 (1 row) They're still about .001 apart, but now a little closer to the decimal value below rather than the decimal value above, which accounts for the different rounding behavior when showing fewer digits than this. > Regardless of the default rounding mode (up / down / half even / > you-name-it) I would expect these two to round in the same direction -- or > at least the rounding algorithm to be specified and documented. It's not really Postgres' business to try to document these rounding behaviors, because as far as we're concerned they are platform-dependent. Most modern machines follow IEEE 754, https://en.wikipedia.org/wiki/IEEE_floating_point but that's by no means universal and Postgres doesn't assume it. If this is not something you're willing to deal with, use type numeric rather than float4 or float8 --- numeric stores values in decimal notation internally, so you don't get these odd roundoff effects from change of base. regards, tom lane
Thanks for taking the time to show me how to print those extra digits of precision, it really helped! As a follow-up question, I am a bit confused as to the following behavior: elsendb=> set extra_float_digits = 3; SET elsendb=> select 11143.15 :: float4; float4 ------------ 11143.1504 (1 row) elsendb=> select 11143.15 :: float4 :: text :: float4; float4 ------------ 11143.1504 (1 row) elsendb=> select 11143.15 :: float4 :: numeric :: float4; float4 ------------ 11143.2002 (1 row) How come 11143.1504 is not printed in all of these cases? Best, Charles On Sun, May 15, 2016 at 1:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > cooper.charles.m@gmail.com writes: > > Based on my reading of the documentation > > (http://www.postgresql.org/docs/9.5/static/datatype-numeric.html), > float4 > > only supports up to 6 decimal places of precision (even if the number > fits > > comfortably in a 4-byte float). This is fine but I am finding it > difficult > > to predict which way the float will truncate to: > > I think you misunderstand floating-point arithmetic entirely. It's > rounding to the nearest so-many-digits binary value, which makes the > change in the last decimal digit hard to predict. The business about > "6 decimal digits" is only meant to indicate that you can expect that > many decimal digits to be reproduced reliably; whether and how digits > after the 6th change is a complicated business. > > You can see more nearly what's going on by increasing extra_float_digits > so that more digits are printed. For example, at extra_float_digits = 3 > it'll print a 9-decimal-digit rather than 6-decimal-digit approximation > to the underlying binary value, so: > > regression=# select 15422.55 :: float4; > float4 > ------------ > 15422.5498 > (1 row) > > regression=# select 15422.5501 :: float4; > float4 > ------------ > 15422.5498 > (1 row) > > regression=# select 15422.5502 :: float4; > float4 > ------------ > 15422.5498 > (1 row) > > regression=# select 15422.5503 :: float4; > float4 > ------------ > 15422.5508 > (1 row) > > ... > > regression=# select 15422.5512 :: float4; > float4 > ------------ > 15422.5508 > (1 row) > > regression=# select 15422.5513 :: float4; > float4 > ------------ > 15422.5518 > (1 row) > > So adjacent binary values are about .001 apart in this range of values, > but they don't correspond exactly to multiples of .001. Near the > other value you cited: > > regression=# select 11143.15 :: float4; > float4 > ------------ > 11143.1504 > (1 row) > > ... > > regression=# select 11143.1508 :: float4; > float4 > ------------ > 11143.1504 > (1 row) > > regression=# select 11143.1509 :: float4; > float4 > ------------ > 11143.1514 > (1 row) > > They're still about .001 apart, but now a little closer to the decimal > value below rather than the decimal value above, which accounts for the > different rounding behavior when showing fewer digits than this. > > > Regardless of the default rounding mode (up / down / half even / > > you-name-it) I would expect these two to round in the same direction -- > or > > at least the rounding algorithm to be specified and documented. > > It's not really Postgres' business to try to document these rounding > behaviors, because as far as we're concerned they are platform-dependent. > Most modern machines follow IEEE 754, > https://en.wikipedia.org/wiki/IEEE_floating_point > but that's by no means universal and Postgres doesn't assume it. > > If this is not something you're willing to deal with, use type numeric > rather than float4 or float8 --- numeric stores values in decimal notation > internally, so you don't get these odd roundoff effects from change of > base. > > regards, tom lane >
Charles.. On Sun, May 22, 2016 at 9:18 PM, Charles <cooper.charles.m@gmail.com> wrote: > As a follow-up question, I am a bit confused as to the following behavior: > > elsendb=> set extra_float_digits = 3; ... > elsendb=> select 11143.15 :: float4 :: text :: float4; ... > 11143.1504 > elsendb=> select 11143.15 :: float4 :: numeric :: float4; ... > 11143.2002 > How come 11143.1504 is not printed in all of these cases? Probably because extra_f_d only plays when converting to/from text, and not to numeric. It really is a mechanism to insure that text dumps + restores do not modify data. The other way is to use hex or binary floating ( %a in printf ) in dumps, which, IIRC, postgres did understand on input but had no way to generate on output. If you insist on mixing finite precisison binary fractions ( float ) with decimal fractions ( numeric and/or text ) you should be prepared for this. Do not do it or use a text intermediate step always so you can control it. Francico Olarte.
Charles <cooper.charles.m@gmail.com> writes:
> As a follow-up question, I am a bit confused as to the following behavior:
> ...
> How come 11143.1504 is not printed in all of these cases?
float4 to numeric casting doesn't honor extra_float_digits, it just
converts as many digits as will be reliable.
regards, tom lane
I see, so the overriding, default rule in all these cases is, "convert as many digits as will be reliable" -- as opposed to, "convert as many digits as are needed to do a full round-trip"? Best, Charles On Mon, May 23, 2016 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Charles <cooper.charles.m@gmail.com> writes: > > As a follow-up question, I am a bit confused as to the following > behavior: > > ... > > How come 11143.1504 is not printed in all of these cases? > > float4 to numeric casting doesn't honor extra_float_digits, it just > converts as many digits as will be reliable. > > regards, tom lane >