Bug 99625 - CONCAT and TEXTJOIN return (wrongly?) an array when "text argument" is a CSE array.
Summary: CONCAT and TEXTJOIN return (wrongly?) an array when "text argument" is a CSE ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.0.0.alpha1
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.4.0 target:5.2.8 target:5.2....
Keywords:
: 101134 (view as bug list)
Depends on: 97831
Blocks:
  Show dependency treegraph
 
Reported: 2016-05-02 11:41 UTC by GerardF
Modified: 2017-05-10 21:32 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
File with TEXTJOIN entered as array formula (12.28 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-05-02 11:41 UTC, GerardF
Details
Excel Online display (55.12 KB, image/png)
2016-05-02 12:16 UTC, Dennis Roczek
Details
Demo: Bug still living in V5.3.3.1RC (10.38 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-05-09 11:03 UTC, Wolfgang Jäger
Details
File with TEXTJOIN entered as array formula, saved by Excel365 (9.86 KB, application/zip)
2017-05-10 06:32 UTC, Winfried Donkers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description GerardF 2016-05-02 11:41:16 UTC
Created attachment 124781 [details]
File with TEXTJOIN entered as array formula

TEXTJOIN(delimiter,skip empty cells,text) returns an array of cell when "text" range is defined with a formula.

Example : =TEXTJOIN(", ",1,INDEX(A1:A10,{1;3;5})) returns "A1, A3, A5" (means content of A1,A3 and A5) three times instead of one.

See attachment for a more detailled case.
Comment 1 Dennis Roczek 2016-05-02 12:16:00 UTC
Created attachment 124785 [details]
Excel Online display

I Have no new master installed atm (as it seems) but this is a screenshot how it looks when displaying it in Excel Online. Hope that helps.
Comment 2 Winfried Donkers 2016-05-02 15:26:04 UTC
The TEXTJOIN examples in  attachment 124781 [details] (File with TEXTJOIN entered as array formula) and attachment 124785 [details] (Excel Online display) are - as the attachment says - entered as array formula. That means that the result will be an array.

Calc produces the joined test strings and puts the result in 3 cells to create that array.
Excel doesn't join the separate strings, disregards the delimiter and puts the input strings in 3 cells to create that array.

IMHO this is a case which use is not supported by TEXTJOIN. But Excel nor Calc produce an error message. And that presents a problem:
-the output from Excel is not correct, the result is not a concatenated text string with delimiters between the input strings;
-the output from Calc differs from Excel. Which is 'better' is not important, interoperability is. However, it is a doubtful action to make Calc to provide an incorrect result because Excel does.
If my reasoning is sound, one could state that Excel has bug (either not supporting array output or not producing a proper error output) and Calc has to follow as TEXTJOIN in Calc is there because it is in Excel.

Should TEXTJOIN be added to the OpenFormula standard, the situation changes, Then Calc can comply with that standard.

To make a long story short, I hesitate to confirm this bug report and don't have a solution other than waiting for definite information about the unexpected and AFAIK undocumented behaviour of TEXTJOIN in Excel.
Comment 3 GerardF 2016-05-02 16:00:44 UTC
As I said before, I don't have Excel and so can't do more test.
Excel show an array of 3 rows because Calc have store this formula like this.

Wrong result in Excel were expected.
When INDEX have a array as row or column argument, the result of INDEX is not handled properly in another function in array formula in Excel. That's a known bug.

A way to force Excel to recognize the result of INDEX as an array is to force the recognition with N (or T in case of text) and IF.

Dennis, can you try entering this formula in a cell:
=TEXTJOIN(", ",1,INDEX(B:B,N(IF(1,MODE.MULT(IF(A4:A12=D4,{1,1}*ROW(A4:A12)))))))
instead of
=TEXTJOIN(", ",1,INDEX(B:B,MODE.MULT(IF(A4:A12=D4,{1,1}*ROW(A4:A12)))))

in Excel and save the file as .xlsx
Comment 4 GerardF 2016-05-02 17:25:45 UTC
(In reply to GerardF from comment #3)
 
> Dennis, can you try entering this formula in a cell:
> =TEXTJOIN(",
> ",1,INDEX(B:B,N(IF(1,MODE.MULT(IF(A4:A12=D4,{1,1}*ROW(A4:A12)))))))
> instead of
> =TEXTJOIN(", ",1,INDEX(B:B,MODE.MULT(IF(A4:A12=D4,{1,1}*ROW(A4:A12)))))
> 
> in Excel and save the file as .xlsx

This formula was to make the same job, but you can test only a simple formula like :
=TEXTJOIN(",",1,ROW(A1:A3))
Comment 5 Dennis Roczek 2016-05-02 20:06:29 UTC
1) Excel Online is crap. I really do not understand how that web app should replace the "fat client" (as some managers and CEOs think)

2) Excel is crap. I do want to switch to English function names! This is such a big plus in LibreOffice Calc!

3) You cannot "convert" an existing ODS to an XLSX file. You can save the open file under another name (save a copy) but not to save it under a different file format. But always a big yellow fat warning about incompatibilities... -.-

4) Warning that the formula is incorrect. Well ok, but WHAT PART of the formula? Errr. Crap.

