Bug 153422 - Writer Table Formula Evaluation Errors
Summary: Writer Table Formula Evaluation Errors
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Writer (show other bugs)
Version:
(earliest affected)
7.4.5.1 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Writer-Tables-Formulas
  Show dependency treegraph
 
Reported: 2023-02-06 21:33 UTC by David Alden
Modified: 2024-05-09 13:16 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Writer Table Formula Demo Document (29.77 KB, application/vnd.oasis.opendocument.text)
2023-02-06 21:36 UTC, David Alden
Details
Table Formula Demo Document Update (44.08 KB, application/vnd.oasis.opendocument.text)
2023-02-08 23:32 UTC, David Alden
Details
Corrected LO Writer formula evaluation demo document (44.03 KB, application/vnd.oasis.opendocument.text)
2023-03-28 19:31 UTC, David Alden
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Alden 2023-02-06 21:33:03 UTC
Description:
Libre Office Writer Version 7.4.5.1 installed today along with previous versions exhibit table formula evaluation inconsistencies and unpredictable evaluation errors under several conditions including functions using the List Separator and other operators.  An attached Writer document demonstrates examples with descriptions.

Steps to Reproduce:
1. Create multi-column table in Writer documents
2. Populate cells with numbers and formulas as shown in attached document.
3.

Actual Results:
Erroneous calculations and unpredictable evaluations. See attached Writer 7.4.5.1 document.

Expected Results:
Correct and predictable calculations and evaluations.


Reproducible: Always


User Profile Reset: No

Additional Info:
Version: 7.4.5.1 / LibreOffice Community
Build ID: 9c0871452b3918c1019dde9bfac75448afc4b57f
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

Running under up-to-date Linux Lite 6.2 on an quad-core Asus Laptop with 4GB RAM.
Comment 1 David Alden 2023-02-06 21:36:59 UTC
Created attachment 185161 [details]
Writer Table Formula Demo Document
Comment 2 David Alden 2023-02-08 23:32:50 UTC
Created attachment 185240 [details]
Table Formula Demo Document Update

Updated Document Demonstrating LO Writer table formula evaluation errors and inconsistencies with more detailed commentary. The previous attachment (TableDemo.odt) may be ignored.
Comment 3 Dieter 2023-02-26 06:12:14 UTC
Tested with

Version: 7.5.0.3 (X86_64) / LibreOffice Community
Build ID: c21113d003cd3efa8c53188764377a8272d9d6de
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: en-GB
Calc: CL threaded

Steps
1. Open attachment 185240 [details]
2. In empty table cell C1 enter a number between -2 and 2
3. Click into another cell or outside table
4. Compare results in column C with expected results at buttom of page

