Bug Hunting Session
Bug 81796 - Formula result not displayed after cells deletion & sorting
Summary: Formula result not displayed after cells deletion & sorting
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.5.2 release
Hardware: All All
: high critical
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisectRequest, regression
: 81878 (view as bug list)
Depends on:
Blocks: 85490
  Show dependency treegraph
 
Reported: 2014-07-26 21:24 UTC by xanaesx
Modified: 2015-12-17 10:40 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
readme (5.71 KB, text/plain)
2014-07-26 21:24 UTC, xanaesx
Details

Note You need to log in before you can comment on or make changes to this bug.
Description xanaesx 2014-07-26 21:24:35 UTC
Created attachment 103525 [details]
readme

Here I send them a load on with LibreOffice Calc sheet, I use it to keep track of customer accounts.

The error makes me LibreOffice version 4.2.4.2 and version 4.2.5.2; I also tried in different PC with different operating systems and still give the same error.

In OpenOffice version 4.1 works correctly and does not give any error in the calculations.

So it makes me think that is a problem in LibreOffice.

Below I explain the operation I give this sheet.

The leaf is from column A to V and from row 1 to 500.

In the range A3: G500 is for income or modified data.

In the range A3: A500 Discount each customer the M or F is entered or left blank according to the case.

In the range B3: B500 Seller each customer is entered or left blank according to the case.

In the range C3: C500 the voucher number is entered.

In the range D3: D500 the date the voucher is entered.

In the range E3: E500 Client Name is entered.

In the range F3: F500 the amount of the voucher is entered without discount.

In the range G3: G500 P if you enter voucher or payment total amount if partial payment or is left blank if not paid.

In the range H3: V500 is where the calculations are performed and the results are shown.

In the range M2: O2 totals and subtotals are displayed according to the search.

The sheet has 3 macros that run with the 3 located between F1 and L1.

Macro Print print what is displayed according to the print range C1: O500.

Macro Sort sorts the range A3: G500 per Customer (alphabetical order) and then by date (ascending).

Expired Macro commands the range A3: G500 by Date (Ascending).
Sort values ​​in cells not recalculate see 4.2.4.2; 4.2.5.2

The range A3: K500 Automatic Filter is applied to filter as needed for consultation.

The range A3: O500 has applied Conditional formatting.

All cells are protected except the range A3: G500 that are on the entry or modification of data.

In image 01 the total shown in M2 (MUST yellow, Total 311,610.79).

The problem I have with this paper is that when entering new data for example in row 240 the image sum and 02 is calculated correctly (MUST BE 02 Total 311,710.79 image); but then applying the SORT macro recalculates and displays a different value (TO 03 Total 310,627.30 image); also shows me another value by applying macro UP (TO 04 Total 311,561.83 image).

Applying again the macro SORT shows me another full (MUST image 05 Total 310,627.30); also at the end of the entries in row 236 from column H onwards does not recalculate the data and are shown as empty.

For the tests I've done the problem is presented to sort data, the error also occurs when sorting manually from the menu Data> Sort; so that tells me that's not a problem of MACROS.

Download sheet and pictures 01; 02; 03; 04; 05 from here:

https://drive.google.com/folderview?id=0Bwf1Dk2NSstbQjZ6MVFsVzdGenM&usp=sharing

or from here:

