When using some functions with *_ADD within the name, the table don't work correctly when saving in different formats. This concerns: - CUMIPMT_ADD - CUMPRINC_ADD - EFFECT_ADD - GCD_ADD - ISEVEN_ADD - ISODD_ADD - LCM_ADD - NOMINAL_ADD Not concerned are: - CONVERT_ADD - DURATION_ADD - WEEKNUM_ADD 1) When saving as ODS and opening again, these functions are renamed to its variant without _ADD. E.g.: Type into A1 "=GCD_ADD(24;18)" and saving the spreadsheet as ODS. Expected behavior: When opening A1 contains =GCD_ADD(24;18). Shown behavior: When opening A1 contains =GCD(24;18). 2) When saving as XLSX and opening again, functions without _ADD are converted to subtractions. E.g.: Type into A1 "=GCD(24;18)" and saving the spreadsheet as XLSX. Expected behavior: When opening A1 contains =GCD(24;18). Shown behavior: When opening A1 contains =(24-18). 3) When saving as XLS and opening again, functions without _ADD show #MACRO? as output value. E.g.: Type into A1 "=GCD(24;18)" and saving the spreadsheet as XLS. Expected behavior: When opening A1 shows 6. Shown behavior: When opening A1 shows #MACRO?.
Created attachment 83295 [details] file saved as ODS
Created attachment 83296 [details] file saved as XLSX
Created attachment 83297 [details] file saved as XLS
Hello Christian, *, I can confirm this bug with LO Version: 4.1.1.0.0+ Build ID: 8c1e1d99f10894718975300a052358de7e3fef2 TinderBox: Linux-x86_64@31-Release-Configuration-RHEL5-Baseline, Branch:libreoffice-4-1, Time: 2013-07-28_13:00:24 and installed Germanophone lang- as well as helppack under Debian Testing AMD64 ... :( Furthermore, all this new functions seem not anywhere in LO's OLH ... :( HTH Thomas.
I can't confirm missing the OLH's content of these functions, the built in help as well as the online help contain these functions. E.g. the GCD_ADD function you can find here in the OLH: https://help.libreoffice.org/Calc/Mathematical_Functions#GCD_ADD
I can confirm behaviour 1 and 3 on LibreOffice 4.0.4 already: Shown behavior: When opening A1 contains =GCD(24;18). Shown behavior: When opening A1 shows #MACRO?. so not a release regression in 4.1 there at least, but likely older. As I have not confirmed it to have been working in any previous version, not marking it as a regression yet. Requesting bibisect though. Splitting out behaviour 2 to bug 67572 as is does _not_ show in 4.0.4. In the future, please file one report for each bug. Thanks.
since I took bug# 67572 probably these have the same root cause
ok, didn't check yet whether the 1 & 3 actually worked previously or not bug # 67572 is marked as a regression but I guess in my notes here I cover some it as well ( suspect bug #67572 is related to http://cgit.freedesktop.org/libreoffice/core/commit/?id=1162738c6fbd8505ffa27b28118318cc522a5368 but it didn't unapply for me cleanly to check... also I think Eike knows what he is doing so I guess dealing with how it is now is the way to go ) here's how it seems to go.. scenario a) functions saved to ods ================================== for those function that additionally have builtin variants..... a) WEEKNUM - actually it seems that the builtin variant for this actually has the text "ISOWEEKNUM" associated with it ( ok makes sense as we need to disambiguate between '_ADD' and without ) b) WEEKNUM_ADD -> this one actually gets saved as table:formula="of:=WEEKNUM(X,Y)" but on import gets mapped to WEEKNUM_ADD ( because ISOWEEKNUM->WEEKNUM ) c) GCD - builtin - text representing the formula is "GCD" ( or I guess a localized name ) - there is opcode -> rsid type relationship ( think I saw that somewhere ) and we write -> table:formula="of:=GCD(34;1048)" d) GCD_ADD ( addin ) - the text representing the formula is also "GCD" ( and same table:formula="of:=GCD(34;1048)") sent to ods. It seems the Symbol -> Addin name is created from the entries in ScCompiler::maAddInMap and it is the ODF name that is used as the Symbol ( e.g. in the case of GCD_ADD ( the english name ) it is "GCD" (the odf name) that is returned Question? why is the odf name GCD the same for the builtin and addin functions ? how can we distinguish them? Or is this a mapping error ( e.g. mistake in the maAddInMap entries ) Shouldn't we be outputting the English ( I didn't see how a localised name could be got for those ScCompiler::maAddInMap entries ) Or is it possible to have a 'ISOGSD" type arrangement like for WEEKNUM ( didn't look yet at where the WEEKNUM->ISOWEEKNUM relationship is established ) scenario b) functions saved as xlsx =================================== afaict all the "_add" functions behave the same, basically if the "_add" name is used then the correct version is exported to xlsx however if say you have GCD(x,y) then it is "(x,y)" that is exported, basicially when attempting to create a string from the formula tokens GCD is 'correctly' seen as a builtin opCode, however it has no entry when FormulaCompiler::getSymbol( builitn opcode for GCD ) is queried. Well odf builtin GCD doesn't exist in XLSX ( Compiler set CONV_XL_A1 ) with so I guess the code is doing what it is supposed to do ( but not what we expect... or is that right ) I mean I would expect that it would sortof do the the mapping from GCD->GCD ( etc. ) perhaps in FormulaCompiler::CreateStringFromToken if the builtin token doesn't resolve to a string we could fallback to the native string? would that be a viable solution ?
Eike, I could do with some advice how to proceed ( was going to cc you but see you are already cc'd )
1) save/load ODF .ods AddIn *_ADD functions that have a functional internal equivalent without _ADD that is defined in ODFF are saved without _ADD suffix and when loaded map to the internal one. This is on purpose and doesn't harm at all. 2) save/load OOXML .xlsx or BIFF .xls The internal functions without _ADD seem not to be known to the Excel mappings, likely because the LO AddIn ones actually were implemented as an equivalent to the Excel Analysis Pack AddIn ones, those AddIn functions have to follow specific conventions when saved to Excel files, and such definitions do not exist for the LO internal functions. The AddIn functions are exported correctly automatically. For the BIFF case apparently the internal function is stored but along with some #MACRO? error because the function is not known to Excel and thus stored as macro. For the OOXML case an explicit mapping exists but resolves to writing the AddIn function. Not sure yet why when saving the internal function the name is omitted, which when loading (a,b) correctly resolves to the Union (not minus) operator (a~b)
Maybe best that I take this over, also bug 67572.
I don't know how useful a bibisect is here. Bjoern - you mind trying? For #1 - bibisect is useless For #2 - can't reproduce For #3 - I don't get the #macro but it does add _ADD in bibisect, vs. in 4.1 it does change it to something weird with a ~. So basically I can't confirm it was ever working correctly so bibisecting is difficult
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=06a5b1ecc39bb50dadc0bcb7378455500943ece3 resolved fdo#67536 export internal equivalents as add-in to .xls 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.
Note that the change fixes the .xls export only, for .xlsx exists the split-off bug 67572
Pending review for 4-1 at https://gerrit.libreoffice.org/6525
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=db0d51e647e01d852ef95e46e12f1dba2d09be2b&h=libreoffice-4-1 resolved fdo#67536 export internal equivalents as add-in to .xls It will be available in LibreOffice 4.1.5. 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.