[Bug 1261048] Re: Wrong results from rounding functions for large argument
Thank you Christopher M. Penalver for the guidance towards a more streamlined problem report. Row 5 in the posted spreadsheet, LibreOfficeRoundingIssues.ods, shows the precise errors described in my initial report here. The cells in that row contain, successively, x=50, y=power(2,x)+1, round(y,0)-y, roundup(y,0)-y, rounddown(y,0)-y, trunc(y,0)-y, ceiling(y,1)-y, floor(y,1)-1, int(y)-y, even(y)-y and odd(y)-y. The expected result is a 0 in the cells for functions round ... int, a 1 for even(y)-y (because y is odd), and a 0 for odd(y)-y. As is highlighted in yellow in the sheet the actual result is different in each case except for the first function, plain round. A minimal set of instructions to reproduce the core of the problem follows. Open a new blank spreadsheet. In the A1 cell enter =power(2,50)+1 (without the quotes) and in the B1 cell enter =rounddown(a1,0)-a1 (without the quotes). Expected result: B1=0. Actual result: B1=5. I take it as understood that when an integer is rounded to integer then the result should be the same integer. The actual result in LibreOffice is a different integer, therefore it is a wrong result. I am asked to explain the significance of the error and to address possible negatives in fixing it. To be clear, the relative magnitude of the error is tiny and when viewed purely as a numerical error it is insignificant in any engineering or financial context. The risk of this kind of error in applied code is rather that it breaks assumptions. A person may write a spreadsheet program and take for granted that for positive argument y, rounddown(y,0) can never exceed y. The present report shows that this entirely justifiable assumption is broken in LibreOffice Calc. That risk is a risk to a third party (user of LibreOffice) and not directly to the LO project team. The significance of this error directly to LibreOffice is, I think, primarily a risk to the reputation of the product. There may be cases where one wants to follow Excel in a questionable specification of a spreadsheets function, because users rely on that specification. However, I cannot conceive of any spreadsheet design that would in any way rely on erroneous behavior of the rounding functions for large argument. Therefore I think that the Calc team could correct this error without concern over breaking any compatibility with Excel. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
Bastiaan J. Braams, thank you for reporting this and helping making Ubuntu better. Regarding this report: - This is a clearcut upstream issue. You are welcome to send this to the developers of the software by following the instructions at http://wiki.documentfoundation.org/BugReport . If you have done so, please tell us the number of the upstream bug (or the link), so we can add a bugwatch that will inform us about its status. - Marking LibreOffice Packaging and libreoffice (Ubuntu) = Won't Fix Wishlist. This does not mean the issue will not be cared about, but if it is cared about (even by Ubuntu/Canonical contributors), it is done upstream at LibreOffice. ** Description changed: + 1) lsb_release -rd + Description: Ubuntu Trusty Tahr (development branch) + Release: 14.04 + + 2) apt-cache policy libreoffice-calc + libreoffice-calc: + Installed: 1:4.1.3-0ubuntu3 + Candidate: 1:4.1.3-0ubuntu3 + Version table: + *** 1:4.1.3-0ubuntu3 0 + 500 http://us.archive.ubuntu.com/ubuntu/ trusty/main amd64 Packages + 100 /var/lib/dpkg/status + I am using Ubuntu 12.04.3 LTS and within that LibreOffice 3.5.7.2, Build - ID: 350m1(Build:2). This report concerns LibreOffice Calc; functions - roundup, rounddown, trunc, ceiling, floor, int, even and odd. + ID: 350m1(Build:2) but this is also reproducible in LO Trunk + 4.3.0.0.alpha0+ on Windows Vista: - Let y=power(2,50)+1 and please note that all integers up to power(2,53) - are exactly representable. Therefore I expect that rounding y to integer - will return y exactly, for any of the functions roundup, rounddown, - trunc, ceiling, floor and int. I also expect that even(y)=y+1 and - odd(y)=y. + What is expected to happen at a terminal: + cd ~/Desktop wget https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+attachment/3942054/+files/LibreOfficeRoundingIssues.ods localc --nologo LibreOfficeRoundingIssues - Actual results: functions roundup(y,0), rounddown(y,0), trunc(y,0), - ceiling(y,1), floor(y,1) and int(y) all return y+5. Moreover, even(y)=y - and odd(y)=y+6 according to Calc. + Is that for cell D5 is it 0. - (Note that the second argument to roundup, rounddown and trunc indicates - number of places behind the decimal symbol and the second argument to - ceiling and floor indicates the unit.) + What happens instead is that it is 5. This would be an issue with Calc + numerical precision, as the actual outcome of 5 is also the same with + Excel. - There are similar problems with these rounding functions when rounding - to some number (0) of places behind the decimal symbol. - - Example: - https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+attachment/3942054/+files/LibreOfficeRoundingIssues.ods + WORKAROUND: Use gnumeric: + apt-cache policy gnumeric + gnumeric: + Installed: 1.12.9-1 + Candidate: 1.12.9-1 + Version table: + *** 1.12.9-1 0 + 500 http://us.archive.ubuntu.com/ubuntu/ trusty/universe amd64 Packages + 100 /var/lib/dpkg/status --- ApportVersion: 2.0.1-0ubuntu17.6 Architecture: i386 DistroRelease: Ubuntu 12.04 InstallationMedia: Ubuntu 12.04.3 LTS Precise Pangolin - Release i386 (20130820.1) MarkForUpload: True Package: libreoffice 1:3.5.7-0ubuntu5 PackageArchitecture: i386 ProcEnviron: TERM=xterm PATH=(custom, no user) LANG=en_US.UTF-8 SHELL=/bin/bash ProcVersionSignature: Ubuntu 3.8.0-35.50~precise1-generic 3.8.13.13 Tags: precise running-unity Uname: Linux 3.8.0-35-generic i686 UpgradeStatus: No upgrade log present (probably fresh install) UserGroups: adm cdrom dip lpadmin plugdev sambashare sudo ** Changed in: libreoffice (Ubuntu) Importance: Undecided = Wishlist ** Changed in: libreoffice (Ubuntu) Status: Incomplete = Won't Fix -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
I have filed Bugzilla - Bug 73410 - Wrong results from rounding functions for large argument. Filed under LibreOffice Spreadsheet. Link: https://www.libreoffice.org/bugzilla/show_bug.cgi?id=73410. ** Bug watch added: LibreOffice Bugzilla #73410 https://www.libreoffice.org/bugzilla/show_bug.cgi?id=73410 -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
I have filed a report - Wrong results from rounding functions for large argument - to Bugzilla. Filed under LibreOffice Spreadsheet. Link: https://www.libreoffice.org/bugzilla/show_bug.cgi?id=73410. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: [Upstream] Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
** Also affects: df-libreoffice via https://www.libreoffice.org/bugzilla/show_bug.cgi?id=73410 Importance: Unknown Status: Unknown ** Summary changed: - Wrong results from rounding functions for large argument + [Upstream] Wrong results from rounding functions for large argument -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: [Upstream] Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
apport information ** Tags added: apport-collected running-unity ** Description changed: I am using Ubuntu 12.04.3 LTS and within that LibreOffice 3.5.7.2, Build ID: 350m1(Build:2). This report concerns LibreOffice Calc; functions roundup, rounddown, trunc, ceiling, floor, int, even and odd. Let y=power(2,50)+1 and please note that all integers up to power(2,53) are exactly representable. Therefore I expect that rounding y to integer will return y exactly, for any of the functions roundup, rounddown, trunc, ceiling, floor and int. I also expect that even(y)=y+1 and odd(y)=y. Actual results: functions roundup(y,0), rounddown(y,0), trunc(y,0), ceiling(y,1), floor(y,1) and int(y) all return y+5. Moreover, even(y)=y and odd(y)=y+6 according to Calc. (Note that the second argument to roundup, rounddown and trunc indicates number of places behind the decimal symbol and the second argument to ceiling and floor indicates the unit.) There are similar problems with these rounding functions when rounding to some number (0) of places behind the decimal symbol. I have placed a *.ods spreadsheet that shows the issues in two places for public access: https://drive.google.com/file/d/0B36cHU4sR7HmS25tcXh5WTg1Q1U/edit?usp=sharing https://www.dropbox.com/s/uy0hzgginny4fuc/LibreOfficeRoundingIssues.ods + --- + ApportVersion: 2.0.1-0ubuntu17.6 + Architecture: i386 + DistroRelease: Ubuntu 12.04 + InstallationMedia: Ubuntu 12.04.3 LTS Precise Pangolin - Release i386 (20130820.1) + MarkForUpload: True + Package: libreoffice 1:3.5.7-0ubuntu5 + PackageArchitecture: i386 + ProcEnviron: + TERM=xterm + PATH=(custom, no user) + LANG=en_US.UTF-8 + SHELL=/bin/bash + ProcVersionSignature: Ubuntu 3.8.0-35.50~precise1-generic 3.8.13.13 + Tags: precise running-unity + Uname: Linux 3.8.0-35-generic i686 + UpgradeStatus: No upgrade log present (probably fresh install) + UserGroups: adm cdrom dip lpadmin plugdev sambashare sudo ** Attachment added: Dependencies.txt https://bugs.launchpad.net/bugs/1261048/+attachment/3942052/+files/Dependencies.txt -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
** Attachment added: LibreOfficeRoundingIssues.ods https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+attachment/3942054/+files/LibreOfficeRoundingIssues.ods ** Description changed: I am using Ubuntu 12.04.3 LTS and within that LibreOffice 3.5.7.2, Build ID: 350m1(Build:2). This report concerns LibreOffice Calc; functions roundup, rounddown, trunc, ceiling, floor, int, even and odd. Let y=power(2,50)+1 and please note that all integers up to power(2,53) are exactly representable. Therefore I expect that rounding y to integer will return y exactly, for any of the functions roundup, rounddown, trunc, ceiling, floor and int. I also expect that even(y)=y+1 and odd(y)=y. Actual results: functions roundup(y,0), rounddown(y,0), trunc(y,0), ceiling(y,1), floor(y,1) and int(y) all return y+5. Moreover, even(y)=y and odd(y)=y+6 according to Calc. (Note that the second argument to roundup, rounddown and trunc indicates number of places behind the decimal symbol and the second argument to ceiling and floor indicates the unit.) There are similar problems with these rounding functions when rounding to some number (0) of places behind the decimal symbol. - I have placed a *.ods spreadsheet that shows the issues in two places - for public access: + Example: + https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+attachment/3942054/+files/LibreOfficeRoundingIssues.ods - https://drive.google.com/file/d/0B36cHU4sR7HmS25tcXh5WTg1Q1U/edit?usp=sharing - https://www.dropbox.com/s/uy0hzgginny4fuc/LibreOfficeRoundingIssues.ods - --- + --- ApportVersion: 2.0.1-0ubuntu17.6 Architecture: i386 DistroRelease: Ubuntu 12.04 InstallationMedia: Ubuntu 12.04.3 LTS Precise Pangolin - Release i386 (20130820.1) MarkForUpload: True Package: libreoffice 1:3.5.7-0ubuntu5 PackageArchitecture: i386 ProcEnviron: - TERM=xterm - PATH=(custom, no user) - LANG=en_US.UTF-8 - SHELL=/bin/bash + TERM=xterm + PATH=(custom, no user) + LANG=en_US.UTF-8 + SHELL=/bin/bash ProcVersionSignature: Ubuntu 3.8.0-35.50~precise1-generic 3.8.13.13 Tags: precise running-unity Uname: Linux 3.8.0-35-generic i686 UpgradeStatus: No upgrade log present (probably fresh install) UserGroups: adm cdrom dip lpadmin plugdev sambashare sudo -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
Bastiaan J. Braams, thank you for performing the apport-collect. In the future, please do not use dropbox, but instead just add the attachment to the Launchpad report. Despite this, converting your .ods to xls and opening Excel, I see the exact same results. So, from a Excel compatibility expectation perspective, which is important to many unless a clear cut benefit exists that significantly outweight the negatives, the results are expected as encountered. Despite this, in order to create a more streamlined report, please feel free to focus the discussion down to one particular row of cells and function, not all cells and all functions encountered, what was expected, and what specific value you obtained, and why this would benefit LibreOffice (including those in the expectation compatibility camp). -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
** Tags added: 12.04-lts calc ceiling even floor functions int libreoffice odd rounddown roundup trunc -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1261048] Re: Wrong results from rounding functions for large argument
Bastiaan J. Braams, thank you for taking the time to report this and helping to make Ubuntu better. Please execute the following command, as it will automatically gather debugging information, in a terminal: apport-collect 1261048 When reporting bugs in the future please use apport by using 'ubuntu-bug' and the name of the package affected. You can learn more about this functionality at https://wiki.ubuntu.com/ReportingBugs. ** Tags removed: 12.04-lts calc ceiling even floor functions int libreoffice odd rounddown roundup trunc ** Tags added: precise ** Changed in: libreoffice (Ubuntu) Status: New = Incomplete -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1261048 Title: Wrong results from rounding functions for large argument To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1261048/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
