Bug 46898 - [PATCH] Unexpected Error -60 Circular reference
Summary: [PATCH] Unexpected Error -60 Circular reference
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.11-dev
Hardware: HP Linux
: P2 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: PatchAvailable
Depends on:
Reported: 2009-03-24 01:27 UTC by Cheong Kok Kik
Modified: 2016-11-25 21:58 UTC (History)
3 users (show)

Java Application (7.03 KB, application/octet-stream)
2009-03-24 08:01 UTC, Cheong Kok Kik
Patch which checks for circular reference error code (491 bytes, application/x-gtar)
2014-02-27 00:45 UTC, Cliff Binstock
XLSX with circular reference (8.55 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-02-27 00:45 UTC, Cliff Binstock

Note You need to log in before you can comment on or make changes to this bug.
Description Cheong Kok Kik 2009-03-24 01:27:28 UTC

I've posted similar issue on Jan 2009. I thought the problem was solved but it re-occur again. I've attached 2 java source files and an excel spreadsheet. As mentioned, the unexpected error code -60 was stating circular reference which is not found in excel spreadsheet.

Please update the source file as i've hardcoded the excel spreadsheet path into it.


Comment 1 Cheong Kok Kik 2009-03-24 05:12:21 UTC

I've tested the code using 3.5 beta 5 as well. The result return is same. I'll upload the file via other website and provide a link later. (Due to 1 MB constraint in bugzilla website)


Kok Kik.
Comment 2 Cheong Kok Kik 2009-03-24 08:01:45 UTC
Created attachment 23409 [details]
Java Application

Small app to 
a. Update parameter at worksheet "Parameters & Data"
b. Extract data on worksheet "SI".

Command to execute:
java -Xms128m -Xmx512m -cp "lib\poi-scratchpad-3.5-beta5-20090219.jar;lib\po
i-3.5-beta5-20090219.jar;lib\poi-contrib-3.5-beta5-20090219.jar;." poi_hssf "spreadsheet name.xls"

Please note that the spreadsheet will be provided in URL later. (Once uploaded successfully)
Comment 3 Cheong Kok Kik 2009-03-24 09:08:20 UTC
Please download the spreadsheet at ftp site located at ftp.streamsolutions.com.sg.
User id is ftp@streamsolutions.com.sg and no password.
The file is poi-200903.zip.
Comment 4 Josh Micich 2009-03-30 13:56:44 UTC
Fixed in svn 760162

junit added

Thanks for the example - it would have been difficult to guess the cause of this error.  The problem was with the interaction between caching formula results and  detection of circular references.  The bug depends on the order of evaluation.  This is probably why you mentioned the problem as being intermittent. Take at look at the new unit test for an isolated example.
Comment 5 Cliff Binstock 2014-02-27 00:45:03 UTC
Created attachment 31349 [details]
Patch which checks for circular reference error code
Comment 6 Cliff Binstock 2014-02-27 00:45:45 UTC
Created attachment 31350 [details]
XLSX with circular reference
Comment 7 Cliff Binstock 2014-02-27 00:49:20 UTC
It seems that circular references are handled correctly for HSSF, but not XSSF.  In fact, all of the circular reference tests cases (and most others for that matter) seem to be related to HSSF, so I wasn't sure where to put an XSSF-specific test case (or suite).

I have included a patch that works (doesn't create an Exception, and creates a "VALUE#!" error in the spreadsheet. But, it seems like maybe there is an architectual issue in that this isn't handled at the HSSF level.
Comment 8 Dominik Stadler 2015-01-02 21:30:51 UTC
I have changed the tests via r1649122 to also run for XSSF, they run fine currently so the cases verified for HSSF work fine for XSSF as well already.

For the proposed change to FormulaError to change the error to INVALID_VALUE I am not sure if this is the right thing to do as it leaves ERROR_CIRCULAR_REF in some places (e.g. value.getErrorValue()) and changes the error to VALUE_INVALID in others, obviously I don't know this part of the code well enough to be sure, but wouldn't we change the error-code in a more central place to have INVALID_VALLUE everywhere?
Comment 9 Dominik Stadler 2016-11-25 21:58:02 UTC
I think the main part is fixed here, please create a separate bug with an up-to-date patch if you think there should be more changes applied.