Bug 80137 - EDITING: Paste array formula into range pastes as non-array formula
Summary: EDITING: Paste array formula into range pastes as non-array formula
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium minor
Assignee: Not Assigned
Whiteboard: target:4.5.0 target:4.4.2
Keywords: bibisected, bisected, regression
: 86625 89132 (view as bug list)
Depends on:
Reported: 2014-06-17 14:33 UTC by Peter Johnson
Modified: 2016-08-01 17:31 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:

Spreadsheet exhibiting the error described (31.83 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-21 22:16 UTC, Peter Johnson
ODS file demonstrating the state after step 13 of above instructions (24.17 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-20 16:06 UTC, Wolfgang Jäger

Note You need to log in before you can comment on or make changes to this bug.
Description Peter Johnson 2014-06-17 14:33:21 UTC
Pasting an array formula into a single cell works as expected (i.e. array formula is pasted).  Pasting an array into a range pastes only the 'bare' formula without the {} braces.  The value is incorrect.

LibreofficeDev 4.3
Mac OSX 10.9.3
Comment 1 Joel Madero 2014-07-21 17:36:06 UTC
Cannot confirm with:
Ubuntu 14.04 x64
LibreOffice rc

@info@ - please attach a test kit that we can see the exact results that you're seeing. I just kind of guessed at an array to use and it seemed to paste just fine but it's always better if we're using the same test kit that you're seeing the problem.

Setting as NEEDINFO - once you provide a test document please set to UNCONFIRMED. Thanks!
Comment 2 Peter Johnson 2014-07-21 22:16:21 UTC
Created attachment 103239 [details]
Spreadsheet exhibiting the error described

Example of error described.  More info: array formulae pasting error occurs when a single cell is pasted into a column.  Seems OK pasting a cell L to R, and a row of array formulae pastes downwards OK.
Comment 3 Joel Madero 2014-07-22 04:23:35 UTC
Thanks for the sample. Interestingly enough this is a regression:

Ubuntu 14.04 x64

LibreOffice 3.3 - works as expected

LibreOffice - broken as described

New - confirmed
Minor - can slow down professional quality work but not prevent it
Medium - bumped up from low as it's a regression

Bibisect: 0d4c20a601a3cfff27d6685d0e81463086bd9d74 is the first bad commit
commit 0d4c20a601a3cfff27d6685d0e81463086bd9d74
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Sun May 11 05:25:38 2014 +0000

    commit f1b1e73227471192682d303a58618ca8bd65a74d
    Author:     Caolán McNamara <caolanm@redhat.com>
    AuthorDate: Sat Jan 25 20:19:29 2014 +0000
    Commit:     Caolán McNamara <caolanm@redhat.com>
    CommitDate: Sat Jan 25 20:56:30 2014 +0000
        coverity#1158347 Uncaught exception
        Change-Id: Iacaf59724b71d810270545d1e9dd04d59125cc92

:100644 100644 368976e6dbd4cdb43e492ba5eadabe099660a339 70730e767408d2206d17bf278178d2d9909ba37f M	ccache.log
:100644 100644 acdce77d197e69bf89e98f6a3b6423de34b1754b 2e178fe08a1d28d341e79db69400a2cfe0ed6c96 M	commitmsg
:100644 100644 1d0e4ca60cc4187e89af26fcb86e7d165af0bd7b f5beaf27de34aa7963d0b561b320c3ac0246b81e M	make.log
:040000 040000 253f4f4a2f845ca9d4e9765c9d9e96828b22d64d 8871808799c896d3651d27d2b44f5f041296070c M	opt

# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
git bisect good e02439a3d6297a1f5334fa558ddec5ef4212c574
# good: [4850941efe43ae800be5c76e1102ab80ac2c085d] source-hash-980a6e552502f02f12c15bfb1c9f8e6269499f4b
git bisect good 4850941efe43ae800be5c76e1102ab80ac2c085d
# skip: [a900e72b6357882284c5955bdf939bf14269f5fb] source-hash-dd1050b182260a26a1d0ba6d0ef3a6fecc3f4e07
git bisect skip a900e72b6357882284c5955bdf939bf14269f5fb
# skip: [3dda83fc3a43afc6af7f5c0ffd029e610ec1b9a3] source-hash-c59b3d6c5c8096486730007d9b9b053793b90b1e
git bisect skip 3dda83fc3a43afc6af7f5c0ffd029e610ec1b9a3
# bad: [4f705a8cfb1998b09f2062510b207d35a33647d8] source-hash-1eeb20f3958666ec6ba6e0fcf52e92e5eb447a14
git bisect bad 4f705a8cfb1998b09f2062510b207d35a33647d8
# bad: [3c72d6d27e2a0c420f74941355400b0834c550bb] source-hash-c30677731c55688c764a669ecea1b1c4d17ae57d
git bisect bad 3c72d6d27e2a0c420f74941355400b0834c550bb
# skip: [92457a48f3715b1233ea025387627280dae681b0] source-hash-c1503da35d8879366da13258837cf0084a536809
git bisect skip 92457a48f3715b1233ea025387627280dae681b0
# skip: [7055a406c05b6da54733841dd0174c91d35f8652] source-hash-f985e3add3870c1dee7fe88d37388281a333403a
git bisect skip 7055a406c05b6da54733841dd0174c91d35f8652
# bad: [98a55bf95f3ec29298751fd8fba76dd2236dce43] source-hash-58dfc97ca697875c36b7ddf14f5505a93d7b9cf8
git bisect bad 98a55bf95f3ec29298751fd8fba76dd2236dce43
# good: [92ca7e7dd4470107453ce3e99f3675387f91bf24] source-hash-ed5065d8b080bfaf51ea1232cebf3ff72af1e640
git bisect good 92ca7e7dd4470107453ce3e99f3675387f91bf24
# good: [c3997dfb709772c28f4b90559431662e3a81d651] source-hash-d803483f6a5938b0d0708b8db74b30c511dd8e31
git bisect good c3997dfb709772c28f4b90559431662e3a81d651
# good: [5d0e5af3cc4db0c25b97ec65cc5258b07daca350] source-hash-4f3012fc05fa0eeae412d9e2bfca3d7e60914a8c
git bisect good 5d0e5af3cc4db0c25b97ec65cc5258b07daca350
# bad: [0d4c20a601a3cfff27d6685d0e81463086bd9d74] source-hash-f1b1e73227471192682d303a58618ca8bd65a74d
git bisect bad 0d4c20a601a3cfff27d6685d0e81463086bd9d74
# first bad commit: [0d4c20a601a3cfff27d6685d0e81463086bd9d74] source-hash-f1b1e73227471192682d303a58618ca8bd65a74d
Comment 4 Wolfgang Jäger 2014-10-20 16:06:29 UTC
Created attachment 108122 [details]
ODS file demonstrating the state after step 13 of above instructions

It is not an issue of Copy/Paste but an issue of Fill Up/Down.
My description:
[Subject[Array formula: FillUp, FillDown only working for the immediately adjacent cell]]

LibreOffice Calc 3.6 worked correctly so far. 
Versions 4.x contain the error.

Problem description: 
Array formula should fill into as many adjacent cells as wanted by dragging the "little square" Up, Down, Left, Right simultaneously pressing Ctrl. The same for using Ctrl+D or related menu items.
It works correctly for Left, Right.
It works for one row (1 row) per step Up, Down.
Filling Up, Down more than one rows in a single step does not carry the "array formula property".

Steps to reproduce:
1. Create a new spreadsheet
2. Enter the array formula {=COUNTIF(ISBLANK(B11:C11);TRUE())} into A11.
3. Enter numbers or letters into some cells of the range B1:C21
4. 'FillUp' the formula from A11 into A10 dragging with Ctrl.
5. 'FillDown' the formula from A11 into A12 dragging with Ctrl.
6. 'FillDown' the formula from A12 till A13 now dragging with Ctrl.
7. Copy the formula from A11 and paste it into A1.
8. Make sure to note everything was as expected so far.
9. 'FillDown' the formula from A12 till A16 now dragging with Ctrl.
10. 'FillUp' the formula from A10 till A8 now dragging with Ctrl.
11. Make sure to note "Err:504" reported in A8:A9, A13:A16.
12. Delete the formulae in column A and start anew in A11. Repeat steps 4 through 11 with Ctrl+D or the appropriate menu item.
13. Copy A10:A12, select A19 and paste.

Current behavior:
Filling up or down an array formula using a well established method does only work correctly if performed for exactly 1 cell to get the formula. Performed for more than one cells in a single step the formula lose its array-formula property. In cases where the formula is only correct as an array formula we get an error (504 in the above described case). In other cases we may get wrong results.
Using Copy/Paste no errors occur.

Expected behavior:
Correctly copied array formulae returning correct results.
Comment 5 Wolfgang Jäger 2014-10-23 14:23:25 UTC
Bug is very annoying. Unavoidable use of array formulae is likely to occur in a context where 'Fill' 'Down' would apply extensively. My most recent example: Reorganising a database-like data collections: extracting information from one to many cells. 
Resolving should be not too expensive as filling to the Left / Right is not broken.
This is a vote for higher 'Importance'.
Comment 6 GerardF 2014-11-23 08:34:29 UTC
There is 3 ways to copy down array formula:
● Copy/Paste
● Ctrl + D
● Drag/drop with Ctrl

All 3 are affected by the bug, so "importance" set to Minor seems low to me.
Unless one provide an easy workarround...

The only one I can found is:
Copy 1st cell into 1 cell below,
select both cells and copy into following 2 cells,
select 4....etc
range extand by power of 2 at each step.
Comment 7 GerardF 2014-11-24 07:39:34 UTC
*** Bug 86625 has been marked as a duplicate of this bug. ***
Comment 8 Alex Thurgood 2015-01-03 17:41:12 UTC
Adding self to CC if not already on
Comment 9 Matthew Francis 2015-01-06 02:11:30 UTC
The behaviour seems to have changed as of the below commit.

commit 80ea2822c368e6655d216c32904268fc335ef6a4
Author: Kohei Yoshida <kohei.yoshida@collabora.com>
Date:   Fri Jan 24 11:38:47 2014 -0500

    Optimize copy-n-pasting of single cell replicated over a range.
    This is a very common operation. Let's speed it up a bit.
    TODO : Handle cell comments.
    Change-Id: I5442bf19008475a9427b889c1eb641cacd8ff161
Comment 10 Wolfgang Jäger 2015-01-11 13:41:35 UTC
Appreciate recent comments!

In addition:
Fixing this bug should be a good opportunity to change the behaviour of array formulae on a 'Fill by DragMouse' procedure. Also before this functionality broke it was bound to pressing Ctrl in addition to the mouse movement. This lacked the rationale. A 'Fill Down but drop ArrayCase while doing this' cannot plausibly be seen as the 'Standard' behaviour. 

+Ctrl on filling with DragMouse is shaped in many user's mind by its usage filling in numbers (mumerical suffixes). The analogous effect when filling formulae should be 'Dont adapt relative references'.

This concept of consequent behaviour might be also extended to the 'Fill' procedures called the menu way: 'Edit' > 'Fill' > select direction with or without Ctrl.
Comment 11 Joel Madero 2015-01-11 16:55:04 UTC
Please do not add additional requests/bugs to a bug report. One bug report should be equal to one bug.
Comment 12 Commit Notification 2015-03-09 09:55:02 UTC
Henry Castro committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":


Resolves tdf#80137 Paste array formula into range pastes as non-array formula

It will be available in 4.4.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:
Affected users are encouraged to test the fix and report feedback.
Comment 13 Matthew Francis 2015-03-17 09:54:57 UTC
*** Bug 89132 has been marked as a duplicate of this bug. ***
Comment 14 Robinson Tryon (qubit) 2015-12-17 08:23:50 UTC Comment hidden (obsolete)