Bug 145235 - TEXTJOIN Function gives incorrect results for referenced empty cells
Summary: TEXTJOIN Function gives incorrect results for referenced empty cells
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.0.4 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.3.0 target:7.2.3 target:7.1.7
Keywords:
Depends on:
Blocks:
 
Reported: 2021-10-20 08:40 UTC by Bob Garrett
Modified: 2021-10-27 03:17 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Bob Garrett 2021-10-20 08:40:58 UTC
Description:
Incorrect result to a textjoin function where one cell is calculated from another.

Steps to Reproduce:
Create a spreadsheet where...
cell A1 contains A
cell A2 contains formula =B2
cell B2 is empty
cell A3 contains C
cell A4 contains =TEXTJOIN("-",1,A1:A3)


Actual Results:
The result shown in A4 is "A-A-C" 

Expected Results:
It should be "A-C"


Reproducible: Always


User Profile Reset: No



Additional Info:
Give the correct result
Comment 1 Kevin Suo 2021-10-20 09:01:15 UTC
Reproducible on 
Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: c82aa206ab496248dd107c41ff5f66ec5a2399ac
CPU threads: 8; OS: Linux 5.14; UI render: default; VCL: gtk3
Locale: zh-CN (zh_CN.UTF-8); UI: zh-CN
Build Platform: Fedora34@X64, Branch:master, bibisect-linux-64-7.3-CN
Calc: threaded

Set status to NEW.
Comment 2 Eike Rathke 2021-10-20 09:19:52 UTC
"A-A-C" is of course wrong, but A2 is not empty (and displays 0) so I'd guess the result should be "A-0-C" instead.
Or what does Excel do?
Comment 3 Xisco Faulí 2021-10-20 09:27:30 UTC
(In reply to Eike Rathke from comment #2)
> "A-A-C" is of course wrong, but A2 is not empty (and displays 0) so I'd
> guess the result should be "A-0-C" instead.
> Or what does Excel do?

NISZ Team, do you have Office 2019  to test it ?
Comment 4 Bob Garrett 2021-10-20 09:38:07 UTC
Quite right that A2 would contain 0 - though for me it did not as I had 0s being hidden.
Off the topic/bug report but should a reference to an empty cell show 0?
Comment 5 Xisco Faulí 2021-10-20 09:46:14 UTC
Google Sheets returns A-C
Comment 6 Eike Rathke 2021-10-20 09:56:26 UTC
(In reply to Bob Garrett from comment #4)
> Off the topic/bug report but should a reference to an empty cell show 0?
Yes. That's what all major spreadsheet implementations do. And the main reason why that display zero values option exists.
Comment 7 Commit Notification 2021-10-20 14:39:07 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/f4f2c94513e7d06691a73d9f12707c33d131d537

Resolves: tdf#145235 TEXTJOIN() clear last string also for referenced empty

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2021-10-20 16:27:20 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/56d750a5c0f857aedf036f0cd49cfad2de9238ae

tdf#145235: sc_text_functions_test: Add unittest

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2021-10-20 16:28:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/fb2b0e1836431f9c16d7125dfd3f596436899134

Resolves: tdf#145235 TEXTJOIN() clear last string also for referenced empty

It will be available in 7.2.3.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2021-10-20 16:28:48 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

https://git.libreoffice.org/core/commit/045b1137b855b59c166212dcad10dce9c067dd00

Resolves: tdf#145235 TEXTJOIN() clear last string also for referenced empty

It will be available in 7.1.8.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2021-10-20 16:51:08 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/45f53338461ba61c53c51a04f99fa281bf5150ae

Related: tdf#145235 TEXTJOIN() allow empty delimiter of referenced empty

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2021-10-20 20:16:57 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/4a8184ce0cc59e4e223cf963141c16644b762440

tdf#145235: sc_text_functions_test: Add unittest for empty delimiter

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Xisco Faulí 2021-10-21 08:29:49 UTC
Verified in

Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: ea65215855462e5e48f7a3a78724c38b5fc0e4d1
CPU threads: 4; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

@Eike, thanks for fixing this issue!!
Comment 15 BogdanB 2021-10-21 15:02:55 UTC
I checked this bug, and indeed is solved when inserting the formula again, but if I save the document with an older version (which shows A-A-C) and reopen the document with 7.3 master (latest version) it is the same A-A-C. Only if I remove the formula and insert again it is correct, OR if I use Recalcule Hard (Shift + Control +F9).
Comment 16 Eike Rathke 2021-10-21 16:59:35 UTC
It has to be, because unless recalculated the result stored in file is displayed. Either hard, or via changed dependencies, or triggered by volatile functions in the expression or its dependencies.
Comment 17 Commit Notification 2021-10-26 10:13:14 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-1-7":

https://git.libreoffice.org/core/commit/989eb3461bd4ba8716da2c62386e4f044731ff16

Resolves: tdf#145235 TEXTJOIN() clear last string also for referenced empty

It will be available in 7.1.7.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.