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
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
(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?
(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?
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
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)
(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 ;)
(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.
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.
Hi Laurent, Ah yes, I deleted the columns too ;)
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
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
Apologies Laurent
(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.
@ Laurent: hope this summary is ok ;)
(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.
I've fixed this locally. Now I'm just writing tests before pushing the fix.
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
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.
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.
Pushed to 4-3 http://cgit.freedesktop.org/libreoffice/core/commit/?h=libreoffice-4-3&id=bc38612d349b9efd877dec23f3e68a3f7b945cec
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.
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 :)
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