Bug 106763 - Problems with references when new columns/rows are inserted
Summary: Problems with references when new columns/rows are inserted
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.4.2 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Reference
  Show dependency treegraph
 
Reported: 2017-03-25 02:48 UTC by David Lochrin
Modified: 2018-09-24 01:34 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Four rows are adjusted correctly or not, as noted in the comments. (8.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-25 02:48 UTC, David Lochrin
Details
The AddRow macro in this sheet demonstrates the problem (18.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-27 10:30 UTC, David Lochrin
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Lochrin 2017-03-25 02:48:35 UTC
Created attachment 132138 [details]
Four rows are adjusted correctly or not, as noted in the comments.

This bug is so simple I wonder whether I misunderstand something but, if not, I think it's critical.  It may be related to #42261 from Winfried Donkers.

The attachment illustrates the problem.  The four rows 6-9 containing value "1" are summed with =SUM($B$6:$B$9).  If one right-clicks on row-5 "Add Rows Above" the formula is correctly adjusted to =SUM($B$7:$B$10).  However the same operation on row-6 results in the formula =SUM($B$6:$B$10) which sums five rows.  I would expect it to also adjust the range to $B$7:$B$10.

This problem was first identified when a macro using:
oActSheet.insertCells (oCellRangeAddress, com.sun.star.sheet.CellInsertMode.ROWS)
failed to work as expected though in a more complex way.

Regards,
David Lochrin
Comment 1 tommy27 2017-03-25 08:37:00 UTC
did you try LibO 5.2.6?
the 5.1.4 release you are using is obsolete
Comment 2 Xisco Faulí 2017-03-25 09:44:48 UTC
Yes, could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Comment 3 David Lochrin 2017-03-26 10:10:57 UTC
I'm still running an old version of Linux at the moment (SuSE 11.4) and am unable to run LibreOffice 5.3, though I plan to buy 64-bit hardware soon and upgrade.

However I uninstalled LibreOffice-5.1.4.2 and performed a fresh install of all version 5.2.6.2 software (core packages, GB language pack & help pack, and the SDK).

I have to report that the problem reported above occurs on 5.2.6.2 also.

DL
Comment 4 m.a.riosv 2017-03-26 13:56:51 UTC
Please review the option:
Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted.
Comment 5 David Lochrin 2017-03-27 02:30:51 UTC
(In reply to m.a.riosv from comment #4)
> Please review the option:
> Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted.

You're absolutely correct Miguel, and I apologise for wasting everyone's time.  I thought it was a bit too simple for a bug!

However I do think this option is rather dangerous because it can obviously lead to incorrect results when a spreadsheet designer is unaware of it and a function argument then operates on unintended cells.  I imagine most would not be aware of it; I wasn't and I count myself as a knowledgable user.

Should it be set "off" by default, and has the default changed in recent versions?  Though I realise changing defaults is tricky.

In my case the effect arose quite indirectly.  I have written macros for adding & deleting multiple rows and sorting up to two columns of commonly formatted sheets (with header row(s), a data area, and an end row), and I've been using them for years.

But recently a calculation located below the data area (i.e. outside the nominal scope of the macros) generated incorrect results when rows were added anywhere in the data such that the last row of the new data area extended at least to the row ("x") immediately before a SUM(x:y) function.  The SUM() function then summed the range (x:y+n) where "n" is the number of rows added.

Thanks for your response.

Regards,
David Lochrin
Comment 6 m.a.riosv 2017-03-27 08:54:15 UTC
(In reply to David Lochrin from comment #5)

As more options we have becomes more complicated deal with them.

You are right change defaults, it's usually a hard decision. 
> 
> But recently a calculation located below the data area (i.e. outside the
> nominal scope of the macros) generated incorrect results when rows were
> added anywhere in the data such that the last row of the new data area
> extended at least to the row ("x") immediately before a SUM(x:y) function. 
> The SUM() function then summed the range (x:y+n) where "n" is the number of
> rows added.

Please attach a sample file with a step by step about how to reproduce the issue.
Comment 7 David Lochrin 2017-03-27 10:26:57 UTC
I've attached a sheet containing the AddRow macro which demonstrates the problem (with DelRow & Sort).  This also contains a note describing how to reproduce it.

Please note that my previous description of the condition which manifests the problem was not quite accurate, but not in any fundamental way.


As a more philosophical observation, I'd also like to say I think the option “Expand references when new columns / rows are added” is not consistent with absolute cell referencing using the "$" syntax.

I'd expect an absolute cell range like $A$5:$A$10 to refer to those particular cells regardless of what rows or columns were added outside the range.  With this option enabled, however, a user could unintentionally introduce a "bug" into an existing macro without even being aware that it was a possibility.

In effect the concept of an absolute cell reference should be extended by adding the words "...providing the option to expand references is not enabled."

DL
Comment 8 David Lochrin 2017-03-27 10:30:46 UTC
Created attachment 132168 [details]
The AddRow macro in this sheet demonstrates the problem

Here's the attachment for the previous comment...
Comment 9 Jean-Baptiste Faure 2018-06-24 15:58:52 UTC
I do not reproduce the problem with the steps described in bug description: if I insert a row above row 5 or row 6, in both cases the formula is updated as SUM($B$7:$B$10) as expected.
In my user profile the option "Expand references when new columns/rows are inserted" is not checked.

Tested with Version: 6.0.4.2
Build ID: 1:6.0.4~rc2-0ubuntu0.16.04.1
CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk3; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group threaded

Best regards. JBF
Comment 10 malboarg 2018-09-23 22:12:05 UTC
I can't reproduce the error. (default options)
The bug was tested in 2 versions:

Version 4.0.0.1 (Build ID: 527dba6f6e0cfbbc71bd6e7b88a52699bb48799)

Version: 6.0.5.1
Build ID: 0588a1cb9a40c4a6a029e1d442a2b9767d612751
CPU threads: 2; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: es-AR (es_AR.UTF-8); Calc: group
Comment 11 David Lochrin 2018-09-24 00:43:04 UTC
(In reply to malboarg from comment #10)
> I can't reproduce the error. (default options)
> The bug was tested in 2 versions:
> 
> Version 4.0.0.1 (Build ID: 527dba6f6e0cfbbc71bd6e7b88a52699bb48799)
> 
> Version: 6.0.5.1
> Build ID: 0588a1cb9a40c4a6a029e1d442a2b9767d612751
> CPU threads: 2; OS: Linux 4.15; UI render: default; VCL: gtk2; 
> Locale: es-AR (es_AR.UTF-8); Calc: group

I apologise for any confusion which has occurred in relation to this, but it was cleared up in Comment-4 by m.a.riosv:

> Please review the option:
> Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted.
which I acknowledged in Comment-5:

> You're absolutely correct Miguel, and I apologise for wasting everyone's time.  I thought it was a bit too simple for a bug!
I assumed I'm not authorised to close a bug report.

David Lochrin