Bug 44383 - Copy plus paste special with shift cells updates formulas after cells are shifted (per Comment 9 and 10)
Summary: Copy plus paste special with shift cells updates formulas after cells are shi...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL: https://help.libreoffice.org/6.4/en-U...
Whiteboard:
Keywords: needsUXEval
Depends on:
Blocks: Cell-Formula Clipboard
  Show dependency treegraph
 
Reported: 2012-01-02 04:33 UTC by Winfried Donkers
Modified: 2019-07-25 12:23 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
document to illustrate the described problem (9.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-01-02 04:33 UTC, Winfried Donkers
Details
document to illustrate the described problem (9.44 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-01-03 03:36 UTC, Winfried Donkers
Details
sample document 2 (7.69 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-06-26 05:57 UTC, Winfried Donkers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Winfried Donkers 2012-01-02 04:33:17 UTC
Created attachment 55043 [details]
document to illustrate the described problem

This bug is a bit difficult to show because of bug 42265, but can be reproduced by using a macro that bypasses the problem of bug 42265.

-use the simple document that I have attached;
-select columns D,E,F;
-cut;
-select cell A1;
-use paste special with 'move cells to right' or use following basic macro with the following code to bypass the disabled option problem:
  sub insCol
    dim document   as object
    dim dispatcher as object

    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

    dim args2(5) as new com.sun.star.beans.PropertyValue
    args2(0).Name = "Flags"
    args2(0).Value = "A"
    args2(1).Name = "FormulaCommand"
    args2(1).Value = 0
    args2(2).Name = "SkipEmptyCells"
    args2(2).Value = false
    args2(3).Name = "Transpose"
    args2(3).Value = false
    args2(4).Name = "AsLink"
    args2(4).Value = false
    args2(5).Name = "MoveMode"
    args2(5).Value = 1

    dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args2())
  end sub  'insCol
-note that the formulas that were in C1 and C2 and are now in F1 and F2 no longer produce the sum of the two cells to their left.

Bug is also present in master, and occurs with Windows and with Linux.
I suspect a connection with bugs 42261 and 27987 as these also report problems after inserting/delting/moving columns/rows.
Comment 1 GerardF 2012-01-02 12:11:53 UTC
Reproductible using your macro with both LibO 3.4.4 and 3.5.0 beta2 running on windows vista.

But not reproduce using shorcuts :
1. Select column D E & F
2. Cursor in one cell of those columns
3. Drag in the first column with mouse
4. Press Alt key before drop.

This way, I have expected result with both versions.
Comment 2 Winfried Donkers 2012-01-02 22:53:31 UTC
(In reply to comment #1)
> Reproductible using your macro with both LibO 3.4.4 and 3.5.0 beta2 running on
> windows vista.
> 
> But not reproduce using shorcuts :
> 1. Select column D E & F
> 2. Cursor in one cell of those columns
> 3. Drag in the first column with mouse
> 4. Press Alt key before drop.
> 
> This way, I have expected result with both versions.

I didn't know this shortcut, it provides an alternative solution as long as the bug itself isn't solved.
Unfortunately, bugs 42261 and 27987 still occur with this shortcut.
Comment 3 Winfried Donkers 2012-01-02 22:57:49 UTC
I made a typing error: for 
 bugs 42261 and 27987
read
 bug 42261 and bug 37987
I'm sorry...
Comment 4 Winfried Donkers 2012-01-03 03:36:46 UTC
Created attachment 55074 [details]
document to illustrate the described problem

When using shortcut Alt, another, related, problem shows.
Use tabsheet 2
-select row E;
-drag the row to cell C1 with the mouse, keep mouse button pressed down;
-press _right_ Alt-key (or Ctrl-key) and release mouse button;
-column is copied, formula in pasted column reports error.

When doing this with the _left_ Alt-key the column is moved and no error occurs in the formula.

version 3.4.4, Windows XP.

on master (code updated yesterday) and openSUSE 12.1 the behaviour with Ctrl-key is the same. With right Alt-key the behaviour is identical with left Alt-key (i.e. move).
Comment 5 Eike Rathke 2012-01-04 11:15:27 UTC
Well, there's a reason that "shift cells right" is disabled on source rows once you have cut the range, it messes around with positions that aren't part of the document anymore but in the clipboard instead. Tricking that into bypassing the disabled option is just not nice ;-)

So we'll need to respect the proper state when evaluating the API call and make it fail.
Comment 6 Winfried Donkers 2012-01-04 22:37:13 UTC
(In reply to comment #5)
Given that there is a reason, the use of dragging in combination with right Alt-key should be disabled too. Also, why is the macro (uno:InsertContents) allowed then?

But it may be that there is a misunderstanding:
IMHO when moving columns, shift cells right should be enabled and shift cells down disabled
and when moving rows vice versa.
Comment 7 Winfried Donkers 2013-01-09 06:38:31 UTC
(In reply to comment #4)
> Created attachment 55074 [details]
> document to illustrate the described problem
> 
> When using shortcut Alt, another, related, problem shows.
> Use tabsheet 2
> -select row E;
> -drag the row to cell C1 with the mouse, keep mouse button pressed down;
> -press _right_ Alt-key (or Ctrl-key) and release mouse button;
> -column is copied, formula in pasted column reports error.
> 
> When doing this with the _left_ Alt-key the column is moved and no error
> occurs in the formula.
> 
> version 3.4.4, Windows XP.
> 
> on master (code updated yesterday) and openSUSE 12.1 the behaviour with
> Ctrl-key is the same. With right Alt-key the behaviour is identical with
> left Alt-key (i.e. move).

Alt-problem still reproducible with version 3.6.4.3, Windows XP (left Alt is OK, right Alt is not), but not with version 3.5, openSUSE12.2.
Ctrl-problem stiil reproducible on both Windows XP and openSUSE 12.2.
Comment 8 Winfried Donkers 2013-02-12 15:20:35 UTC
(In reply to comment #5)
> Well, there's a reason that "shift cells right" is disabled on source rows
> once you have cut the range, it messes around with positions that aren't
> part of the document anymore but in the clipboard instead. Tricking that
> into bypassing the disabled option is just not nice ;-)
> 
> So we'll need to respect the proper state when evaluating the API call and
> make it fail.

Now that the shift options in the paste special dialog are disabled/enabled as meant to (i.e. disable when source and destination area intersect), I did some additional tests with the sample document. I used copy this time to avoid messing around with positions that aren't part of the document anymore (but only in the clipboard).

Sheet1:
1. select D1:F4
2. (Ctrl+C) copy
3. select A1
4. Ctrl+Shift+V, paste special dialog opens
5. select shift right and paste
6. cells in row 1 have expected values, but I expected cells C2 and I2 to have the same value (6), which they have not. The references are absolute, yet cell I2 has its references shifted and cell C2 has not.

Test done with version 4.1alpha+ on openSUSE12.2
Comment 9 Winfried Donkers 2013-06-26 05:57:00 UTC
Created attachment 81443 [details]
sample document 2

Attached document can be used to demonstrate the problem.

1. open document
2. select range A1:A2
2. copy (Ctrl+C)
3. select cell C1
4. open paste special dialog (Shift+Ctrl+V)
5. select shift right
6. paste
7. expected behaviour: at least one of the pasted cells (C1:C2) should still have the same result as in the cells (A3:A4) which contain sthe same formulas as in (A1:A2).
   actual behaviour: both pasted cells have now changed results.

This means that there is currently no way to copy & shift+paste without breaking formulas containing references to shifted cells.

Probably the copied (but not yet pasted) cell's contents are not updated when the shift takes place so that the pasted cells have incorrect references in the formulas.
Comment 10 Eike Rathke 2013-06-26 17:29:57 UTC
> 7. expected behaviour: at least one of the pasted cells (C1:C2) should still
> have the same result as in the cells (A3:A4) which contain sthe same
> formulas as in (A1:A2).

No. When copy&pasting formulas references are never adapted and relative
references always point to the same relative offset as the original,
which here for A1 (=D1) is 3 columns to the right, maintained after
pasting into C1 (=F1), and absolute references don't change at all
(A2:=$D2 stays C2:=$D2). This is the same for normal copy&paste without
shifting cells. What happens internally is that the cells are shifted
before the content is pasted (which is logical because otherwise to be
pasted content could overwrite to be shifted content before that is
shifted).

It seems regarding reference handling you expect:

a) when shifting cells the references are updated in the clipboard as if
   they were already inserted in the sheet, with all so far usual
   reference updating, which means after paste the result will be C1:=G1
   and C2:=$E2

Another but illogical handling would be

b) references are treated as if the content was pasted before the cells
   are shifted, but if that was done then the shift would include the
   already pasted cells and they would end up in column D instead of
   column C; I don't think this was meant.

For a) we'd need a different implementation of clipboard and reference
update handling for the copy&paste+shift case.
Comment 11 Winfried Donkers 2013-07-03 05:40:57 UTC Comment hidden (obsolete)
Comment 12 Winfried Donkers 2013-07-04 08:43:12 UTC Comment hidden (obsolete)
Comment 13 QA Administrators 2015-04-01 14:40:41 UTC Comment hidden (obsolete)
Comment 14 Winfried Donkers 2015-04-02 06:05:31 UTC Comment hidden (obsolete)
Comment 15 tommy27 2016-04-16 07:23:20 UTC Comment hidden (obsolete)
Comment 16 Winfried Donkers 2016-04-18 06:19:26 UTC Comment hidden (obsolete)
Comment 17 QA Administrators 2017-05-22 13:23:11 UTC Comment hidden (obsolete)
Comment 18 Winfried Donkers 2017-05-24 06:12:24 UTC Comment hidden (obsolete)
Comment 19 QA Administrators 2018-07-01 02:42:21 UTC Comment hidden (obsolete)
Comment 20 Winfried Donkers 2018-07-02 06:09:21 UTC Comment hidden (obsolete)
Comment 21 QA Administrators 2019-07-03 02:42:41 UTC Comment hidden (obsolete)
Comment 22 Winfried Donkers 2019-07-03 06:43:14 UTC
(In reply to QA Administrators from comment #21)

LO version 6.2.4.2-64bit on Windows 10: problem as described in description and comment 9 is still present.
Comment 23 Timur 2019-07-03 10:16:02 UTC
Question here is whether this is a bug and should behavior that has some logic be changed. 
In cases like this one, I'm not in favor of changing behavior, but I'd rather see an option to choose behavior. So this should IMHO be an enhancement. 
We need UX decision.
Comment 24 Heiko Tietze 2019-07-22 13:27:17 UTC
(In reply to Winfried Donkers from comment #22)
> LO version 6.2.4.2-64bit on Windows 10: problem as described in description
> and comment 9 is still present.

Eike replied in c10. When I shift-paste the reference is updated to F, which makes sense. Shift first (and update references), paste after. So WFM, in my opinion.
Comment 25 Heiko Tietze 2019-07-25 12:23:50 UTC
No further input, so closing as WFM.