Download it now!
Bug 131442 - Sorting breaks array formula
Summary: Sorting breaks array formula
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3 all versions
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.0.0 target:6.4.4
Keywords: bibisected, bisected
Depends on:
Blocks:
 
Reported: 2020-03-20 13:30 UTC by Mike Kaganski
Modified: 2020-04-19 19:46 UTC (History)
2 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 Mike Kaganski 2020-03-20 13:30:50 UTC
1. In a new spreadsheet, select A1:A10
2. In formula bar, enter "=RAND()" and press Shift+Ctrl+Enter to create an array formula
3. Sort the range (using sort ascending/descending buttons on toolbar)

The range becomes broken, with most cells showing #REF! and having {=A#REF!}. One of the cells is still showing a number and has correct array formula; trying to edit it shows "You cannot change only part of an array."; Ctrl+/ on it selects new range (e.g., A3:A12), and it's still impossible to edit the formula.

Tested with: Version: 6.4.2.2 (x64)
Build ID: 4e471d8c02c9c90f512f7f9ead8875b57fcb1ec3
CPU threads: 12; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; 
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: CL
Comment 1 Roman Kuznetsov 2020-03-20 13:50:04 UTC
confirm in

Version: 7.0.0.0.alpha0+ (x64)
Build ID: 6388c578c672690fff662cb04b6a0436cd742f37
CPU threads: 4; OS: Windows 10.0 Build 18362; UI render: Skia/Raster; VCL: win; 
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: threaded
Comment 2 Oliver Brinzing 2020-03-21 10:02:21 UTC
also reproducible with:

Version: 6.3.5.2 (x64)
Build-ID: dd0751754f11728f69b42ee2af66670068624673
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: 

Version: 6.2.8.2 (x64)
Build-ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: 

Version: 6.1.6.3 (x64)
Build-ID: 5896ab1714085361c45cf540f76f60673dd96a72
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: 

but *not* reproducible with:

Version: 6.0.7.3 (x64)
Build-ID: dc89aa7a9eabfd848af146d5086077aeed2ae4a5
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc:
Comment 3 Oliver Brinzing 2020-03-21 10:25:24 UTC
seems to have started with:

https://gerrit.libreoffice.org/plugins/gitiles/core/+/2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a

commit	2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a	[log]
author	Eike Rathke <erack@redhat.com>	Tue Jul 10 15:58:17 2018 +0200
committerMarkus Mohrhard <markus.mohrhard@googlemail.com>	Fri Jul 13 22:00:45 2018 +0200
tree	f23c859f0338062a38e773db4f3e126bbdfbd9e1
parent	4e4421325d406cc555f15c2b8b5bbab443eb6a7d [diff]

Resolves: tdf#118624 let RAND() in array/matrix mode fill a matrix

... instead of only top left that is referenced for other
elements.

Change-Id: I718946d7e4327b152e2d9f80712395fd7ab67dee



$ git bisect bad
0e5c8e481472e7ab607efbf60b2a9a0e096c1165 is the first bad commit
commit 0e5c8e481472e7ab607efbf60b2a9a0e096c1165
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Fri Jul 13 13:07:11 2018 -0700

    source sha:2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a

    source sha:2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a

:040000 040000 8d13ca7b7a4be2c45efdba9e0b800616948e8040 a093ae446c6dc0bd3e32ad783c984dcb986f11c2 M      instdir

