Bug 85282 - Wrong references after columns are deleted - when the range of delete columns partially covers the range of the cells the formula refers to
Summary: Wrong references after columns are deleted - when the range of delete columns...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: Other All
: medium normal
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.4.0 target:4.3.4 target:4.3.3
Keywords: regression
Depends on:
Blocks:
 
Reported: 2014-10-21 10:51 UTC by Laurent BP
Modified: 2018-11-06 02:19 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Test file: suppress columns A to F, check new formula in E2 (66.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-21 16:04 UTC, Laurent BP
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Laurent BP 2014-10-21 10:51:57 UTC
Description: part of reference in a formula is not updated after row suppression, if new cell location is inside reference

Step to reproduce:
1. Create new spreadsheet
2. In H1, insert formula =SUM(C1:G1)
3. Suppress columns A through E in one shot (select them, right click, delete selected columns)

Expected behavior:
As expected, formula is now in C1. 
But formula should be updated to =SUM(A1:B1) as it is with LibO 4.1.6.2

Observed behavior:
Formula in C1 is now =SUM(C1:B1) which is wrong and should deliver an error (circular reference)

Bug reproduced with:
- Version: 4.2.0.4 Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71
- Version: 4.2.6.2 Build ID: 185f2ce4dcc34af9bd97dec29e6d42c39557298f
- Version: 4.2.8.0.0+
Build ID: da2accb41f902c82436075dc9d0b85a6d7588fa1
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-10-15_23:40:10
- Version: 4.3.0.0.beta1 Build ID: b7cfa1eab1cb1e94f71d6df6612b73f231d0bf92
- LibO 4.3.1.2
- LibO 4.3.2.2
- LibO 4.3.3.1
- Version: 4.3.4.0.0+
Build ID: 77c6ce66696a997269b9fe4dfed1dc2e51ecd00e
TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-10-17_16:12:56
- Master Version: 4.4.0.0.alpha1+
Build ID: a8c24b25fd9fb21097a08a22797bf61b59099ea1
TinderBox: Win-x86@42, Branch:master, Time: 2014-10-21_06:31:17
So resolution of bug 80284 does not solve this one.

Bug NOT reproduced with:
- LibO 4.1.6.2
Comment 1 Cor Nouws 2014-10-21 11:53:52 UTC
Hi Laurent,

(In reply to Laurent BP from comment #0)

> Bug NOT reproduced with:
> - LibO 4.1.6.2

Nor with 4.3.3 release candidate 1 :)
And note that the 4.2 series is at 4.2.7.x currently. SO filing a bug against 4.2.0.4 makes little sense?

Set as WorksForMe
Cheers - Cor
Comment 2 Laurent BP 2014-10-21 12:21:07 UTC
(In reply to Cor Nouws from comment #1)
> Hi Laurent,
> 
> (In reply to Laurent BP from comment #0)
> 
> > Bug NOT reproduced with:
> > - LibO 4.1.6.2
> 
> Nor with 4.3.3 release candidate 1 :)
I do reproduce, as other on fr-qa list, with many versions as indicated. I will ask other to report with their versions. My tests were made with SI-GUI, so with new profiles. All tests were however made on Windows OS. That could make a difference?

> And note that the 4.2 series is at 4.2.7.x currently. SO filing a bug
> against 4.2.0.4 makes little sense?
The bug is still present in master, so it makes sense for me to report it. The version should reflect the earliest version where the bug is present, or am I wrong?
Comment 3 Cor Nouws 2014-10-21 13:35:24 UTC
(In reply to Laurent BP from comment #2)

> The bug is still present in master, so it makes sense for me to report it.
> The version should reflect the earliest version where the bug is present, or
> am I wrong?

Ah yes, apologies if I missed that.
But for me it simply works fine in 4.3 and master.
And honestly I do not expect that this basic function is broken on Windows and not Linux...

Do you have a test document pls?
Comment 4 pierre-yves samyn 2014-10-21 15:47:09 UTC
Hi

(In reply to Cor Nouws from comment #3)
> But for me it simply works fine in 4.3 and master.
> And honestly I do not expect that this basic function is broken on Windows
> and not Linux...

I confirm the bug with the "steps to reproduce".

Reproduced on :

Windows XP & Version: 4.3.1.2
Build ID: 958349dc3b25111dbca392fbc281a05559ef6848 

Windows 7/64 & Version: 4.3.3.1
Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d

Windows 7/64 & Version: 4.4.0.0.alpha0+
Build ID: f33002aa5de7e88960e7c21286a661c89fd478c7
TinderBox: Win-x86@42, Branch:master, Time: 2014-10-04_03:30:14

I set status to NEW

Regards
Pierre-Yves
Comment 5 Laurent BP 2014-10-21 16:04:38 UTC
Created attachment 108192 [details]
Test file: suppress columns A to F, check new formula in E2

Hope to be a little clear with a test file taken from real life, where I detected the problem:
- select columns A to F
- suppress them
formulas in column K move to column C and change from =SUM(E2:J2) to =SUM(E2:D2)

Expected behavior:
Formulas should be =SUM(A2:D2)
Comment 6 Cor Nouws 2014-10-21 16:09:42 UTC
(In reply to Laurent BP from comment #0)
> Description: part of reference in a formula is not updated after row
> suppression, if new cell location is inside reference
> 
> Step to reproduce:
> 1. Create new spreadsheet
> 2. In H1, insert formula =SUM(C1:G1)
> 3. Suppress columns A through E in one shot (select them, right click,
> delete selected columns)

Just to be clear: with suppressing you mean 'deleting', yes?

(Interesting that I do not have the bug ;)
Comment 7 Cor Nouws 2014-10-21 16:13:37 UTC
(In reply to Laurent BP from comment #5)
> Created attachment 108192 [details]
> Test file: suppress columns A to F, check new formula in E2
> 
> Hope to be a little clear with a test file taken from real life, where I
> detected the problem:
> - select columns A to F
> - suppress them
> formulas in column K move to column C and change from =SUM(E2:J2) to
> =SUM(E2:D2)
> 
> Expected behavior:
> Formulas should be =SUM(A2:D2)


I can reproduce this - but not the initial description.
Comment 8 Laurent BP 2014-10-21 17:17:56 UTC
Sorry Cor, but I can reproduce procedure of comment #0 on Linux with:
- Version: 4.3.2.2 Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d
- Version: 4.2.5.1 Build ID: 881bb88abfe2992c6cede97c23e64a9885de87de

On step 3, I do not delete content, but I remove columns, so formula is moved in column C. Columns are removed altogether. If you suppress columns one by one, then bug does not occur, because references are correctly updated in this case.

I do not understand why you could not reproduce.
Comment 9 Cor Nouws 2014-10-21 18:02:08 UTC
Hi Laurent,

Ah yes, I deleted the columns too ;)
Comment 10 Jacques Guilleron 2014-10-21 18:19:08 UTC
Hi,

In test-supress-column.ods, by selecting colums A-F and Delete Selected Colums giving Sum(Ex:Dx) in Ex (x from 1 to 29).
Bug reproduced with:
LO 4.4.0.0.alpha0+
Build ID: 3e2bd1e4022e25b77bcc8eba5e02c1adc57008a1
TinderBox: Win-x86@39, Branch:master, Time: 2014-10-16_00:50:04
LO 4.3.2.1 Build ID: f9b3ad49d92181b0a1fe7e76f785a2c2cd0847d3
LO 4.3.2.2 Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d
LO 4.2.7.0.0+
Build ID: 92216be6ce13990b8ea6b6264c656d2bc1746401
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-07-14_16:21:42
LO 4.2.1.1 Build ID: d7dbbd7842e6a58b0f521599204e827654e1fb8b

Works as expected 
Sum(Ax:Dx) in colum Ex
with:
LO 4.1.6.2

OS: Windows 7 Home Premium.

Regards,

Jacques
Comment 11 Laurent BP 2014-10-21 19:58:25 UTC
Hi Cor,

You modify the summary in a way it does not correspond to the description made in comment #0. I do not agree because, with my tests, bug occurs with or without formula in deleted columns. 

In procedure describe in comment #5, if you delete content of column D (which is the only column containing formulas in the deleted zone), then delete selected columns A through F, does the bug disappear for you? From my test, the bug occurs even if column D is empty in attachment 108192 [details] and procedure in comment #5
Comment 12 Cor Nouws 2014-10-21 20:03:20 UTC
Apologies Laurent
Comment 13 Cor Nouws 2014-10-21 20:12:22 UTC
(In reply to Laurent BP from comment #11)
 
> In procedure describe in comment #5, if you delete content of column D
> (which is the only column containing formulas in the deleted zone), then
> delete selected columns A through F, does the bug disappear for you? 

Yes.

> From my test, the bug occurs even if column D is empty in attachment 
> 108192 [details] and procedure in comment #5

For me too. Indeed.
Only when I  first delete columns A-D and then E-G (A-C) the formula is OK.
Comment 14 Cor Nouws 2014-10-21 20:36:06 UTC
@ Laurent: hope this summary is ok ;)
Comment 15 Laurent BP 2014-10-21 21:21:46 UTC
(In reply to Cor Nouws from comment #14)
> @ Laurent: hope this summary is ok ;)

+1 for me. I was just afraid of making a so long summary ;)

What I feel (I'm not sure about it), it is that the bug appears if the cell is moved to a place contained by the original reference.
Comment 16 Kohei Yoshida 2014-10-27 13:53:16 UTC
I've fixed this locally.  Now I'm just writing tests before pushing the fix.
Comment 17 Kohei Yoshida 2014-10-27 14:46:14 UTC
Pushed the fix to master, and 4.3 backport to gerrit.

cgit is not coorporating, so no URL to the fix, but the gerrit one is here:

https://gerrit.libreoffice.org/12111
Comment 18 Commit Notification 2014-10-27 14:54:54 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c3c16d5b74fb5707691283538a969a03c2f918b0&h=master

fdo#85282: Correct adjustment of range reference on delete & shift.

It will be available in 4.4.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 2014-10-27 14:57:04 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#85282: Correct adjustment of range reference on delete & shift.

It will be available in 4.4.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 21 Commit Notification 2014-11-04 16:59:23 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

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

fdo#85282: Correct adjustment of range reference on delete & shift.

It will be available in 4.3.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 22 Laurent BP 2014-11-06 12:28:24 UTC
Verified: works as expected with Version: 4.4.0.0.alpha1+
Build ID: 56019dcb79475606952a954fe732a3109441ffec
TinderBox: Win-x86@42, Branch:master, Time: 2014-10-30_07:26:09

Thanks Kohei :)
Comment 23 Laurent BP 2014-11-06 20:46:44 UTC
Verified with:
- Version: 4.3.4.0.0+
Build ID: c244e2e2d64ee88e0e3f44f573247c8ce566e190
TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-11-06_17:40:38

Change status to verified