Bug Hunting Session
Bug 68566 - EDITING: Cross references between tables / cell data broken
Summary: EDITING: Cross references between tables / cell data broken
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: Other Mac OS X (All)
: highest critical
Assignee: Not Assigned
URL:
Whiteboard: BSA deleterequest
Keywords: regression
Depends on:
Blocks: 85490
  Show dependency treegraph
 
Reported: 2013-08-26 10:21 UTC by MICKRU
Modified: 2014-12-04 20:51 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
The file that is working with 4.0.4 and fails with 4.1.0 (deleted)
2013-08-26 10:21 UTC, MICKRU
Details
Sample file to reproduce the bug of sorting (25.77 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-09-05 08:28 UTC, MICKRU
Details
File working with 4.2.4.2 - but not with newer Office suite (26.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-09-05 08:35 UTC, MICKRU
Details

Note You need to log in before you can comment on or make changes to this bug.
Description MICKRU 2013-08-26 10:21:39 UTC
Created attachment 84641 [details]
The file that is working with 4.0.4 and fails with 4.1.0

I have a spreadsheet created with Libreoffice 4.0.4 which includes a master table into which I enter a sort of address book, listing lastname, firstname, position, roles, phone numbers, email and postal addresses.

I use an auto filter to be able to sort that table using the column headers.

Within the same file I have sheets for a subset of the information of the master table. All is working perfect in 4.0.4 and when I upgraded to the 4.1.0 release the following issues came up and made me downgrade back to the 4.0.4 release:

1) If you enter an email address in one cell on the master sheet the email is recognized as URI and reformated as a mailto link. This was no issue in 4.0.4, but in 4.1.0 other sheets refering to this column using a formular like: =T(Master.J22) do no longer get me the text from the master table but generate an empty cell. Seems that if the master contains text only (no URI) the reference is working, if its converted to a URI the reference is broken, and all child sheets show empty cells instead of showing the email address.

2) If you use the master table to resort the address list, the information is not always updated on the child sheets. E.g on the master I have roles for some persons. These roles are e.g. chief, leader team 1, leader team2, clerk, etc.
In 4.0.4 I was able to sort the table by role to have all assigned roles at the top of the table. The child sheets reflected this and content sorting order matched the master.
In 4.1.0 the roles will show according the sorting order as well as in their original position they have been before the master was resorted using a different criteria.

Hope this is clear. I attach the sample file used. 
I made the first two entries on the master sheet URLs again, and they show on the child sheet using 4.0.4 correctly, but are empty on 4.1.0. 
Using lastname / function sorting again the dependent sheets are working fine on 4.0.4 and fail on 4.1.0.
 
              
Operating System: Mac OS X
Version: 4.1.0.4 release
Comment 1 Jean-Baptiste Faure 2013-12-14 11:31:56 UTC
Please, could explain step by step what to do on your file to reproduce the problem?

Your file opens without problem with versions 4.2.0.0.beta2+, 4.1.5.0+ and 4.0.6 under Ubuntu 13.10 x86-64

Best regards. JBF
Comment 2 GerardF 2013-12-14 14:38:20 UTC
What I understand:
1st presume bug: The T() function returns an empty string instead off the cell text when cell contains an URL.
2nd presume bug: When sorting "master sheet", other sheets are not refreshed.

Both presume bug are not reproducible with 4.1.3.
Update with a most recent vesion and tell us if you still reproduce.
Comment 3 MICKRU 2013-12-15 11:32:30 UTC
Hi,

thanks for having a look at my issue. I appreciate this very much.
I just downloaded LibreOffice 4.1.3.2 and can confirm that the T(..) function now seems to work, because the mailto: URIs on the master sheet will produce the email address as text on the sheet called "AdressListe". So one issue is remaining.

The sheet is used for our voluntary fire-fighter department. For this we hand out the telephone/address list to all members of the fire-fighters.

The sheet called "TelefonListe" should be sorted using the column "Fkt-Sort". The goal is to have all people that have a specific role in the fire department at the top of the "TelefonListe" sheet so that their phone numbers are listed first.

