Bug 68385 - Cut/paste from different spreadsheets corrupts cell references
Summary: Cut/paste from different spreadsheets corrupts cell references
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: All All
: highest normal
Assignee: Eike Rathke
URL:
Whiteboard: target:4.5.0 target:4.4.0.2 target:4...
Keywords:
Depends on:
Blocks: mab4.3
  Show dependency treegraph
 
Reported: 2013-08-21 15:44 UTC by tmacalp
Modified: 2015-01-04 20:56 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
PDF demonstrating the bug. (227.02 KB, application/pdf)
2013-08-21 15:44 UTC, tmacalp
Details
Another (more detailed) pdf demonstrating the cut/paste bug. (282.61 KB, application/pdf)
2013-08-23 18:24 UTC, tmacalp
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tmacalp 2013-08-21 15:44:17 UTC
Created attachment 84392 [details]
PDF demonstrating the bug.

Cutting and pasting cells from one spreadsheet to another modifies formula references in the "pasted-to" spreadsheet. Formulas referencing cells in the "cut-from" region on the "pasted-to" spreadsheet are modified.

Sorry if this is difficult to describe.  If you cut from file2 a1:a7 and paste in file1 c1:c7, any formulas in file1 that reference file1 a1:a7, will now reference the cells where you actually pasted.

Note that this doesn't happen when you COPY.  It also doesn't happen if you paste the cut contents somewhere else first.

Steps to reproduce:
1. Create a new spreadsheet (spreadsheet1)
2. On spreadsheet1, make each cell in Column B from B1:B20 reference Column A (B1 contains =A1, B2 contains =A2, ... B20 contains =A20)
3. Create another new spreadsheet (spreadsheet2)
4. On spreadsheet2, insert data (ex. a number sequence1 through 7) into A1:A7
5. On spreadsheet2, CUT (don't copy) A1:A7
6. On spreadsheet1, immediately paste contents to A9:A15

Expected results:
Column B will still contain cells referencing each adjacent cell from Column A (B1->A1, B2->A2, etc...)

Actual results:
Column B cells that referenced cells in A1:A7(the cut from region on the other sheet) have now CHANGED to reference the cut-to region on the current sheet.(B1->A9, B2->A10, ... B7->A15, B8->A8)

I can confirm this affects 4.1.0.4 on 32bit MS Windows Vista, Version: 4.1.0.4 on 64bit RHEL6, Version 4.0.4.2 on current 64bit Arch Linux, Version 4.0.2.2 under 32bit Fedora 17, and even back to the oldest version I currently have access to: OpenOffice 3.2.1 under 64bit RHEL6. Since it has been around since before LibreOffice began, I marked it as version 3.3.0.

Since it's been rather difficult for me to describe this bug, I've included a pdf to demonstrate.
Comment 1 ign_christian 2013-08-22 15:58:56 UTC
This behavior looks similar with Bug 51757, perhaps a duplicate.

Found the workaround: after doing those reproducing steps, then undo & paste again. Magically expected results shown.. :)
Comment 2 tmacalp 2013-08-23 18:22:30 UTC
It does look a bit similar to bug 51757 on the surface, but is different.  This is a difficult behavior to describe, so I've created/attached another pdf in an attempt to better describe what's happening.

You're right, there are a number of ways to work around it (use copy instead of paste, paste somewhere else first, paste-undo-paste, drag-and-drop, etc...).  This sneaky bug can and will mysteriously ruin spreadsheets.   Cutting/pasting is not an uncommon operation.  In many cases, users will cause unintentional errors that aren't discovered until months later.

The user that discovered this bug uses calc for charting/listing financial data.  He cut/pasted data in from a generated csv file.  It wasn't until he was about to show these results that he noticed certain formulas throughout his spreadsheet mysteriously changed to incorrect values.  

While there are “work arounds,” this bug will lead to extremely unprofessional and embarrassing mistakes.  I'm sure it has affected quite a few people over the years, but they simply chalked it up to not being careful.
Comment 3 tmacalp 2013-08-23 18:24:05 UTC
Created attachment 84535 [details]
Another (more detailed) pdf demonstrating the cut/paste bug.
Comment 4 crxssi 2013-10-07 14:45:53 UTC
I can confirm this issue.  Marked as "NEW"
Comment 5 Winfried Donkers 2014-12-18 07:53:33 UTC
This bugs corrupts data and is a MAB.

Simple way to reproduce:
-open calc document (I)
-enter 34 in cell A1
-open new calc document (II)
-enter 12 in cel A1
-enter =A1 in cell A2
-select cell A1 from document I and cut
-select cell A1 on sheet 2 of document II and paste
-the result of the formula in cell A2 in sheet 1 has changed

Problem does not occur with copy and paste.
With more complex functions and references this bug severely corrupts the data.

Reproduced on versions
4.1.6, openSUSE13.1
4.3.4.1, Windows7
4.5.0.0.alpha0+, openSUSE13.1

@erack: If you have a hint, I could have a look at it. This bug causes serious problems in out company...
Comment 6 Björn Michaelsen 2014-12-18 10:22:04 UTC
(This is an automated message.)

Setting priority to highest as this is a MAB. This is part of an effort to make the importance of MAB reflected in priority too.
Comment 7 Winfried Donkers 2014-12-18 15:06:11 UTC
(In reply to Winfried Donkers from comment #5)
> Simple way to reproduce:
> -open calc document (I)
> -enter 34 in cell A1
> -open new calc document (II)
> -enter 12 in cel A1
> -enter =A1 in cell A2
> -select cell A1 from document I and cut
> -select cell A1 on sheet 2 of document II and paste
> -the result of the formula in cell A2 in sheet 1 has changed
> 
Extra:
after saving document II, closing the document and reopening cell B1 contains =sheet2.A1
Comment 8 Winfried Donkers 2014-12-18 15:25:52 UTC
(In reply to Winfried Donkers from comment #5)
> Simple way to reproduce:
> -open calc document (I)
> -enter 34 in cell A1
> -open new calc document (II)
> -enter 12 in cel A1
> -enter =A1 in cell A2
> -select cell A1 from document I and cut
> -select cell A1 on sheet 2 of document II and paste
> -the result of the formula in cell A2 in sheet 1 has changed
>

Extra:
Problem does not occur with =$A1 in cell A2 and 
problem does occur with =A$1 in cell A2
Comment 9 Eike Rathke 2014-12-18 18:07:20 UTC
I'm investigating.
Comment 10 Commit Notification 2014-12-18 19:11:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

fdo#68385 update references only if cut originates from the same document

It will be available in 4.5.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 11 Commit Notification 2014-12-18 19:18:21 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

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

fdo#68385 update references only if cut originates from the same document

It will be available in 4.4.0.2.

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 12 Eike Rathke 2014-12-18 19:22:40 UTC
Pending review https://gerrit.libreoffice.org/13529 for 4-3
Comment 13 Winfried Donkers 2014-12-19 06:30:40 UTC
Eike, many thanks for your fast action :-)
Comment 14 tmacalp 2014-12-19 15:37:39 UTC
I tested the nightly and can confirm that it is now fixed.  Thank you for the very fast response.
Comment 15 Commit Notification 2014-12-21 15:09:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

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

fdo#68385 update references only if cut originates from the same document

It will be available in 4.3.6.

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 16 Commit Notification 2015-01-04 20:56:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

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

fdo#68385 update references only if cut originates from the same document

It will be available in 4.2.9.

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.