Bug 102525 - F4 breaks array formula
Summary: F4 breaks array formula
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: target:5.4.0 target:5.3.4
Keywords:
Depends on:
Blocks:
 
Reported: 2016-09-26 16:36 UTC by petrelharp
Modified: 2017-04-27 20:22 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Screenshot of pre-bug state. (111.77 KB, image/png)
2016-10-11 21:18 UTC, petrelharp
Details
Test case. Select B1:B4; hit F4; observe bug. (7.58 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-10-11 21:18 UTC, petrelharp
Details

Note You need to log in before you can comment on or make changes to this bug.
Description petrelharp 2016-09-26 16:36:30 UTC
Using the F4 shortcut to change absolute/relative referencing breaks array formulae, at least for me with version 5.2.0.4, build id 1:5.2.0-2, installed with apt-get on debian sid.

To reproduce:

1. Enter numbers 1,2,3,4 in cells A1:A4
2. Type '=IF(A27:A30>2,1,2)' and do ctrl-shift-enter in B1.
- this produces {2,2,1,1} in column B, as expected.
3. Select B1:B4 and hit F4.

Expected result: values don't change but $'s appear in the array formula.

Actual result: values change to Err:512, because the formula are (a) no longer an array formula, but instead separate formulae, and (b) now read:
    B1: ={=IF($A$27:$A$30>2,1,2)}
 B2:B4: ={=IF(A$27:A$30>2,1,2)}

best, peter.
Comment 1 Buovjaga 2016-10-11 18:25:42 UTC
(In reply to petrelharp from comment #0)
> 2. Type '=IF(A27:A30>2,1,2)' and do ctrl-shift-enter in B1.
> - this produces {2,2,1,1} in column B, as expected.

This produces Err:509 for me.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.3.0.0.alpha0+
Build ID: 65f2d6b1cc40b4b90f8987e8ea14d24b5f38f950
CPU Threads: 8; OS Version: Linux 4.7; UI Render: default; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on October 10th 2016
Comment 2 petrelharp 2016-10-11 18:38:12 UTC
Sorry, a mistake in step 2 in copying over the code.  Where I say A27:A30 it should be A1:A4.  It should read:

"
To reproduce:

1. Enter numbers 1,2,3,4 in cells A1:A4
2. Type '=IF(A1:A4>2,1,2)' and do ctrl-shift-enter in B1.
- this produces {2,2,1,1} in column B, as expected.
3. Select B1:B4 and hit F4.


Expected result: values don't change but $'s appear in the array formula.

Actual result: values change to Err:512, because the formula are (a) no longer an array formula, but instead separate formulae, and (b) now read:
    B1: ={=IF($A$1:$A$4>2,1,2)}
 B2:B4: ={=IF(A$1:A$4>2,1,2)}

"
Comment 3 Buovjaga 2016-10-11 18:47:50 UTC
(In reply to petrelharp from comment #2)
> 2. Type '=IF(A1:A4>2,1,2)' and do ctrl-shift-enter in B1.
> - this produces {2,2,1,1} in column B, as expected.

Well, I still get Err:509
Comment 4 petrelharp 2016-10-11 21:16:44 UTC
That's interesting.  What you are observing is a much worse bug, then.

Hm: the computer I'm on currently has version 5.2.2, contrary to what the version was listed as previously on this bug (5.2.0).  Maybe my other machine has 5.2.0; not sure.  Could that explain the discrepancy?

Attached is a screenshot of steps 1 & 2 working on my computer.  

Also attached is an .ods file of the bug: column B shows steps 1 & 2 working; column 3 is the result after selecting column B and using F4 to make the references absolute instead of relative.

To verify the bug with the attached sheet:
  1) select B1:B4
  2) Hit F4.
Desired behavior:
  - values should remain unchanged.
Actual behavior:
  - column turns into Err:512, just like column C.
Comment 5 petrelharp 2016-10-11 21:18:00 UTC
Created attachment 127954 [details]
Screenshot of pre-bug state.

As Buovjaga couldn't replicate the pre-bug state.
Comment 6 petrelharp 2016-10-11 21:18:51 UTC
Created attachment 127955 [details]
Test case. Select B1:B4; hit F4; observe bug.
Comment 7 Buovjaga 2016-10-12 04:27:53 UTC
You did refer to build id 1:5.2.0-2 in your description, so let's set the version back.
Comment 8 petrelharp 2016-10-12 05:02:03 UTC
Confirmed: same thing happens on both 5.2.0 and 5.2.2.
Comment 9 Jacques Guilleron 2016-11-28 14:12:11 UTC
Hi petrelharp, Buovjaga,

Reproduced from Comment 4 with same results Err:512 under Windows 7 Home and
LO 5.3.0.0.alpha1 Build ID: f4ca1573fcf445164c068c1046ab5d084e1b005f
Threads CPU : 2; Version de l'OS :Windows 6.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: CL
and 
LO LibreOffice 3.5.3.2 Version ID : 235ab8a-3802056-4a8fed3-2d66ea8-e241b80

So probably inherited from Ooo.
Found no such previous bug.

Have a nice day,

Jacques
Comment 10 Commit Notification 2017-04-19 09:19:10 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#102525 handle array/matrix formula in cycle cell reference types

It will be available in 5.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.
Comment 11 Eike Rathke 2017-04-19 09:30:11 UTC
Pending review https://gerrit.libreoffice.org/36669 for 5-3
Comment 12 Commit Notification 2017-04-19 10:47:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

keep the leading '=' equal character, tdf#102525 follow-up

It will be available in 5.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.
Comment 13 Jacques Guilleron 2017-04-20 14:51:59 UTC
Works as expected (absolute or relative references) with 
LO 5.4.0.0.alpha0+ Build ID: 100e00fd3273536ebff130cfddc5da2b1fe609b1
CPU threads: 2; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2017-04-20_05:12:24
Locale: en-US (fr_FR); Calc: CL

Thank you very much, Eike.
Comment 14 Commit Notification 2017-04-27 20:22:46 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

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

Resolves: tdf#102525 handle array/matrix formula in cycle cell reference types

It will be available in 5.3.4.

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.