The lastname column on the master sheet is used to sort the sheet "AddressList" in alphabetical order based on the peoples last name.

I can confirm that the sorting bug remains in the 4.1.3.2 version while in 4.0.4 it is working well. This is what you need to do:

1) Go on the master sheet and sort by lastname.
2) Verify that the members are sorted on the TelefonListe and AdressListe sheets in alphabetical order. Especially in the TelefonListe the functional role of the people should be the same on the master and on the TelefonListe. 
3) I now print the AdressListe sheet to have that sheet in alphabetical order.
4) Next I use the Fkt-Sort column on the master sheet to sort the list based on the functional role of the fire-fighters.
5) Now you go on the TelefonListe sheet to see if the list has been sorted. With 4.1.3.2 the names are sorted correctly, but the column with the functional roles is not. When I use the release 4.0.4 the functional roles are sorted correct and reflect the order on master sheet.

Hope this helps you to figure out whats going wrong here.

Regards and many thanks,
Michael
Comment 4 MICKRU 2013-12-17 09:26:29 UTC
One more thing. The behavior looks to me, as if in 4.0.x releases clears table cells on dependent sheets before filling them again. Thats why the functional roles appear correct in that release. In 4.1 releaes this seems to have been optimized such that cell data is overwritten with the new data depending on sorting results. However if an empty cell is put on top of a cell that previously contained data, then the cell content is no longer cleared. Therefore in 4.1.x the sorting appears broken in the dependent sheets for columns that do not contain data in every cell.

Does this make sense?
Comment 5 Tollef Fog Heen 2014-05-05 15:33:54 UTC
The content of attachment 84641 [details] has been deleted by
    Tollef Fog Heen <tfheen@err.no>
who provided the following reason:

Confidential

The token used to delete this attachment was generated at 2014-05-05 15:33:48 UTC.
Comment 6 Jean-Baptiste Faure 2014-07-20 15:26:48 UTC
It is impossible to test further without a test file.
If you still encounter this bug with current stable version, could you attach an anonymized version of your original file ? An easy mean to anonymize your file is to replace all alphabetical character by x and numerical character by 9.

Thank you for your understanding.

Best regards. JBF
Comment 7 Robinson Tryon (qubit) 2014-09-05 07:51:50 UTC
Removing comma from whiteboard (please use a space to delimit values in this field)
https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Whiteboard#Getting_Started
Comment 8 MICKRU 2014-09-05 08:26:21 UTC
(In reply to comment #6)
> It is impossible to test further without a test file.
> If you still encounter this bug with current stable version, could you
> attach an anonymized version of your original file ? An easy mean to
> anonymize your file is to replace all alphabetical character by x and
> numerical character by 9.
> 
> Thank you for your understanding.
> 
> Best regards. JBF

I just tested the bug with the lastest release of 4.3.1 and it got worse. Sorting does not at all work on dependent tables. I added again a sample file.
Comment 9 MICKRU 2014-09-05 08:28:00 UTC
Created attachment 105788 [details]
Sample file to reproduce the bug of sorting

Comments are in the file. Can be used to reproduce the issue. File working well with 4.1 release and is not working with 4.2 or 4.3 releases of LibreOffice.
Comment 10 MICKRU 2014-09-05 08:35:19 UTC
Created attachment 105789 [details]
File working with 4.2.4.2 - but not with newer Office suite

I just realized that the 4.3.1 messed up my test file I uploaded before. This one is working with 4.2.4.2 just fine, but breaks e.g. with 4.2.5 or 4.3.1
Comment 11 Jean-Baptiste Faure 2014-09-05 10:04:08 UTC
set back status to UNCONFIRMED
Comment 12 Alex Thurgood 2014-10-07 10:25:54 UTC
Confirming on
Version: 4.3.2.2
Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d

OSX 10.9.5

1) Open attachment of comment 10
2) Sort on master sheet with Lastname ascending - data sorted accordingly
3) Click on Dependent sheet - note that data has not been sorted
Comment 13 Alex Thurgood 2014-10-07 10:28:26 UTC
Sort and update work in

