[Libreoffice-bugs] [Bug 132083] FORMATTING - Wrong date substraction in cell's operation

Tue Apr 14 21:35:37 UTC 2020

https://bugs.documentfoundation.org/show_bug.cgi?id=132083

Albrecht Müller <albrecht.mueller at astrail.de> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |REOPENED
         Resolution|NOTABUG                     |---

--- Comment #15 from Albrecht Müller <albrecht.mueller at astrail.de> ---
@Mike Kaganski:
If you look at the description of the bug the problem boils down that you
subtract 8 minutes from 18 minutes. A fourth grader should be able to tell you
that this gives 10 minutes. But Calcs actual result is 9. This is clearly
wrong. That is why I think this is a bug.

Especially as it is that simple I would classified this as a major bug.
Therefore I went to great lengths to analyse what went wrong, detected a can of
worms and tried to describe some of them in form of bug reports. The following
is just a summary of my findings:

LibreOffices provides a very simple mechanism for date and time calculations.
You can consider it as just a fancy representation of floating point numbers
which is good enough for many common date or time calculations. It fails as
soon as you have to deal with concepts such as daylight saving times.

Programs that are around for decades show that it is possible to use the
internal representation of date and time values in a way that the average user
will never notice the fact that the representation in general cannot represent
time values exactly. LibreOffice was one of those programs. Bug 127334 comment
8 seems to indicate that you understand how this can be achieved. The basic
idea: Use some basic unit of time such as a second or something that is an
integral fraction of a second, e.g. a millisecond. Round any values to the
nearest integral multiples of this basic unit. The rest of the calculations
uses integer multiples of the basic unit only. As these calculation are based
on integer values they are exact and consistent – no additional round off
errors will be introduced. With the right choice of this basic unit the round
off errors of the floating point calculations will almost never accumulate to a
degree that rounding will select the wrong time unit. Therefore a user will
almost never see effects caused by round off errors. I think this is how this
established industry standard works but I never saw a formal specification of
it. No fancy stuff like infinite precision or the like is required, just
careful rounding and integer arithmetic.

I think I could trace the source of the problems to a BrokenDateTimeSpec.
That’s why I gave bug 127334 this alias which recently has been removed. Why?

The problem is that this spec does not specify the essential features of the
LibreOffice time calculation mechanism. I did not find any specification that
states what a concept of "wall clock time" or "duration" means. Given the crude
approximation of date and time calculations does it make sense to use the full
power of double floating point precision? If you really want to do this you
have to be specific about the meaning of the number 1: At this level of
precision it matters if you define it as one revolution of the earth or as 24 *
60 * 60 seconds of an atomic clock. What about daylight saving times, time
zones, calendars defined by astronomical observations or depending on future
events you don’t know yet, leap seconds etc.? That is where things start to
become really complicated.

Instead the specification defines some algorithms for the MINUTE, SECOND etc.
functions that are contradictory (e.g. the SECOND function is supposed to
return integer values between 0 and 59, but also to round to the nearest
second. What shall this function return for 59.6 seconds?, see
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018202_715980110
), use inconsistent rounding (the SECONDS function rounds to the next second,
the MINUTE function does not, see
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018194_715980110
) and thus deviate from this established industry standard. I fear the authors
of this specification were not aware of the implications of their work.
Unfortunately I don’t know how to write a bug report against the specification
and I was not able to find someone to fix these problems.

The help information leaves out a lot of essential information. So I simply
don’t know what to expect if I use features like the [HH]:MM or HH:MM format
constructs (see
https://help.libreoffice.org/6.4/en-US/text/shared/01/05020301.html?&DbPAR=CALC&System=WIN#hd_id3155870,
where is a reference to the special treatment of wall clock times or
durations?) or the various date and time functions.

These deficiencies leave a lot of room for different interpretations. In the
case at hand LibreOffice contains a pseudo random generator that essentially
uses the round off errors of floating point calculations to decide if it will
return a correct or a wrong value. This typically gives 50% correct values
where the user expects 100%. I think I saw a bug report which reported
essentially the same problem for text tables in Writer. There are other
problems related to these deficiencies, e.g. inconsistent results of date and
time functions.

I reopened this bug. Before you revert this to NOT A BUG again, please answer
the following questions:
- I think there should be pretty good reasons to justify a change that breaks
the billions of spreadsheets you mention in comment #13. The change causes
these spreadsheets to show pseudo random numbers where they used to show
correct results before. What are these reasons? I think an argument as in
comment #3 is not sufficient. The intended semantics of some time information
may be something like "about three o'clock". In this case you need duration
time rounding for wall clock times. And we are talking about time frames that
are well below the milliseconds range.
- Do you really want do demonstrate that the quality standards of LibreOffice
are so low that the expected behaviour (definition of NOT A BUG!) of this
program is to produce wrong results where a fourth grader is expected to come
up with the right solutions?

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20200414/001e3926/attachment.htm>