Download it now!
Bug 67489 - EDITING: copied and pasted cells containing formulas are not recalculated
Summary: EDITING: copied and pasted cells containing formulas are not recalculated
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: Other All
: medium normal
Assignee: Kohei Yoshida
URL:
Whiteboard: BSA target:4.2.0 target:4.1.1
Keywords: regression
Depends on:
Blocks: mab4.1
  Show dependency treegraph
 
Reported: 2013-07-29 15:48 UTC by Markus Hoenicka
Modified: 2013-08-02 20:14 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Markus Hoenicka 2013-07-29 15:48:05 UTC
Problem description: 

The result of a formula is not recalculated properly if the formula has been copied and pasted with some constant context. Things work correctly if only the cell containing the formula is copied and pasted. The error is triggered when e.g. that cell and the cell right above it, containing no formula, are copied and pasted. Problem is that the formula may be recalculated at a later time, e.g. if some input value of the formula is altered, resulting in transiently wrong values which may go unnoticed. 

Steps to reproduce:
1. create a new table
2. enter the number "1" in cell A2
3. enter the number "2" in cell A4
4. enter the formula "=A4" in cell B4
5. mark cells B3 and B4
6. copy the marked cells
7. paste the marked cells into B1. Note the value in B2
8. enter the number "3" in cell A2

Current behavior:
B2 points to cell A2 but displays "2" in step 7. The correct value is displayed in step 8, after the cell pointed to has been altered and the formula was recalculated.

Expected behavior:
B2 is supposed to display the same value as A2 in step 7, i.e. "1"

I haven't noticed this bug in 4.0.4.2 although it might have been present there. I'm pretty sure that it did not exist in versions 3.x.
              
Operating System: Windows XP
Version: 4.1.0.4 release
Comment 1 Cor Nouws 2013-07-29 19:09:46 UTC
Hi Markus,

I can confirm the behaviour.
But also can tell that the problem is result of a new stetting.

Pls go to Tools > Options > Calc > Formula ...
  Recalculate on file load
Set this to ODF spreadsheet 'always recalculate' and the problem is gone.


But now the question: is this effect expected with that function, or not..
Or should we write a comprehensive blog with information about new Calc stuff...
Kohei, can you pls give your advise?
thanks!
Cor
Comment 2 Markus Hoenicka 2013-07-29 22:05:15 UTC
(In reply to comment #1)
> Hi Markus,
> 
> I can confirm the behaviour.
> But also can tell that the problem is result of a new stetting.
> 
> Pls go to Tools > Options > Calc > Formula ...
>   Recalculate on file load
> Set this to ODF spreadsheet 'always recalculate' and the problem is gone.
> 
> 
> But now the question: is this effect expected with that function, or not..
> Or should we write a comprehensive blog with information about new Calc
> stuff...
> Kohei, can you pls give your advise?
> thanks!
> Cor

Thanks for the clarification. My humble opinion is:

1. Pasting a formula and have it display the wrong result does not make any sense. There is no point in having this as a default behaviour.

2. The result of pasting a formula must not depend on how many cells you copy. Either you never recalculate, or you always recalculate. Anything else is confusing at best and makes the spreadsheet inconsistent at worst.

3. As an end user, I prefer consistent behaviour to blog entries that explain inconsistent behaviour.

4. The bug (or misfeature) is not related to file loading whatsoever. Before reporting the bug, I checked if there is any option that might affect cell recalculation after copy/paste. I did not find anything appropriate. A setting labelled "Recalculation on file load" must not affect the behaviour of pasting cells.

just my 2cc
Markus
Comment 3 Kohei Yoshida 2013-07-31 02:11:34 UTC
It's a bug. Plain and simple.
Comment 4 Kohei Yoshida 2013-07-31 02:18:19 UTC
BTW, this has nothing to do with that "recalc on file load" setting, since no file loading is involved to reproduce this.
Comment 5 Kohei Yoshida 2013-07-31 02:26:10 UTC
The bad (good?) news is that, this is no longer reproducible on master.  This happens only on the 4.1 branch... which makes it a bit of a hassle to debug.
Comment 6 Markus Hoenicka 2013-07-31 06:00:09 UTC
(In reply to comment #4)
> BTW, this has nothing to do with that "recalc on file load" setting, since
> no file loading is involved to reproduce this.

Thanks for confirming this. Cor mentioned that setting and edited the bug title accordingly, but I was not able to reproduce that this setting affects the bug in any way. I've reverted the title change to avoid further confusion.
Comment 7 Cor Nouws 2013-07-31 08:42:49 UTC
(In reply to comment #4)
> BTW, this has nothing to do with that "recalc on file load" setting, since
> no file loading is involved to reproduce this.

In my situation setting that option clearly had influence.
But of course I agree that it should not do (and does not for others what I read...) :)
Comment 8 Kohei Yoshida 2013-08-01 19:35:54 UTC
I'll take it.
Comment 9 Kohei Yoshida 2013-08-01 19:38:47 UTC
Here is the fix for 4.1: https://gerrit.libreoffice.org/#/c/5233/

The master branch doesn't have this bug because of my cell storage rework. So, I'll just forward-port the unit test code to master.
Comment 10 Commit Notification 2013-08-01 19:52:00 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#67489: Add test for this.



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 2013-08-02 13:32:27 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

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

fdo#67489: Don't stop range search even when the first cell is empty.


It will be available in LibreOffice 4.1.1.

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 Kohei Yoshida 2013-08-02 13:34:39 UTC
With that, I'll call this fixed.
Comment 13 Cor Nouws 2013-08-02 19:59:58 UTC
(In reply to comment #12)
> With that, I'll call this fixed.

thanks a lot Kohei :)
Comment 14 Papamatti 2013-08-02 20:14:24 UTC
Thank you Kohei! :-)