Bug 97150 - OPENCL: VLOOKUP returns wrong data when it is used at many cells
Summary: OPENCL: VLOOKUP returns wrong data when it is used at many cells
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.4.2 release
Hardware: All Windows (All)
: medium normal
Assignee: Tor Lillqvist
URL:
Whiteboard: target:5.0.5
Keywords:
Depends on:
Blocks: OpenCL
  Show dependency treegraph
 
Reported: 2016-01-15 11:54 UTC by Jesus Jimenez
Modified: 2016-02-03 08:47 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
vlookup file whe it is used in many rows (555.94 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-01-15 11:54 UTC, Jesus Jimenez
Details
An example of the error with pictures (338.32 KB, application/vnd.oasis.opendocument.text)
2016-01-28 10:56 UTC, Jesus Jimenez
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jesus Jimenez 2016-01-15 11:54:36 UTC
Created attachment 121951 [details]
vlookup file whe it is used in many rows

I have a Calc file with two pages. The second one use VLOOKUP on the first page.

The original file was created in v4.3.5.2 and it's work correctly.

When I open the file in v5.0.4.2 doesn't work correctly. It changes all the correct values (text) to numbers.
It doesn't work in version 5.0 compiled at 14/01/2016

In the attached file you must go to the second page, I use VLOOKUP at column G.
If you copy one cell that use VLOOKUP to other cell in the same column (or int he same cell) it changes all the values in that column to numbers.

If you modify the function it's work correctly again (Try to put an space before the parenthesis)
Comment 1 GerardF 2016-01-15 13:38:57 UTC
I don't check all the 17000+ VLOOKUP formula, but I don't see any errors.
With 5.0.4, nor with 5.2.0alpha

Please give more precise information.
What cell is incorrect, what do you expect and what is the (incorrect) result?
Comment 2 m.a.riosv 2016-01-15 23:28:37 UTC
Reproducible.
Win10x64
Version: 5.0.4.2 (x64) Build ID: 2b9802c1994aa0b7dc6079e128979269cf95bc78

For me disabling OpenCL solves the issue.

Menu/Tools/Options/LibreOffice/OpenCL - Unmark "Allow use of OpenCL"
Comment 3 Jesus Jimenez 2016-01-18 12:50:51 UTC
(In reply to GerardF from comment #1)
> I don't check all the 17000+ VLOOKUP formula, but I don't see any errors.
> With 5.0.4, nor with 5.2.0alpha
> 
> Please give more precise information.
> What cell is incorrect, what do you expect and what is the (incorrect)
> result?

It's not necesary to check all rows, copying only one cell changes all the values of the column from the code returned from page "Control" to a numeric value.

I have Windows [Versión 6.1.7601]

I proved it on others computers in my company and it works in some computers and fail in others computers.

I disabled OpenCL and it works correctly.
Comment 4 Jesus Jimenez 2016-01-19 06:54:02 UTC
I supose it's not a bug of LibreOffice, it's a problem with OpenCL
Comment 5 m.a.riosv 2016-01-19 09:28:07 UTC
Not so quick, Jesús. :)

The code to every function for use with OpenCL is done inside LibreOffice.

https://gerrit.libreoffice.org/gitweb?p=core.git&a=search&h=HEAD&st=commit&s=opencl+vlookup
Comment 6 Eike Rathke 2016-01-26 23:07:18 UTC
@mmeeks:
This is yet another "calculating wrong with OpenCL" report.
Any estimate if, how and in what time frame it can be fixed on your side?
As an interim measure I suggest to default the OpenCL option to disabled
in all releases.
Comment 7 Tor Lillqvist 2016-01-28 10:21:40 UTC
Jesus, could you please answer exactly to the question in comment #1? What cell is incorrect, what do you expect and what is the (incorrect) result?

It is enough to point to one cell to look at.
Comment 8 Jesus Jimenez 2016-01-28 10:56:29 UTC
Created attachment 122254 [details]
An example of the error with pictures
Comment 9 Tor Lillqvist 2016-01-28 11:13:27 UTC
I guess the cells in the G column on the Hoja2 sheet should not contain numbers, even after a hard recalc (Ctrl+Shift+F9)? In that case, this seems to be fixed in the 5.1 and master branches. I also checked the TDF build 5.1.0.2, is also fixed there. Sadly not fixed in 5.0.5.1, though.
Comment 10 Tor Lillqvist 2016-01-28 11:32:07 UTC
Cherry-picking commit 476bef70f1d9fd58b29a1f6fb95e54567b031acf to the 5.0 (and 5.0.5) branches would fix it. Submitting such requests to gerrit.
Comment 12 Commit Notification 2016-02-02 17:23:24 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f50edb1aabaf9de37782e63abd109e2276bd0c4&h=libreoffice-5-0

tdf#97150: tdf#94924: If we can't handle strings, don't try to then

It will be available in 5.0.6.

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 Commit Notification 2016-02-02 23:28:12 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "libreoffice-5-0-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=320246d24d8a07cdb6b8400e8afa48696cc39343&h=libreoffice-5-0-5

tdf#97150: tdf#94924: If we can't handle strings, don't try to then

It will be available in 5.0.5.

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 14 m.a.riosv 2016-02-03 02:00:58 UTC
Hi @Tor,

in the patch there is comment:
"And since a while, we don't claim to handle strings in VLOOKUP. Which is true."

Please forgive me, but I can't find any place with this assertion, and don't seems such from this release note:
https://wiki.documentfoundation.org/ReleaseNotes/4.2#Calc_2 - second point.

And overall I hope it's not a target, it should make calc unusable, it's one of more used functions, at least my impression after a lot of year in forums.

There is a unsolved regression with the slowness searching for strings when the option for "search criteria for = and <> must apply to whole cells" is disable for VLOOKUP. fdo#79892
Comment 15 Tor Lillqvist 2016-02-03 05:29:32 UTC
My comment of course meant that we don't handle strings in the OpenCL implementation of VLOOKUP. See (for the 5-0 branch) commit 3253cc2b4ab547cc42cb9f62254ecccac40b5459 where the takeString() virtual member function of the sc::OpenCL::OpVLookup class was dropped.
Comment 16 m.a.riosv 2016-02-03 08:47:36 UTC
Thanks Tor, sorry for my bad interpretation.