Download it now!
Bug 77647 - EDITING: calc 4.2.3.3 formula sum isn't work correct when insert rows
Summary: EDITING: calc 4.2.3.3 formula sum isn't work correct when insert rows
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: All All
: high critical
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.5
Keywords: regression
: 76912 77807 78086 78235 78633 78856 78991 80092 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-04-18 13:57 UTC by VLB
Modified: 2014-07-22 17:14 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
Test-file (202.89 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-04-18 13:57 UTC, VLB
Details
Sample file test inserting/deleting rows/columns (13.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-05 14:57 UTC, m.a.riosv
Details
demonstrate errors with col insertion (34.06 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-05 22:30 UTC, kabull2
Details

Note You need to log in before you can comment on or make changes to this bug.
Description VLB 2014-04-18 13:57:38 UTC
Created attachment 97561 [details]
Test-file

LO 4.2.3.3 and earlier windows 7 64 bit

step to reproduce in test file:

1) select rows 40 to 45 and insert rows
2) go to m56 (was m50) and the formula sum isn't correct range

When insert select rows 47 to 48 or 43 to 44 it is correct.

I hope you can solve the issue
Comment 1 VLB 2014-04-18 13:59:34 UTC
In LO 4.1.5 it was oke
Comment 2 Thomas Hackert 2014-04-18 15:07:16 UTC
Comment on attachment 97561 [details]
Test-file

Hello,
for whatever reason, your attachment was added as "text/plain", so I could not open it ... :( I hope, my edit will fix it ... ;)
Sorry for the inconvenience
Thomas.
Comment 3 Thomas Hackert 2014-04-18 16:05:05 UTC
Hello vlb, *,
I can confirm your bug with LO Version: 4.2.4.1 Build ID: d4c441391e20647b3d2e8dde4d20aa868e77e515 (parallel installed, following the instructions from https://wiki.documentfoundation.org/Installing_in_parallel) with Germanophone lang- as well as helppack under Debian Testing i686, so I will change "Platform" to "All", as it is not only a Win 64bit bug ... ;)

Interestingly, if I use my also parallel installed Version: 4.1.6.1 Build ID: a59ce81388f477fc89db57f0c27f222f31884eb (also with Germanophone lang- as well as helppack on the same system), I see, that the sum at cell m56 stays the same (with the value "7,6"). This is also the case with my installed version of LO Version: 4.1.5.3 Build-ID: 1c1366bba2ba2b554cd2ca4d87c06da81c05d24, so this seems a regression ... :(

Better description for the steps to reproduce:
0. Download the attached document (Issue-som.ods from https://bugs.freedesktop.org/attachment.cgi?id=97561)
1. start Calc
2. click on the header of row 40 to mark the whole row
3. press <Shift> and click on the header of row 45
4. rightclick on the marked headers of these rows and select "Insert Rows Above"
5. Now look at cell m56 (which changed its value)
6. Go to cell M56. You will see, that the sum formular changed as well (=SUM(M45:M55)) ... (

In both older versions, the sum formular just changed from "=SUM(M45:M49)" to "=SUM(M51:M55)", adding 6 cells to both sides of the sum formular instead of staying at "M45" and enhancing its range to "M55" ... :(
HTH
Thomas.
Comment 4 VLB 2014-04-18 18:12:13 UTC
@ Thomas thanks for it and when i added the attachment, i can't choose the file type. Sorry for it.
Comment 5 Andras Timar 2014-04-18 19:38:43 UTC

*** This bug has been marked as a duplicate of bug 76912 ***
Comment 6 Kohei Yoshida 2014-04-28 18:14:00 UTC
Not enough evidence to mark this a duplicate.
Comment 7 Kohei Yoshida 2014-04-28 18:14:11 UTC
Back to new.
Comment 8 Kohei Yoshida 2014-04-29 00:54:30 UTC
I'm looking into this right now.
Comment 9 Commit Notification 2014-04-29 03:01:24 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#77647: Write test for this.



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 10 Commit Notification 2014-04-29 03:01:36 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#77647: Expand reference only when the top of the selected range is...



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 Kohei Yoshida 2014-04-29 03:02:26 UTC
4.2 backport: https://gerrit.libreoffice.org/9190
Comment 12 Andras Timar 2014-04-29 12:44:11 UTC
*** Bug 77807 has been marked as a duplicate of this bug. ***
Comment 13 Commit Notification 2014-04-29 12:45:35 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

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

fdo#77647: Expand reference only when the top of the selected range is...


It will be available in LibreOffice 4.2.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 14 VLB 2014-04-29 16:09:01 UTC
Thanks die solve the bug!
Comment 15 Kohei Yoshida 2014-04-29 17:30:16 UTC
*** Bug 76912 has been marked as a duplicate of this bug. ***
Comment 16 VLB 2014-05-01 19:06:13 UTC
(In reply to comment #13)
> Kohei Yoshida committed a patch related to this issue.
> It has been pushed to "libreoffice-4-2":
> 
Thanks for solving the bug and works oke.
Is it possible to solved the bug in 4.2.4?
Comment 17 Kohei Yoshida 2014-05-01 19:56:04 UTC
(In reply to comment #16)
> (In reply to comment #13)
> > Kohei Yoshida committed a patch related to this issue.
> > It has been pushed to "libreoffice-4-2":
> > 
> Thanks for solving the bug and works oke.
> Is it possible to solved the bug in 4.2.4?

I'm afraid that would be very difficult at this point.  Let's wait for 4.2.5.
Comment 18 Kohei Yoshida 2014-05-02 01:06:44 UTC
*** Bug 78086 has been marked as a duplicate of this bug. ***
Comment 19 Jean-Baptiste Faure 2014-05-02 16:56:04 UTC
*** Bug 78086 has been marked as a duplicate of this bug. ***
Comment 20 m.a.riosv 2014-05-04 01:49:44 UTC
Sorry but I think the issue has not been solved, with this reported bug and their sample file, and inserting as mentioned in my comment#1 https://bugs.freedesktop.org/show_bug.cgi?id=76912 (marked as duplicate of this one in comment#15).

https://bugs.freedesktop.org/show_bug.cgi?id=78235 reproduce the issue.

With a clean spreadsheet:

In B3: =SUM(B1:B2)
Select columns A plus B.
Insert columns.
In D3: =SUM(B1:D2)

In C2: =SUM(A2:B2)
Select rows 1 plus 2.
Insert rows.
In D3: =SUM(A2:B4)

The first address in the formulas is not updated.

For both, inserting columns and rows, and this bug:
Win7x64Ultimate
Version: 4.2.4.1 Build ID: d4c441391e20647b3d2e8dde4d20aa868e77e515
Version: 4.2.5.0.0+ Build ID: 59906c3d54e6541185f4bf85b1d1c70530198059
   TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-04-30_09:30:13

Only inserting columns with (not reproducible this bug):
Version: 4.3.0.0.alpha1+ Build ID: 0b03f7ed575838f90e6b1ebec3538a3a214f81fb
   TinderBox: Win-x86@39, Branch:master, Time: 2014-04-30_01:30:46
Comment 21 VLB 2014-05-04 08:08:54 UTC
> With a clean spreadsheet:
> 
> In B3: =SUM(B1:B2)
> Select columns A plus B.
> Insert columns.
> In D3: =SUM(B1:D2)

This can i reproduce in Version: 4.2.5.0.0+
Build ID: 59906c3d54e6541185f4bf85b1d1c70530198059
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-04-30_09:30:13
Windows 7 64 bit

This is the same issue with the inserting rows (bug 77647 and is solved). The bug with inserting rows is solved, but inserting colums isn't solved.

> 
> In C2: =SUM(A2:B2)
> Select rows 1 plus 2.
> Insert rows.
> In D3: =SUM(A2:B4)

I didn't now this, when i do this than is the sum formula in C3 and is oke.
Comment 22 m.a.riosv 2014-05-04 12:32:53 UTC
*** Bug 78235 has been marked as a duplicate of this bug. ***
Comment 23 Kohei Yoshida 2014-05-04 12:53:45 UTC
Check your "Expand references when new columns/rows are inserted" option.  When that option is checked, it behaves as if this bug isn't fixed but is an intended behavior (as in 4.1 does the same thing).

I'll check about colum insertion case.
Comment 24 m.a.riosv 2014-05-04 16:04:37 UTC
Thanks for the information, disabling the option "Expand references when new columns/rows are inserted" make it works as expected for rows not for columns.

I need to rethink twice to understand why with the option enable it's the adequate how it works.
Ok servers e.g. to insert a new row(s) from the first row of a data table, without lost the references to the data ta ble, when the row(s) to insert start at first row of the referenced range.
But I have not so clear when the rows to insert star before.
Comment 25 kabull2 2014-05-04 19:32:26 UTC
I'm the originator of 78235.
I've checked that 'Expand formatting' and 'Expand references ...' are not checked in my configuration.
Comment 26 Kohei Yoshida 2014-05-05 14:39:53 UTC
Maybe this behavior of expanding rows in 4.1 was a bug.  It didn't make sense to me (I just copied this behavior from 4.1) and I guess it's confusing a lot of folks.
Comment 27 Kohei Yoshida 2014-05-05 14:42:55 UTC
There is a lot of behaviors in 4.1 that makes no sense when the Expand references option is enabled.  At least the current behavior in 4.2 is consistent between row and column expansions.
Comment 28 Kohei Yoshida 2014-05-05 14:51:05 UTC
Given this, I'm not sure if I want to spend any more time on this issue without first making clear what this option is supposed to do.  I'm very very confused as to what the proper behavior should be...

I for one won't mind just limiting the expansion to the bottom row side, and disabling it in all the other sides.
Comment 29 m.a.riosv 2014-05-05 14:57:32 UTC
Created attachment 98492 [details]
Sample file test inserting/deleting rows/columns

Perhaps for a temporary stable solution, sounds fine.

Maybe a table on how the different options must work, could help devs to implement and user to know how it works.

Attached a test for this matter, started a few days ago, but not finish.
Comment 30 kabull2 2014-05-05 20:59:35 UTC
This behaviour is important to me as I have sheets in which I move a block of data & formulas to the right at intervals by inserting new columns to the left.  The intention is to replicate formulas so that new data can be introduced.  Until earlier this year (I cant remember which version brought the change), I could just select the required number of columns, insert left, then copy the relevant formula block into the new area.  All formulas adjusted as expected (and as happens correctly in MS Excel).

Since this bug appeared I have to leave a blank col to the left of my data and progressively add the required additional columns before copying the old data.  (A bit tedious when several new cols are needed!)  All is OK so long as I do not include the first column of the previous block in the insert selection.

Inserting cols in the middle of the data block results is OK.
Comment 31 Kohei Yoshida 2014-05-05 21:41:47 UTC
(In reply to comment #30)
> This behaviour is important to me as I have sheets in which I move a block
> of data & formulas to the right at intervals by inserting new columns to the
> left.  The intention is to replicate formulas so that new data can be
> introduced.  Until earlier this year (I cant remember which version brought
> the change), I could just select the required number of columns, insert
> left, then copy the relevant formula block into the new area.  All formulas
> adjusted as expected (and as happens correctly in MS Excel).

Could you give us a specific example to make sure we all understand which behavior you are referring to?
Comment 32 VLB 2014-05-05 21:52:25 UTC
(In reply to comment #23)
> 
> I'll check about colum insertion case.

Is this bug richt solved yet?
Comment 33 kabull2 2014-05-05 22:30:24 UTC
Created attachment 98521 [details]
demonstrate errors with col insertion
Comment 34 kabull2 2014-05-05 22:31:58 UTC
Kohei
This is a very simple demonstration.  I think it should be sufficient.  Does Err:522 help?

Tony
Comment 35 Kohei Yoshida 2014-05-06 16:32:54 UTC
(In reply to comment #34)
> Kohei
> This is a very simple demonstration.  I think it should be sufficient.  Does
> Err:522 help?

Yes.  At least now I know you are just referring to the original bug, not what we were discussing with respect to "what the proper behavior is when the expand reference option is checked".
Comment 36 Commit Notification 2014-05-06 19:26:44 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#77647: Test for reference adjustment on column insertion.



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 37 Commit Notification 2014-05-06 19:27:01 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#77647: Fix the column insertion use case.



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 38 Kohei Yoshida 2014-05-06 19:27:30 UTC
4.2 backport of the column insertion case: https://gerrit.libreoffice.org/9261
Comment 39 Kohei Yoshida 2014-05-06 19:28:29 UTC
I'll set the issue of inconsistent behaviors with the Expand reference options turned on for now.  Let's focus on the column insertion case with that option turned off.
Comment 40 Kohei Yoshida 2014-05-06 19:28:50 UTC
(In reply to comment #39)
> I'll set the issue of inconsistent behaviors with the Expand reference
> options turned on for now.  Let's focus on the column insertion case with
> that option turned off.

I meant "set aside".
Comment 41 Commit Notification 2014-05-06 22:12:14 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=043dc226967d28a7ccbc2a39df232ea92a25bc66&h=libreoffice-4-2

fdo#77647: Fix the column insertion use case.


It will be available in LibreOffice 4.2.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 42 Kohei Yoshida 2014-05-06 22:18:58 UTC
I'll call it fixed for now.  Let's handle any other weird issues with the "Expand reference" option separately.
Comment 43 kabull2 2014-05-08 05:16:05 UTC
Tested with libo-42~2014-05-07_23.35.01_LibreOfficeDev_4.2.5.0.0_Win_x86.msi

Performed exactly as expected.

Thank you.
Comment 44 VLB 2014-05-12 20:56:50 UTC
*** Bug 78633 has been marked as a duplicate of this bug. ***
Comment 45 m.a.riosv 2014-05-25 21:54:39 UTC
*** Bug 78991 has been marked as a duplicate of this bug. ***
Comment 46 m.a.riosv 2014-05-26 00:00:27 UTC
*** Bug 78856 has been marked as a duplicate of this bug. ***
Comment 47 m.a.riosv 2014-06-16 12:11:54 UTC
*** Bug 80092 has been marked as a duplicate of this bug. ***
Comment 48 Tom 2014-07-22 17:14:56 UTC
*** Bug 78633 has been marked as a duplicate of this bug. ***