Bug 73080 - COUNTBLANK counts formula with reference to blank cell
Summary: COUNTBLANK counts formula with reference to blank cell
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86 (IA32) All
: high normal
Assignee: Not Assigned
Keywords: bibisected, regression
Depends on:
Reported: 2013-12-27 18:16 UTC by Regina Henschel
Modified: 2015-12-15 11:03 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Examples with blank cells and empty strings (13.62 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2014-10-20 20:44 UTC, Regina Henschel

Note You need to log in before you can comment on or make changes to this bug.
Description Regina Henschel 2013-12-27 18:16:08 UTC
Make sure B2 is empty, that means, that it does not contain any content.
Write the formula =B2 into cell B3
Write the formula =COUNTBLANK(B2:B3) into cell A1.
Expected result: A1 shows number 1.
Seen result: A1 shows number 2.

The content of cell B3 is a formula, therefore it is not empty and may not be counted. The specification only allows the exception, that a cell, which contains a zero-length string, may be counted by COUNTBLANK.

Apache OpenOffice, Gnumeric, and Excel does not count cell B3 and return 1 in the example given above.

Suggestion: LibreOffice does not count cell which has a formula that refers an empty cell.
Comment 1 Dominique Boutry 2014-01-13 13:13:47 UTC
It is mandatory to adher to ISO/CEI 26300 norm, but this norm leaves an open implementation-defined on the subject.

I support the suggestion of Regina, for :
- facilitating the application port to/from other spreadsheets,
- the consistency with "ISBLANK".
Comment 2 A (Andy) 2014-09-20 13:12:37 UTC
also for me this seems to be inconsistent if COUNTBLANK returns 2 but ISBLANK returns another result
Comment 3 ign_christian 2014-09-20 14:06:30 UTC
Still reproduced with LO - Ubuntu 12.04 x86

Not reproduced with LO and below
Comment 4 raal 2014-10-06 19:10:10 UTC
7be7cf83087144563a18000acdae82c8fd6f4872 is the first bad commit
commit 7be7cf83087144563a18000acdae82c8fd6f4872
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Mon Dec 10 00:13:53 2012 +0000

    commit d59024b652ccfaf7247da113ec36788fe260de74
    Author:     Michael Stahl <mstahl@redhat.com>
    AuthorDate: Thu Jul 12 22:18:43 2012 +0200
    Commit:     Michael Stahl <mstahl@redhat.com>
    CommitDate: Thu Jul 12 22:18:43 2012 +0200
        warning C4101: unreferenced local variable
        Change-Id: I0648821a4d0c716371bb011df8cd9b21db79ccf5

:100644 100644 2e11de60e03551ff9cb9202630fabddffd9b4d03 13d66368624db1158439984d29b9b6b4e382edc1 M	autogen.log
:100644 100644 1aef3215ed700bc4e85fd12d18339c24ebf234a7 1c9624dd465ee0e59acd9c9dd05950b51be0c666 M	ccache.log
:100644 100644 f13bcc6696e46ed88900fa5b2390642319998036 b55a2bd9212e07b4042ebd0bb46515c8a4e76c04 M	commitmsg
:100644 100644 9d65d2f7c48177028e23641bc3eb0528844456f9 5270492409e164dc751ec11e4524f63b7739b35f M	dev-install.log
:100644 100644 9134f8edd2ee62e54d25821b3cc071e2aee24b5a f188090f0a4137ab411a3ff359ec440f0da4644c M	make.log
:040000 040000 e1e8f612101470e3ff2f6a53fc8c5e664103243f bb164bd44517d96b184ae0ecb1e8ccaf46ba0b4d M	opt

 git bisect log
# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# bad: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
git bisect bad e02439a3d6297a1f5334fa558ddec5ef4212c574
# bad: [8f4aeaad2f65d656328a451154142bb82efa4327] source-hash-1885266f274575327cdeee9852945a3e91f32f15
git bisect bad 8f4aeaad2f65d656328a451154142bb82efa4327
# good: [369369915d3582924b3d01c9b01167268ed38f3b] source-hash-45295f3cdceb4c289553791071b5d7f4962d2ec4
git bisect good 369369915d3582924b3d01c9b01167268ed38f3b
# good: [6fce03a944bf50e90cd31e2d559fe8705ccc993e] source-hash-47e4a33a6405eb1b5186027f55bd9cb99b0c1fe7
git bisect good 6fce03a944bf50e90cd31e2d559fe8705ccc993e
# bad: [da317333e5675622f55c9dda17396c659af65320] source-hash-15af925c254f27046427de70a59011e2ac3d6bdb
git bisect bad da317333e5675622f55c9dda17396c659af65320
# good: [daa21bbd8c7b50e2ca1c2cbed0e39f0e7b5a1cb2] source-hash-6b11a18071254a443c8fe7e7b0b1c95b0f9fd35e
git bisect good daa21bbd8c7b50e2ca1c2cbed0e39f0e7b5a1cb2
# good: [a08143f4bae3d6658dd756b42b6f343298d1f48c] source-hash-b7822657fa67e7265d07f5852057e975e9efae0d
git bisect good a08143f4bae3d6658dd756b42b6f343298d1f48c
# good: [5255e1fbf1f3fa3ca61c4db3005940205577863c] source-hash-63c004fc3f0bc53ce888ef012f7993b9203a7503
git bisect good 5255e1fbf1f3fa3ca61c4db3005940205577863c
# bad: [7be7cf83087144563a18000acdae82c8fd6f4872] source-hash-d59024b652ccfaf7247da113ec36788fe260de74
git bisect bad 7be7cf83087144563a18000acdae82c8fd6f4872
# good: [ad874a5319e9f68e6b3a974e44de838b8a0a82e1] source-hash-4b4ca8030285bd66526ff5bb2b6ea5a75a6c6bc7
git bisect good ad874a5319e9f68e6b3a974e44de838b8a0a82e1
# first bad commit: [7be7cf83087144563a18000acdae82c8fd6f4872] source-hash-d59024b652ccfaf7247da113ec36788fe260de74
Comment 5 Kohei Yoshida 2014-10-16 00:06:55 UTC
Taking this.
Comment 7 Kohei Yoshida 2014-10-16 00:15:51 UTC
4.3 backport request: https://gerrit.libreoffice.org/11993
Comment 8 Kohei Yoshida 2014-10-16 00:16:25 UTC
I've written a unit test for this, so this corner case is covered.
Comment 9 Kohei Yoshida 2014-10-20 13:01:45 UTC
Regina, could you check with Eike (and vise versa)?  He says in the gerrit review that

"Um.. what I meant was that ="" should not be counted as blank/empty cell at all, the previous revision of this patch introduced that for range references, and this revision now introduces it also for single references, this was not the case before and not an existing issue. This changes behaviour. However, ISBLANK() does not yield TRUE for ="" and COUNTBLANK() should match that."

That contradicts with what you originally said which is

"The specification only allows the exception, that a cell, which contains a zero-length string, may be counted by COUNTBLANK."

Comment 10 Kohei Yoshida 2014-10-20 13:15:20 UTC
Perhaps this is another item that should go to the calculation settings.  Excel counts ="" as blank in COUNTBLANK at least.
Comment 11 Eike Rathke 2014-10-20 18:01:35 UTC
Which probably was the reason to have that implementation-defined in ODFF. I agree that we should align with Excel and Gnumeric in the behavior, but changing it in a micro release might not be good. I suggest to

* for 4.3 fix the reference to empty cell to not be counted, which the
  original bug report is about, and not touch the ="" behavior
* for 4.4 introduce yet another compatibility calculation setting for empty
  string returns
Comment 12 Regina Henschel 2014-10-20 20:44:00 UTC
Created attachment 108137 [details]
Examples with blank cells and empty strings

I agree with Eike.

My report is not about handling empty strings, but about handling a reference to an empty cell. That can be fixed in 4.3. code line.

Handling of empty strings should not be changed in a micro release and only with compatibility options. Empty strings are widely used and changing behavior must not break existing spreadsheets; especially as it is not an error but an implementation dependent behavior. That is not suitable for a micro release.

The attached spreadsheet contains several test usages of ISBLANK and COUNTBLANK. [BTW: I have detected a similar bug for ISBLANK, issue will follow.]
Comment 13 Kohei Yoshida 2014-10-21 01:28:06 UTC
Fair enough.  I've abandoned my backport to 4.3.
Comment 14 Joel Madero 2014-11-02 16:37:21 UTC
Can this be closed as FIXED?
Comment 15 Regina Henschel 2014-11-02 18:31:40 UTC
I have tested Version:
Build ID: b167d1127e1579468a74c4414e7f4f33d9d24122
TinderBox: Win-x86@51-TDF, Branch:MASTER, Time: 2014-11-02_03:04:44

The error, that references to empty cells are count, is fixed.

But there has been introduced a new behavior, that empty strings are count. This was introduced without the compatibility option Eike mentioned. And when opening an older document saved by OpenOffic.org, this is recalculated to the new behavior, although the option "never recalculate" is selected.

Without a proper compatibility option as document setting, and an automatic detection for older documents, counting cells with empty strings should not be implemented. In addition the help should mention counting empty strings, because it is an implementation dependent feature. When you decide to close this issue, a follow up issue for the compatibility of the new behavior is needed.
Comment 16 QA Administrators 2014-11-02 19:11:47 UTC
(In reply to Regina Henschel from comment #15)
> I have tested Version:
> Build ID: b167d1127e1579468a74c4414e7f4f33d9d24122
> TinderBox: Win-x86@51-TDF, Branch:MASTER, Time: 2014-11-02_03:04:44
> The error, that references to empty cells are count, is fixed.

Then can we close this and create a new bug?
Comment 17 Regina Henschel 2014-11-02 20:40:52 UTC
Comment 18 Robinson Tryon (qubit) 2015-12-15 11:03:23 UTC
Migrating Whiteboard tags to Keywords: (bibisected)