Bug 72041 - Vlookup external file sort order
Summary: Vlookup external file sort order
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.0.beta1
Hardware: x86-64 (AMD64) Linux (All)
: high major
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.2
Keywords: regression
: 74948 (view as bug list)
Depends on:
Blocks:
 
Reported: 2013-11-26 15:41 UTC by p_kongstad
Modified: 2017-06-01 21:33 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Base for vlookup (11.87 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-11-26 20:27 UTC, p_kongstad
Details
Result of the vlookup (9.10 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-11-26 20:28 UTC, p_kongstad
Details
Screenshot about what I see (141.48 KB, image/png)
2013-11-26 20:55 UTC, m_a_riosv
Details
Instruction for the error with pictures (233.63 KB, application/vnd.oasis.opendocument.text)
2013-11-27 17:31 UTC, p_kongstad
Details

Note You need to log in before you can comment on or make changes to this bug.
Description p_kongstad 2013-11-26 15:41:26 UTC
Version: 4.3.0.0.alpha0+
Build ID: ee1b1b865b0ccc7418bbfad2a60cd14f851135a8
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2013-11-22_23:47:31

Create a file with columns index and text.
In the index column enter A1, A3 and A2. Fill text column with text.

Save the file with a file name.

Now open a new file without closing the file you saved above.

Make header index and text.

Enter the value A2 twice in the index column.

Now you go to text column. Open the 'Function Wizard' and find the vlookup function.

In search criterion you mark the value in index column.
Go to array and then mark the data area (index and text) you would like to use in the file saved.

In the index you give the value 2.

In sort order you now give the value 0 for FALSE and the returned value will be #N/A. If you change this value to 1 for TRUE, the text value is returned.

What happened for sort order 0:
Returning #N/A
Expected: Returning text value


What happened for sort order 1:
Returning text value
Expected: Returning text value

I noticed that on the 'Search Criterion' the value the wizard is returning is in format 'sheet1.A2:A2) while in the past only returned value was 'A2'.
Comment 1 m_a_riosv 2013-11-26 19:47:20 UTC
Hi p_kongstad,

please attach the sample files where verify the issue.
Comment 2 p_kongstad 2013-11-26 20:27:29 UTC
Created attachment 89858 [details]
Base for vlookup
Comment 3 p_kongstad 2013-11-26 20:28:27 UTC
Created attachment 89859 [details]
Result of the vlookup

The result of the vlookup in external file
Comment 4 m_a_riosv 2013-11-26 20:55:39 UTC
Created attachment 89864 [details]
Screenshot about what I see

For me works fine.

See the attached screenshot. After set up the range for look up with the same initial cell range.
Comment 5 p_kongstad 2013-11-27 07:29:23 UTC
On 11/26/2013 09:55 PM, bugzilla-daemon@freedesktop.org wrote:
>
> *Comment # 4 <https://bugs.freedesktop.org/show_bug.cgi?id=72041#c4> 
> on bug 72041 <https://bugs.freedesktop.org/show_bug.cgi?id=72041> from 
> mariosv <mailto:mariosv@miguelangel.mobi> *
> Createdattachment 89864  <attachment.cgi?id=89864>  [details]  <attachment.cgi?id=89864&action=edit>
> Screenshot about what I see
>
> For me works fine.
>
> See the attached screenshot. After set up the range for look up with the same
> initial cell range.
> ------------------------------------------------------------------------
> You are receiving this mail because:
>
>   * You reported the bug.
>
Something wrong with your attachment as it cannot be opened.
Comment 6 p_kongstad 2013-11-27 17:31:55 UTC
Created attachment 89910 [details]
Instruction for the error with pictures
Comment 7 p_kongstad 2013-11-27 20:52:27 UTC
Tested in windows version as well and same issue.
Comment 8 m_a_riosv 2013-11-27 22:40:53 UTC
I was trying with 4.0 and 4.1 where it works fine.

Not reproducible with:
Win7x64Ultimate
Version 4.0.6.2 (Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24)
Version: 4.1.4.0.0+ Build ID: d6ee64b75581cbeb92534271ee6f4e87f07aa5c


Reproducible with:
Version: 4.2.0.0.beta1 Build ID: f4ca7b35f580827ad2c69ea6d29f7c9b48ebbac7

Version: 4.2.0.0.alpha1+ Build ID: 2c39e778873f10037721d844697962dc41e3bcc3
TinderBox: Win-x86@42, Branch:master, Time: 2013-11-20_00:23:29

Changed status to new.
Set up as regression.
Change version to 4.2
Comment 9 p_kongstad 2013-11-27 23:06:06 UTC
Can also be reproduced with
Version: 4.3.0.0.alpha0+
Build ID: 513eadd422ff6a41cfe9a16f82cf32872d729652
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2013-11-26_23:58:22
Comment 10 p_kongstad 2013-11-27 23:10:57 UTC
Just noticed that the sort order accept values both negative and positive.
As an example it returns the expected value with sort order -1000 and 1000. Seems to me that sort order is wrongly defined as the only values should be TRUE and FALSE which equals to 1 and 0.
Comment 11 p_kongstad 2013-12-06 10:51:29 UTC
Tested bug against Version: 4.2.0.0.beta2
Build ID: 1a27be92e320f97c20d581a69ef1c8b99ea9885d and it is still present.
Comment 12 p_kongstad 2013-12-14 12:06:17 UTC
Bug confirmed in Version: 4.3.0.0.alpha0+
Build ID: 6265876ae9f1bb862440182d2d2c9b9b5b322668
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2013-12-13_23:47:25
Comment 13 p_kongstad 2013-12-20 09:22:37 UTC
Bug confirmed in Version: 4.2.0.1
Build ID: 7bf567613a536ded11709b952950c9e8f7181a4a
Comment 14 p_kongstad 2014-01-13 20:10:43 UTC
bug persists in Version: 4.2.0.2
Build ID: 601a398b803303d1a40a3299729531824fe0db56
Comment 15 p_kongstad 2014-01-18 09:56:52 UTC
As this is a longstanding regression it should be corrected.

The bug is still present in Version: 4.3.0.0.alpha0+
Build ID: 602c87b4259d118e5db6d8a990c4695103f916dd
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-01-18_00:02:21

At the same time changed from normal to major to attract attention.
Comment 16 m_a_riosv 2014-02-13 21:14:56 UTC
*** Bug 74948 has been marked as a duplicate of this bug. ***
Comment 17 Kohei Yoshida 2014-02-20 19:02:55 UTC
(In reply to comment #10)
> Just noticed that the sort order accept values both negative and positive.
> As an example it returns the expected value with sort order -1000 and 1000.
> Seems to me that sort order is wrongly defined as the only values should be
> TRUE and FALSE which equals to 1 and 0.

Any value other than 0 will be interpreted as TRUE.
Comment 18 Kohei Yoshida 2014-02-20 20:08:45 UTC
I'm on it.
Comment 19 Commit Notification 2014-02-20 21:08:08 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#72041: Add test for this.



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 20 Commit Notification 2014-02-20 21:08:21 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2bcd18892f1903d2434bc5dc6828841e49bb78a2

fdo#72041: Intern strings in the external ref cache with the host document.



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 21 Kohei Yoshida 2014-02-20 21:09:54 UTC
4.2 backport request: https://gerrit.libreoffice.org/8149
Comment 22 Commit Notification 2014-02-20 23:58:44 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=9da3e4f6bece47218a7149e7c37e5f2f06c8e480&h=libreoffice-4-2

fdo#72041: Intern strings in the external ref cache with the host document.


It will be available in LibreOffice 4.2.3.

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 23 Kohei Yoshida 2014-02-21 00:23:11 UTC
Fixed. The problem was in the external reference cache where we weren't probably interning shared strings which made the equality check of two strings fail.  This bug was about VLOOKUP but this fix could potentially fix other use cases where external references and string comparisons are involved.
Comment 24 p_kongstad 2014-02-21 11:10:55 UTC
Should it not be part of 4.2.2 as well?

On 02/21/2014 12:58 AM, bugzilla-daemon@freedesktop.org wrote:
> Commit Notification <mailto:libreoffice-commits@lists.freedesktop.org> 
> changed bug 72041 <https://bugs.freedesktop.org/show_bug.cgi?id=72041>
> What 	Removed 	Added
> Whiteboard 	target:4.3.0 	target:4.3.0 target:4.2.3
>
> *Comment # 22 <https://bugs.freedesktop.org/show_bug.cgi?id=72041#c22> 
> on bug 72041 <https://bugs.freedesktop.org/show_bug.cgi?id=72041> from 
> Commit Notification <mailto:libreoffice-commits@lists.freedesktop.org> *
> 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=9da3e4f6bece47218a7149e7c37e5f2f06c8e480&h=libreoffice-4-2
>
> fdo#72041: Intern strings in the external ref cache with the host document.
>
>
> It will be available in LibreOffice 4.2.3.
>
> 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.
> ------------------------------------------------------------------------
> You are receiving this mail because:
>
>   * You reported the bug.
>
Comment 25 p_kongstad 2014-02-21 11:30:03 UTC
Working in Version: 4.3.0.0.alpha0+
Build ID: db0222881be20744c071be451d77a7dc4a0dbb56

Also tested Hlookup with success.
Comment 26 Christians 2014-02-22 19:16:23 UTC
Works on Windows7 Version: 4.2.3.0.0+
Build ID: 5fd90cdd1fdb20ab7f6a2b67c384f0994f09a86b
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-02-21_22:38:15

Did NOT work on LibreOffice_4.2.1_Win, nor on LibreOffice_4.2.0_Win_x86.
Did work on LibreOffice_4.1.5_Win_x86
Comment 27 Commit Notification 2014-02-26 01:50:27 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5bba4c56d2d0a57725b3bc97a3bb8b13a727fa9f

fdo#72041: Add import test to catch an issue with external ref cache.



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 28 Commit Notification 2014-02-26 01:50:41 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5706ff70dbb18d03e396fd484bd1392dbcefb6c7

fdo#72041: Intern strings as we populate the external cache.



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 29 Commit Notification 2014-02-26 04:59:29 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=5a13bf7bc741aeb7543b9ff736563b89c086d4fd&h=libreoffice-4-2

fdo#72041: Intern strings as we populate the external cache.


It will be available in LibreOffice 4.2.3.

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 30 Commit Notification 2014-02-26 07:02:28 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2-2":

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

fdo#72041: Intern strings in the external ref cache with the host document.


It will be available already 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 31 Commit Notification 2014-02-26 15:27:28 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2-2":

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

fdo#72041: Intern strings as we populate the external cache.


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