Bug 127982 - An array formula with SMALL gives blanks, where it used to work as in Excel previously
Summary: An array formula with SMALL gives blanks, where it used to work as in Excel p...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.2.7.1 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.4.0 target:6.3.4
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2019-10-06 09:54 UTC by Mike Kaganski
Modified: 2019-11-01 17:10 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
A sample with failing array formula (18.56 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-10-06 09:54 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2019-10-06 09:54:59 UTC
Created attachment 154776 [details]
A sample with failing array formula

The attachment has array formulas in D6, E6, and J6. They should give the same results; but in 6.3, only the one in J6 works (giving the list of items that constitute an order# given in E2), while the other two gives blanks. It works in Excel 2016, and in 6.1.5.2, bit fails in 6.3.2.2.

Tested to fail with Version: 6.3.2.2 (x64)
Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU threads: 12; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: threaded

and to work with Version: 6.1.5.2 (x64)
Build ID: 90f8dcf33c87b3705e78202e3df5142b201bd805
CPU threads: 12; OS: Windows 10.0; UI render: GL; 
Locale: en-US (ru_RU); Calc: group threaded
Comment 1 Oliver Brinzing 2019-10-06 11:25:16 UTC
reproducible with:

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

Version: 6.4.0.0.alpha0+ (x64)
Build ID: b0b8851021cee26412182b51ed28fe574632a4d6
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

but not *reproducible* with:

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: 

btw: LO 6.1.6.3 (not LO 6.2.7.1/6.3.2.3) *crashes* if one tries to open Function Wizard:
- select: J6 (or D6:D20)
- press Ctrl + F2
- crash
Comment 2 Oliver Brinzing 2019-10-06 11:41:52 UTC
seems to have started with:

https://gerrit.libreoffice.org/plugins/gitiles/core/+/e22ab5e6f6b0ea49231ca454a567133996306116

commit	e22ab5e6f6b0ea49231ca454a567133996306116[log]
author	Takeshi Abe <tabe@fixedpoint.jp>	Tue Oct 30 18:03:10 2018 +0900
committer Eike Rathke <erack@redhat.com>	Thu Nov 15 22:12:01 2018 +0100
tree	449d95b120c58a26ab8e0b589701c325c2579030
parent	973a3dd9623107c18c6765d0b247aa34018a0447 [diff]
Resolves: i#32345 Make LARGE()/SMALL() return an array

... if the second parameter is an array.

This change follows their specification in ODF 1.2.

Change-Id: I45c8923f462e9477e1234b47e39dcdd8d2198784
Reviewed-on: https://gerrit.libreoffice.org/62541
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>

/cygdrive/d/sources/bibisect/bibisect-win32-6.2
$ git bisect good
84f5ddce4b293c2961284547118c58cae5492633 is the first bad commit
commit 84f5ddce4b293c2961284547118c58cae5492633
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Thu Nov 15 16:36:58 2018 -0800

    source sha:e22ab5e6f6b0ea49231ca454a567133996306116

    source sha:e22ab5e6f6b0ea49231ca454a567133996306116

:040000 040000 78bc616af2691d7f6ee5534015b66991b06fffc2 71284b2717a63388f0acc534e17a10e1c5976a28 M      instdir

/cygdrive/d/sources/bibisect/bibisect-win32-6.2
$ git bisect log
# bad: [35a87a66cfc6dfb661f6fed49fb32c081dd26bc7] source sha:d250c94d78ac7e79753aa30f869db919b01fc450
# good: [b0a56ec98b1368cb5e3e531e0b3f69565af91609] source sha:3a801799536e6870f2fb111b1cc00b9575a35a39
git bisect start 'master' 'oldest'
# good: [7f7b05d44d7d7f13cc9c865963a72e555b516b3d] source sha:1b88de0a07180661c4d5d6706247d546d06bc983
git bisect good 7f7b05d44d7d7f13cc9c865963a72e555b516b3d
# good: [1c3155d561cb094926cd19aa514856dfb4e23c5e] source sha:a626bdd56d7116efa57e65403ad51b56657148c3
git bisect good 1c3155d561cb094926cd19aa514856dfb4e23c5e
# bad: [ae0a07b0d72f65d3de33abe942073e21270f85a8] source sha:1d3a07415eda3014d67d7c56466a8ad1d0ec51d9
git bisect bad ae0a07b0d72f65d3de33abe942073e21270f85a8
# good: [607c9fdb05aa8360e2a78db992331f186b43f618] source sha:f644e4be3c8568c34aca1a63d566269ce084eca2
git bisect good 607c9fdb05aa8360e2a78db992331f186b43f618
# bad: [7c5c9afd1d8ac728e068a9fbb5c4a6e64076d47f] source sha:05a8d3ce837f6fe488a099fc599b1fe3090d0b23
git bisect bad 7c5c9afd1d8ac728e068a9fbb5c4a6e64076d47f
# good: [3145005eb786a8011fb44f0294b67e5083579bd8] source sha:a08e5729f052ebef3221a522a816033fa553d9b4
git bisect good 3145005eb786a8011fb44f0294b67e5083579bd8
# bad: [ebf4003233829782c46cff8ecfcf1d1a5189b07b] source sha:a2751c0795cdac9d78f8919aab319a418b6e0bbc
git bisect bad ebf4003233829782c46cff8ecfcf1d1a5189b07b
# good: [1f5b5a6d84f149dcdcce51361b96041afc5183f6] source sha:17cf1b2695e20f5120c308fd59af35e6b83b2732
git bisect good 1f5b5a6d84f149dcdcce51361b96041afc5183f6
# good: [b8a9862734612c23de68f35b5e49b8f9205c005d] source sha:32a8c55c9746f27db6a825372913e639d67c3476
git bisect good b8a9862734612c23de68f35b5e49b8f9205c005d
# good: [077fe64ad3617f09bd5c7ef952cda7a29837ae32] source sha:0d3f1ed35a1f211d459b5da8eba83b404b2d8e31
git bisect good 077fe64ad3617f09bd5c7ef952cda7a29837ae32
# bad: [84f5ddce4b293c2961284547118c58cae5492633] source sha:e22ab5e6f6b0ea49231ca454a567133996306116
git bisect bad 84f5ddce4b293c2961284547118c58cae5492633
# good: [94eecb1ddc570e0f8c253b2d48d415cca763d228] source sha:1d988778095ecbe84f1a1002511377d0708b3443
git bisect good 94eecb1ddc570e0f8c253b2d48d415cca763d228
# good: [fa9bfddba775b7d4fc68e63a169cda079b7dd640] source sha:973a3dd9623107c18c6765d0b247aa34018a0447
git bisect good fa9bfddba775b7d4fc68e63a169cda079b7dd640
# first bad commit: [84f5ddce4b293c2961284547118c58cae5492633] source sha:e22ab5e6f6b0ea49231ca454a567133996306116
Comment 3 m.a.riosv 2019-10-07 21:01:28 UTC
If the array is shortened to D6:D8 (dragging from D20 to D8) it works.
Comment 4 m.a.riosv 2019-10-07 21:18:20 UTC
An array like
{=IFERROR(INDEX($B$2:$B$16;SMALL(IF($E$2=A2:A16;ROW(B2:B16)-1;9999);ROW()-5));"")}
works, so seems the issue is with a string inside the SMALL function.
Doing a test, with a SMALL array if some value it's a string the whole array returns #VALUE
Comment 5 Eike Rathke 2019-10-21 13:04:45 UTC
Investigating.
Comment 6 Commit Notification 2019-10-21 20:14:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/12b54e485103aad4e7dd26423c355b96403b88ba

Related: tdf#127982 In JumpMatrix do not propagate individual errors as global

It will be available in 6.4.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 7 Commit Notification 2019-10-21 23:20:48 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/475165e431b5392e426db0de4cea50efc2513875

Resolves: tdf#127982 SMALL()/LARGE() rank array can be larger than data array

It will be available in 6.4.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 2019-10-21 23:38:57 UTC Comment hidden (obsolete)
Comment 9 Eike Rathke 2019-10-22 17:17:55 UTC
Pending review https://gerrit.libreoffice.org/81346 for 6-3
Comment 10 Takeshi Abe 2019-10-23 04:35:11 UTC
Thank you Eike for spending time to fix a regression from my commit.
Comment 11 Xisco Faulí 2019-10-23 10:20:21 UTC
Verified in

Version: 6.4.0.0.alpha1+
Build ID: 437dc68285dab0f08a1ded2193d86d64f560cd9b
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US
Calc: threaded

@Eike, thanks for fixing this issue!
Comment 12 Eike Rathke 2019-10-24 16:42:26 UTC
(In reply to Takeshi Abe from comment #10)
> Thank you Eike for spending time to fix a regression from my commit.
Well, I didn't see it in the code review back then, so.. ;-)  You're welcome!
Comment 13 Commit Notification 2019-11-01 17:10:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/commit/2d23e19f7991405315bb1ab6a6461cb3e022ca5a

Resolves: tdf#127982 SMALL()/LARGE() rank array can be larger than data array

It will be available in 6.3.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.