Bug Hunting Session
Bug 87973 - XLSX:vlookup formula changed in path to external csv file after saved and re-opened
Summary: XLSX:vlookup formula changed in path to external csv file after saved and re-...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.3.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Function-Vlookup
  Show dependency treegraph
 
Reported: 2015-01-02 16:51 UTC by kenw
Modified: 2017-07-25 19:12 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
the file that contains the changed formulae (5.58 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-01-02 16:51 UTC, kenw
Details
The referenced csv file. (42 bytes, text/csv)
2015-01-03 08:29 UTC, kenw
Details

Note You need to log in before you can comment on or make changes to this bug.
Description kenw 2015-01-02 16:51:17 UTC
Created attachment 111659 [details]
the file that contains the changed formulae

In "test2.xlsx", there are formulae fetching data from a csv file "data2.csv" located in a different folder.

After the file was saved and re-opened, the formulae changed to something like those reported in an already reported bug (https://bugs.freedesktop.org/show_bug.cgi?id=63992).

Then, I tried to correct the formulae by deleting the extra parts.  After the file was saved and re-opened again, the formulae changed to something like this:

VLOOKUP(A2,[3]data2!a$1:B$20,2,0)

That is, the path to the referenced file (data2.csv) has changed to [3].
Comment 1 raal 2015-01-02 18:51:02 UTC
Hello,
is it duplicate of bug 76047?
Comment 2 kenw 2015-01-03 04:04:04 UTC
I don't think they are the same.

Take a look at this screenshot: http://imgbox.com/Fz3agg6H

This is what I got after I saved the file and re-opened it. You can see that the path is something like 'file:///(path to this file)/(path to the referenced file) ...'

The correct path should be simply 'file:///home/kenneth/downloads/data2.csv'

Then, as I said in the report, I tried to correct the path by deleting the extra parts. I saved the file and re-opened it and now the formulae look like [3]data2.csv

as shown in this screenshot:

http://imgbox.com/1Y9jQ3xv
Comment 3 raal 2015-01-03 06:48:06 UTC
Your file is in xlsx format (test2.xlsx). Are you able to reproduce issue with .ods format (test2.ods)?
Please attach .csv, I'll try to reproduce this.
Comment 4 kenw 2015-01-03 08:29:37 UTC
Created attachment 111681 [details]
The referenced csv file.

the referenced csv file.
Comment 5 kenw 2015-01-03 08:30:17 UTC
Seems that the ods format works fine.

I now attach the csv file.
Comment 6 raal 2015-01-03 12:02:06 UTC
I can confirm with Version: 4.5.0.0.alpha0+
Build ID: 7f476fea47f06a7f8cc961dd4f6595a524346fa5
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-12-27_23:36:28

steps  to reproduce:
 - save csv file to /tmp
 - create xlsx file  in /tmp/testBug
 - add formula to xlsx file
      =IF(B2=0;"";VLOOKUP(A2;'file:///tmp/data2.csv'#$data2.A1:B20;2;0))
 - save as xlsx, close , reopen

Actual results:
formula changed to
 =IF(B2=0;"";VLOOKUP(A2;'file:///tmp/testBug/data2.csv'#$data2.A$1:B$20;2;0))

Expected results:
formula unchanged
Comment 7 kenw 2015-01-03 16:38:23 UTC
raal,

What if you now in the re-opened file delete "testBug/" (without quotes, of course), save the file and re-open it again?

Does the formula change?
Comment 8 raal 2015-01-03 21:46:49 UTC
(In reply to kenw from comment #7)
> raal,
> 
> What if you now in the re-opened file delete "testBug/" (without quotes, of
> course), save the file and re-open it again?
> 
> Does the formula change?

formula will change 
  =IF(B2=0;"";VLOOKUP(A2;[2]data2!a$1:B$20;2;0))
Comment 9 QA Administrators 2016-01-17 20:05:02 UTC Comment hidden (obsolete)
Comment 10 QA Administrators 2017-03-06 14:35:25 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice 
(5.2.5 or 5.3.0  https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and 
your operating system, and any changes you see in the bug behavior
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave 
a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword


Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug-20170306