Version: 4.1.4.2
Build ID: 0a0440ccc0227ad9829de5f46be37cfb6edcf72

therefore regression
Comment 14 ign_christian 2014-10-07 14:30:14 UTC
(In reply to Alex Thurgood from comment #12)
> 1) Open attachment of comment 10
> 2) Sort on master sheet with Lastname ascending - data sorted accordingly
> 3) Click on Dependent sheet - note that data has not been sorted
Looks similar with Bug 84055 which is marked as not a bug. New sorting behavior since 4.3.1 and 4.2.7

Perhaps this report should also be closed.
Comment 15 Alex Thurgood 2014-10-07 17:24:21 UTC
(In reply to ign_christian from comment #14)


> Looks similar with Bug 84055 which is marked as not a bug. New sorting
> behavior since 4.3.1 and 4.2.7
> 
> Perhaps this report should also be closed.

Unfortunately, yes, but as a duplicate of 81633 (which is weird because actually much later bug declaration) - the behaviour will be configurable in LO 4.4 release and will not be backported - see bug 81663 for more details. Sigh.
Comment 16 Alex Thurgood 2014-10-07 17:25:06 UTC

*** This bug has been marked as a duplicate of bug 81633 ***
Comment 17 MICKRU 2014-10-08 04:34:09 UTC
Too bad that I need this for my work...
I tend to agree to some other guy saying that this basic functionality (not updating references) should not have been introduced in the first place on the otherwise wonderful working calc on the 4.2 branch.

But what can I say. The guy that had the desire to get this for me intuitive and natural working thing removed from a stable version came first and won.

If I would have a vote: revert that change, and have the new configurable feature introduced with V4.4.

Anyway I appreciate that you are having a look at my issues. Thank you all.
Comment 18 Joel Madero 2014-10-08 04:49:02 UTC
This is literally like asking for a bug to be reintroduced into LibreOffice....Kohei was nice enough to add an option for users to bring the bug back if they so choose.

Anyways - feel free to download a daily 4.4 build or of course wait for release
Comment 19 MICKRU 2014-10-08 06:17:45 UTC
Fair enough - I do not understand what the original bug was. It can't be that the bug is that one feels that its unexpected that referenced cells worked as in 4.2.6 which was that sorting was updating references. Because that to me is not a bug but was expected behavior ...
Comment 20 Jean-Baptiste Faure 2014-10-08 07:03:48 UTC
(In reply to Joel Madero from comment #18)
> This is literally like asking for a bug to be reintroduced into
> LibreOffice....Kohei was nice enough to add an option for users to bring the
> bug back if they so choose.

I disagree. It is _not always_ a bug. The difficulty is to know when in which case it is a bug and in which case it is a feature. That is the reason  why a configuration option was necessary. Please try again my test file in bug 81633.

I asked the ESC to consider the decision to revert this commit for branches 4.2 and 4.3.

Best regards. JBF
Comment 21 Joel Madero 2014-10-08 07:06:40 UTC
It will not be backported - there is a new string involved - translation and all that fun stuff. So 4.4 it is.
Comment 22 ign_christian 2014-10-08 07:19:50 UTC
(In reply to Jean-Baptiste Faure from comment #20)
> I asked the ESC to consider the decision to revert this commit for branches
> 4.2 and 4.3.
Hope that reverting commits is possible. Unless we're facing dozens new bug report with similar issue until early 2015 and after 4.2.7 released.
Comment 23 Joel Madero 2014-10-08 07:27:03 UTC
Also not reverting the commit . . . removing myself from cc
Comment 24 Eike Rathke 2014-12-04 17:29:05 UTC
For the records, 4.2.8 and 4.3.5 will restore the old behavior, for 4.4 the UpdateReferenceOnSort option will default to false/unchecked for compatibility.