Bug Hunting Session
Bug 92468 - Range reference not justified in order when copied&pasted
Summary: Range reference not justified in order when copied&pasted
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2 all versions
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.1.0 target:4.4.6 target:5.0...
Keywords:
: 87709 (view as bug list)
Depends on:
Blocks:
 
Reported: 2015-07-01 03:04 UTC by Óvári
Modified: 2016-10-25 19:23 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Óvári 2015-07-01 03:04:02 UTC
Thank you Eike Rathke for enabling cell highlighting and formula colors for entire column(s)/row(s) notation.  It made it easier to find the bug below.

1. In cell C7 enter: 1
2. In cell A1 enter: =SUM(B:$C)
3. Press Enter
4. Click on cell A1
5. Ctrl+C (copy)
6. Highlight cells B1:E1
7. Ctrl+V (paste)

8. LibreOffice Calc outputs are:
   Cell   Formula      Value   Expected value
   A1     =SUM(B:$C)   2       Err:522 {Circular reference}
   B1     =SUM(C:$C)   1       Err:522 {Circular reference}
   C1     =SUM(D:$C)   0       Err:522 {Circular reference}
   D1     =SUM(E:$C)   0       Err:522 {Circular reference}
   E1     =SUM(F:$C)   0       Err:522 {Circular reference}

LibreOffice
Version: 5.1.0.0.alpha1+ (x64)
Build ID: ab23efcc2bb9ca178dee16b3d01eabb3f80d8e17
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-06-30_19:11:33
Locale: en-AU (en_AU)


9. Microsoft Excel advises there is a circular reference with:
a) a dialog box; and
b) the status bar shows: Circular References: C1

10. Microsoft Excel outputs are:
   Cell   Formula      Expected formula   Value   Expected value
   A1     =SUM(B:$C)   =SUM(B:$C)         1       Err {Circular reference}
   B1     =SUM(C:$C)   =SUM(C:$C)         1       Err {Circular reference}
   C1     =SUM($C:D)   =SUM(D:$C)         1       Err {Circular reference}
   D1     =SUM($C:E)   =SUM(E:$C)         1       Err {Circular reference}
   E1     =SUM($C:F)   =SUM(F:$C)         1       Err {Circular reference}
Comment 1 Óvári 2015-07-01 06:27:40 UTC
I made a mistake in what I wrote at 2015-07-01 03:04:02 UTC in the section regarding Microsoft Excel.

The 'Expected formula' are incorrect and the 'Formula' (which is actually output by Excel) is correct.  LibreOffice Calc should also store ranges in the format LowerBound:UpperBound.

This may:
1. simplify error finding such as circular references; and
2. make LibreOffice Calc consistent with Microsoft Excel.

Whether a formulae is entered or pasted, LibreOffice Calc should check and store each range in the LowerBound:UpperBound order.

Thank you
Comment 2 Óvári 2015-07-01 11:47:15 UTC
Eike Rathke work in enabling correct references in LibreOffice is to be congratulated.

As Jerzy Tyszkiewicz has raised a concern about LibreOffice Calc formulae capabilities, it might be advisable to continue to use Excel until the resolution of this circular reference.

LibreOffice Calc formula does not correctly flag a circular reference and provides in the spreadsheet while Excel does flag the circular reference correctly.

Thinking further a solution to ensure that any formulae entered or pasted, LibreOffice Calc should check and change if necessary each range in the LowerBound:UpperBound to be in ascending order for both column and row. For example:
B10:B1 should be changed and stored as B1:B10
C:A      --> A:C
A3:B2    --> A2:B3
B2:A3    --> A2:B3
$E3:C$8  --> C3:$E$8

The Name Box in LibreOffice Calc automatically list the range correct if you select cells from E8 to C3, or E3 to C8, or C3 to E8 or C8 to E3. Perhaps the code in the Name Box could be reused to change the formulae in ascending order.

Thank you
Comment 3 Eike Rathke 2015-07-06 22:43:59 UTC
@Ovari:
Again, would you please stop advising the use of a different product? Thank you.
Comment 4 Eike Rathke 2015-07-06 23:24:24 UTC
Actually this isn't even related to A:A/1:1 notation, it happens the same when copying =SUM(B$1:$C$1048576)
Comment 5 Commit Notification 2015-07-07 10:42:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d24c6a0280b0287ee6c23ca89068323c6b7c3dd7

always justify a referenced range in order, tdf#92468

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 6 Commit Notification 2015-07-07 10:42:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ad3d2b6c2e88d191d76f90eb5be927f7ca76c670

(re-)introduce ScComplexRefData::PutInOrder(), tdf#92468

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2015-07-07 10:42:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=369ee0b1faf79f1bd23c75ee04dd0dcc5bf283af

introduce ScTokenArray::AdjustReferenceOnCopy(), tdf#92468

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2015-07-07 10:42:48 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3ddaeaab37d585971e376de6ad7b0f06f55f2e1a

call ScTokenArray::AdjustReferenceOnCopy() in ScFormulaCell clone, tdf#92468

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 9 Eike Rathke 2015-07-07 11:59:57 UTC
Pending review https://gerrit.libreoffice.org/16815 for 5-0
Comment 10 Commit Notification 2015-07-07 12:49:36 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2873d7586199adaaf43144d6b6d4b2f253cfe4dd

unit test for copy&paste put reference in order, tdf#92468

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 11 Eike Rathke 2015-07-07 18:26:52 UTC
Pending review https://gerrit.libreoffice.org/16829 for 4-4
Comment 12 Commit Notification 2015-07-08 11:47:22 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9bb4b60fd6deb348af37d9a51860e4d554ffbc35&h=libreoffice-4-4

always justify a referenced range in order, tdf#92468

It will be available in 4.4.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2015-07-08 11:47:29 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=21d5e7bbb5ba986a3fd2b4194f83c18003fc3bac&h=libreoffice-5-0

always justify a referenced range in order, tdf#92468

It will be available in 5.0.0.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2015-07-22 20:53:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d004a44336f58b7acc146c7d27707f142797042f&h=libreoffice-4-4-5

always justify a referenced range in order, tdf#92468

It will be available in 4.4.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 15 Matthew Francis 2015-09-06 04:38:19 UTC
*** Bug 87709 has been marked as a duplicate of this bug. ***