Bug Hunting Session
Bug 72949 - Dsum gives different results in 4.2.0.1
Summary: Dsum gives different results in 4.2.0.1
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.1 rc
Hardware: Other All
: highest critical
Assignee: Eike Rathke
URL:
Whiteboard: target:4.3.0 target:4.2.0.2
Keywords: regression
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2013-12-21 14:27 UTC by tim
Modified: 2014-01-20 16:17 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Example spreadsheet (21.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-12-21 14:27 UTC, tim
Details
Sample file with first condition duplicated. (57.40 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-12-21 16:53 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tim 2013-12-21 14:27:18 UTC
Created attachment 91086 [details]
Example spreadsheet

The attached sheet works in 4.1.4.2 and previous, but produces incorrect dsums in 4.2.0.1.

The Interest sheet sums the balances within each interest range, in the criteria to the right, from the AC Cashflow sheet.  The total should be 120.

The criteria select a range of rates to sum. (eg > 0.005 and <= 0.0055).

In 4.2.0.1, the result sometimes seems to depend on whether the Interest sheet is before or after the AC Cashflow sheet.  Move the sheet, and you may get a different answer.  

When the sum is wrong, it is as if the lower bound has been ignored.  The Interest sheet then sums balances up to a specific rate, rather than between the 2 rates in the criteria, and gives a total much larger than 120.

It took me a long time to find this since I have a very involved set of financial sheets that produced different results for reasons that were very obscure.  I narrowed it down to this dsum.
Comment 1 m.a.riosv 2013-12-21 16:53:06 UTC
Created attachment 91097 [details]
Sample file with first condition duplicated.

Hi tim, thanks for reporting, important catch.

Confirmed.
Win7x64Ultimate
Version: 4.2.0.1 Build ID: 7bf567613a536ded11709b952950c9e8f7181a4a

If I open your sample file in 4.2, I can see the issue, after doing a trials seems that function calculation doesn't take care of the first condition, except for B4 duplicating the first condition shows the calculations right. Attached file.

The bug is with all database functions.
Comment 2 m.a.riosv 2013-12-21 16:54:49 UTC
A regression and a blocker for 4.2, broke the calculations without any notice.
Comment 3 Eike Rathke 2014-01-03 15:12:51 UTC
Investigating.
Comment 4 Commit Notification 2014-01-03 22:30:52 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

resolved fdo#72949 evaluate criteria on the query range



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 5 Commit Notification 2014-01-03 22:48:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d16363aa6c8d7a01f9579f7f062a2d005fa1a854&h=libreoffice-4-2

resolved fdo#72949 evaluate criteria on the query range


It will be available in LibreOffice 4.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.
Comment 6 m.a.riosv 2014-01-19 16:26:39 UTC
Thanks Eike.
Sorry for the delay in check.

Seems solved:
Win7x64Ult.
Version: 4.1.6.0.0+ Build ID: 80df333af59124d484f7d87d83ba0acaef003eb
Version: 4.2.0.2 Build ID: cd65d6220c5694ee7012d7863bcde3455c9e3c30
Version: 4.2.1.0.0+ Build ID: d72321b665f54946cf603e6f30740f31151c898f
         TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-01-16_02:01:06
Version: 4.3.0.0.alpha0+ Build ID: 42f551d524a1df46f6a311d5897ac30bd8fc1aaf
         TinderBox: Win-x86@39, Branch:master, Time: 2014-01-15_22:44:37
Comment 7 tim 2014-01-20 16:17:46 UTC
I have just checked in my main spreadsheet now on 4.2.0 RC2.  

Fixed.  Thanks very much.