Bug 74558 - Defect with the VLOOKUP function
Summary: Defect with the VLOOKUP function
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: x86 (IA32) Linux (All)
: high major
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.2
Keywords: regression
: 72348 74777 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-02-05 10:59 UTC by Richard Palmer
Modified: 2014-02-27 15:57 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
ods file that shows the reported problem (88.10 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-05 10:59 UTC, Richard Palmer
Details
Another example showing VLOOKUP error (91.70 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-09 09:30 UTC, Josir Gomes
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Richard Palmer 2014-02-05 10:59:44 UTC
Created attachment 93439 [details]
ods file that shows the reported problem

I was editing a spreadsheet created with previous versions of LibreOffice and modified with the previous version 4.1.4.2. The first sheet of the spreadsheet contains a summary table, of which cells are filled using the VLOOKUP function.

Attempts to edit cells within the VLOOKUP range referenced by the function cause the entries in cells N614:N645 to display #N/A. The VLOOKUP function in cells N646 and below are still good but any attempts to restore the correct answer to the cells above, for example by pasting the formula (apparently identical) into the cells N614:N645, does not solve the problem.

If I take the defective spreadsheet and open it in another machine running version 4.1.4.2., the problem is quickly resolved. A test file that showed this defect is attached.
Comment 1 m.a.riosv 2014-02-05 22:55:13 UTC
Hi Richard, thanks for reporting.

Reproducible with:
Win7x64Ultimate.
Version: 4.2.0.4 Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71

I can not find any set up in M/T/O/Lc Calculation or Formula that change the behaviour.

As workaround, VLOOKUP() seems to works fine adding the fourth parameter with zero (unsorted data).
Comment 2 Josir Gomes 2014-02-09 09:30:03 UTC
Created attachment 93696 [details]
Another example showing VLOOKUP error

VLOOKUP stops to work after upgrading
Using portuguese version

Version 4.2.0.4
ID de compilação: 420m0(Build:4)
Ubuntu/Libreoffice installed from ppa-repository.
Comment 3 m.a.riosv 2014-02-10 10:14:11 UTC
*** Bug 74777 has been marked as a duplicate of this bug. ***
Comment 4 Kohei Yoshida 2014-02-12 20:49:59 UTC
Ok. I think I've found the pattern here.  It happens when the top two cells in the search range are empty *and* the match is on the first non-empty cell.
Comment 5 Kohei Yoshida 2014-02-14 00:49:26 UTC
It took me a while but I finally understand the reason for the failure and a proper fix for it.  The bad news is that the fix will be a bit large since I had to re-implement the faluty binary search algorithm to make it functional again.  The good news is that the same fix also fixes Bug 74558.
Comment 6 Kohei Yoshida 2014-02-14 00:50:28 UTC
(In reply to comment #5)

> The good news is that the same fix also fixes Bug 74558.

I meant Bug 72348.
Comment 7 Commit Notification 2014-02-14 01:33:11 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#74558: Add new test case to catch this regression.



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 8 Commit Notification 2014-02-14 01:33:38 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#74558: Re-implement BinarySearch() to disregard empty cell blocks.



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 9 Kohei Yoshida 2014-02-14 01:37:42 UTC
4.2 backport request: https://gerrit.libreoffice.org/8042
Comment 10 Kohei Yoshida 2014-02-14 01:39:30 UTC
*** Bug 72348 has been marked as a duplicate of this bug. ***
Comment 11 Commit Notification 2014-02-15 17:36:07 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ff53d15a6633b1188e97b8b15a71190f072afeea&h=libreoffice-4-2

fdo#74558: Re-implement BinarySearch() to disregard empty cell blocks.


It will be available in LibreOffice 4.2.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:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2014-02-17 15:12:56 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1c49a6b61200fef0f6263c074282b2127359bb15&h=libreoffice-4-2-1

fdo#74558: Re-implement BinarySearch() to disregard empty cell blocks.


It will be available already in LibreOffice 4.2.1.

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 Kohei Yoshida 2014-02-17 15:14:06 UTC
Now it's fixed.
Comment 14 Josir Gomes 2014-02-19 18:48:11 UTC
Hi folks, I just upgrade to 

Versão: 4.2.1.1
ID de compilação: 420m0(Build:1)
Portuguese Version

using the ppa version and the bug persists :(

Just open the example that I uploaded that you can see the error.
Comment 15 Kohei Yoshida 2014-02-19 19:10:12 UTC
Check with Bjoern and see if the PPA build contains this fix.  Or wait for 4.2.2 to verify.

The state of 4.2.1 build is a bit of a mess and it shouldn't be used to verify this.
Comment 16 Björn Michaelsen 2014-02-20 12:19:19 UTC
@(In reply to comment #15)
> Check with Bjoern and see if the PPA build contains this fix.  Or wait for
> 4.2.2 to verify.
> 
> The state of 4.2.1 build is a bit of a mess and it shouldn't be used to
> verify this.

Checking the git log, the commit 1c49a6b61200fef0f6263c074282b2127359bb15 came in _after_ the 4.2.1.1 tag we released as 4.2.1 final, so this is indeed not in _any_ 4.2.1 release (neither the TDF builds, nor Ubuntu PPA builds). Adjusting target in Whiteboard Status as such. Automated Comment 12 is wrong. As Kohei says, we fasttracked 4.2.1 for some other issues and this unfortunately causes confusion for some automated tools.
Comment 17 m.a.riosv 2014-02-20 22:49:50 UTC
Thanks Kohei.
Verified in:
Win7x64Ult.
Version: 4.2.3.0.0+ Build ID: 7af1124f83df4d96f6ebe58536de7c51406df661
   TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-02-20_11:39:39
Version: 4.3.0.0.alpha0+ Build ID: 22b709e84a7b6d38cab2dd37f2f2b28e0fc9d062
   TinderBox: Win-x86@39, Branch:master, Time: 2014-02-20_00:01:31

Also for windows the bug is present in 4.2.1
Comment 18 Michael Stahl (CIB) 2014-02-21 10:57:10 UTC
correcting target, since 4.2.1.1 is 4.2.1 release