Bug Hunting Session
Bug 85571 - Calc 4.3 Corrupts Excel Spreadsheets. 1+1=3? (sort issue)
Summary: Calc 4.3 Corrupts Excel Spreadsheets. 1+1=3? (sort issue)
Status: RESOLVED DUPLICATE of bug 81633
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.2.2 release
Hardware: All Windows (All)
: medium critical
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-10-28 20:07 UTC by stephanie park
Modified: 2014-11-06 19:04 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Simplified example without data before 4.3 (18.50 KB, application/vnd.ms-excel)
2014-10-28 20:13 UTC, stephanie park
Details
Spreadsheet mangled by 4.3 ;-( (18.50 KB, application/vnd.ms-excel)
2014-10-28 20:14 UTC, stephanie park
Details

Note You need to log in before you can comment on or make changes to this bug.
Description stephanie park 2014-10-28 20:07:53 UTC
I have been using the same Excel spreadsheet to calculate ranking for over a decade with Excel and recently Calc. After I upgraded to the 4.3 release, I noticed Calc had corrupted all of my spreadsheets so badly that I needed to restore them from a backup and switched back to an old copy of Excel. Please address this issue before more people's spreadsheets are harmed!

Version: 4.3.2.2
Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d
Comment 1 stephanie park 2014-10-28 20:13:57 UTC
Created attachment 108589 [details]
Simplified example without data before 4.3

I can't attach the actual spreadsheet as it had confidential information. Here’s a simplified example.
Comment 2 stephanie park 2014-10-28 20:14:44 UTC
Created attachment 108590 [details]
Spreadsheet mangled by 4.3 ;-(
Comment 3 raal 2014-10-28 20:57:14 UTC
Hello,
RankBefore:
Total Hours Top  =B2+B4
Total Hours Bottom =B3+B5

RankAfter
Total Hours Top =B5+B3  
Total Hours Bottom =B4+B2

B2 = 1
B3 = 1
B4 = 1
B5 = 2
Calculations is correct. Please, where is the problem?
Comment 4 GerardF 2014-10-28 21:06:43 UTC
Another manifestation of mess put in Data > Sort feature.

*** This bug has been marked as a duplicate of bug 81633 ***
Comment 5 stephanie park 2014-10-29 02:27:34 UTC
raal,
The formulas in RankAfter should be the same as RankBefore. I've been using spreadsheets for over a decade now and never seen anything like this. According to bug 81633, Calc 4.2 and Excel correctly sort formulas with relative references by not changing their value. Calc 4.3 suffers from a serious regression where relative references are tread as absolute references scrambling user's spreadsheets.
Comment 6 Jean-Baptiste Faure 2014-10-29 05:36:45 UTC
Please, could you explain step by step what to do, starting from RankBefore (attachment 108589 [details]) to get RankAfter (attachment 108590 [details]). I do not understand what changed in the data.
Sorting, ok, but sorting exactly which range of cells and on which key ? 

Are you sure of your formulas in RankBefore? According to A8 and A9 I had put D8=B2+B3 and D9=B4+B5 instead.

Set status to NEEDINFO. Please set it back to UNCONFIRMED once you have provided requested informations. Thank you for your understanding.

Best regards. JBF
Comment 7 stephanie park 2014-11-06 06:51:42 UTC
Jean-Baptiste Faure,

Please understand that this is an extremely simplified version of the model I use at work. It ran for years perfectly in both Excel and until recently Calc. To run the model, you open the "good" spreadsheet  attachment 108589 [details] and select B1:C5 and sort by descending rank. The totals hours for the top and bottom half are given at the end.

Attachment 108590 [details] shows how the “new” sorting behavior in 4.3, silently breaks spreadsheets causing them to give invalid results. For example it incorrectly calculates the top as taking 3 hours , 1 hour + 1 hour = 3 hours. This bug wasted days of work and made me look like an idiot, because I trusted the invalid results.

I see you have now changed the sorting behavior of 4.2.7 to break more spreadsheets for users of the stable branch. This clearly shows that TDF does not care about business users. MS would not intentionally break interoperability, especially on a point release.
Comment 8 Jean-Baptiste Faure 2014-11-06 19:04:41 UTC
(In reply to stephanie park from comment #7)
> Jean-Baptiste Faure,
> 
> Please understand that this is an extremely simplified version of the model
> I use at work. It ran for years perfectly in both Excel and until recently
> Calc. 

It was not my question. My test file in bug 81633 ran perfectly for years in Calc too.

> To run the model, you open the "good" spreadsheet  attachment 108589 [details]
> [details] and select B1:C5 and sort by descending rank. The totals hours for
> the top and bottom half are given at the end.

Thank you for your answer. I am still confused by your formulas in D8 and D9. I do not understand their link with "top" and "bottom". Are your sure that the formulas shouldn't be D8=B2+B3 and D9=B4+B5 ?

That said it is clearly a duplicate of bug 81633.

As a workaround you could do one of the following:
1/ go back to version 4.2.6.3 (if you do that take care of https://www.libreoffice.org/about-us/security/advisories/cve-2014-3693/

2/ jump to the development version 4.3.4.0.0+ or 4.3.4 RC1 which will be released soon

Best regards. JBF

*** This bug has been marked as a duplicate of bug 81633 ***