Bug 92779 - Converting to entire column is inconsistent (sometimes works sometimes produces #REF!)
Summary: Converting to entire column is inconsistent (sometimes works sometimes produc...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.0.3 rc
Hardware: All All
: medium enhancement
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard: target:5.2.0 target:5.1.0.1
Keywords:
: 92751 92759 94959 (view as bug list)
Depends on:
Blocks:
 
Reported: 2015-07-16 02:34 UTC by Óvári
Modified: 2016-10-25 19:08 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Row test with failures (15.98 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-12-10 07:47 UTC, Óvári
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Óvári 2015-07-16 02:34:33 UTC
---- Examples where bug disappears ----

Example A (bug disappears)
1. Open LibreOffice (LO) Calc
2. In cell A2 enter: =SUM(B$1:B$1048575)
3. Right click on row heading 4
4. Select 'Insert Rows Above'
5. Cell A2 correctly shows: =SUM(B:B)
6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B:B)

Example B (bug disappears)
1. Open LibreOffice (LO) Calc
2. In cell A2 enter: =SUM(B$1:$B$1048575)
3. Right click on row heading 4
4. Select 'Insert Rows Above'
5. Cell A2 correctly shows: =SUM(B:$B)
6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B:$B)

Example C (bug disappears)
1. Open LibreOffice (LO) Calc
2. In cell A2 enter: =SUM($B$1:B$1048575)
3. Right click on row heading 4
4. Select 'Insert Rows Above'
5. Cell A2 correctly shows: =SUM($B:B)
6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM($B:B)

Example D (bug disappears)
1. Open LibreOffice (LO) Calc
2. In cell A2 enter: =SUM($B$1:$B$1048575)
3. Right click on row heading 4
4. Select 'Insert Rows Above'
5. Cell A2 correctly shows: =SUM($B:$B)
6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM($B:$B)


---- Examples where bug appears ----

Example E (bug appears)
1. Open LibreOffice (LO) Calc
2. In cell A2 enter: =SUM(B1:B1048575)
3. Right click on row heading 4
4. Select 'Insert Rows Above'
5. Cell A2 incorrectly shows: =SUM(B1:B1048576)
   Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5}
6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B1:B#REF!)

Example F (bug appears)
1. Open LibreOffice (LO) Calc
2. In cell A2 enter: =SUM(B1:B$1048575)
3. Right click on row heading 4
4. Select 'Insert Rows Above'
5. Cell A2 incorrectly shows: =SUM(B1:B$1048576)
   Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5}
6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B1:B$#REF!)

Example G (bug appears)
1. Open LibreOffice (LO) Calc
2. In cell A2 enter: =SUM(B$1:B1048575)
3. Right click on row heading 4
4. Select 'Insert Rows Above'
5. Cell A2 incorrectly shows: =SUM(B$1:B1048576)
   Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5}
6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B$1:B#REF!)


