Created attachment 201938 [details] A spreadsheet document with examples of problematic formulas. * What happens: On document load some formulas return the error '#N/A' in some cells when multi-threaded calculation is enabled, while the same formula in another cell return a correct result. 'Data > Calculate > Recalculate' doesn't solve the problem but the formulas are recalculated and display the correct result on 'Data > Calculate > Recalculate Hard'. If the document is saved and reloaded, the formulas return '#N/A' again. When the option 'Libreoffice Calc > Calculate > Enable multi-threaded calculating' is disabled, the result is correct in any case. The problem seems to only concern formulas with specific functions like the vlookup one. * What should happen : On document load formulas the result of the formulas should be the same whether multi-threaded calculating is enabled or not. * How to reproduce : - Enable the 'Libreoffice Calc > Calculate > Enable multi-threaded calculating' option - Open the document provided as attachment => formula in cells "$Formulas.D2", "$Formulas.D4" and "$Formulas.D6" returns the '#N/A' error. - Disable the 'Libreoffice Calc > Calculate > Enable multi-threaded calculating' option - Reload the document with 'File > Reload' (or close and reopen the document) => formula in cells "$Formulas.D2", "$Formulas.D4" and "$Formulas.D6" returns the correct result. * Reproducible: Always * User Profile Reset: Yes * Libreoffice informations : Version: 25.2.5.2 (X86_64) / LibreOffice Community Build ID: 03d19516eb2e1dd5d4ccd751a0d6f35f35e08022 CPU threads: 8; OS: Linux 6.12; UI render: default; VCL: gtk3 Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR Calc: threaded
Changed version number from LibreOffice informations given in description. Best regards. JBF
Opens fine for me on Win 11: Version: 25.2.5.2 (X86_64) / LibreOffice Community Build ID: 03d19516eb2e1dd5d4ccd751a0d6f35f35e08022 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded I can reproduce the issue changing: Menu>Tools>Options>LibreOffice Calc>Calculate - ODF spreadsheet (not saved by LibreOffice
Created attachment 201949 [details] Screenshot - wrong result when multi-threading is enabled.
Created attachment 201950 [details] Screenshot - correct result when multi-threading is disabled.
Hi, Doesn't open fine for me on Win 11. * Multi-threaded calculation enabled (wrong result) : https://bugs.documentfoundation.org/attachment.cgi?id=201949 Version: 25.2.5.2 (X86_64) / LibreOffice Community Build ID: 03d19516eb2e1dd5d4ccd751a0d6f35f35e08022 CPU threads: 4; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: en-US Calc: threaded * Multi-threaded calculation disabled (correct result) : https://bugs.documentfoundation.org/attachment.cgi?id=201950 Version: 25.2.5.2 (X86_64) / LibreOffice Community Build ID: 03d19516eb2e1dd5d4ccd751a0d6f35f35e08022 CPU threads: 4; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: en-US Calc: default This bug first appears in version 24.8.0.0.alpha1 Regards.
Can't reproduce. Some cells have '#N/A' with and without multi threaded calculation enabled. Version: 25.2.5.2 (X86_64) / LibreOffice Community Build ID: fb4792146257752f54eab576deb869869b108571 CPU threads: 12; OS: Linux 6.14; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: CL
Hi, I can still reproduce this bug with recent versions of LibreOffice on various platforms : Version: 25.8.4.2 (X86_64) Build ID: 290daaa01b999472f0c7a3890eb6a550fd74c6df CPU threads: 4; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: fr-FR Calc: threaded Version: 25.8.4.2 (X86_64) Build ID: 290daaa01b999472f0c7a3890eb6a550fd74c6df CPU threads: 8; OS: Linux 6.12; UI render: default; VCL: gtk3 Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR Calc: threaded For the Linux OS, I have the same result on : - Debian Bookworm and Debian Trixie - Debian Bookworm on two different computers I can see in other comment that platforms where the bug can't be reproduced are both OpenCL enabled. Could this have an impact on the result? Can someone try to reproduce the problem without OpenCL enabled ? Can I provide more information to make this bug report progress ? Regards
This bug appeared with commit https://git.libreoffice.org/core/+/b38974391e8d4bf0d450abfaa86bbccbe1022995%5E%21/
Seems this bug is also related to the use of absolute / relative ranges in the vlookup function, but also to the size of the "Array" parameter used this function. In the provided document, when I fill the range $formulas.D2:D7 with : =$C2*VLOOKUP(CONCATENATE($A2;" ";$B2);$data.A:B;2;FALSE()) or =$C2*VLOOKUP(CONCATENATE($A2;" ";$B2);$data.A:$B;2;FALSE()) or =$C2*VLOOKUP(CONCATENATE($A2;" ";$B2);$data.$A:$B;2;FALSE()) errors appear on document load. But there is no error with : =$C2*VLOOKUP(CONCATENATE($A2;" ";$B2);$data.$A:B;2;FALSE()) If I fill the same range with : =$C2*VLOOKUP(CONCATENATE($A2;" ";$B2);$data.$A$1:$B$84;2;FALSE()) there is no error on document load. But with : =$C2*VLOOKUP(CONCATENATE($A2;" ";$B2);$data.$A$1:$B$85;2;FALSE()) errors reappear on document load. If I fill the same range with : =$C2*VLOOKUP(CONCATENATE($A2;" ";$B2);$data.$A:$C;2;FALSE()) while the $data.C column is empty, there is no error on document load. But if the column $data.C contains any data, errors reappear.
(In reply to vatairethibault from comment #8) > This bug appeared with commit > https://git.libreoffice.org/core/+/ > b38974391e8d4bf0d450abfaa86bbccbe1022995%5E%21/ I checked with linux-64-24.8 and confirm the result. Same commit was also blamed for bug 166268
In attachment 201938 [details]: The content of $formulas.B2: 0.5 The content of $data.A2: p1 0,5 "zero point 5" is not the same as "zero comma 5".
Created attachment 205071 [details] A new test document where commas are not hard-written in formulas. OK, I better understand why some people was not able to reproduce the problem : - the problem seems only reproducible when the user's locale use a comma as decimal separator, - my test document use hard-written commas in formula which make it incompatible when the user's locale use a dot as decimal separator. Here is a new test document where commas are not hard-written in formulas. With this new document, when I switch to the "English (USA)" locale and I use "Data > Calculate > Recalculate Hard" to hard recalculate all formulas in the document, dot is used instead of comma as decimal separator and the bug is no more present, even on document load. If I switch again locale to "French (France)" and hard recalculate formulas, the problem reappears on document load.
IDK how you create these documents nor what exactly you mean with commas "not hard-written". In other words, use either numbers (0&decimal_separator&5) or not (0¬_a_decimal_separator&5) in _both_ spreadsheets within the workbook. FWIW, I have multi-threaded calculation enabled. When I just open attachment 205071 [details], I still see "0.5" in one spreadsheet while there is "0,5" in the other. After Recalculate Hard, they are all "0.5". IDK whether this is expected behavior, but as a user I am surprised. I don't even understand why users "must" manually use Recalculate Hard when the AutoCalculate setting is already enabled (but we have seen such issue before, even having to use it twice consecutively). Since Recalculate Hard is involved in the procedure, I should also mention that I have OpenCL disabled. I could understand that there might be some problem when the decimal separator is set as a comma, but why changing the setting for multi-threaded calculation would modify the end result? Using the same symbol in both spreadsheets, the end result should not be affected by the multi-threaded calculation setting. That would be indeed a problem. Just as an additional test, you might want to try using a completely different symbol (e.g. underscore), but the same one for both spreadsheets. Please re-test whether changing the multi-threaded calculation setting is really affecting the end result (while maintaining the same symbol in both spreadsheets, with multi-threaded calculation enabled, and when it is disabled).
(In reply to ady from comment #13) > IDK how you create these documents nor what exactly you mean with commas > "not hard-written". > > In other words, use either numbers (0&decimal_separator&5) or not > (0¬_a_decimal_separator&5) in _both_ spreadsheets within the workbook. By hard-written, I mean use a string like "p1 0,5" instead of a formula like =concatenate("p1";$formulas.B2) where $formulas.B2 cell contains the decimal value 0,5. (see column $data.A in documents provided as an example) In the first case the decimal separator may not match the decimal separator of the user's locale if the locale use a dot as decimal separator. In the second case, the formula adapts the decimal separator to the user's locale. > When I just open attachment 205071 [details], I still see "0.5" in one > spreadsheet while there is "0,5" in the other. After Recalculate Hard, they > are all "0.5". IDK whether this is expected behavior, but as a user I am > surprised. Indeed, seems the result of the formulas are not recalculated on document load. So when the document is open on a platform with a different decimal separator, the result of the above "concatenate" formula remains the same until "Recalculate Hard" is used or the value of one of the cells used in the formula is changed. > I don't even understand why users "must" manually use Recalculate Hard when > the AutoCalculate setting is already enabled (but we have seen such issue > before, even having to use it twice consecutively). Don't know it's either a bug or a feature. (may be to speed up document load ?) > Just as an additional test, you might want to try using a completely > different symbol (e.g. underscore), but the same one for both spreadsheets. Already tested and the result is same both when multi-threading is enabled or disabled. Furthermore, the problem does not occur when using integer values instead of decimal values. > Please re-test whether changing the multi-threaded calculation setting is > really affecting the end result (while maintaining the same symbol in both > spreadsheets, with multi-threaded calculation enabled, and when it is > disabled). When I : - switch from a locale which use a comma to a locale which use a dot as decimal separator - recalculate hard - save the document - reload the document the same symbol is used in both spreadsheets, and multi-threaded calculation affect the end result : there's no error when multi-threading is disabled and errors reappears when multi-threading is enabled.