Bug Hunting Session
Bug 106459 - incorrect results in dragged sum formula spanning multiple sheets (3d reference)
Summary: incorrect results in dragged sum formula spanning multiple sheets (3d reference)
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.0.0.alpha1
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.4.0 target:5.2.7 target:5.3.2
Keywords: regression
Depends on:
Blocks:
 
Reported: 2017-03-09 14:20 UTC by Stefan Walsen
Modified: 2017-04-04 07:59 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Minimal example showing the incorrect behaviour (8.31 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-09 14:20 UTC, Stefan Walsen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Stefan Walsen 2017-03-09 14:20:08 UTC
Created attachment 131775 [details]
Minimal example showing the incorrect behaviour

When you have 100 or more contiguous cells in one column containing a sum formula spanning multiple sheets (in excel, this is called a "3d reference"), only the data of the first sheet's range is summed up. 

How to reproduce:
  Use the attached example, or do this:
  - create new spreadsheet document
  - add one new sheet (so we now have Sheet1 and Sheet2)
  - enter the value "1" into Sheet1.B1 and Sheet2.B1
  - enter the formula "=SUM(Sheet1.B1:Sheet2.B1)" into Sheet1.A1
  - Sheet1.A1 now shows the value "2", as expected
  - select Sheet1.A1 and drag the formula down to Sheet1.A100 (without letting go!)

Expected result:
  Sheet1.A1 still shows the value "2"

Observed result:
  Sheet1.A1 shows the value "1" instead of "2"

Further Info:
  - "Recalculate", "Recalculate Hard", or (save, close, re-open the document) do not help
  - modify any of the formulas on Sheet1 so it does not match the pattern of the others
    (e.g.: in Sheet1.A100, change "B100" to "B101" in either or both of the cell references),
    then "Recalculate Hard", and the problem goes away.
    Revert the modification, and it still works ok - until you save, close and re-open the document.
  - if you drag the formula to A99 only, then pick up the drag square again and drag to A100,
    A1 will still show "2" as expected, but will not update if you change the value on Sheet2.
Comment 1 Stefan Walsen 2017-03-09 14:27:01 UTC
I also tried this with the functions AVERAGE and MAX. They do not appear to be affected.

In Libreoffice 5.0.6.2, the problem does not occur.
Comment 2 Stefan Walsen 2017-03-09 14:28:48 UTC
The current version from the libreoffice website
 (5.3.0.3, Build ID: 7074905676c47b82bbcfbea1aeefc84afe1c50e1, Flatpak version)
still shows the incorrect behaviour of the SUM function.
Comment 3 Xisco Faulí 2017-03-09 14:40:00 UTC
Confirmed in

-Versión: 5.3.1.1
Id. de compilación: 72fee18f394a980128dc111963f2eefb05998eeb
Subpr. de CPU: 1; SO: Windows 6.1; Repr. de IU: predet.; Motor de trazado: HarfBuzz; 
Configuración regional: es-ES (es_ES); Calc: group

-Version: 5.4.0.0.alpha0+
Build ID: d3676ceeec55a41337ce5e6bc596f4f100d0638e
CPU threads: 4; OS: Linux 4.8; UI render: default; VCL: gtk2; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group

-Version: 5.2.0.0.alpha1+
Build ID: 5b168b3fa568e48e795234dc5fa454bf24c9805e
CPU Threads: 4; OS Version: Linux 4.8; UI Render: default; 
Locale: ca-ES (ca_ES.UTF-8)

but not in

Version: 5.0.0.0.alpha1+
Build ID: 0db96caf0fcce09b87621c11b584a6d81cc7df86
Locale: ca-ES (ca_ES.UTF-8)
Comment 4 Eike Rathke 2017-03-09 16:34:53 UTC
Maybe due to OpenCL or Software Interpreter that don't handle 3D ranges and get triggered with rows>100. Could you try to disable OpenCL and/or Software Interpreter under Tools-Options-LibreOffice-OpenCL?
Comment 5 Stefan Walsen 2017-03-09 16:47:23 UTC
In my installation,
"OpenCL" was disabled, and apparently I cannot enable it.
(I assume this is because I'd need to install additional libraries for this to work?) 

Disabling "Software Interpreter" and restarting LibreOffice does fix the problem for me.
Comment 6 Eike Rathke 2017-03-10 16:50:47 UTC
So yes, that confirms my assumption.
OpenCL being completely disabled means that your driver either is already blacklisted or the tests that are run on first startup discovered it's not usable.
Comment 7 Commit Notification 2017-03-10 17:35:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#106459 3D reference can't be handled as vector reference

It will be available in 5.4.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 8 Eike Rathke 2017-03-10 17:41:58 UTC
Pending review
https://gerrit.libreoffice.org/35051 for 5-3
https://gerrit.libreoffice.org/35052 for 5-2
Comment 9 Stefan Walsen 2017-03-13 14:36:59 UTC
I'll retest when I see a new daily build for platform Linux x86_64.
Comment 10 Commit Notification 2017-03-14 01:34:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

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

Resolves: tdf#106459 3D reference can't be handled as vector reference

It will be available in 5.2.7.

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 11 Commit Notification 2017-03-14 01:36:07 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

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

Resolves: tdf#106459 3D reference can't be handled as vector reference

It will be available in 5.3.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 12 Commit Notification 2017-03-17 01:00:40 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

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

add test for tdf#106459

It will be available in 5.4.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 13 Stefan Walsen 2017-04-04 07:59:52 UTC
finally got around to downloading a daily build of 5.2.7 .

fix confirmed: Formula result is now computed correctly even with either OpenCL or "Software Interpreter" enabled.