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.
Created attachment 185161 [details] Writer Table Formula Demo Document
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.
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.
(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.
Created attachment 186270 [details] Corrected LO Writer formula evaluation demo document Corrected LO Writer demo document attachment. Sorry for any confusion.
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
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.