5) I will retry that tomorrow.
Comment 6 Winfried Donkers 2016-05-03 06:03:46 UTC
(In reply to Dennis Roczek from comment #5)
> 1) Excel Online is crap. I really do not understand how that web app should
> replace the "fat client" (as some managers and CEOs think)
> 
> 2) Excel is crap. I do want to switch to English function names! This is
> such a big plus in LibreOffice Calc!
> 
> 3) You cannot "convert" an existing ODS to an XLSX file. You can save the
> open file under another name (save a copy) but not to save it under a
> different file format. But always a big yellow fat warning about
> incompatibilities... -.-
> 
> 4) Warning that the formula is incorrect. Well ok, but WHAT PART of the
> formula? Errr. Crap.
> 
> 5) I will retry that tomorrow.

Could you offer your findings with Excel to Microsoft? This bug list only handles LibreOffice bugs/enhancements ;-) 
Looking forward to your results with Excel and this use case of TEXTJOIN.
As soon as I know how Excel handles this I can fix Calc's TEXTJOIN if needed.
Comment 7 Wolfgang Jäger 2016-08-18 11:27:22 UTC
(In reply to Winfried Donkers from comment #2)
> The TEXTJOIN examples in  attachment 124781 [details] (File with TEXTJOIN
> entered as array formula) and attachment 124785 [details] (Excel Online
> display) are - as the attachment says - entered as array formula. That means
> that the result will be an array.
> 
...
This is simply wrong. Only the evaluated parameter(s) have to be passed to the functions as arrays. Compare with accumulating numeric functions like PROD or IMSUM (actually a kind of text-function). The functions will then have to convert these arrays into sequences (See subchapter 4.11.12 of the mentioned document). Of course, a function body may omit this and treat these sequences as arrays for the sake of efficiency, if the results to obtain are not distorted. Please read subchapter 3.3 of http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.odt . For the data-parameters (all those of CONCAT and starting with the second one for TEXTJOIN) we need a data type of "TextSequenceList" which is not yet specified, but may be assumed in analogy to "NumberSequenceList" or "ComplexSequenceList". 
The examples attached to bug#101137, in specific the reworked newer one should be taken in account. If you think I should report the errors demonstrated and explained there using orange background for highlighting as independent bugs anew, please tell me. Given a few days I may also find the time to suggest a complete extension to the mentioned specification with respect to this subject. I may also sketch an implementation for CONCAT and TEXTJOIN in pseudocode or in BASIC. (I cannot study and train in my late days everything needed to contribute to the original code myself.)
Comment 8 Eike Rathke 2016-08-22 18:17:51 UTC
So, can someone with access to the relevant Excel version please come up with a description of what Excel exactly does when entering CONCAT and TEXTJOIN as array formulas?  Pointers to the ODF OpenFormula definition don't help here, CONCAT and TEXTJOIN are not covered there.
Comment 9 Eike Rathke 2016-08-22 18:28:52 UTC
*** Bug 101134 has been marked as a duplicate of this bug. ***
Comment 10 Eike Rathke 2016-08-22 18:29:58 UTC
Bug 101134 has some additional attachments that might be of interest:
https://bugs.documentfoundation.org/attachment.cgi?id=126416
https://bugs.documentfoundation.org/attachment.cgi?id=126823
Comment 11 Wolfgang Jäger 2016-08-22 20:05:38 UTC
(Just a silly workaround.)
Meanwhile you will get the expected scalar results with CONCAT and TEXTJOIN entered for evaluation in array mode using formulae like
{=INDEX(CONCAT(A1:A10&B1:B10);1} or
{=INDEX(TEXTJOIN(", ";TRUE();A1:A10&B1:B10);1)}

This is not avoiding bad results due to bugs concerning the array-evaluation of the concatenation operator & (e.g. A1:A10&B1:B10) as discussed in bug#101137.
Comment 12 Wolfgang Jäger 2016-08-23 09:22:24 UTC
No harm meant! 

(In reply to Eike Rathke from comment #8)
> ...  Pointers to the ODF OpenFormula definition
> don't help here, CONCAT and TEXTJOIN are not covered there.

Pointers to the OpenFormula document cannot help with a specification for CONCAT and TEXTJOIN. 

However, they can help to understand that the actual behaviour under array-evaluation is improper with respect to general mandatory requirements (subchapter 3.3). This would apply even if Excel 2016 did it otherwise - and also was not based on any specification of the new functions in advance. In addition: If Excel 2016 actually returns an array of identical elements under the mentioned conditions, It will most likely also be in disorder concerning general principles applying to Excel itself. Thus MS will have to fix a bug themselves in that case. 

Is somebody capable of checking for my above claim referring  to the (should-be existing) SPECIFICATIONS concerning array-evaluation in Excel (XLSX defining documents)? I found not to be. I though feel sure that the authors of OpenFormula did not fail to be compatible with Excel concerning the fundamentals of array-evaluation.

Or - does somebody assume an accumulating function might be declared to return 1-1-array in the standard case? In this case subchapter 3.3, statement 2.2.1) of OpenFormula would apply. Also consider the analogy with SUM.

To get finally convinced, try something like
{=TEXTJOIN(", ";FALSE();A1;A1:B4;A7:B8)} 

(Appologies for my stubbornness. This touches a relevant question of priniples, imo.)
Comment 13 Eike Rathke 2016-08-23 09:53:43 UTC
Can please someone with access to the relevant Excel version check the results instead we cluttering up this bug with assumptions whatsoever? Thank you.
Comment 14 Winfried Donkers 2016-09-23 06:25:39 UTC
(In reply to Eike Rathke from comment #13)

AFAICS relevant Excel version is Excel365-2016.

Useless versions are:
Excel Online does support TEXTJOIN, but does not support arrays.
Excel 2016 offline doesn't support TEXTJOIN (unless Microsoft puts it in a future update).
Comment 15 Wolfgang Jäger 2016-09-29 12:24:10 UTC
(In reply to Winfried Donkers from comment #14)
>...
> AFAICS relevant Excel version is Excel365-2016.
> 
> Useless versions are:
> Excel Online does support TEXTJOIN, but does not support arrays.
> Excel 2016 offline doesn't support TEXTJOIN (unless Microsoft puts it in a
> future update).

Still insisting: If Excel (365-2016 or whatever version) is returning an array in the mentioned case this is surely a bug and will be fixed by them with one of the next updates.
Comment 16 Winfried Donkers 2016-09-30 05:46:20 UTC
(In reply to Wolfgang Jäger from comment #15)

Comment #13 states the currently needed information. Until that information is present, no substantive statements with respect to the filed bug report are possible.
Comment 17 GerardF 2017-04-20 08:29:18 UTC
See this thread in OpenOffice forum and the links provided.
Comment 18 Dennis Roczek 2017-04-20 08:41:01 UTC
Hi Gerald,

did you miss to paste the url?
Comment 19 GerardF 2017-04-20 08:45:43 UTC
(In reply to Dennis Roczek from comment #18)
> Hi Gerald,
> 
> did you miss to paste the url?

Oops, sorry.
here it is :
https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=88407
Comment 20 Eike Rathke 2017-04-20 10:47:29 UTC
Finally something that sheds light.. there you linked an .xlsx https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1307-1311.xlsx (why didn't you post that link here as well?) which when opened without recalculation on sheet '(1309an)' in cell D5 has a TEXTJOIN in array context that delivers result in one cell and when recalculated spreads over several rows. The Excel behaviour makes sense and we should do the same.

Btw, your comment there
> Developers persist in denying evidence.
really pisses me off. No one denied evidence here, it's just that nobody came up with any evidence so far.
Comment 21 Eike Rathke 2017-04-20 11:07:29 UTC
I'll take care of this.
Comment 22 Commit Notification 2017-04-20 12:10:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT

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 24 Wolfgang Jäger 2017-04-20 17:21:15 UTC
(In reply to Eike Rathke from comment #20)
> Finally something that sheds light.. 
... ???
> Btw, your comment there
> > Developers persist in denying evidence.
> really pisses me off. ...

Would you mind to act your age? 

In fact the arguments an examples were rather clear for a long time now.  

Thanks for the fixing!
Comment 25 Commit Notification 2017-04-25 15:50:35 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1632b5cb4684834b1f4ce47ef47a827d2809ab61&h=libreoffice-5-2

Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT

It will be available in 5.2.8.

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 26 Commit Notification 2017-04-25 18:54:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2-7":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=611e94e56159f2753f1753c3d78ba1b528196c18&h=libreoffice-5-2-7

Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT

It will be available in 5.2.7.

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 27 Commit Notification 2017-05-02 15:44:19 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9e76cc9da61dbe8ee5bbcac5c4378c5ba4fb4d21&h=libreoffice-5-3

Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT

It will be available in 5.3.4.

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 28 Commit Notification 2017-05-02 16:55:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c4ad6a24329446b374a7f01ab9a2214219d3590e&h=libreoffice-5-3-3

Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT

It will be available in 5.3.3.

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 29 Wolfgang Jäger 2017-05-09 11:02:26 UTC
(In reply to Commit Notification from comment #28)
> Eike Rathke committed a patch related to this issue.
> It has been pushed to "libreoffice-5-3-3":
> 
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=c4ad6a24329446b374a7f01ab9a2214219d3590e&h=libreoffice-5-3-3
> 
> Resolves: tdf#99625 add parameter classification for TEXTJOIN and CONCAT
> 
> It will be available in 5.3.3.
> 
> 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.

Tested with V5.3.3.1RC. 

Bugfix not working there. See new attachment.  
I reopened the bug therefore despite the fact that I did not test with any different vesrion.
Comment 30 Wolfgang Jäger 2017-05-09 11:03:08 UTC
Created attachment 133186 [details]
Demo: Bug still living in V5.3.3.1RC
Comment 31 GerardF 2017-05-09 14:21:39 UTC
(In reply to Wolfgang Jäger from comment #30)
> Created attachment 133186 [details]
> Demo: Bug still living in V5.3.3.1RC

Works forme with :
Version: 5.2.7.2
Build ID: 2b7f1e640c46ceb28adf43ee075a6e8b8439ed10
Threads CPU : 2; Version de l'OS :Windows 6.0; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: single
Comment 32 Winfried Donkers 2017-05-10 06:32:05 UTC
Created attachment 133209 [details]
File with TEXTJOIN entered as array formula, saved by Excel365

For comparison: I opened attachment 124781 [details] in Excel365-2016 and saved it as xlsx document.



Note: both when saving the ods as xlsx and when saving the xlsx after a small change, I got a warning, which translates to something like 
"TEXTJOIN_CSE.xlsx contains calculation settings that are not supported in this file type. With some formulas different results may be returned after saving. Do you want to continue?"
This warning does not seem to be related to TEXTJOIN, as the warning still appears after removing all TEXTJOIN formulas.
Comment 33 Wolfgang Jäger 2017-05-10 21:32:55 UTC
Works now for me, too with V5.3.3.2RC. 
Set to RESOLVED FUXED again.