Bug 46898

Summary: [PATCH] Unexpected Error -60 Circular reference
Product: POI Reporter: Cheong Kok Kik <kokkik.cheong>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: blocker CC: cliff.binstock, kokkik.cheong, martin.studer
Priority: P2 Keywords: PatchAvailable
Version: 3.11-dev   
Target Milestone: ---   
Hardware: HP   
OS: Linux   
Attachments: Java Application
Patch which checks for circular reference error code
XLSX with circular reference

Description Cheong Kok Kik 2009-03-24 01:27:28 UTC
Hi,

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.

Regards,

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

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)

Regards,

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.