Bug 85215 - Sorting a column of linked formula gives Error 523
Summary: Sorting a column of linked formula gives Error 523
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.0.0.alpha0+ Master
Hardware: x86-64 (AMD64) Linux (All)
: medium critical
Assignee: Kohei Yoshida
URL:
Whiteboard:
Keywords: regression
Depends on:
Blocks: 85490
  Show dependency treegraph
 
Reported: 2014-10-20 04:44 UTC by Jean-Baptiste Faure
Modified: 2014-12-04 16:54 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
New version of the test file (15.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-28 21:08 UTC, Jean-Baptiste Faure
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jean-Baptiste Faure 2014-10-20 04:44:24 UTC
There is something incomplete or broken in the UpdateReferenceOnSort option that make sorting not working in the following case. It is a regression from version 4.2.6.3 and all previous versions of LibreOffice and OpenOffice.org :

Steps to reproduce with master and LibreOffice 4.3.4.0.0+ :
1/ open attachment 104016 [details] from bug 81633
2/ make sure that the hidden option /org.openoffice.Office.Calc/input UpdateReferenceOnSort is kept to its default value false. To do that go to Tools > Options > LibreOffice > Advanced > Expert configuration
3/ click on A1 and go to menu Data > Sort
4/ in Option tab select The range contains column labels
5/ go back to Sort criteria tab and choose "Date" ascending as Sort key 1
6/ click OK button

Result: Err:523 (Calculation does not converge) in range D3:E6

Same problem in LO 4.4.0.0.alpha1+

Build ID for 4.3: f9050536650835b776b9aa022ae1b47315cd5e14
Build ID for 4.4: c60872984e78511c621ae76ed28ba6338b6d3a68
both built at home under Ubuntu 14.04 x86-64.

Best regards. JBF
Comment 1 Laurent Balland 2014-10-22 07:35:58 UTC
Reproduced with Version: 4.3.4.0.0+
Build ID: 77c6ce66696a997269b9fe4dfed1dc2e51ecd00e
TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-10-17_16:12:56

NOT reproduced with Version: 4.4.0.0.alpha1+
Build ID: a8c24b25fd9fb21097a08a22797bf61b59099ea1
TinderBox: Win-x86@42, Branch:master, Time: 2014-10-21_06:31:17
but formulas are wrongly updated (with or without option UpdateReferenceOnSort)
Comment 2 Jean-Baptiste Faure 2014-10-22 19:02:24 UTC
Updated version of attachment 104016 [details] : attachment 108255 [details].

Best regards. JBF
Comment 3 ribotb 2014-10-23 07:36:10 UTC
With attachment file of JBF and steps to reproduce :
- when UpdateReferenceOnSort is set to false, I get 'Err:523' in each cell in the range D2-E23;
- when UpdateReferenceOnSort is set to true, 'Err:523' does not appear.

Version: 4.4.0.0.alpha1+
Build ID: 9ecac3874d179b1d7aa6b45337001b1def06a9dd
TinderBox: Win-x86@42, Branch:master, Time: 2014-10-22_06:31:01

Regards,
Bernard
Comment 4 A (Andy) 2014-10-24 19:09:03 UTC
I don't have this /org.openoffice.Office.Calc/input UpdateReferenceOnSort on LO 4.3.2.2 (Win 8.1)?  Therefore, I tried to skip this step, but in step 5 my computer doesn't show "Date", because there is no column name "Date"?
Am I doing something wrong?  Is this a Linux only issue?
Comment 5 Buovjaga 2014-10-24 19:37:51 UTC
(In reply to Jean-Baptiste Faure from comment #2)
> Updated version of attachment 104016 [details] : attachment 108255 [details].
> 
> Best regards. JBF

UpdateReferenceOnSort set to false, I can't reproduce the error.

Version: 4.4.0.0.alpha1+
Build ID: 0a82645c360158f9cc0fdabe2a52f1ff8f981bed
TinderBox: Win-x86@39, Branch:master, Time: 2014-10-24_06:59:23
Comment 6 Jean-Baptiste Faure 2014-10-25 05:35:36 UTC
(In reply to Beluga from comment #5)
> [...]
> UpdateReferenceOnSort set to false, I can't reproduce the error.
> 
> Version: 4.4.0.0.alpha1+
> Build ID: 0a82645c360158f9cc0fdabe2a52f1ff8f981bed
> TinderBox: Win-x86@39, Branch:master, Time: 2014-10-24_06:59:23

What is the result in your case ?

Best regards. JBF
Comment 7 Buovjaga 2014-10-25 07:42:15 UTC
(In reply to Jean-Baptiste Faure from comment #6)
> (In reply to Beluga from comment #5)
> > [...]
> > UpdateReferenceOnSort set to false, I can't reproduce the error.
> > 
> > Version: 4.4.0.0.alpha1+
> > Build ID: 0a82645c360158f9cc0fdabe2a52f1ff8f981bed
> > TinderBox: Win-x86@39, Branch:master, Time: 2014-10-24_06:59:23
> 
> What is the result in your case ?
> 
> Best regards. JBF

Weird: now that I tested again, I *do* get Err:523 in range D3:E6!
Tested several times, reopening the file and I got the Err every time, setting to NEW.
Comment 8 Jean-Baptiste Faure 2014-10-26 14:37:51 UTC
It works without error if, instead of selecting only the cell A1 and going to menu Data > Sort, I select the range A1:C14 and sort on column "Date". It works the same if I select A1, hit the shift key and click on C14, or if I select the columns A, B and C and use the menu Data > Sort.

For me it is usable but it is a big change in the workflow which I was used to. I am sure I am not alone in this case.

That said, the error Err:523 is still a bug because it worked in previous versions (up to 4.2.6.3).

Best regards. JBF
Comment 9 Kohei Yoshida 2014-10-26 20:34:03 UTC
I see why it's happening.  I'll fix it shortly.
Comment 10 Kohei Yoshida 2014-10-26 22:01:35 UTC
Just pushed the fix to master: http://cgit.freedesktop.org/libreoffice/core/commit/?id=10fc138307afb4b39baddb0d56eb8e986e5d29ea

and 4.3 backport request is here: https://gerrit.libreoffice.org/12106
Comment 11 Jean-Baptiste Faure 2014-10-27 08:33:13 UTC
(In reply to Kohei Yoshida from comment #10)
> Just pushed the fix to master:
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=10fc138307afb4b39baddb0d56eb8e986e5d29ea
> 
> and 4.3 backport request is here: https://gerrit.libreoffice.org/12106

Thank you very much Kohei, no Error 523 anymore in master.
Tested with Version: 4.4.0.0.alpha1+
Build ID: ae5ac4807cab26de2a149162576d2ef927cc8326
built at home under Ubuntu 14.04 x86-64

Waiting the backport to 4.3 to mark this bug as Verified Fixed.

Best regards. JBF
Comment 12 Kohei Yoshida 2014-10-27 15:56:03 UTC
I pushed additional fix for this, just FYI.
Comment 14 Jean-Baptiste Faure 2014-10-28 21:08:26 UTC
Created attachment 108591 [details]
New version of the test file

Hi Kohei, thank you very much for this fix, all seems to work as expected.

I did my tests with master on attachment 108255 [details] following steps from the bug description: no problem.
No error 523 with LO 4.3.4.0.0+ containing the backport of the first commit.

I attached a new version of the same spreadsheet with the following changes which make it a little more complex and close to the real life:
1/ I added a line to shift the initial value in column D and E from the first row to the second. Now all headers are text, not numbers.
2/ I added the column F so that the data are split in two ranges, columns A - C and column F. Data in column F may be seen as the description of the expenses or receipt in columns A-C, and they must be kept linked together.

Now the tests (with UpdateReferenceOnSort = false):
1/ select A1 then click the "Sort ascending" button: formulas are correct in both D and E columns, values in both columns are identical and the values in the last row of the columns are unchanged. In column F data are sorted alphabetically (they were prepared to get that if sorting went well).

2/ select only rows 9 to 15, then click the "Sort ascending" button: same correct result.
3/ after 2/ select rows 4 to 15, then click the "Sort ascending" button: same correct result.

My conclusion: I am happy, it works for me as it worked before.

Best regards. JBF
Comment 15 Eike Rathke 2014-11-20 10:35:58 UTC
For the records, also on 4-2 as f4c179ea7f69e87e55a416c1588ee0aad7c146c0 and 1f0bca308e677a3e7a75837f93f14b679f698846