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
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
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:
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 2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a source 2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a :040000 040000 8d13ca7b7a4be2c45efdba9e0b800616948e8040 a093ae446c6dc0bd3e32ad783c984dcb986f11c2 M instdir f0830413@LAPTOP-98M8UIU5 /cygdrive/d/sources/bibisect/bibisect-win32-6.1 $ git bisect log # bad: [75d131082ce51ed5a898d97bdc2b7a9fe5ddb340] source 5b3765f4d881e7ddefd0c4aad6886a46f000b4fc # good: [29d08f54c2f71ffee4fe12dbb24c5f5cbedecfd2] source 6eeac3539ea4cac32d126c5e24141f262eb5a4d9 git bisect start 'master' 'oldest' # good: [6227e15df9be101688e37cd891817cd858b49e03] source b8b7f8a8f8d97088181d287bb75e74facece16c6 git bisect good 6227e15df9be101688e37cd891817cd858b49e03 # good: [50b236fe0d359b9d5cc9998d2e72009a90a11d08] source b6025e6cffe2024fefebd161ea739188b4b4fdaf git bisect good 50b236fe0d359b9d5cc9998d2e72009a90a11d08 # good: [d59609a1bfbbb4f924492755719b7d340a51de1b] source eeaf4b0b7ad21da879554bdd93c9a9b97b8268d6 git bisect good d59609a1bfbbb4f924492755719b7d340a51de1b # bad: [75029ade8fd0bbe5abc530394b85b346b499bc55] source ec79d0127f90d65d722e46688b6cfcf2f5e59794 git bisect bad 75029ade8fd0bbe5abc530394b85b346b499bc55 # good: [615238624584cc719cd84469c8fa9f44f4fb717f] source 945cca96e110262d2e33554716692f230215b189 git bisect good 615238624584cc719cd84469c8fa9f44f4fb717f # good: [3866642d485b7ae20db3223046ab2716c785a900] source 8f900385503f3b0b8d045fd6c2256d2e2c78f0d4 git bisect good 3866642d485b7ae20db3223046ab2716c785a900 # bad: [eabc85c658401f6e8953da678a71229e1281713b] source 7b62c5266e62c3fb0ce1285949d51020075a3f81 git bisect bad eabc85c658401f6e8953da678a71229e1281713b # bad: [717ae8ffa0dce230765571c5d91dcee27c3b2419] source 10ab80a6fb921e9c84f511a8e1d63ca961fbe412 git bisect bad 717ae8ffa0dce230765571c5d91dcee27c3b2419 # bad: [4871d6f5e9391e53777de216297994d2a64c1800] source 8a71cdf34cf55d906dc77ab4509978d45717057a git bisect bad 4871d6f5e9391e53777de216297994d2a64c1800 # good: [ce3890c4495dba02979e2e07258e6b4dac53cf00] source ff0e7e5427c2851341d01e8917a1b5df0c08e557 git bisect good ce3890c4495dba02979e2e07258e6b4dac53cf00 # bad: [962871bfafe41248f61f0c92dd95be0ffbf6656f] source df2a525de7812bc654d1c4dd27041d7afcf91c7a git bisect bad 962871bfafe41248f61f0c92dd95be0ffbf6656f # good: [c4a55844d72bb529a161920fa52cb291c278ecb7] source 4e4421325d406cc555f15c2b8b5bbab443eb6a7d git bisect good c4a55844d72bb529a161920fa52cb291c278ecb7 # bad: [0e5c8e481472e7ab607efbf60b2a9a0e096c1165] source 2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a git bisect bad 0e5c8e481472e7ab607efbf60b2a9a0e096c1165 # first bad commit: [0e5c8e481472e7ab607efbf60b2a9a0e096c1165] source 2dc7a3b7515ffd6181d740aca4ad6e0549ea4a3a
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.
(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").
Taking.
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.
Pending review https://gerrit.libreoffice.org/c/core/+/91723 for 6-4
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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/6d82ea2bd47af3ea94054a9dc57a590f16483369 tdf#131442: sc_uicalc: Add unittest It will be available in 7.2.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.