Issue 110588

Summary: Sort fails to move cells (in other columns) which contain references to another file.
Product: Calc Reporter: matt_uk <mlander6>
Component: editingAssignee: kla <thomas.klarhoefer>
Status: CLOSED FIXED QA Contact: issues@sc <issues>
Severity: Trivial    
Priority: P3 CC: issues, nesshof, niklas.nebel, rainerbielefeld_ooo_qa, rb.henschel
Version: OOo 3.2Keywords: oooqa, regression
Target Milestone: 3.4.0   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
ODS File 1 containing simple OpenOffice.org 3.2 spreadsheet to sort
none
ODS File 1 containing simple OpenOffice.org 3.2 spreadsheet to sort
none
Sorry, 'data.ods' contains the reference data that file 1 points to.
none
example2.ods - an example of a sort where the rows contain non-absolute links within the same sheet none

Description matt_uk 2010-04-03 17:17:47 UTC
Sorting by a chosen column (via Data|Sort) fails to move the cells in another
column if those cells reference another ODS file.  These cells are left in their
original positions.

Example

Enter data in columns.  One of the columns (eg column B) has cells with formulae
which reference cells in another ODS file on the same computer.

eg

='file:///C:/test/data.ods'#$Sheet1.A1

Select the data to be sorted.  Choose Data|Sort.  Choose to sort by a column
which contains just numbers (eg column A).  The columns in the selected area are
resequenced together as they should be, except that the cells of the column
containing the references to another file (colmun B) are left in their original
positions.
Comment 1 matt_uk 2010-04-03 17:20:48 UTC
Created attachment 68723 [details]
ODS File 1 containing simple OpenOffice.org 3.2 spreadsheet to sort
Comment 2 matt_uk 2010-04-03 17:21:39 UTC
Created attachment 68724 [details]
ODS File 1 containing simple OpenOffice.org 3.2 spreadsheet to sort
Comment 3 matt_uk 2010-04-03 17:22:48 UTC
Created attachment 68725 [details]
Sorry, 'data.ods' contains the reference data that file 1 points to.
Comment 4 matt_uk 2010-04-03 17:55:00 UTC
I should explain these attachments.

The first file is 'test.ods' which contains 3 columns to sort.  Cells in column
B of 'test.ods' reference the second file, 'data.ods'.

The third file is a duplicate of 'data.ods' - sorry.
Comment 5 matt_uk 2010-04-03 18:20:37 UTC
These files have to be in directory

C:\test

for the references to work.
Comment 6 Rainer Bielefeld 2010-04-03 19:07:34 UTC
Reproducible with "Ooo 3.1.1 WIN XP DE[OOO310m19 (Build 9420)]" and "Ooo-Dev
3.2.1 multilingual version English UI WIN XP: [OOO300m14 (Build 9489)]"!

Works for me with  "2.4.1  Multilingual version German UI WIN XP:
[680m17(Build9310)]" and "1.1.4 (German) WIN XP: [645m52 (Build 8824)]" (not for
DDE-Links, only for ='file:///....

Always works for data from links to cells in the same document.
Comment 7 Regina Henschel 2010-04-03 19:17:21 UTC
I don't know whether this is a bug or more an enhancement, but I think, that the
behavior should be changed. Reasoning: If the referenced data are on another
sheet in the same file as the reference, then during sorting the references are
treated as if they where absolute. To be consistent the same should be done, if
the referenced data are in another file.

@matt_uk: You can use absolute references to get them sorted correctly.
Comment 8 Regina Henschel 2010-04-03 19:21:01 UTC
OOps, that changes were not intended, setting back.
Comment 9 matt_uk 2010-04-03 20:25:52 UTC
Thanks for the tip to use absolute references as a workaround, though absolute
references can be a problem to use sometimes.

As rainerbielefeld has identified, this problem arises for all non-absolute
links to cells, even within the same sheet. It cannot be right for a sort to
leave some cells on the wrong rows.
Comment 10 matt_uk 2010-04-04 11:45:06 UTC
I can see now that the issue is more complex, because of the behaviour of
non-absolute links.  Another file, example2.ods is attached below.  Select
A3:D10, then choose Data|Sort, choose Column A.  This has links to other cells
in the same sheet.  These links are in column B. The consequence of the sort
algorithm is to change the links in this case, because they maintain a fixed
offset to the cell containing the link.  (Sort by column C to reset this sheet).

The sort routine seems to have different rules for movings different types of
links, but it is unpredictable for the user. As originally identified,
non-absolute links to another file do not get moved at all by the sort routine
when their row is moved.

Perhaps the behaviours can be looked at.  There is no warning to users of what
rule is being applied, and it can even vary within a single sort event.

The most obvious way a sort should work is to move whole rows together, not
altering the source references of any links, just moving the cells containing
the links with the row they are in.  This is what a user would naturally expect
to happen.  However, are non-absolute links stored as offsets?  If so, perhaps
the offsets should be recalculated by the sort routine when moving rows?
Comment 11 matt_uk 2010-04-04 11:48:41 UTC
Created attachment 68732 [details]
example2.ods - an example of a sort where the rows contain non-absolute links within the same sheet
Comment 12 matt_uk 2010-04-05 11:45:26 UTC
If I sort test.ods or example2.ods manually using drag-and-drop (with the ALT
key) to move a row of selected of cells (eg move row of cells A5:D5 up or down),
then the whole row of selected cells remains intact, and links in all cells
continue to point to the originally referenced cells, regardless of whether
those referenced cells are in the same sheet or a different ODS document, and
regardless of whether the links are absolute or non-absolute.

This is exactly the functionality the user would expect, and is surely the way
Data|Sort should behave.
Comment 13 matt_uk 2010-04-05 15:14:00 UTC
Could the Sort routine move rows (or columns) using the same routines used by
Drag-and-Drop(with 'ALT' key).  Or the routines to move rows/columns could be
based on the routines used by Drag-and-Drop('ALT' key).
Comment 14 ooo 2011-02-15 12:24:09 UTC
Grabbing issue.
Treat external references the same as references to other sheets.
Comment 15 niklas.nebel 2011-02-28 11:32:50 UTC
Code change reviewed.
Comment 16 ooo 2011-03-03 12:53:05 UTC
changeset 55737a716866 http://hg.services.openoffice.org/cws/calc66/changeset/55737a716866
M sc/source/core/tool/interpr1.cxx

You can observe the progress and possible integration date of CWS calc66 at
http://tools.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fcalc66
Comment 17 ooo 2011-03-15 12:51:43 UTC
Reassigning to QA for verification.
Comment 18 kla 2011-03-21 11:54:16 UTC
Seen ok in cws calc66 -> verified