Bug 74177 - EDITING TABLE: using comments on cells in WRITER is incompatible with formulas
Summary: EDITING TABLE: using comments on cells in WRITER is incompatible with formulas
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Writer (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: high critical
Assignee: Justin L
URL:
Whiteboard: target:5.4.0 target:5.5.0
Keywords:
Depends on:
Blocks: Writer-Comments
  Show dependency treegraph
 
Reported: 2014-01-29 12:38 UTC by Mike Kaganski
Modified: 2018-07-13 04:52 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
bugdoc (11.97 KB, application/vnd.oasis.opendocument.text)
2014-01-29 12:38 UTC, Mike Kaganski
Details
commentFormulaTest.odt: lots of different comment placements test document (15.51 KB, application/vnd.oasis.opendocument.text)
2017-01-09 18:44 UTC, Justin L
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2014-01-29 12:38:36 UTC
Created attachment 92992 [details]
bugdoc

Inserting a comment to a table cell with a manually written number makes that number inaccessible to formulas that refer to that cell.
Inserting a comment to a cell with a formula freezes the displayed value of that cell, while the underlying formula is still evaluated properly, which can be observed in other formulas that refer to that cell.

Steps to reproduce:
1. Create a new text document, add a table 3x3.
2. In the first column (A), insert values 1, 2, 3.
3. In columns B and C, insert formulas that refer to the neighbour left cell. E.g., insert into <B1> a formula like this: =(<A1>+1)*2, and copy the cell to other cells of the two columns.

Now the table has these numbers:
1  4  10
2  6  14
3  8  18

4. Put caret into <A2> cell, press Ctrl+Alt+C to insert a comment; put some text to the comment.
5. Tools->Update->Update All
6. Put caret into <B3> cell, press Ctrl+Alt+C to insert a comment; put some text to the comment.
7. Go to cell <A3> and change its contents to 4.

Expected results:
After step 5, the table must still look like this:
1  4  10
2  6  14
3  8  18
After step 7, the table must become like this:
1  4  10
2  6  14
4  10  22

Actual results:
After step 5, the table becomes this:
1  4  10
2  2  6
3  8  18
After step 7, the table becomes this:
1  4  10
2  2  6
4  8  22

The second row shows that the cell A2 is no longer considered to hold a number (although the number is indeed displayed in the cell). The result of the formula in <B2> is as if <A2> hold 0.
The third row shows that the <B3> shows incorrect outdated value (that was actual at the time the comment was inserted), but the formula in that cell is still active, and gives correct (invisible) result that is used by <C3>.

The problem persists after updating, and after save/close/reopen.

The impact is that it is impossible to use comments on tables. Using them may give unnoticed wrong results that are tricky to track down, especially in complex big tables, where one may not notice that come cells change (or don't change) as they should.
While it could be considered an enhancement request (on grounds that this will just broaden the comments feature to support new usage scenario), I think that this is actually a rather severe bug:
Using comments on individual cells seems natural, especially in complex tables, where those comments could help to clarify/remind what is contained/counted.
Even if using comments on tables with formulas is not permitted at the moment, this should be noted in the documentation, as well as measures must be taken to prevent undesired behaviour, e.g. by disabling comments in tables, or displaying proper notifications when either a comment is inserted into cell with formula/referenced by formula, or a formula is created that references a commented cell.
For now, the behaviour is inconsistent and is not what one would expect, causes difficult-to-notice (but probably severe) errors in data in professional documents.

This problem is inherited from OOo, is already reproducible with OOo 3.3.0.
Reproducible with 4.1.4.2 under Ubuntu 13.10 x64, and 4.2.0.3 under Win7x64.

Attached is a test document with a table before inserting comments, and another with comments inserted and <A3> modified, to reproduce the problem.
Comment 1 sophie 2014-01-29 13:13:48 UTC
I can confirm using your document using Version: 4.2.0.4
Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71 on Ubuntu 13.10 - Set as New - Sophie
Comment 2 sergio.callegari 2014-05-23 13:07:21 UTC
Verified on 4.2.4.2

Rised priority to high, since tables with formulas can be used for *contracts* and having LibO getting numbers wrong in a contract can be quite serious.
It's completely inacceptable that a program that is supposed to do math provides incorrect results. Rised severity to critical for the same reason.

Note that in the short term it may not be necessary to fully fix the bug, but at least to assure that an *error is returned* if there are comments that make computations impossible.  Here, the issue is that values are returned that may seem realistic, but that are not the correct ones.
Comment 3 QA Administrators 2015-06-08 14:42:38 UTC Comment hidden (obsolete)
Comment 4 Mike Kaganski 2015-06-09 13:52:53 UTC
Still reproducible with Version: 5.1.0.0.alpha1+
Build ID: 2af499f9dd9039a7228b5bafe1d4e9df4882014b
Locale: ru-RU (ru_RU)
Comment 5 Justin L 2016-12-26 16:48:02 UTC
Interesting bug.  Still a problem in 5.4alpha.
Comment 6 Justin L 2017-01-09 18:44:01 UTC
Created attachment 130284 [details]
commentFormulaTest.odt: lots of different comment placements test document

two part fix proposed:
https://gerrit.libreoffice.org/32909 tdf#74177 svl: ignoring comment allows number recognition
remote:   https://gerrit.libreoffice.org/32910 tdf#74177 Writer table: ignoring comment allows formula refresh
Comment 7 Justin L 2017-01-11 10:54:41 UTC
another fix to allow a comment at the very beginning: https://gerrit.libreoffice.org/#/c/32949/
Comment 8 Commit Notification 2017-01-16 04:25:25 UTC
Justin Luth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0611017c1df3c98684fbb904c1ab5ec7f68074d8

tdf#74177 Writer table: comment shouldn't prevent IsNumberFormat

It will be available in 5.4.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 9 Xisco Faulí 2017-03-01 11:03:05 UTC
Hello Justin,
Is this bug fixed?
If so, could you please close it as RESOLVED FIXED?
Comment 10 Justin L 2017-03-01 12:30:04 UTC
(In reply to Xisco Faulí from comment #9)
> Hello Justin,
> Is this bug fixed?

No, it is not fixed yet.  There are three parts to this bug, and only one part has been accepted into master so far.  The other two are waiting for approval.
Comment 11 Commit Notification 2017-05-26 11:04:35 UTC
Justin Luth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#74177 Writer table: refresh formula without losing comments

It will be available in 5.5.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 12 Commit Notification 2017-06-07 04:54:28 UTC
Justin Luth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9d6bf9d45d43a00f6dc87b9c83d28b93450ec51d

tdf#74177 writer: ignoring comment allows number recognition

It will be available in 5.5.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 13 Mike Kaganski 2017-06-08 05:15:59 UTC
Thank you Justin!
Comment 14 Thomas Lendo 2017-06-21 22:09:27 UTC
VERIFIED with Version: 6.0.0.0.alpha0+
Build ID: 6ef59d7ace7e4db52caea601a384ed016365bcaf
CPU threads: 4; OS: Linux 4.8; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-06-20_01:21:33
Locale: de-DE (de_DE.UTF-8); Calc: group

and the second attachment.

Thanks, Justin!