[Libreoffice-bugs] [Bug 121978] New: Problem with new function SECOND

[b***@bugs.documentfoundation.org]

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

Bug ID: 121978
Summary: Problem with new function SECOND
Product: LibreOffice
Version: 6.0.6.2 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-***@lists.freedesktop.org
Reporter: ***@us.es

When fixing the 118800 bug
(https://bugs.documentfoundation.org/show_bug.cgi?id=118800), the developers
changed the SECOND function to not round. All right.

But now, by subtracting two dates with hours, the result is a little less than
the correct one and as a consequence the SECOND function fails to give the
second of the correct difference.

Now if you write in Calc:

A2: 00:03:09
A3: 2018-10-22 11:31:44
A4: 2018-10-22 11:34:53
A5: =A4-A3 --> result 00:03:09
A6: =SECOND(A5) --> result in Calc 6.1.3.2 is 8, BAD. In Calc 6.0.6.2 and Excel
2016 is 9 (RIGHT).

The internal number in A2 is 0,0021875 that is 60*3 minutes + 9 seconds * 1/(24
hours*60 minutes*60 seconds) and in A5 is 0,002187499995. Now SECOND function
not round.

8 seconds, 99 hundredths is still the second 8 as the year of 12/31/2018 is
2018 and not 2019. You can wait 2018 if you write =YEAR("12/31/2018") and 8 if
you write =SECOND("00:00:08,99"). In 6.0.6.2 and Excel 2016 this function
results 9 and in Calc 6.1.3.2 the result is 8.

The problem is if you subtract two dates and the result goes wrong narrowly.
Before nothing happened and now offers a second less than it should.

[b***@bugs.documentfoundation.org]

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

--- Comment #1 from ***@us.es ---
Created attachment 147373
--> https://bugs.documentfoundation.org/attachment.cgi?id=147373&action=edit
SECOND(...) return second-1.

[b***@bugs.documentfoundation.org]

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

***@us.es changed:

What |Removed |Added
----------------------------------------------------------------------------
Version|6.0.6.2 release |6.1.3.2 release

[b***@bugs.documentfoundation.org]

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

***@us.es changed:

What |Removed |Added
----------------------------------------------------------------------------
Keywords| |regression

[b***@bugs.documentfoundation.org]

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

Oliver Brinzing <***@gmx.de> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmx.de
Ever confirmed|0 |1
Status|UNCONFIRMED |NEW

--- Comment #2 from Oliver Brinzing <***@gmx.de> ---
confirming with lo 6.1.4.1, 6.0.7.3: result is 8

[b***@bugs.documentfoundation.org]

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

Oliver Brinzing <***@gmx.de> changed:

What |Removed |Added
----------------------------------------------------------------------------
See Also| |https://bugs.documentfounda
| |tion.org/show_bug.cgi?id=11
| |8800

[b***@bugs.documentfoundation.org]

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

Oliver Brinzing <***@gmx.de> changed:

What |Removed |Added
----------------------------------------------------------------------------
See Also| |https://bugs.documentfounda
| |tion.org/show_bug.cgi?id=11
| |9533

[b***@bugs.documentfoundation.org]

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

--- Comment #3 from Winfried Donkers <***@libreoffice.org> ---
With version 6.3 (current master, Linux), I get 00:03:08 in cell A5, and 8 in
cell C5.
I will investigate further once I have finished working on another bug.

[b***@bugs.documentfoundation.org]

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

--- Comment #4 from ***@us.es ---
I think of two possible solutions, or have two functions: SECOND("00:00:08,99")
with result 9 and SECONDNOTROUNDED("00:00:08,99") that return 8. Or a function
with a second parameter optional rounding: =SECOND("00:00:08,99" [, BOOLEAN
ROUND]), default BOOLEAN ROUND is TRUE.

=SECOND("00:00:08,99") = SECOND("00:00:08,99",TRUE) = 9
=SECOND("00:00:08,99", FALSE) = 8

The good thing would be that computer doing the subtraction correctly, but I
imagine that they are intrinsic problems to the floating-point calculations of
the processors.

[b***@bugs.documentfoundation.org]

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

Oliver Brinzing <***@gmx.de> changed:

What |Removed |Added
----------------------------------------------------------------------------
Keywords| |bibisected, bisected

--- Comment #5 from Oliver Brinzing <***@gmx.de> ---
seems to be a regression from:

tdf#118800 fix rounding error in Calc function HOUR, MINUTE, SECOND.

Change-Id: I7a875b172493112b66fca8f70d2061371a05486c
Reviewed-on: https://gerrit.libreoffice.org/57721
Tested-by: Jenkins
Reviewed-by: Eike Rathke <***@redhat.com>
(cherry picked from commit c69e7266916ac1b8917477fb4eccdb9098da5792)
Reviewed-on: https://gerrit.libreoffice.org/57728

https://gerrit.libreoffice.org/plugins/gitiles/core/+/bda9288ffee552b55eed9dbf02e1204957bd4513

$ git bisect log
# bad: [1d66cc00ca6fd2e562cbed88704051b2f5d989e3] source
sha:8d2abb388b0a2423c9b7e1f52373e1b06dd9786f
# good: [29d08f54c2f71ffee4fe12dbb24c5f5cbedecfd2] source
sha:6eeac3539ea4cac32d126c5e24141f262eb5a4d9
git bisect start 'master' 'oldest'
# good: [3ac46f6c41b5044f162a451b10af0dc5afdcc113] source
sha:22c7c3f54dbb93f856190c561b2540064c5a767d
git bisect good 3ac46f6c41b5044f162a451b10af0dc5afdcc113
# good: [63fc3e0d41dd91f9fb3fe9891e009451285d9619] source
sha:13a1bc409d9b2f0d14f4d316b7977b1fc2eb3c8a
git bisect good 63fc3e0d41dd91f9fb3fe9891e009451285d9619
# good: [6a75149c2bee2fa1c01b36aef4b734ceee7bc025] source
sha:91d8af2c5cf4e8ec0f1ce0e532e0c896de77750b
git bisect good 6a75149c2bee2fa1c01b36aef4b734ceee7bc025
# good: [6440df548810a3adf5f7d125fe738467a5db7891] source
sha:76c0b3c516f6b0d43136522b4d476eb60211cec1
git bisect good 6440df548810a3adf5f7d125fe738467a5db7891
# bad: [809628c3decd16dacc705bda20efc603037667bf] source
sha:3c20597ada7f74a4a96dec841264593fdbf0bcd5
git bisect bad 809628c3decd16dacc705bda20efc603037667bf
# good: [da141926f04f873160a6b3c3b9c9a2e5ef754543] source
sha:7196aa63832c9d7fe59b31f98cd64696795d2841
git bisect good da141926f04f873160a6b3c3b9c9a2e5ef754543
# bad: [11d929709827f777669545d6c45744ee4857808a] source
sha:51c7a6f68ef25d7c292dc988400a2c59ef479017
git bisect bad 11d929709827f777669545d6c45744ee4857808a
# good: [dfe687583b5b2c81fa4a7a49aa17e81391043354] source
sha:337012970f62a8a40da2a02806363b560cf295df
git bisect good dfe687583b5b2c81fa4a7a49aa17e81391043354
# bad: [eabc85c658401f6e8953da678a71229e1281713b] source
sha:7b62c5266e62c3fb0ce1285949d51020075a3f81
git bisect bad eabc85c658401f6e8953da678a71229e1281713b
# good: [80d46d9ed0972d75f5c338220d74750e4dae7383] source
sha:076cb54472d5bba8916df9ee50074ac74433d694
git bisect good 80d46d9ed0972d75f5c338220d74750e4dae7383
# bad: [64716bbf3fab04a4d6300700c9d7ee205a8e1aef] source
sha:e6eb1644f84dd1fd48bbbf5418acdcc750f723bc
git bisect bad 64716bbf3fab04a4d6300700c9d7ee205a8e1aef
# bad: [6996350c78b99fc62232b3638fb7b3b9392e81ae] source
sha:bda9288ffee552b55eed9dbf02e1204957bd4513
git bisect bad 6996350c78b99fc62232b3638fb7b3b9392e81ae
# bad: [6996350c78b99fc62232b3638fb7b3b9392e81ae] source
sha:bda9288ffee552b55eed9dbf02e1204957bd4513
git bisect bad 6996350c78b99fc62232b3638fb7b3b9392e81ae
# good: [6a205cee7c62db8e04fe65ff241c7b3285e53458] source
sha:896b7e69a3182a0142a323ba5f76a2d8a811091a
git bisect good 6a205cee7c62db8e04fe65ff241c7b3285e53458
# first bad commit: [6996350c78b99fc62232b3638fb7b3b9392e81ae] source
sha:bda9288ffee552b55eed9dbf02e1204957bd4513

[b***@bugs.documentfoundation.org]

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

--- Comment #6 from ***@us.es ---
Beware that they have corrected the failure of which =SECOND("00:00:08.99") = 9
and not 8, same as YEAR ("12/31/2018") <> 2019.

Another solution would be to round up the later unit:
=SECOND("00:00:08.990") -> SECOND("00:00:08.99") = 8, but
=SECOND("00:00:08.999") -> SECOND("00:00:09.00") = 9.

Units?
DATE (dd/mm/yyyy)
HOUR
MINUTE
SECOND
HUNDREDTH or THOUSANDTHS

[b***@bugs.documentfoundation.org]

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

Korrawit Pruegsanusak <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Attachment #147373|application/octet-stream |application/vnd.oasis.opend
mime type| |ocument.spreadsheet

[b***@bugs.documentfoundation.org]

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

Winfried Donkers <***@libreoffice.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
Assignee|libreoffice-***@lists.free |***@libreoffice
|desktop.org |.org
Status|NEW |ASSIGNED

--- Comment #7 from Winfried Donkers <***@libreoffice.org> ---
I'll take it.

Bug 118800 was meant to fix rounding 59.9 seconds to 60, which is not a valid
result for SECOND, which must return integer values between 0 and 59
(inclusive).

This bug shows that the fix for tdf118800 is not correct, as it returns the
integer part of the second value, instead of rounding the value.

ODF1.2 Part 2 §6.10.16 states that the second value is to be rounded.

That means that SECOND(11:22:59.9) should return 0 (11:23:00).

[b***@bugs.documentfoundation.org]

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

Roman Kuznetsov <***@mail.ru> changed:

What |Removed |Added
----------------------------------------------------------------------------
Blocks| |108827


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=108827
[Bug 108827] [META] Calc functions bugs and enhancements