Bug 43004 - EDITING: Cell references modified wrongly after horizontal sort
Summary: EDITING: Cell references modified wrongly after horizontal sort
Status: RESOLVED DUPLICATE of bug 45146
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2011-11-16 15:32 UTC by Abstro
Modified: 2014-11-20 14:46 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet illustrating the issue of sorting by row (8.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-11-16 15:32 UTC, Abstro
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Abstro 2011-11-16 15:32:58 UTC
Created attachment 53614 [details]
Spreadsheet illustrating the issue of sorting by row

Problem description: 
  When you sort BY ROW a Spreadsheet containing reference to "one cell on the row just on the right" (for example B2 using a reference to A1) the resulting spreadsheet cells keep reference to the old reference (if A1 moved to C1 and B2 to D2, then D2 still reference A1 and not C1).

Steps to reproduce:
1. Open "SupprisingSortResult.ods" attached
2. Select $Feuille2.a1:h3
3. Sort by row on the 3rd line
(that is) Menu "Data", item "Sort..."
          "option"
          direction "from left to right (row sorting)"
          "sort criteria"
          "sort by" "line 3"
          "OK"

Current behavior:
  1st line is sorted    (A       B       C       D      )
  2nd line isn't sorted (A.a B.b B.a A.b C.a D.b D.a C.b)
  3rd line is sorted    (A.1 A.2 B.1 B.2 C.1 C.2 D.1 D.2)

Expected behavior:
  1st line is sorted    (A       B       C       D      )
  2nd line is sorted    (A.a A.b B.a B.b C.a C.b D.a D.b)
  3rd line is sorted    (A.1 A.2 B.1 B.2 C.1 C.2 D.1 D.2)
  This is expected because before the sort A.b is in the same row as A.2 ; B.b as B.2...

Platform (if different from the browser): 
              
Browser: Mozilla/5.0 (Windows NT 6.0; rv:8.0) Gecko/20100101 Firefox/8.0

Remark :
  This is NOT an issue when sorting by line (as more usual). You can use $Feuille1 to ensure that.
  In "a" previous version sorting by line has a similar problem, but it is not the case any more. I think that this "resolved" issue is the object of the Bug#36073.
  As I used an French localised version of LibreOffice, I have to guess the menu, item, button... label in English (I hope that I'm not to far from the real ones).
Comment 1 Abstro 2011-11-16 15:47:44 UTC
One possible workaround IN THE PRESENT example :

  If you replace "B2"=A1&".b" by "B2"=OFFSET(B1;0;-1)&".b", then the resulting sorted spreadsheet will behave itself.

  BUT this is not a general solution. It only works when the raw "just on the left" is the same before and after the sort. In other words, that workaround works when each pair of row (A and B, C and D, E and F...) are mean to be AND stay together.
Comment 2 Rainer Bielefeld Retired 2011-11-16 21:32:43 UTC
It seems [Reproducible] with "LibreOffice 3.4.4  - WIN7 Home Premium (64bit) German UI [OOO340m1 (Build:402)]" 

I expect that after sort the references will be to the cell with the same contents as before

Before sort: Cell above "B.1" references to A1="B"
After Sort:  Cell above "B.1" references to C1="B"     ok

Before sort: Cell above "B.2" references to A1="B"
After Sort:  Cell above "B.2" references to A1="A"     WRONG

Before sort: Cell above "A.1" references to C1="A"
After Sort:  Cell above "A.1" references to A1="A"     ok

Before sort: Cell above "A.2" references to C1="A"
After Sort:  Cell above "A.2" references to C1="B"     WRONG

Before sort: Cell above "D.1" references to E1="D"
After Sort:  Cell above "D.1" references to G1="D"     ok

Before sort: Cell above "D.2" references to E1="D"
After Sort:  Cell above "D.2" references to E1="C"     WRONG

It seems that all references below empty cells get wrong after sort, but I am not sure whether I understood the mechanism, it has not to do with "empty".

And yes, works fine with sort by column.

@Kohei:
Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.

- Reported with Bug Submission Assistant -
Comment 3 Rainer Bielefeld Retired 2011-11-16 21:37:52 UTC
Seems to be inherited from OOo, already a problem with OOo 3.1.1.

Still a problem with Server installation of Master "LibO-dev 3.5.0 – WIN7 Home Premium (64bit) ENGLISH UI [(Build ID:  d38713d-5d03837-ca7e6f5-c4bb9bd-ce71330)]" ( Voreppe_Win32_Tinderbox 111115)
Comment 4 Abstro 2012-09-04 19:57:14 UTC
Doesn't work with "LibreOffice 3.5.6.2"
Version ID : e0fbe70-5879838-a0745b0-0cd1158-638b327
Comment 5 Abstro 2012-09-06 00:49:44 UTC
Sorry, I'd learn that "Version" is most old version of LO where bug appeared. Not current version.
Changing back to 3.3.0 Beta2
Comment 6 Winfried Donkers (retired) 2013-08-26 06:53:24 UTC
Although this bug report is older than bug 45146, lots of other bug reports have been marked as duplicates of bug 45146, making bug 45146 more appropiate to act as the central report.

*** This bug has been marked as a duplicate of bug 45146 ***
Comment 7 Jean-Baptiste Faure 2014-10-19 15:30:47 UTC
For the record:
- does not work as expected in LO 4.0.6 and LO 4.1.6
- works as expected in LO 4.2.6.3
- works as expected in LO 4.3.4.0.0+ only if hidden configuration option "UpdateReferenceOnSort" is set to true (default value: false). If option is set to false the result of sorting is different from what it was in 4.0 and 4.1:
A		B		C		D	
B.a	B.b	A.a	A.b	D.a	D.b	C.a	C.b
A.1	A.2	B.1	B.2	C.1	C.2	D.1	D.2
that is no change in row 2.
- same behavior with LO 4.4.0.0.alpha+ as with LO 4.3.4.0.0+


Build ID for 4.3.4.0.0+ : f9050536650835b776b9aa022ae1b47315cd5e14
Build ID for 4.4.0.0.alpha+: 68a18a6249dc9d8842a72413f3cd6f39ecf416e3

Best regards. JBF
Comment 8 Eike Rathke 2014-11-20 14:46:15 UTC
The observation in comment 7 is actually bug 83765, fixed in master and pending review for 4-3 and 4-2