Bug 31577 - Calc "INDIRECT" range intermittently fails
Summary: Calc "INDIRECT" range intermittently fails
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.1.0 target:5.0.0.3 target:4...
Keywords:
Depends on:
Blocks:
 
Reported: 2010-11-12 02:06 UTC by Phil Hibbs
Modified: 2023-10-12 12:23 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Document at Step 5 of my explanation (7.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-12-04 15:24 UTC, Phil Hibbs
Details
Document after Step 6 (7.70 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2010-12-04 15:26 UTC, Phil Hibbs
Details
Screenshot of an incorrect result being returned (27.16 KB, image/png)
2014-10-24 10:20 UTC, Phil Hibbs
Details
Step 1 for incorrect result demonstration (14.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-24 11:30 UTC, Phil Hibbs
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Phil Hibbs 2010-11-12 02:06:33 UTC
Create a new, blank spreadsheet
1. Put 'a' in A1 and 'z' in A2
2. Put 'a' in C1 and 1 in D1
3. Put 'z' in C1048576 and 26 in D1048576
4. Put formula in B1 =VLOOKUP(A1,INDIRECT("C1:D1048576"),2,0)
5. Copy formula into B2, which should give
=VLOOKUP(B1,INDIRECT("C1:D1048576"),2,0)

Everything works fine, the first vlookup returns 1 and the second returns 26... then...

6. Delete Row 3

The vlookup in B2 now returns blank! Can anyone else replicate this behaviour and explain why?

7. Put 'z' in C2, then remove it

The vlookup in B2 works again!

This has also been observed in OOo 3.2.1 using row 65536 instead of 1048576.
Comment 1 Phil Hibbs 2010-11-12 02:08:42 UTC
p.s. Why does OOo not allow C:D as a range, whereas Google Docs does? Is Google breaking the ODF standard by allowing this?
Comment 2 Rainer Bielefeld Retired 2010-12-04 00:48:08 UTC
@Phil Hibbs:
May I ask you to read <http://wiki.documentfoundation.org/BugReport>?
Then please:
- complete information (your OS, LibO version, ...)
- Attach a sample document representing your step 5
Comment 3 Phil Hibbs 2010-12-04 15:24:25 UTC
Created attachment 40805 [details]
Document at Step 5 of my explanation

Document at Step 5 of my explanation.
Comment 4 Phil Hibbs 2010-12-04 15:26:23 UTC
Created attachment 40806 [details]
Document after Step 6

The bug is not apparent in this file. Immediately after performing Step 6, the bug is apparent. However, on saving and closing and reopening the file, the bug is not apparent. There may be some clues in the saved file though so I have uploaded it just in case. Currently downloading Beta3 to test it in that.
Comment 5 Phil Hibbs 2010-12-04 16:00:33 UTC
Confirming that it is still observed in Beta3 using the "Step 5" attachment.
Comment 6 Rainer Bielefeld Retired 2010-12-05 01:04:10 UTC
Both sample documents "indirectbug1.ods" and "indirectbug2.ods" look identical when I open them with my "LibreOffice 3.3.0Beta3 - WIN XP DE [OOO330m12 (build 3.2.99.3)]". 

Reproducible with  "indirectbug1.ods", when I delete row 3 value in B2 disappears. Will not reappear with <f9>. Safe, close and reopen heals the problem.

It seems that the problem is only reproducible with fond value in last row of the sheet. The problem was not reproducible when I saved / reopened the document and the found values were in last but one row 

Only a very vague suspect: this problem might have common roots with the one in Bug 31939?

@Phil Hibbs:
You also observed that with Beta2?
Comment 7 Kohei Yoshida 2010-12-07 12:50:30 UTC
This is my area.
Comment 8 Florian Reisinger 2012-08-14 13:59:35 UTC Comment hidden (obsolete)
Comment 9 Florian Reisinger 2012-08-14 14:00:46 UTC Comment hidden (obsolete)
Comment 10 Florian Reisinger 2012-08-14 14:05:29 UTC Comment hidden (obsolete)
Comment 11 Florian Reisinger 2012-08-14 14:07:30 UTC Comment hidden (obsolete)
Comment 12 sasha.libreoffice 2012-08-22 06:41:07 UTC
reproduced in 3.6.0rc on Fedora 64 bit
Comment 13 Rainer Bielefeld Retired 2012-08-22 07:56:43 UTC
Modified OS due to comment before.

Effect is [Reproducible] with "LibreOffice 3.6.1.1  German UI/Locale [Build-ID:  4db6344] on German WIN7 Home Premium (64bit), but I know too few to understand it. But I am pretty sure that it's a bug.

Indeed AOOo 3.4 and LibO 3.3.0 show the same problem, so problem seems inherited form OOo.
Still  [Reproducible] with parallel installation of Master "LOdev  3.7.0.0.alpha0+   - WIN7 Home Premium (64bit) ENGLISH UI [Build ID: 6900781]" (tinderbox: 2008R2@20, pull time 2012-08-14 09:27:23)
Comment 14 QA Administrators 2014-10-23 17:32:08 UTC Comment hidden (obsolete)
Comment 15 Phil Hibbs 2014-10-24 09:39:21 UTC
Bug is still present. 

Libre Office version: 4.3.2.2 
Operating System: Windows 7 Enterprise SP1

I installed LiO fresh from the web site onto a new Windows 7 VM, followed my original instructions on a new blank workbook, and experienced the same behaviour. The formula in B2 stops working (returns blank instead of 26) when row 3 is deleted.
Comment 16 Phil Hibbs 2014-10-24 10:18:46 UTC
The same thing happens if I delete row 3 first and then type in the formula with the INDIRECT function call - any row beyond the row that was deleted is not found by the VLOOKUP.

While experimenting, I have also managed to get it to return the wrong result. The A column contains "h", the C and D columns on Row 6 contain "h" and 7 respectively, but the VLOOKUP in the B column returns 8.

I will upload a screenshot showing the problem, as saved files ODS do not exhibit the problem when re-opened.
Comment 17 Phil Hibbs 2014-10-24 10:20:46 UTC
Created attachment 108347 [details]
Screenshot of an incorrect result being returned
Comment 18 Phil Hibbs 2014-10-24 11:16:56 UTC
The same behaviour is also observed with the OFFSET function instead of INDIRECT in step 4:

4. Put formula in B1 =VLOOKUP(A1,OFFSET($C$1,0,0,1048576,2),2,0)
Comment 19 Phil Hibbs 2014-10-24 11:30:45 UTC
Created attachment 108349 [details]
Step 1 for incorrect result demonstration

I have isolated a set of steps that result in the WRONG value being returned, which I consider much more serious than a blank value (it would be embarrassing to send out an invoice with a blank value, but if I invoice a customer with an incorrect value then that would be much worse).

1. Load the attached file
2. Delete row 5
3. Observe that the result in B3 for the VLOOKUP of "g" changes from 7 to 8, which is the value that "h" would have returned

As with the earlier symptoms, saving the file after the delete and re-loading does not exhibit the problem.

It occurs to me that the root cause of the problem appears to be that the results of the VLOOKUP are being held in the form of a cell reference, i.e. the VLOOKUP of "g" is stored in the form of "The contents of Cell D7", and the deletion of the row is not causing this cell reference to be re-assessed.
Comment 20 Phil Hibbs 2015-01-30 16:20:34 UTC
Confirming that this bug is still present in 4.4.0.3, both the original example that returns a blank, and the example from comment 19 that returns the wrong value from a VLOOKUP.
Comment 21 Matthew Francis 2015-04-07 05:27:25 UTC
Also occurs in OOo 3.3.0

-> Version: Inherited from OOo
Comment 22 Commit Notification 2015-06-29 20:25:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#31577 volatile lookup ranges must not be cached

It will be available in 5.1.0.

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 Commit Notification 2015-06-29 20:32:43 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

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

Resolves: tdf#31577 volatile lookup ranges must not be cached

It will be available in 5.0.0.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 24 Eike Rathke 2015-06-29 20:36:31 UTC
Pending review for 4-4 https://gerrit.libreoffice.org/16598
Comment 25 Commit Notification 2015-06-30 11:22:46 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

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

Resolves: tdf#31577 volatile lookup ranges must not be cached

It will be available in 4.4.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 26 Buovjaga 2023-10-12 12:23:38 UTC
Notes for unit test writers:

Revert has to be done manually.

Possibly something to add into sc/qa/unit/data/functions/spreadsheet/fods/vlookup2.fods