Bug 107850 - editing, fileopen, filesave; A wrong sheet reference breaks the cell content
Summary: editing, fileopen, filesave; A wrong sheet reference breaks the cell content
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.6.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-05-14 19:59 UTC by dumblob
Modified: 2017-10-16 19:09 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description dumblob 2017-05-14 19:59:49 UTC
1. Create a new Calc document.
2. Write into cell A1: "Sheet1.A5 + Sheet1.$A$6".
3. Write into A5: "2".
4. Write into A6: "3".
5. Save document as "test00.ods".
6. Close all running LO instances and open Calc and then the file "test00.ods".
7. Change content of cell A1 to: "Sheet1X.A5 + Sheet1.$A$6" and press Enter. #REF! appears instead of the number "5".
8. Click on the cell A1 and look at the content - "Sheet1X.A5" changed to "#REF!.A5". This is the first bug (the correct behavior would be to leave the cell content as it was).
9. Save the document (again to "test00.ods").
10. Close all running LO instances and open Calc and then the file "test00.ods".
11. Click on the cell A1 and look at the content - "#REF!.A5" changed to "#REF!". This is the second bug (the correct behavior would be to leave the cell content as it was).

Few months ago I was working with a simple .ods spreadsheet referencing other sheets from the same document and after writing one reference incorrectly (similar to above), it changed itself to a weird reference to a file (but this reference was incorrect and not functional). Back then I didn't have time to report bugs in this Bugzilla and right now I can't reliably reproduce it, so I'm just mentioning it for the person who'll dive into the referencing code to check if there is not any visible bug with sheet references changing to file references.
Comment 1 Xavier Van Wijmeersch 2017-05-15 15:33:08 UTC
first i have a 522 error and than the ref error
for me its a normal behavior to tel the user something went wrong
test it with 5.3.4 daily build 2017_05_15 old laptop Dell D620
slackware current X86
Comment 2 dumblob 2017-05-17 16:53:36 UTC
(In reply to Xavier Van Wijmeersch from comment #1)
> first i have a 522 error

Not sure what 522 you're talking about as I don't get anything like that (fully updated Arch Linux, LibreOffice still 5.2.6.2). Is this new in 5.3.4?

>and than the ref error
> for me its a normal behavior to tel the user something went wrong
> test it with 5.3.4 daily build 2017_05_15 old laptop Dell D620
> slackware current X86

I might have not been clear. The issue is about cell content (which is the content which gets saved in the .ODS file), but not about visual representation of the cell. I totally agree, that the cell should show e.g. #REF! instead of the result of the cell content expression. But it definitely must not under any circumstances save any other value than the user wrote there!

I lost so much work (i.e. my time) just with this. And believe me, correcting ultra long expressions full of references, where copying or importing or just mistyping and pressing Enter completely discards any information which should have stayed at the place of mistakes is an utterly enraging situation.
Comment 3 Xavier Van Wijmeersch 2017-05-19 07:44:00 UTC
read the help file press F1 in calc there you find a list off errors
and i advice you the make always a backup so loss of data stays at minimum

first cell A1 may change to #ref! but when closing the calc and reopen the content in A5 may not change; the second behavior is not right.
Comment 4 Buovjaga 2017-05-26 18:55:44 UTC
Yep, it seems like a regression as 3.6 keeps the incorrect Sheet1x.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.5.0.0.alpha0+
Build ID: e60529fdfe0502f64e3c975f71539b28146943e8
CPU threads: 8; OS: Linux 4.10; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on May 26th 2017

Arch Linux 64-bit
Version 3.6.7.2 (Build ID: e183d5b)
Comment 5 Eike Rathke 2017-06-14 10:24:55 UTC
(In reply to dumblob from comment #0)
> 7. Change content of cell A1 to: "Sheet1X.A5 + Sheet1.$A$6" and press Enter.
> #REF! appears instead of the number "5".
It has to because a sheet named Sheet1X does not exist.

> 8. Click on the cell A1 and look at the content - "Sheet1X.A5" changed to
> "#REF!.A5". This is the first bug (the correct behavior would be to leave
> the cell content as it was).
That's debatable. If the reference string was left as it was entered wrongly, in a longer formula expression you wouldn't even see which part of the expression was wrong.

> 9. Save the document (again to "test00.ods").
> 10. Close all running LO instances and open Calc and then the file
> "test00.ods".
> 11. Click on the cell A1 and look at the content - "#REF!.A5" changed to
> "#REF!". This is the second bug (the correct behavior would be to leave the
> cell content as it was).
It has to be #REF! because the ODF OpenFormula specification specifies that an invalid reference is to be written as #REF!. Earlier versions wrote the more detailed but wrong #REF!.A5 string, that has been fixed which seems to be viewed as regression. It is not.
Comment 6 dumblob 2017-10-14 14:45:07 UTC
Hi Eike,


thank you for your time and an answer shedding some light on this situation.

(In reply to Eike Rathke from comment #5)
> (In reply to dumblob from comment #0)
> > 7. Change content of cell A1 to: "Sheet1X.A5 + Sheet1.$A$6" and press Enter.
> > #REF! appears instead of the number "5".
> It has to because a sheet named Sheet1X does not exist.

Of course, it's correct (I didn't write it's a bug ;) ).

> > 8. Click on the cell A1 and look at the content - "Sheet1X.A5" changed to
> > "#REF!.A5". This is the first bug (the correct behavior would be to leave
> > the cell content as it was).
> That's debatable. If the reference string was left as it was entered
> wrongly, in a longer formula expression you wouldn't even see which part of
> the expression was wrong.

It might be debatable, but the fact is, that it destroys a lot of work and therefore I can't see any other label than a "serious usability bug". There are plenty ways to show which part of the formula is causing issues (font formatting like underline or strikethrough or a different color or a different font etc. or my favourite: just adding (not replacing) the "#REF!" string at the end of the problematic reference in the formula...). See my questions below.

> > 9. Save the document (again to "test00.ods").
> > 10. Close all running LO instances and open Calc and then the file
> > "test00.ods".
> > 11. Click on the cell A1 and look at the content - "#REF!.A5" changed to
> > "#REF!". This is the second bug (the correct behavior would be to leave the
> > cell content as it was).
> It has to be #REF! because the ODF OpenFormula specification specifies that
> an invalid reference is to be written as #REF!. Earlier versions wrote the
> more detailed but wrong #REF!.A5 string, that has been fixed which seems to
> be viewed as regression. It is not.

Let me express my opinion. Let me also apologize in advance for my strong words (I'm aware you're one of the ODF authors).

This behavior seems utterly wrong and absolutely misleading. There seems to be no sane reason to throw away such an important part of a formula (a particular reference having been invalidated by actions that occurred after the formula was validly created). I strongly believe, that such a nonsense is not defined in the ODF specification.

Could you please point me to the relevant parts of the ODF specification? I found "#REF!" in

http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2-part2.html

with a description not clearly distinguishing between the formula itself and the visible result shown in the cell. Then I found

http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017900_715980110

with the same impression. I could not find definitions of "constant Error", "Error value", "original specific Error name", "Error constant", "Error", "data value #N/A is an error value", "error information". These terms are unfortunately used interchangeably in a quite messy manner.

According to my understanding, the OpenFormula spec does not dictate any replacing of the erroneous reference with the string representing the error which occurred.

More importantly, could you also point me to the correct place where I could raise this issue with suggestions? Some errata bug tracker or similar for the ODF specification.