Bug 70806 - EDITING: DCOUNT and DCOUNTA function parameter.
Summary: EDITING: DCOUNT and DCOUNTA function parameter.
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: BSA target:5.3.0 target:5.2.0.1
Keywords:
Depends on:
Blocks:
 
Reported: 2013-10-23 18:09 UTC by John Smith
Modified: 2016-10-25 18:55 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
An ODT document, illustrating with a screenshot, the situation described above. (28.15 KB, application/vnd.oasis.opendocument.text)
2013-10-23 18:09 UTC, John Smith
Details
Spreadsheet illustrating the problem described. (11.87 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-10-23 18:25 UTC, John Smith
Details

Note You need to log in before you can comment on or make changes to this bug.
Description John Smith 2013-10-23 18:09:37 UTC
Created attachment 88047 [details]
An ODT document, illustrating with a screenshot, the situation described above.

Problem description: 
The DCOUNT and DCOUNTA functions have a parameter 'Database field', stated as 'required' which appears to be optional.

Steps to reproduce:
1. Open an instance of Calc and launch the Function Wizard.
2. Select the Database category and double-click on DCOUNT or DCOUNTA.
3. Click in the Database field input box to display the explanation.

Current behavior:
It states that this parameter is required. It can be omitted in both functions without returning an error and with the correct result being returned.
Expected behavior:
Either the instructions for both functions are wrong and the parameter is optional, or the functions have a bug which fails to return an error.
The Help file text for DCOUNT specifically states the parameter cannot be omitted. The Help text for DCOUNTA is not specific about it, but that conclusion can be drawn from the phrasing.
It appears that as long as the Search criteria parameter includes the field heading a result will be returned.
              
Operating System: Windows XP
Version: 4.1.2.3 release
Comment 1 John Smith 2013-10-23 18:25:32 UTC
Created attachment 88048 [details]
Spreadsheet illustrating the problem described.
Comment 2 Dominique Boutry 2013-10-27 20:25:02 UTC
Let's examine the behaviour of the function DCOUNT or DCOUNTA :
1/ first, it applies a selection on the DB lines (the DB is the fisrt argument, the criteria is the third argument),
2/ secondly, it counts the number of selected lines which have one column (pointed to by the second argument, by name or by rank from 1 to N) or the totality of the columns (second argument = 0 or omited) containing a value either numeric (DCOUNT) or non empty (DCOUNTA).

I added "or omited" because it is what we see and it is logical. So NotABug, but a clarification to do in the documentation.
Comment 3 John Smith 2013-10-29 19:07:18 UTC
Hi Dominique.
I'm working with the LO Docs team doing the User Guides, but not the Help documentation. I'm trying to determine whether the documentation is wrong in its description or the function is buggy. I'm quite happy to write up the User Guide to indicate this is an optional parameter if that's the case. Thanks,  JS
Comment 4 bfoman (inactive) 2013-11-20 22:01:07 UTC
@Dominique Boutry and John Smith: if it is NOTABUG and only documentation correction is needed can we close this bug and report new one for docs/help fix?
Comment 5 John Smith 2013-11-21 21:37:03 UTC
(In reply to comment #4)
> @Dominique Boutry and John Smith: if it is NOTABUG and only documentation
> correction is needed can we close this bug and report new one for docs/help
> fix?

I am of the opinion that this is a bug having read through the specification here; 
http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#DCOUNT
which shows no parameter to be optional.
Comment 6 Robinson Tryon (qubit) 2014-12-24 02:52:29 UTC
Winfried: Your insight regarding these functions would be appreciated. Thanks!
Comment 7 Winfried Donkers 2014-12-24 07:14:23 UTC
(In reply to Robinson Tryon (qubit) from comment #6)
> Winfried: Your insight regarding these functions would be appreciated.
> Thanks!

I will have a look at the ODF1.2 standard and the code, but probably not this week.
Comment 8 Winfried Donkers 2014-12-24 12:14:48 UTC
(In reply to Robinson Tryon (qubit) from comment #6)

My conclusion after a quick look in the code base is that the code explicitly allows the absence of the field argument, as long as the criteria argument produces valid results (DCOUNT as well as DCOUNTA).

The ODF1.2 standard does not mark any argument optional.

One could wonder if this is a violation of the standard or functionality that is more than required by the standard. We have more functions in Calc that offer more functionality than is required by the standard.

The code has not been changed since the start of LibreOffice, the last change to the DCOUNT/DCOUNTA code is from March 2010.

I would suggest to change the (help) documentation to reflect its actual functionality, which IMHO is more user friendly than strict coherence to the standard. But then, I'm only a developer ;-)
Comment 9 Robinson Tryon (qubit) 2014-12-24 17:11:18 UTC
(In reply to Winfried Donkers from comment #8)
> (In reply to Robinson Tryon (qubit) from comment #6)
> 
> My conclusion after a quick look in the code base is that the code
> explicitly allows the absence of the field argument, as long as the criteria
> argument produces valid results (DCOUNT as well as DCOUNTA).
> 
> The ODF1.2 standard does not mark any argument optional.
> 
> One could wonder if this is a violation of the standard or functionality
> that is more than required by the standard. We have more functions in Calc
> that offer more functionality than is required by the standard.

So strictly speaking, it sounds like the code disagrees with the standard.

> The code has not been changed since the start of LibreOffice, the last
> change to the DCOUNT/DCOUNTA code is from March 2010.
> 
> I would suggest to change the (help) documentation to reflect its actual
> functionality, which IMHO is more user friendly than strict coherence to the
> standard. But then, I'm only a developer ;-)

Fair enough. That solution seems reasonable to me, especially if we were to make a note of our deviation from the standard the way that we do with extensions to ODF:
https://wiki.documentfoundation.org/Development/ODF_Implementer_Notes/List_of_LibreOffice_ODF_Extensions

In any case, it seems clear that there's some real work to be done here (Documentation, development, or etc..), so Status -> NEW.
Comment 10 Alex Thurgood 2015-01-03 17:38:49 UTC Comment hidden (no-value)
Comment 11 QA Administrators 2016-01-17 20:03:38 UTC Comment hidden (no-value, obsolete)
Comment 12 Eike Rathke 2016-05-25 12:07:02 UTC
ODFF doesn't specify the Field parameter optional, but Excel accepts an empty missing argument, which is why Calc also does. Furthermore, at least in the DCOUNT case Excel behaves different if the argument is specified or missing. If missing, DCOUNT behaves like DCOUNTA on the matching set no matter whether there is numeric content or not, i.e. all rows that are matched by the criteria are counted.

These things need clarification in the help documentation and the Field argument value of 0 shouldn't be mentioned at all, it caters for old versions and is incompatible with other implementations. Additionally the ODFF specification needs to be adapted for the empty missing argument case.
Comment 13 Commit Notification 2016-05-26 21:58:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/help/commit/?id=f5f2b4982350ee6e0616d49742b29b0a57edbbbb

tdf#70806 get rid of the "number 0 for the entire database" nonsense
Comment 14 Commit Notification 2016-05-26 22:42:58 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/help/commit/?id=c1724f5eeef808cefac89d5101c25f06aab1a55a&h=libreoffice-5-2

tdf#70806 get rid of the "number 0 for the entire database" nonsense
Comment 15 Eike Rathke 2016-05-27 09:35:36 UTC
Submitted to the ODF-TC comment list:

6.9.3 DCOUNT
Syntax: DCOUNT( Database D ; Field F ; Criteria C )

6.9.4 DCOUNTA
Syntax: DCOUNTA( Database D ; Field F ; Criteria C )

For these two functions the Field argument can be omitted (empty) in
LibreOffice Calc and Microsoft Excel, it should be defined as

Syntax: DCOUNT( Database D ; [ Field F ] ; Criteria C )

Semantics: If the Field argument is omitted, DCOUNT returns the count of
           all records that satisfy Criteria.


Syntax: DCOUNTA( Database D ; [ Field F ] ; Criteria C )

Semantics: If the Field argument is omitted, DCOUNTA returns the count
           of all records that satisfy Criteria.
Comment 16 Commit Notification 2016-05-27 10:27:26 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/help/commit/?id=5b1cb4f0bfbae734818ae791165cd1ea7ac5fdcb

tdf#70806 DCOUNT and DCOUNTA DatabaseField argument can be omitted
Comment 17 Commit Notification 2016-05-27 10:33:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/help/commit/?id=a3f89c38a446d74b5327544de7b753964ce94a29&h=libreoffice-5-2

tdf#70806 DCOUNT and DCOUNTA DatabaseField argument can be omitted
Comment 18 Commit Notification 2016-05-27 16:10:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

omit DCOUNT or DCOUNTA 2nd argument if 0 when writing ODFF or OOXML, tdf#70806

It will be available in 5.3.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 19 Commit Notification 2016-05-27 16:45:25 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=acb0f28e97874b22bd899e5cf04f2a8363a42e7a&h=libreoffice-5-2

omit DCOUNT or DCOUNTA 2nd argument if 0 when writing ODFF or OOXML, tdf#70806

It will be available in 5.2.0.1.

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 20 Commit Notification 2016-05-27 19:59:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

strip 0 argument only if not part of an expression, tdf#70806

It will be available in 5.3.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 21 Commit Notification 2016-05-27 20:27:02 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=88630f20d1f26d74980e81721cd621b6f309a9ec&h=libreoffice-5-2

strip 0 argument only if not part of an expression, tdf#70806

It will be available in 5.2.0.1.

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 22 Commit Notification 2016-06-10 10:58:22 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

check for svDouble when stripping DCOUNT/A argument, tdf#70806 follow-up

It will be available in 5.3.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 23 Commit Notification 2016-06-10 11:00:53 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=bfef572871441a8cdc9509e26afbef208e4f6243&h=libreoffice-5-2

check for svDouble when stripping DCOUNT/A argument, tdf#70806 follow-up

It will be available in 5.2.0.1.

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.