Calc: |MAX| converted to 0,0000..0000E+00 in save-load cycle

[newbie-02]

hello @all,

sorry that i have so much questions theese days, life (with calc) is quite ‘not so easy’ for me theese days …

i need to play with values to check possibilities for rounding against fp-conversion artefacts,
i tested 1,79769313486232E+308 - see screenshot - being the biggest number calc can handle,
but after save-load of the file a group of cells keyed in as in the green bordered area comes up as in the red bordered area, see the difference between the green and magenta highlighted cells,

you can see the formula for cell A3 in the formula bar, A1 has! been a ‘big value’ on save, autocalc is! on, i didn’t ‘tricks around’ with the screenshot, just stumbled about something new ‘irritating’ …

on superuser.com i’d read that the max limit for a double float value is 1.79769313486231570 • 10^308, but if i key that in a calc cell it’s rounded by calc to 1.79769313486232E+308, without any message that this value is too large,

larger numbers are usually simply converted to text? without notice to the user! this not

happened with ver 7.1.0.0.a01 from 2020-09-05, rechecked with ver 6.1.6.3 release, same problem … could someone retest before i file a bug?

[erAck]

the max limit for a double float value is 1.79769313486231570 • 10^308

Yes, that is a significand of 1111111111111111111111111111111111111111111111111111 and exponent of 11111111110

but if i key that in a calc cell it’s rounded by calc to 1.79769313486232E+308, without any message that this value is too large

The value is not too large, otherwise it wouldn’t be accepted as numeric input. The display string of the underlying floating point value though is limited to a precision of 15 digits, that’s why it appears rounded.

larger numbers are usually simply converted to text?

No. It’s the other way around. Text input larger than an acceptable numeric value is not converted to numeric but stays text. Like with any text input, if it’s not convertible to numeric or currency or date or time or … it stays text.

[newbie-02]

@erAck:
still confused,
cell format ‘number standard’, keyed in 1,79769313486232E+308, shown as text, cell format changed itself to text,
cell format ‘number standard’, keyed in 1,7976931348623157E+308, widened decimal range, shown as number (right aligned), cell format changed itself to scientific, shown as ‘1,79769313486232E+308’,
edited! that to ‘1,79769313486232E+308’, still a number, cell format still scientific,
cell format ‘scientific’, keyed in 1,79769313486233E+308 or any bigger value, shown as number (right aligned), cell format still scientific, cutted down to ‘1,79769313486232E+308’,
different handling of ‘too big to nail’ values acc. to cell format?
irritating for simple minds (like me) …

[erAck]

It looks like editing a cell trying to overwrite it with a too large value is refused. I can’t say for sure without inspecting things which I won’t do right now. But if so it seems like a good feature to me.

[erAck]

The problem is that the rounded display string 1.79769313486232E+308 is saved to file and thus upon load can’t be read as a double floating point value as it is too large.

Update 2021-01-27

Fwiw, to pull this up from the comments, this was bug tdf#136272 and accepting the value as DBL_MAX double maximum value and writing the correct value 1.7976931348623157E+308 was fixed for LibreOffice 7.0.4 and 7.1.0.

[newbie-02]

i’d suggest to either not round the display string (why is it neccessary? covering errors in evaluation of last fractional bits), or in the range of +/- |MAX| truncate the values ‘towards zero’ instead … or … to stay consistent with ‘ceiling’ for input … cut down all values above |MAX|, but don’t toggle in handling messing up valid user input in display - save - load cycle …

[erAck]

Fwiw, with https://gerrit.libreoffice.org/c/core/+/106586 and cherry-picks to branches 1.79769313486232e+308 will be accepted as DBL_MAX to be able to read back such saved values.

And please finally understand that display values have to be rounded (though the implementation could and should be enhanced) because otherwise in some cases we’d get even more unexpected strings, such as 9.999999999999999E22 or 0.999999999999999E23 instead of 1E23; the decimal precision of binary64 double floating-point with a 53 bit mantissa is log2(pow(2,53)) ~= 15.95 so there is almost no value 15 or 16 or even 17 decimal digits could exactly represent without any treatments. Though there are 17 decimal digits necessary to represent double values as strings that convert back to the same values, those are not the display strings a user expects. Also there are reasons that it took until the C++17 standard to define std::to_chars() and std::from_chars() and yet many compilers still do not implement them.

[newbie-02]

@erAck: from your answer and comment i understand that it is ‘work in progress’, as well on underlying compiler(s) as in LO,
and that it is ‘not very easy’ happy hacking … (be aware of all the patches implemented to overcome weaknesses now or in future becoming weaknesses themself),
‘display values have to be rounded’ - my ‘vague feeling’ is that this is done, and in some cases it is! good to take the rounded value for downstream use (e.g. 0,1 + 0,2 != 0,3 problem), and in some cases it’s bad (this case, storing a rounded string representing a value above [MAX] in a file with respective reload problems),
in general i’d think that ‘correcting’ instead of rounding (which already fails in itself for some cases) would be better, but admit it’s difficult …

[newbie-02]

addendum … sometimes there is! valid value / content in the 16th and or 17th digit not shown to the user, e.g. ‘=2^53+2^50+1’, ‘=2^53+2^50+2’, ‘=2^53+2^50+3’, ‘=2^53+2^50+4’ contain a ‘granular 2’ information if and what was added as last summand ‘behind’ the ‘00’ of the ‘10133099161583600,0’ or ‘1,0133099161583600E+16’ display string, and as this value is! accounted for downstream calculations it would be nice for the user to be able to see it …

[newbie-02]

@erAck:
‘was fixed for LibreOffice 7.0.4 and 7.1.0.’ …