Likewise converting to entire row is inconsistent (sometimes works sometimes produces #REF!)


Thank you
Comment 1 Jean-Baptiste Faure 2015-07-16 04:48:54 UTC
What is the purpose of this bug report, considering that bug 92759 and bug 92751 have been closed as WontFix?

Set status to NEEDINFO, please set it back to UNCONFIRMED once you have provided requested information.

Best regards. JBF
Comment 2 Óvári 2015-07-16 05:58:25 UTC
Thank you Jean-Baptiste for your question.

Perhaps I have caused some confusion if I made a few mistakes like:
1. changing bug 92759 and bug 92751 status to WONTFIX;
2. creating a new bug 92779 instead of adding to one of the previous ones.

In bug 92751 Erike wrote “There's no automatic conversion to $B:B in between and should not be.”

However, upon further testing there are cases where there *is* automatic conversion.  Example cases are shown in bug 92779.

Perhaps bug 92759 and bug 92751 status should be set to NEW as implementing this will assist in having feature parity and reduce the likelihood of creating #REF! in a formula.

I think this bug is slightly different to bug 92759 and bug 92751 as it relates to changing the range in a formula for an entire column/entire row from A1:A1048576/A1:AMJ1 to A:A/1:1 notation in the event that single row/column is inserted.

Thank you
Comment 3 Eike Rathke 2015-07-16 11:37:32 UTC
(In reply to Óvári from comment #0)
> Example E (bug appears)
> 1. Open LibreOffice (LO) Calc
> 2. In cell A2 enter: =SUM(B1:B1048575)
> 3. Right click on row heading 4
> 4. Select 'Insert Rows Above'
> 5. Cell A2 incorrectly shows: =SUM(B1:B1048576)
>    Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5}

Note that this is different, example A has both absolute row references.
References that result from inserting/deleting/shifting/moving and have
not both anchors absolute are not displayed as entire col/row
references. For user convenience, only when entering an expression and
both anchors are relative it is taken as entire col/row reference,
because that is what you also get when selecting a range with
Shift+Ctrl+Down for example.

> 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as:
> =SUM(B1:B#REF!)


> Example F (bug appears)
> 1. Open LibreOffice (LO) Calc
> 2. In cell A2 enter: =SUM(B1:B$1048575)
> 3. Right click on row heading 4
> 4. Select 'Insert Rows Above'
> 5. Cell A2 incorrectly shows: =SUM(B1:B$1048576)
>    Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5}

Mixed anchors, one relative one absolute. This will never yield an
entire col/row reference. On purpose, because when copy&paste such
reference the absolute part is sticky and the relative parts gets
adjusted relatively to the new position.

> 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as:
> =SUM(B1:B$#REF!)
> 
> Example G (bug appears)
> 1. Open LibreOffice (LO) Calc
> 2. In cell A2 enter: =SUM(B$1:B1048575)
> 3. Right click on row heading 4
> 4. Select 'Insert Rows Above'
> 5. Cell A2 incorrectly shows: =SUM(B$1:B1048576)
>    Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5}

Again the same, mixed anchors.

> 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as:
> =SUM(B$1:B#REF!)


It is debatable what actually should happen with references that were
not entire col/row but only become when inserting rows/cols.
Excel is a bit more lax there and if both anchors are either absolute or
relative, the reference is displayed as entire col/row reference. Not
with mixed anchors. Also if inserting rows results in an entire column
that the reference wasn't before, even if not displayed as A:A/1:1
because of mixed anchors, the reference anchors become sticky,
regardless of relative or absolute addressing, and are not shifted when
deleting rows or when inserting further rows. However, copy&paste
=SUM(B$1:B1048576) one row further down still results in #REF!

Maybe we could implement similar behaviour.
Comment 4 Ari Latvala 2015-11-28 20:19:36 UTC
I'd love to see similar behaviour for selecting whole rows/columns as on Excel, just by clicking row number/column label. Currently really lack compared to Excel way of doing things.
Comment 5 Óvári 2015-11-29 04:22:14 UTC
Ari Latvala comment 4 refers to bug 92439.
Comment 6 Commit Notification 2015-12-09 20:16:10 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

sticky end col/row anchor for range references, tdf#92779

It will be available in 5.2.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 Óvári 2015-12-09 20:32:31 UTC
Thank you Eike Rathke for this patch.

Will the LibreOffice 5.0 Release Notes be updated?
“Mixed absolute/relative anchors lead to a full range notation being displayed, e.g. A$1:A1048576 or A1:A$1048576 because Calc assumes the user did that on purpose.”
https://wiki.documentfoundation.org/ReleaseNotes/5.0#A:A_.2F_1:1_entire_column.2Frow_references
Comment 8 Commit Notification 2015-12-09 21:33:09 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

display as entire col/row also if both anchors relative, tdf#92779

It will be available in 5.2.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-12-09 21:58:36 UTC
(In reply to Óvári from comment #7)
> Will the LibreOffice 5.0 Release Notes be updated?

No, because it will not be available in 5.0
Comment 10 Commit Notification 2015-12-09 23:07:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

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

sticky end col/row anchor for range references, tdf#92779

It will be available in 5.1.0.1.

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 Commit Notification 2015-12-09 23:07:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

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

display as entire col/row also if both anchors relative, tdf#92779

It will be available in 5.1.0.1.

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 12 Óvári 2015-12-10 07:47:46 UTC
Created attachment 121183 [details]
Row test with failures

Thank you for also fixing bug 92759 and bug 92751 with this patch.

1. Please look at formulae in column C. They should be the same as the formulae in column A (without the ')
2. Select rows 30-34 (i.e. 5 rows)
3. Right click and select ‘Insert Rows Above’
4. Look at formuilae in column C. They should be the same as the formulae in column B (without the '); however some are incorrect as shown in column D.

Thank you
Comment 13 Óvári 2015-12-10 07:49:00 UTC
*** Bug 92759 has been marked as a duplicate of this bug. ***
Comment 14 Óvári 2015-12-10 07:49:28 UTC
*** Bug 92751 has been marked as a duplicate of this bug. ***
Comment 15 Eike Rathke 2015-12-10 12:17:38 UTC
(In reply to Óvári from comment #12)
> Created attachment 121183 [details]
> Row test with failures

That is the same scenario I already explained in comment 3, mixed absolute and relative addressing. If one row is addressed absolute and the other relative, the reference will never be displayed as an entire column reference.
Comment 16 Commit Notification 2015-12-10 15:57:25 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

sticky end only if it already was a range on the same axis, tdf#92779

It will be available in 5.2.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 17 Commit Notification 2015-12-11 13:46:28 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

geez, how about actually checking the Move() error return? tdf#92779 related

It will be available in 5.2.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 18 Commit Notification 2015-12-11 13:46:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

unit test for sticky end col/row anchors, tdf#92779

It will be available in 5.2.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 19 Commit Notification 2015-12-11 15:50:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=74397f2fab816c11b73c36ef70bed97ae146969e&h=libreoffice-5-1

sticky end only if it already was a range on the same axis, tdf#92779

It will be available in 5.1.0.1.

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 20 Commit Notification 2015-12-11 15:50:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

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

geez, how about actually checking the Move() error return? tdf#92779 related

It will be available in 5.1.0.1.

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 21 Commit Notification 2015-12-11 15:50:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

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

unit test for sticky end col/row anchors, tdf#92779

It will be available in 5.1.0.1.

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 22 Commit Notification 2015-12-11 20:15:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

separate ScRange::Move() and MoveSticky(), tdf#92779

It will be available in 5.2.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 23 Commit Notification 2015-12-11 21:02:33 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=478917a2c0fe7e7e3c79d9a12b4488128e6da969&h=libreoffice-5-1

separate ScRange::Move() and MoveSticky(), tdf#92779

It will be available in 5.1.0.1.

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 24 Óvári 2015-12-29 02:13:32 UTC
Danke schön Eike Rathke! (Thank you)
Comment 25 raal 2016-03-24 06:19:17 UTC
*** Bug 94959 has been marked as a duplicate of this bug. ***