Bug 104452 - "Cut, add row, paste" doesn't update references
Summary: "Cut, add row, paste" doesn't update references
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All Linux (All)
: medium minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Paste Cell-Reference
  Show dependency treegraph
 
Reported: 2016-12-06 22:26 UTC by libreoffice
Modified: 2021-05-28 18:05 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description libreoffice 2016-12-06 22:26:36 UTC
Description:
When you cut a cell and insert a row in the sheet before pasting the cell, references are not updated and resulting formula is invalid

Steps to Reproduce:
1. Create A1→"100", G10→"=A1*2"
2. Verify that G10 gives 200
3. Cut G10
4. Insert row below line 1
5. Paste into D20

Actual Results:  
D20 → "=#REF!11"

Expected Results:
D20 → "=A1*2"


Reproducible: Always

User Profile Reset: No

Additional Info:
Works well if cut cell is B2 (strange).


User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:50.0) Gecko/20100101 Firefox/50.0
Comment 1 Buovjaga 2016-12-10 20:41:30 UTC
My formula in D20: =A11*2

Arch Linux 64-bit, KDE Plasma 5
Version: 5.4.0.0.alpha0+
Build ID: 1fce5b024e9f25c3fcef2537a22474ece0dc416f
CPU Threads: 8; OS Version: Linux 4.8; UI Render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on December 10th 2016

Arch Linux 64-bit
LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4
Comment 2 QA Administrators 2018-07-10 02:37:21 UTC Comment hidden (obsolete)
Comment 3 Ascaf 2018-07-10 22:41:58 UTC
Versione: 6.1.0.1
Build ID: 378e26bd4f22a135cef5fa17afd5d4171d8da21a
Thread CPU: 2; SO: Linux 3.16; Resa interfaccia: predefinito; VCL: kde4; 
Versione locale: it-IT (it_IT.UTF-8); Calc: group threaded
(https://gerrit.libreoffice.org/gitweb?p=core.git&a=log&h=378e26bd4f22a135cef5fa17afd5d4171d8da21a)
 Default settings (clean config)

I confirm I can reproduce this bug.
But, isn't it the normal behaviour of Calc that, when you copy a cell, referring to another cell, the references got updated accordingly and, if you paste the cell too far to the left (or above) the pasted cell, you end up pointing to a non-existent cell?
I.e. for example in case 1, D20 should finally be pointing to {A-6position to the left}{1+10position to the bottom} that is  #REF!11
and, for case 2, if I have 1 in A1 and =A1 in B2 if I copy-paste B2 in B1 I got =#RIF!1 (as I would expect); if I copy-paste B2 in D20 I got 0 (i.e.: =C19)...
Should it be normal (as I thought) or should default behaviour be different?
Comment 4 Buovjaga 2018-07-11 06:10:17 UTC
Hmm, it seems Ascaf is right and actually I get the *same* result now without any row insertions! That is, =#REF!20*2 after pasting in D20. A similar result is indeed seen in any paste to the left of G column.

Let's close.
Comment 5 Octavio Alvarez 2018-07-11 13:24:44 UTC
> But, isn't it the normal behaviour of Calc that, when you copy a cell,

Yes, but the report is not about copying. It is about cutting.

So, this is the formula in D20 after the following procedures:

A. Cutting G10, no row insert, paste in D20: =A1*2

B. Cutting G10, row 2 insert, paste in D20: =#REF!11*2

C. Copying G10, no row insert, pate in D20: =#REF!11*2

D. Copying G10, row 2 insert, paste in D20: =#REF!11*2

Cases C and D are about copying and are consistent. Cases A and B are about cutting and are inconsistent.

In case B, "cut" is adopting "copy" behavior.

I think this is the intent of the original bug report.
Comment 6 Buovjaga 2018-07-11 13:38:48 UTC
Indeed, sorry for sloppy testing back there.
Comment 7 Ascaf 2018-07-12 01:03:15 UTC
Thanks for the explanation. So I thought it might be interesting to check the clipboard while doing the usual sequences and to see if the inconsistencies were triggered by action different from adding row.
In particular I got this (below, with klipper I mean the values stacked in the clipboard, newest to the right):

IN new spreadsheet: A1 --> 100
g20 --> =A1*2 (200)
g20 --> ctrl-x
klipper: 200 (the value on the clipboard stack)
d20 --> ctrl-v
now d20 is 200 (=A1*2)
klipper: 200 (unchanged)
d20 --> ctrl-v (accept overwriting)
now d20 is #RIF! (=#RIF!11*2 like if cutting-pasting)
klipper: 200, #RIF! (the same value you'll get if you copy-paste instead of cutting-pasting)
keep on overwriting (if you want) but the value will not change any more)