https://onedrive.live.com/redir?resid=4571DC9D7435B2B0!148&authkey=!AO6MJtBNLRSSlPo&ithint=folder%2cjpg
Comment 1 tommy27 2014-07-27 11:57:18 UTC
(In reply to comment #0)
> ....
> The error makes me LibreOffice version 4.2.4.2 and version 4.2.5.2
>....

I change version field to 4.2.4.2 since that's the 1st version where you reproduce the bug.
Comment 2 m.a.riosv 2014-07-29 19:22:13 UTC
*** Bug 81878 has been marked as a duplicate of this bug. ***
Comment 3 ign_christian 2014-07-30 14:54:00 UTC
(In reply to comment #0)
> For the tests I've done the problem is presented to sort data, the error
> also occurs when sorting manually from the menu Data> Sort; so that tells me
> that's not a problem of MACROS.
I'm sorry I don't understand macros & your file is quite complex. If you tell what we have to do (steps to reproduce the issue) by sorting (also what is expected) it's easier for us to observe.
Comment 4 xanaesx 2014-07-30 17:43:26 UTC
(In reply to comment #3)
> (In reply to comment #0)
> > For the tests I've done the problem is presented to sort data, the error
> > also occurs when sorting manually from the menu Data> Sort; so that tells me
> > that's not a problem of MACROS.
> I'm sorry I don't understand macros & your file is quite complex. If you
> tell what we have to do (steps to reproduce the issue) by sorting (also what
> is expected) it's easier for us to observe.

Download new CONTROL_LIGHT.rar from here:
https://onedrive.live.com/redir?resid=4571DC9D7435B2B0!156&authkey=!AJH3Mybf6EhBwlg&ithint=file%2crar

or here:
https://drive.google.com/file/d/0Bwf1Dk2NSstbYUhQTFpxQVVZams/edit?usp=sharing
Comment 5 ign_christian 2014-07-31 14:31:24 UTC
Steps to reproduce:
1. Open CONTROL_LIGHT.ods from compressed attachment in comment 4
2. Delete cells A18:C18
3. Select all columns A:C
4. Sort Ascending

See that result in cell D18 not displayed (formula still there)

Reproduced in:
- LO 4.2.6.2, 4.3.0.4 (hard recalc resolve the issue)
- LO 4.3.1.0.0+ 2014-07-30_10:54:10 (not resolved by hard recalc)

Not reproduced in: LO 4.1.6.2, 4.2.0.0.beta1, 4.2.4.2

*) Set Version to 4.2.5.2 (attached screenshot tell that it's using 4.2.5.2)
Comment 6 ign_christian 2014-07-31 14:40:43 UTC
Possibly duplicate/related to Bug 81542
Comment 7 xanaesx 2014-09-27 17:39:46 UTC
In version 4.3.2.2 the same mistakes continue to sort and recalculate.
Comment 8 xanaesx 2014-09-27 17:41:44 UTC
In version 4.3.2.2 the same mistakes continue to sort and recalculate
Comment 9 tommy27 2014-09-27 18:39:28 UTC
reverting version to 4.2.5.2
that field should indicate first version where the bug appeared not the latest
Comment 10 Jean-Baptiste Faure 2014-10-11 18:11:09 UTC
Tested the scenario in comment #4 with versions 4.0.6, 4.1.6, 4.2.6, 4.3.4.0.0+ and master (4.4.0 alpha) under Ubuntu 14.04 x86-64:
# works as expected in 4.0 and 4.1
# does not work in 4.2, 4.3 and 4.4

I think that the observed behavior is the result of 2 bugs:

1/ in 4.2.6 (NB: _before_ the change introduced by bug 81309) D18 remains empty but contains the right formula (=C18/1.105) --> here the formula is not recalculated after sorting. ctrl+shift+F9 forces the recalculation and the result is correct

2/ in 4.3.4.0.0+ (NB: _after_ the change introduced by bug 81309 but _without_ the configuration option introduced by bug 81633) D18 remains empty but does not contains the right formula, it refers to C73 instead of C18, that is to the last row of the cells range. D19 refers to C18, D20 to C19, and so on until D73.
That said, D73 is correctly recalculated so the problem of 4.2.6 is fixed here.
In this case a temporarily solution is to propagate the formula from D2. 

3/ in 4.4.0 alpha (NB: _after_ the change introduced by bug 81309 and _with_ the configuration option introduced by bug 81633) :
  case 1 : option "update references when sorting range of cells" _not checked_: after sorting D18 is empty but contains the right formula and ctrl+shift+F9 forces the recalculation and the result is correct --> same case as 4.2.6
  case 2 : option "update references when sorting range of cells" _checked_: same behavior as in 4.3.4.0.0+

Summary:
4.0.6 and 4.1.6                : sort OK, recalculation OK
4.2.6.3                        : sort OK, recalculation KO
4.3.4.0.0+                     : sort KO, recalculation OK
4.4.0 alpha option not checked : sort OK, recalculation KO
4.4.0 alpha option checked     : sort KO, recalculation OK

Best regards. JBF
Comment 11 Jean-Baptiste Faure 2014-10-11 18:42:46 UTC
Did my tests with the following maturation stage of 4.4.0 alpha:
Version: 4.4.0.0.alpha0+
Build ID: 96adec2fd56d1ca09d679c0966567c674d812dfb

Best regards. JBF
Comment 12 Norbert Scheibner 2014-10-12 00:58:56 UTC
@JBF

Don't forget another sort related bug fixed first in 4.2.7, which means that sort in 4.2.6 is technically not ok.

See Bug 80874
Comment 13 xanaesx 2014-10-13 19:45:26 UTC
Comment on attachment 103525 [details]
readme

>ÿþDownload sheet and pictures from here:
>
>https://drive.google.com/folderview?id=0Bwf1Dk2NSstbQjZ6MVFsVzdGenM&usp=sharing
>
>or from here:
>
>https://onedrive.live.com/redir?resid=4571DC9D7435B2B0!148&authkey=!AO6MJtBNLRSSlPo&ithint=folder%2cjpg
>
>Sort values   in cells not recalculate see 4.2.4.2; 4.2.5.2 and 4.3.2.2
>
>The error makes me LibreOffice version 4.2.4.2 and version 4.2.5.2; and version 4.3.2.2 I also tried in different PC with different operating systems and still give the same error.
>
>In OpenOffice version 4.1 works correctly and does not give any error in the calculations.
>
>So it makes me think that is a problem in LibreOffice.
>
>Below I explain the operation I give this sheet.
>
>The leaf is from column A to V and from row 1 to 500.
>
>In the range A3: G500 is for income or modified data.
>
>In the range A3: A500 Discount each customer the M or F is entered or left blank according to the case.
>
>In the range B3: B500 Seller each customer is entered or left blank according to the case.
>
>In the range C3: C500 the voucher number is entered.
>
>In the range D3: D500 the date the voucher is entered.
>
>In the range E3: E500 Client Name is entered.
>
>In the range F3: F500 the amount of the voucher is entered without discount.
>
>In the range G3: G500 P if you enter voucher or payment total amount if partial payment or is left blank if not paid.
>
>In the range H3: V500 is where the calculations are performed and the results are shown.
>
>In the range M2: O2 totals and subtotals are displayed according to the search.
>
>The sheet has 3 macros that run with the 3 located between F1 and L1.
>
>Macro Print print what is displayed according to the print range C1: O500.
>
>Macro Sort sorts the range A3: G500 per Customer (alphabetical order) and then by date (ascending).
>
>Expired Macro commands the range A3: G500 by Date (Ascending).
>
>The range A3: K500 Automatic Filter is applied to filter as needed for consultation.
>
>The range A3: O500 has applied Conditional formatting.
>
>All cells are protected except the range A3: G500 that are on the entry or modification of data.
>
>In image 01 the total shown in M2 (MUST yellow, Total 311,610.79).
>
>The problem I have with this paper is that when entering new data for example in row 240 the image sum and 02 is calculated correctly (MUST BE 02 Total 311,710.79 image); but then applying the SORT macro recalculates and displays a different value (TO 03 Total 310,627.30 image); also shows me another value by applying macro UP (TO 04 Total 311,561.83 image).
>
>Applying again the macro SORT shows me another full (MUST image 05 Total 310,627.30); also at the end of the entries in row 236 from column H onwards does not recalculate the data and are shown as empty.
>
>For the tests I've done the problem is presented to sort data, the error also occurs when sorting manually from the menu Data> Sort; so that tells me that's not a problem of MACROS.
Comment 14 Jean-Baptiste Faure 2014-10-13 20:05:22 UTC
Please, do not change the version number, it is intended to show the oldest version in which the bug has been seen.

Set back the version number to 4.2.5.2

Best regards. JBF
Comment 15 xanaesx 2014-11-24 04:35:12 UTC
continues with the same error in libreoffice version 4.3.4.1
Comment 16 tommy27 2014-11-24 06:22:23 UTC
please xanaesx stop changing version field in a way that is incorrect
see comment 14 too
Comment 17 Eike Rathke 2014-12-04 20:50:55 UTC
I might be too dumb for this as I didn't fully understand how to reproduce an error and what the error actually would be, but I believe this has been fixed in recent to-be releases 4.2.8, 4.3.5 and 4.4

Please check with builds as of today or later and feel free to reopen (in which case please also provide a step-by-step scenario to reproduce the scenario and state what exactly goes wrong).
Comment 18 Robinson Tryon (qubit) 2015-12-17 10:40:28 UTC
Migrating Whiteboard tags to Keywords: (BibisectRequest)
[NinjaEdit]