Download it now!
Bug 97462 - REPORT: Function SECONDS() of a timefield will sometimes produce one second less than expected
Summary: REPORT: Function SECONDS() of a timefield will sometimes produce one second l...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:5.2.0 target:5.1.2
Keywords:
Depends on:
Blocks:
 
Reported: 2016-01-31 16:03 UTC by Robert Großkopf
Modified: 2016-10-25 19:08 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Open the report and have a look at the field for SECOND() (9.96 KB, application/vnd.oasis.opendocument.database)
2016-01-31 16:03 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2016-01-31 16:03:40 UTC
Created attachment 122298 [details]
Open the report and have a look at the field for SECOND()

There has been reported bug92693. It has been fixed for LO 5.1. Now open the attached database,
open the report,
have a look at the seconds.

Function SECOND([Timefield]) will produce sometimes a value 1 second less than expected.
Comment 1 Julien Nabet 2016-01-31 16:23:52 UTC
I suppose the relevant part is in pentaho/reporting/libraries/formula/function/datetime/SecondFunction.java
     68     // time * 24 so that we get the full hours (which we remove later)
     69     final BigDecimal bd = NumberUtil.getAsBigDecimal(n);
     70     final BigDecimal hours = bd.multiply(MINUTES_PER_DAY);
     71     final BigDecimal dayAndHoursAsInt = NumberUtil.performIntRounding(hours);
     72     final BigDecimal minutesFraction = hours.subtract(dayAndHoursAsInt);
     73 
     74     // Multiply the minutes with 60 to get the minutes as ints
     75     final BigDecimal seconds = minutesFraction.multiply(SECONDS);
     76     final BigDecimal secondsAsInt = NumberUtil.performIntRounding(seconds);

Some docs about performIntRounding:
http://javadoc.pentaho.com/reporting/libformula/org/pentaho/reporting/libraries/formula/util/NumberUtil.html#performIntRounding%28java.math.BigDecimal%29

So can we rely on performIntRounding?
Comment 2 Lionel Elie Mamane 2016-01-31 20:27:48 UTC
for me it says:
 the document "report" could not be opened
...
Comment 3 Lionel Elie Mamane 2016-01-31 20:39:11 UTC
(In reply to Lionel Elie Mamane from comment #2)
> for me it says:
>  the document "report" could not be opened
> ...

Hmm.. turns out I didn't have report builder installed on this computer... sorry for the noise.
Comment 4 Buovjaga 2016-02-09 11:47:36 UTC
Confirmed.

Win 7 Pro 64-bit Version: 5.2.0.0.alpha0+
Build ID: 76ec54e8c9f3580450bca85236a4f5af0c328588
CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-02-09_00:10:35
Locale: fi-FI (fi_FI)
Comment 5 Robert Großkopf 2016-03-05 07:24:08 UTC
Bug appears also in the first available LO-version (LO 3.3.0.4, OpenSUSE 42.1 64bit rpm Linux). So I set this bug as "Inherited From OOo".
Comment 6 Lionel Elie Mamane 2016-03-05 09:05:23 UTC
This basically comes from rounding issues. Internally a TimeValue is floating-point number, expressed as a number of days. The computation done is:

 60 * fractionalPartOf(numberOfDays * 24 * 60)

The result is then TRUNCATED to an integer. But the result sometimes is something like:

 4.9999999999999999999999999999999999999999999999999999

which is then truncated to 4, while 5 is expected, because the ".9999999999999999999999999999999" comes from a rounding error, not from a time that is actually less than a second...

I first thought of forcing a rounding to the nearest nanosecond, which works well because the rounding errors are well below the nanosecond (even rounding to 10^-15 works in my tests). However, I see that the OpenFormula specification calls for rounding to the nearest second anyway:
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#SECOND

So I'll just do that.
Comment 7 Lionel Elie Mamane 2016-03-05 09:30:26 UTC
Following OpenFormula strictly will sometimes lead to a value of 60 seconds (with fractional seconds). Calc does not follow OpenFormula strictly on this point. <sigh>

On the other hand, in Calc, MINUTE() of a TimeValue of 01:02:59.60 will give 3, not 2, and SECOND() will give 0. Basically, it seems it rounds the whole value to the nearest second and then extracts the fields. Which is coherent, but not OpenFormula, and gives surprising results with fractional seconds.

So I'll stick to my first idea of rounding to the nearest nanosecond.
Comment 8 Commit Notification 2016-03-05 09:40:09 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2491b3217677d51ff3b4ee1a04fcde7a3b8f52a9

tdf#97462 reportbuilder SECOND() force rounding to nearest nanosecond

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2016-03-07 12:32:21 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f0b889e37f2e40163a02c6ca65c4962fa699b383&h=libreoffice-5-1

tdf#97462 reportbuilder SECOND() force rounding to nearest nanosecond

It will be available in 5.1.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.