I can see, that results differ from expected results, but since I'm not familiar with such formulars I can't asses, which results are correct.
Comment 4 Buovjaga 2023-03-28 11:35:35 UTC
(In reply to Dieter from comment #3)
> Steps
> 1. Open attachment 185240 [details]
> 2. In empty table cell C1 enter a number between -2 and 2
> 3. Click into another cell or outside table
> 4. Compare results in column C with expected results at buttom of page
> 
> I can see, that results differ from expected results, but since I'm not
> familiar with such formulars I can't asses, which results are correct.

David: can you clarify what exactly is expected?

Set to NEEDINFO.
Change back to UNCONFIRMED after you have provided the information.
Comment 5 David Alden 2023-03-28 19:31:55 UTC
Created attachment 186270 [details]
Corrected LO Writer formula evaluation demo document

Corrected LO Writer demo document attachment.  Sorry for any confusion.
Comment 6 David Alden 2023-03-28 19:50:24 UTC
Dear Libre Office Writer QA Administrators,
     Thanks for following up with this bug report.  I'm sorry the explanatory material included in the attachment was not sufficiently clear.  The attachment provided (TableDemo1.odt) was derived from an attempt to use the capability of LO Writer to compute formulas according to US Federal and State income tax worksheets within tables and fields in an ODF text document.  The income tax worksheets often require conditional computation of values dependent on other entered or computed values.  In an attempt to use the LO Writer table formula capabilities for this purpose, anomalous results were observed from properly constructed formulas in all currently available versions of LO.  Rows 4-9 in Table1 of TableDemo1.odt provide examples of both correct and anomalous formula evaluations each with a brief note <A1:9> as to what the <C1:9> value or formula is intended to demonstrate (<C1> is editable but <C2:3> are fixed).  The Table1 formulas in <C4> and <C5> evaluate correctly for all values entered in <C1>, specifically the integers -2, -1, 0, 1, or 2 for demonstration purposes.  These are the simple <C4> formula: "=<C3>+<C1>*<C2>" and the conditional <C5> formula "=max((<C1>*<C2>+<C3>)*(<C1>g 0)) | (<C3>*(<C1>leq 0)*3/4)" as shown in the rightmost column at the bottom of the attachment (TableDemo1.odt) [these active formulas can be verified by hovering the pointer over the appropriate cells in Table1 (<C4> or <C5>)].  The simple <C4> formula is provided just to demonstrate that common algebraic logic (e.g., multiplication has computational precedence over addition) is correctly implemented without the need for parentheses.  The conditional <C5> formula is provided to demonstrate that such conditional computation can evaluate correctly if exhaustive parentheses (i.e., none are left out) are included in the formula.  The <C5> formula evaluates correctly to either the same value as <C4> or <C3>*3/4 (4258.5) depending on whether cell <C1> is greater than (g) zero or less than or equal to (leq) zero respectively.  They are shown in the rightmost column of lines marked 4. and 5. at the bottom of the attachment document along with the correct expected values (columns 2-6) these formulas produce for the above mentioned values of <C1> without any highlighted (i.e., no anomalies).  So far so good.  However, the exhaustive parentheses should not be necessary to correctly evaluate the formula in <C5> and when they are removed, anomalous evaluations of the formula <C6> and <C7> are observed (expected results are highlighted in yellow at the bottom of the document in the row labeled 6.).  Note that the "(<C1>g 0)" condition has also been removed for the <C6> and <C7> formulas along with most of the parentheses included in the <C5> formula to simplify the expression to "=max <C3>+<C1>*<C2> | <C3>*(<C1>leq 0)*3/4".  For the two negative values of <C1> and zero, the left component of the max function will be either 3210, 4444 or 5678 respectively compared to 4258.5 on the right of the pipe (element separator).  But the <C6> formula actually evaluates to 9936.5 instead for all three (note that this value shouldn't even occur at all).  For the two positive values of <C1>, the right hand component of the max function should be zero so only the left hand component should matter with values 6912 and 8946 respectively but the function actually evaluates to 5678 instead.  The <C7> function simply reverses the order of the left hand arithmetic expression, <C1>*<C2>+<C3> instead of <C3>+<C1>*<C2>, and the results are even stranger: 1790.5, 3024.5, 4258.5, 1234 and 2468 instead of the expected 4258.5, 4444, 5678, 6912 and 8946 for <C1> -2, -1, 0, 1 and 2 respectively.  {At this point, please accept my apologies for the confusion resulting from my copy/paste and editing failure error caused by playing around with the <C6> and <C7> formulas and neglecting to fix the results that originally included the (<C1>g 0) condition as a part of the left max component after having removed it from both <C6> and <C7> formulas.  I have fixed the expected values in the corresponding lines in the replacement TableDemo1.odt attached below.}  In other words, even without parentheses, the arithmetic, operator and/or function calculations between the list separators (pipe characters) should be completed first regardless of how many there are before evaluating the max function or other functions that take lists (all the LO Writer "statistical functions", e.g. "=product (min 1|2|3) | (mean 2|4) | (max 1|3|5|7)" should compute 1*3*7=21 but produces 8 instead--weird 😕 [generally, the parentheses would usually be necessary to indicate which list separator goes with which function when the formula contains multiple separate lists]).  This reasonable expectation is apparently not what the LO Writer function parser is doing and I haven't been able to tell from various results what actually is happening.  The last two formulas <C8> and <C9> demonstrate that sometimes list components appear to be completely ignored by the parser.  The evaluation of <C8> "=max 1550 | (min 2350 | .45*<C4>) | 1111" always evaluates to 1111 but changing the order of these last two list components as in <C9> "=max 1550 | 1111 |  (min 2350 |.45*<C4>)" helps only with the two negative values of <C1>, formula <C9> evaluates to 2555.1, 3110.4 and 3665.7 for <C1> 0, 1 or 2 respectively instead of the correct 2350 value shown in the last two lines at the bottom of the attachment TableDemo1.odt.  The only part of the LO Writer table function bug report that remains to be mentioned is the failure of the recompute/update routine to interpret a null or blank cell as zero and reevaluate all the formulas accordingly (it appears to retain the last numeric value even after it has been blanked or nulled out).
     I sincerely hope this much more detailed description of the LO Writer formula evaluation anomalies helps to clarify any remaining questions you had regarding the indicated bug report.  Again, sorry for the confusion resulting from the erroneous values left in lines numbered 6. and 7. at the bottom of the demonstration attachment.  Please find a corrected version below.  I will also use the bugzilla interface to update the file.

Appreciatively,
Dave Alden
Comment 7 Dieter 2024-05-09 13:16:13 UTC
I confirm the first problem, you've mentioned with

Version: 24.2.3.2 (X86_64) / LibreOffice Community
Build ID: 433d9c2ded56988e8a90e6b2e771ee4e6a5ab2ba
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: en-GB
Calc: CL threaded

Steps:
1. Open attachment 186270 [details]
2. In empty cell C3 insert value -1
3. Press tab key => Values C4:9 are recalculated
4. Undo 3x (so that C1 is again empty) -> Tab key

Expected result
Values C4:9 are recalculated

Actual result
No change

5. Insert 0 as value and press tab (=> recalculation)
6. Insert 2 as value and press tab (=> recalculation)
7. Undo 2x (so that value C1 is again 0) -> Tab key (=> recalculation)

So it's a problem of empty cell. At least related to bug 140672. If empty cell isn't treated as 0, I would expect an error message when opening document.