for comparison with copy-paste
IN new spreadsheet: A1 --> 100
g20 --> =A1*2 (200)
g20 --> ctrl-c
klipper: 200
d20 --> ctrl-v
now d20 is #RIF! (=#RIF!11*2)
klipper: 200, #RIF! (the value pasted has been updated, in accordance with the internal rules, I suppose)

So it seams the simple action of pasting twice triggered some formula updating to the clipboard?
Comment 8 Ascaf 2018-07-12 01:09:21 UTC
(In reply to Ascaf from comment #7)
> Thanks for the explanation. So I thought it might be interesting to check
> the clipboard while doing the usual sequences and to see if the
> inconsistencies were triggered by action different from adding row.
> In particular I got this (below, with klipper I mean the values stacked in
> the clipboard, newest to the right):
> 
> IN new spreadsheet: A1 --> 100
> g20 --> =A1*2 (200)
> g20 --> ctrl-x
> klipper: 200 (the value on the clipboard stack)
> d20 --> ctrl-v
> now d20 is 200 (=A1*2)
> klipper: 200 (unchanged)
> d20 --> ctrl-v (accept overwriting)
> now d20 is #RIF! (=#RIF!11*2 like if cutting-pasting)
> klipper: 200, #RIF! (the same value you'll get if you copy-paste instead of
> cutting-pasting)
> keep on overwriting (if you want) but the value will not change any more)
> 
> for comparison with copy-paste
> IN new spreadsheet: A1 --> 100
> g20 --> =A1*2 (200)
> g20 --> ctrl-c
> klipper: 200
> d20 --> ctrl-v
> now d20 is #RIF! (=#RIF!11*2)
> klipper: 200, #RIF! (the value pasted has been updated, in accordance with
> the internal rules, I suppose)
> 
> So it seams the simple action of pasting twice triggered some formula
> updating to the clipboard?

Sorry above read g10 instead of g20:

IN new spreadsheet: A1 --> 100
g10 --> =A1*2 (200)
g10 --> ctrl-x
klipper: 200 (the value on the clipboard stack)
d20 --> ctrl-v
now d20 is 200 (=A1*2)
klipper: 200 (unchanged)
d20 --> ctrl-v (accept overwriting)
now d20 is #RIF! (=#RIF!11*2 like if cutting-pasting)
klipper: 200, #RIF! (the same value you'll get if you copy-paste instead of cutting-pasting)
keep on overwriting (if you want) but the value will not change any more)

for comparison with copy-paste
IN new spreadsheet: A1 --> 100
g10 --> =A1*2 (200)
g10 --> ctrl-c
klipper: 200
d20 --> ctrl-v
now d20 is #RIF! (=#RIF!11*2)
klipper: 200, #RIF! (the value pasted has been updated, in accordance with the internal rules, I suppose)
Comment 9 QA Administrators 2019-09-18 02:52:16 UTC Comment hidden (obsolete)
Comment 10 Roland Kurmann 2021-05-28 18:05:01 UTC
Repro

Version: 7.2.0.0.alpha1+ / LibreOffice Community
Build ID: 98a8d8bc15018576e1312493425d7bb3725be735
CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: x11
Locale: de-CH (en_US.UTF-8); UI: en-US
Calc: threaded