Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Error 522 only after a save or autosave. Perfect otherwise.|
|Component:||save-export||Assignee:||AOO issues mailing list <issues>|
|Status:||CONFIRMED ---||QA Contact:|
|Priority:||P3||CC:||elish, issues, kyoshida, rainerbielefeld_ooo_qa, rb.henschel|
|Version:||3.3.0 or older (OOo)||Keywords:||needmoreinfo|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
Description billgradwohl 2009-03-17 01:20:06 UTC
I have a spreadsheet that always produces a 522 error (circular reference) ONLY immediately after either a save or autosave. The sheet works flawlessly until I either force a save or the system autosaves for me. Then the next cell I touch causes a cell relying on it to fail with a 522 error. If I rekey that same cell with a different value the error goes away. If I ctrl-shift-F9 the error disappears and the correct answer appears. This only happens immediately after a save or auto save. The fact that I can rekey the cell or force a sheet recalc and that clears it up should prove that the 522 is bogus. I wrote the spreadsheet and its largely a data entry screen as a few thousand lines of macro code actually make everything happen. The actual cell calcs in it are trivial. When I was using V2.4, I believe, The same thing would happen if I tried using full screen mode. In a normal screen, everything worked fine. As soon as I tried full screen mode it would fail on a 522. Put back to regular and the sheet worked flawlessly. I just gave up on full screen mode and waited for the latest version, but now I have a similar 522 issue in regular mode. I can reproduce the error every time. I force a save and the error appears. I rekey the same cell and all is well. Every time. BTW - I'm running Version 3.0 of OpenOffice. I have no idea what "version" of calc that is. When I ask calc it says build 9379. I hope that clears up any version issues.
Comment 1 dtardon 2009-03-17 13:19:34 UTC
dtardon->billgradwohl: Could you attach the problematic file?
Comment 2 billgradwohl 2009-03-17 18:09:33 UTC
I can send you the spreadsheet (about 500K, 38 sheets) and all the passwords in it via your personal email, but as it contains financial info I don't want to post that. Also, This has to do with another issue I've noticed in the last several releases. If a cell is not visible on the screen at the moment, it does not get calculated. If you scroll the screen to make the cell appear it does indeed have the correct answer, but while it is not visible it has the answer from the previous time it was visible. This is patently absurd. I found the following which addresses the issue tangentially:"Calc: working with elements outside the visible area" on the ux-discuss site. I know this is the case because my macros read from cells that may or may not be visible depending on how the user currently has his display of the sheet set up. My macros do the same thing every time but depending on if the user has a cell visible or not creates different and sometimes incorrect output. Example - Cells A1 thru O42 are visible on the screen. Cell A50 contains a sum for A1 thru A49. When the user enters data in any visible A* cell, the total is NOT updated in A50. If a macro is executed to list what is in A50, it will not contain the correct information. If the user scrolls the screen down to make A50 visible, it will have the correct total. Executing the same macro now produces the correct answer. My macros read the contents of cells all over the place and not having a cell calc'd just because it doesn't happen to be visible to the user at the time makes no sense at all. The spreadsheet will produce different answers depending solely on how the user has his viewing screen set up at the time. That's not a bug, but a major design flaw that renders the app unreliable in the extreme. My spreadsheet is a Point of Sale system I wrote for my wife's restaurant. We were giving meals away because the totals weren't correct simply due to how my wife happened to have the viewing screen set up. If she scrolled down to where the meal totals were, and then printed an invoice all was well. If she didn't "see" the total and simply hit the invoice button, the invoice would be short. I wanted to add this real world comment to show that not calcing cells that just happen to be invisible at the moment is blatantly wrong, as giving away meals solely because of how the screen is set up is wrong. Forcing a recalc of the entire spreadsheet is NOT the answer as that slows things down too much and OO Calc is already pretty slow. The correct thing to do is to follow the chain of dependant cells and update all of them regardless of their current visibility.
Comment 3 billgradwohl 2009-04-17 17:15:18 UTC
Created attachment 61643 [details] Sample spreadsheet that displays repeatable errors under certain circumstances
Comment 4 billgradwohl 2009-04-17 17:30:52 UTC
I attached a stripped down version of my spreadsheet that still displays the same faults to the email I received a month ago. I guess I was supposed to add it here. A stripped down version of my spreadsheet is attached. The following will demonstrate 2 separate errors. I feel that they are linked however, because they appear only when cells are hidden from view. That's the common element. Place the sheet where macro execution is enabled as it relied on macros to function. Any time you want to start over again, just save the sheet and the 522 error will return. I removed huge portions of this spreadsheet so you have less to consider when analysing it. I didn't remove lots of the macro code, but because about 30 sheets are missing, please don't hit any buttons present on the sheets. Normally, all the sheets are protected during macro execution, but to demonstrate these errors protection makes no difference, so I unprotected the Now sheet. Hit enter on the â€œReadyâ€ message. Set your autosave off so it won't interfere. Go to the Now sheet Fix your screen so that cell T47 is NOT visible. Go to B27 Enter 1 You now have a 522 error in U41 and a few other places. Go to B27 Enter 2 The error is gone. Make cell T47 visible by scrolling to see it. Make cell T47 invisible by scrolling to hide it. Execute macro Standard/tableReceipt Go to sheet Receipt and look at it. The total is correct. Go to the Now sheet Go to B27 Enter 1 No more 522's till the next save. Execute macro Standard/tableReceipt Go to sheet Receipt and look at it. The total is wrong. Go to the Now sheet Make cell T47 visible by scrolling to see it. Make cell T47 invisible by scrolling to hide it. Execute macro Standard/tableReceipt Go to sheet Receipt and look at it. The total is now correct. Go to the Now sheet Highlight columns G and T and right click / show the columns in between. Save the spreadsheet to start over again, but this time there will be no 522 errors because the hidden columns are now visible. Fix your screen so that cell T47 is NOT visible. Go to B27 Enter 1 No more 522's under any circumstances. By un-hiding the formerly hidden columns, the 522 error is permanently gone. However, the other error still persists. Make cell T47 visible by scrolling to see it. Make cell T47 invisible by scrolling to hide it. Execute macro Standard/tableReceipt Go to sheet Receipt and look at it. The total is correct. Go to the Now sheet Go to B27 Enter 2 Execute macro Standard/tableReceipt Go to sheet Receipt and look at it. The total is NOT correct Because T47 is not visible, it contains a bogus total. The 522 error can be made to go away via ctrl-shift-F9 as well, but just an F9 won't do it. If you read the code for the tableReceipt macro and what it in turn calls, you'll note that only the following line is worth your concern: grandTotal=sheet.getCellRangeByName("Table.tabLemps").getValue() When T47 (aka tabLemps) is not visible it contains what it did the last time it was visible, so the getValue returns an incorrect value. Obviously, cells that are not visible are not getting updated. Therefore macros that interrogate cells are given wrong answers. Regardless of visibility, all the cells within the spreadsheet MUST get updated or else the spreadsheet will give different answers depending on what cells are visible at any particular time and that means the spreadsheet is absolutely unreliable.
Comment 5 Regina Henschel 2009-06-06 17:26:28 UTC
I cannot confirm the Err 522 problem. I use DEV300m49 on WinXP. You are setting values by macro. You should ask a macro expert, in which cases you need to update the spreadsheet yourself. Have you tried to do a hard recalculate at first action before calculating the receipt?
Comment 6 billgradwohl 2009-06-08 23:10:58 UTC
Testing under a Linux O/S might be the difference. I have several Linux boxes and they all do it on the same spreadsheet.
Comment 7 billgradwohl 2009-06-23 17:17:24 UTC
Can someone with a Linux distro look at this please. Using a Windows box isn't relevant. Also I'm attaching some screen shots that are related to this issue that I believe show that there is something wrong with the internal tables kept by calc. When this sheet is opened, I make an entry in a cell and then (amongst others) cells AN41 and AM47 get 522's. If I re key the cell with a different value the 522 disappears. The first screen shot shows a trace while I'm on cell AN41. Note that the output is staggered down by 1 cell. The second screen shot shows the same thing - I'm on AM47 and as for a trace - output is staggered by 1 cell. On both shots, you can clearly see the formulas involved. AM47 in particular sums the cells immediately above it. There's no possibility of a circular reference. And if I re key the cell that caused this (happens to be C2) the error clears up. The error reappears after a save (auto or otherwise). There is nothing wrong with the sheet, as its the same sheet that was used under a previous release that didn't display these anomalies.
Comment 9 billgradwohl 2009-06-23 17:19:45 UTC
Created attachment 63176 [details] Second screen shot
Comment 10 Rob Weir 2013-02-02 02:59:16 UTC
This Issue requires more information ('needmoreinfo'), but has not been updated within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download Please report back the outcome of your testing, so this Issue may be closed or progressed as necessary - otherwise the issue may be Resolved as Invalid in the future.
Comment 11 Edwin Sharp 2013-11-19 17:05:07 UTC
Confirmed, following steps in comment 4 that total is wrong when T47 is not visible, no 522 errors. AOO410m1(Build:9750) - Rev. 1539999 2013-11-09_04:08:11 - Rev. 1540252 Debian
Comment 12 Rainer Bielefeld 2014-03-03 18:37:25 UTC
Correct Version: has been observed first with OOo 1.0.3