Bug 148948 - Wrong "Empty Cell" match with "=" and "<>" criterion
Summary: Wrong "Empty Cell" match with "=" and "<>" criterion
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.4.0 target:7.3.4
Keywords:
Depends on:
Blocks:
 
Reported: 2022-05-05 10:09 UTC by Mike Kaganski
Modified: 2022-05-09 12:18 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
An XLSX with COUNTIFS and empty string cells (8.56 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2022-05-05 10:09 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2022-05-05 10:09:18 UTC
Created attachment 179943 [details]
An XLSX with COUNTIFS and empty string cells

The attachment contains these formulas in A1 and B1:

=COUNTIFS(A2:A10;"=")
=COUNTIFS(B2:B10;"<>")

A2 and B2 are both:

=""

A3:B10 are all empty.

Excel gives 8 in A1, and 1 in B1.
Calc gives (after recalc): 9 in A1, 0 in B1.

Excel's behavior matches ODF v1.3, which tells:

OASIS ODF 1.3 part 4 sect. 4.11.8 Criterion [1]
> For <>, if the value is empty it matches non-empty cells.

OASIS ODF 1.3 part 4 sect. 4.7 Empty Cell [2]
> An empty cell is neither zero nor the empty string, and an empty cell can be
> distinguished from cells containing values (including zero and the empty
> string). An empty cell is not the same as an Error, in particular, it is
> distinguishable from the Error #N/A (not available).

So this is both standard-incompliance and interoperability bug in Calc.

Tested with Version: 7.3.3.2 (x64) / LibreOffice Community
Build ID: d1d0ea68f081ee2800a922cac8f79445e4603348
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL

[1] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#Criterion
[2] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#EmptyCell
Comment 1 Julien Nabet 2022-05-05 18:52:38 UTC
On pc Debian x86-64 with master sources updated today and new profile, I could reproduce this.

This part should interest you:
https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/queryparam.cxx?r=14001bc7#263

At line 263 rItem.meType = ScQueryEntry::ByValue
then it's changed line 264 to ScQueryEntry::ByString

If I remove this change and so if we keep ScQueryEntry::ByValue, the recalc is ok.

A comment block below tells:
"
266      /* TODO: pFormatter currently is also used as a flag whether matching
267       * empty cells with an empty string is triggered from the interpreter.
268       * This could be handled independently if all queries should support
269       * it, needs to be evaluated if that actually is desired. */
"
Comment 2 Mike Kaganski 2022-05-06 05:54:15 UTC
Thanks Julien!

Related:
Commit 01b615687fe0f39c65e0e8290db434db2f1ef8ac
  Author Eike Rathke <erack@redhat.com>
  Date   Tue Mar 31 14:01:42 2015 +0200
    Resolves: tdf#35636 implement match on empty cells
Comment 3 Commit Notification 2022-05-09 07:47:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/920a083f3818765528899ab38170db5a2917a06c

Resolves: tdf#148948 Use QueryBy(Non)Empty for "=" and "<>" criteria

It will be available in 7.4.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 4 Eike Rathke 2022-05-09 07:48:10 UTC
Pending review https://gerrit.libreoffice.org/c/core/+/134054 for 7-3
Comment 5 Commit Notification 2022-05-09 09:13:14 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-3":

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

Resolves: tdf#148948 Use QueryBy(Non)Empty for "=" and "<>" criteria

It will be available in 7.3.4.

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 6 Commit Notification 2022-05-09 12:18:31 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8bd91a40eae95f38c15d570d6af87b997c7eee39

tdf#148948: sc_statistical_functions: Add unittest

It will be available in 7.4.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.