Bug 34242 - COUNTIF gives inconsistent counts of empty arrays
Summary: COUNTIF gives inconsistent counts of empty arrays
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: All All
: medium normal
Assignee: Winfried Donkers (retired)
URL:
Whiteboard: target:4.5.0 4.4.1
Keywords:
Depends on:
Blocks:
 
Reported: 2011-02-13 17:33 UTC by Patrick Oltmann
Modified: 2015-12-22 01:32 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Example sheet with COUNTIF functions giving inconsistent results marked in red (8.83 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-02-13 17:33 UTC, Patrick Oltmann
Details
Example sheet with COUNTIF functions giving inconsistent results marked in red (9.90 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-26 11:32 UTC, Winfried Donkers (retired)
Details
Example sheet with functions giving inconstent results (10.86 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-27 08:45 UTC, Winfried Donkers (retired)
Details
Example sheet with functions giving inconstent results (11.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-12-03 07:43 UTC, Winfried Donkers (retired)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Patrick Oltmann 2011-02-13 17:33:02 UTC
Created attachment 43326 [details]
Example sheet with COUNTIF functions giving inconsistent results marked in red

COUNTIF should count empty cells belonging to an array both as zero (0) and as
empty string (""). Therefore COUNTIF(<array>;0) and COUNTIF(<array>;"") should
yield the same results if the cells of <array> are empty (e.g. <array> {={||}}).
However, the upper left cell of the array is not counted as zero but is counted
as empty string, thus the results of both functions differ by one.

This bug apparently already existed in OpenOffice for quite some time. It has been filed there as Issue 108991
[http://qa.openoffice.org/issues/show_bug.cgi?id=108991]
but was never confirmed.
Comment 1 Midiar 2011-08-15 11:50:10 UTC
This may be related to bug 40110.
Comment 2 Björn Michaelsen 2011-12-23 11:45:53 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 3 Patrick Oltmann 2011-12-24 19:00:47 UTC
(In reply to comment #2)
The problem still exists in LibO 3.5.0 beta2.
Comment 4 Patrick Oltmann 2012-03-20 07:55:43 UTC
I did further testing on Win7-32, Win7-64, MacOS10.6 both on x86 and x64-32 platforms and the bug was reproducible everywhere with LibO 3.5.1. Thus the updates in the header information.
Comment 5 Patrick Oltmann 2012-07-22 22:58:58 UTC
The problem still exists in LibO 3.6.0 beta2.
Comment 6 Patrick Oltmann 2012-07-30 01:15:22 UTC
I was told that the "version" field should hold the first confirmed version with a bug, not the most recent one. There I reverted it to 3.3.0 (the bug was already present in OpenOffice).
Comment 7 Patrick Oltmann 2013-01-29 16:38:00 UTC
The problem still exists in LibO 4.0.0.2 rc.
Comment 8 Patrick Oltmann 2013-04-10 18:19:49 UTC
The problem is still present in LibO 4.0.2.2. "Version" field was reverted to "3.3.0 release" (see comment #6 on this).
Comment 9 Winfried Donkers (retired) 2014-11-25 11:46:53 UTC
@Tobias Lippert: 
Are you working on this bug? 
If yes, could you please set the status to ASSIGNED?
If no, do you mind if I take it to fix? No guarantees ;-)
Comment 10 Tobias Lippert 2014-11-25 11:58:13 UTC
Hello Winfried, 
Please go ahead and take the bug. 
Tobias
Comment 11 Winfried Donkers (retired) 2014-11-25 12:13:30 UTC
Tobias, adding you to cc, so you can still monitor the bug.
Comment 12 Winfried Donkers (retired) 2014-11-26 11:32:38 UTC
Created attachment 110057 [details]
Example sheet with COUNTIF functions giving inconsistent results marked in red

Added some more examples of unexpected behaviour, all in the case of an array with empty elements as data range.
Comment 13 Winfried Donkers (retired) 2014-11-27 08:45:34 UTC
Created attachment 110120 [details]
Example sheet with functions giving inconstent results

The attachment shows that COUNTIF is not inconsistent, but that the range being a simple range or an array determines the inconsistency.
Functions like ISTEXT and even direct comparisons are quite consistent.
The array notation makes that evaluation of the cell is performed in a different way.
Comment 14 Winfried Donkers (retired) 2014-11-27 08:48:39 UTC
@Eike:
Do you recognise what's in comment #13 and  attachment 110120 [details]  ? 
If so, could you give me code or class pointer?
If not, I'll simply keep digging ;-)
Comment 15 Winfried Donkers (retired) 2014-11-28 07:48:48 UTC
The behaviour is caused by the fact that the first array cell is filled differently than the rest of the array cells:

     <table:table-cell table:number-matrix-columns-spanned="1" table:number-matrix-rows-spanned="3" table:formula="of:={||}">
      <text:p/>
     </table:table-cell>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell office:value-type="float" office:value="0" calcext:value-type="float">
      <text:p/>
     </table:table-cell>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell office:value-type="float" office:value="0" calcext:value-type="float">
      <text:p/>
     </table:table-cell>

The first array cell is considered to be a formula (containing the definition of the array), and the other cells are numeric cells (empty, leading to a value of 0).
That explains the behaviour of COUNTIF, (cell=0), ISTEXT and ISNONTEXT.
I still want to investigate the behaviour of ISBLANK and ISNUMBER.

Knowing the cause does not mean I know the solution yet; fixing this problem must not break other functionalities.
Comment 16 Winfried Donkers (retired) 2014-12-03 07:43:41 UTC
Created attachment 110394 [details]
Example sheet with functions giving inconstent results

Expanded the sheet with some more examples.
It seems that empty cells produce inconsistent results, too (cells F39-F45).
Most inconsistent array results can be explained with the way an array is stored (see comment #15), but not all (cells L27-L29).
Comment 17 Winfried Donkers (retired) 2014-12-03 11:39:59 UTC
Unfortunately, I see no decent way to correct this in a reasonable time.
Therefore I reverted the status to NEW.
Hopefully, someone will fix this before I find the solution.
Comment 18 Winfried Donkers (retired) 2015-02-20 10:12:19 UTC
Found new possibilities as there are some relations with bug 85258.
Comment 19 Winfried Donkers (retired) 2015-02-23 16:47:57 UTC
Problem resolved with the bug fix for bug 39316:

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

Currently in master and 4-4, pending review for 4-3.
Comment 20 Robinson Tryon (qubit) 2015-12-22 01:32:52 UTC
Removing comma from Whiteboard (please use a space to delimit values in this field)
https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Whiteboard#Getting_Started
[NinjaEdit]