f0830413@LAPTOP-98M8UIU5 /cygdrive/d/sources/bibisect/bibisect-win32-6.1
$ git bisect log
# bad: [75d131082ce51ed5a898d97bdc2b7a9fe5ddb340] source sha:5b3765f4d881e7ddefd0c4aad6886a46f000b4fc
# good: [29d08f54c2f71ffee4fe12dbb24c5f5cbedecfd2] source sha:6eeac3539ea4cac32d126c5e24141f262eb5a4d9
git bisect start 'master' 'oldest'
# good: [6227e15df9be101688e37cd891817cd858b49e03] source sha:b8b7f8a8f8d97088181d287bb75e74facece16c6
git bisect good 6227e15df9be101688e37cd891817cd858b49e03
# good: [50b236fe0d359b9d5cc9998d2e72009a90a11d08] source sha:b6025e6cffe2024fefebd161ea739188b4b4fdaf
git bisect good 50b236fe0d359b9d5cc9998d2e72009a90a11d08
# good: [d59609a1bfbbb4f924492755719b7d340a51de1b] source sha:eeaf4b0b7ad21da879554bdd93c9a9b97b8268d6
git bisect good d59609a1bfbbb4f924492755719b7d340a51de1b
# bad: [75029ade8fd0bbe5abc530394b85b346b499bc55] source sha:ec79d0127f90d65d722e46688b6cfcf2f5e59794
git bisect bad 75029ade8fd0bbe5abc530394b85b346b499bc55
# good: [615238624584cc719cd84469c8fa9f44f4fb717f] source sha:945cca96e110262d2e33554716692f230215b189
git bisect good 615238624584cc719cd84469c8fa9f44f4fb717f
# good: [3866642d485b7ae20db3223046ab2716c785a900] source sha:8f900385503f3b0b8d045fd6c2256d2e2c78f0d4
git bisect good 3866642d485b7ae20db3223046ab2716c785a900
# bad: [eabc85c658401f6e8953da678a71229e1281713b] source sha:7b62c5266e62c3fb0ce1285949d51020075a3f81
git bisect bad eabc85c658401f6e8953da678a71229e1281713b
# bad: [717ae8ffa0dce230765571c5d91dcee27c3b2419] source sha:10ab80a6fb921e9c84f511a8e1d63ca961fbe412
git bisect bad 717ae8ffa0dce230765571c5d91dcee27c3b2419
# bad: [4871d6f5e9391e53777de216297994d2a64c1800] source sha:8a71cdf34cf55d906dc77ab4509978d45717057a
git bisect bad 4871d6f5e9391e53777de216297994d2a64c1800
# good: [ce3890c4495dba02979e2e07258e6b4dac53cf00] source sha:ff0e7e5427c2851341d01e8917a1b5df0c08e557
git bisect good ce3890c4495dba02979e2e07258e6b4dac53cf00
# bad: [962871bfafe41248f61f0c92dd95be0ffbf6656f] source sha:df2a525de7812bc654d1c4dd27041d7afcf91c7a
git bisect bad 962871bfafe41248f61f0c92dd95be0ffbf6656f
# good: [c4a55844d72bb529a161920fa52cb291c278ecb7] source sha:4e4421325d406cc555f15c2b8b5bbab443eb6a7d
git bisect good c4a55844d72bb529a161920fa52cb291c278ecb7
# bad: [0e5c8e481472e7ab607efbf60b2a9a0e096c1165] source sha:2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a
git bisect bad 0e5c8e481472e7ab607efbf60b2a9a0e096c1165
# first bad commit: [0e5c8e481472e7ab607efbf60b2a9a0e096c1165] source sha:2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a
Comment 4 Eike Rathke 2020-03-27 22:22:56 UTC
This isn't strictly a regression. The difference is that previous to that commit a repeating "vector" of one value was created (i.e. a range of identical values) that is treated differently in array formulas references. The same buggy effect can be triggered with other array formulas such as ={6;4;2;5;3} and then sorted at least already in LO 5.3.7.

IMHO allowing to sort such an array formula is already wrong.
Comment 5 Mike Kaganski 2020-03-28 05:13:05 UTC
(In reply to Eike Rathke from comment #4)
> The same buggy effect can be triggered with other array formulas such as
> ={6;4;2;5;3} and then sorted at least already in LO 5.3.7.
> 
> IMHO allowing to sort such an array formula is already wrong.

Definitely; and in fact, I chose RAND as the easiest formula allowing me to show the behaviour; I didn't think about ={6;4;2;5;3}.

And MS Excel disallows that sorting (with usual "you can't change part of array formula").
Comment 6 Eike Rathke 2020-04-07 17:56:24 UTC
Taking.
Comment 7 Commit Notification 2020-04-07 23:58:09 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b1da67699bd05b26ee11460347ca7077d366c2fc

Resolves: tdf#131442 Sort must not contain matrix formula except 1x1 array

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Eike Rathke 2020-04-07 23:59:33 UTC
Pending review https://gerrit.libreoffice.org/c/core/+/91723 for 6-4
Comment 9 Commit Notification 2020-04-19 19:46:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/3ef9c47a5ed638ed890dd297e3a69dca6080c932

Resolves: tdf#131442 Sort must not contain matrix formula except 1x1 array

It will be available in 6.4.4.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.