Performing {SUM(Len(B7:B11))} against empty cells produces a non-zero result

Bug #1273659 reported by EliCoten
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
LibreOffice
Fix Released
Medium
libreoffice (Ubuntu)
Fix Released
Medium
Unassigned

Bug Description

1) lsb_release -rd
Description: Ubuntu Trusty Tahr (development branch)
Release: 14.04

2) apt-cache policy libreoffice-calc
libreoffice-calc:
  Installed: 1:4.2.0-0ubuntu1
  Candidate: 1:4.2.0-0ubuntu1
  Version table:
     1:4.2.1-0ubuntu1 0
        400 http://us.archive.ubuntu.com/ubuntu/ trusty-proposed/main amd64 Packages
 *** 1:4.2.0-0ubuntu1 0
        500 http://us.archive.ubuntu.com/ubuntu/ trusty/main amd64 Packages
        100 /var/lib/dpkg/status

3) What is expected to happen in Calc via a terminal:
cd ~/Desktop && wget -c https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1273659/+attachment/3962696/+files/Array%20Formula%20Bug.ods -O example.ods && localc --nologo example.ods

is cell B12 is 0, as it is in Gnumeric and Microsoft Office Excel 2003 (11.5612.8172).

4) What happens instead is it is 5.

---
ApportVersion: 2.12.5-0ubuntu2.2
Architecture: amd64
DistroRelease: Ubuntu 13.10
MarkForUpload: True
Package: libreoffice 1:4.1.3-0ubuntu1
PackageArchitecture: amd64
ProcVersionSignature: Ubuntu 3.11.0-15.23-generic 3.11.10
Tags: saucy
Uname: Linux 3.11.0-15-generic x86_64
UpgradeStatus: Upgraded to saucy on 2013-11-15 (75 days ago)
UserGroups: adm cdrom dip lpadmin plugdev sambashare sudo wireshark www-data

Revision history for this message
EliCoten (launchpad-elicoten) wrote :

apport-collect 1273659
The authorization page:
 (https://launchpad.net/+authorize-token?...N)
should be opening in your browser. Use your browser to authorize
this program to access Launchpad on your behalf.
Press any key to continue or wait (5) seconds...
Waiting to hear from Launchpad about your decision...

(process:7620): GLib-CRITICAL **: g_slice_set_config: assertion 'sys_page_size == 0' failed
Package libreoffice not installed and no hook available, ignoring

description: updated
Revision history for this message
penalvch (penalvch) wrote :

EliCoten, thank you for reporting this and helping make Ubuntu better. Please execute the following in a terminal:
sudo apt-get -y install libreoffice && apport-collect 1273659

As well, could you please attach a file that demonstrates this problem?

Changed in libreoffice (Ubuntu):
status: New → Incomplete
Revision history for this message
EliCoten (launchpad-elicoten) wrote : Dependencies.txt

apport information

tags: added: apport-collected saucy
description: updated
Revision history for this message
EliCoten (launchpad-elicoten) wrote : ProcEnviron.txt

apport information

Revision history for this message
EliCoten (launchpad-elicoten) wrote : Re: Array formula bug SUM(Len(A2:A200)) adds empty cells

See cell B12 of the attachment for an example of the problem

penalvch (penalvch)
Changed in libreoffice (Ubuntu):
status: Incomplete → New
Revision history for this message
In , Elicoten (elicoten) wrote :

In a sheet where the range A2:A200 contain text strings and some are empty, the array formula
{=SUM(Len(A2:A200))} adds one to the sum for every empty cell.

{=SUM(IF(ISBLANK(A2:A200),0,LEN(A2:K200)))}, however, works correctly - but the if(isblank()) shouldn't be necessary.

It seems to be more than just the SUM function, as I tried the QUARTILE function with LEN as an array function and I also got the result 1 when I was expecting 0. So perhaps any time you do an array function LEN() with empty cells, LEN gives 1 when it should give 0.

(Needless to say the nonarray variation where you use a range of cells to perform the same calculation e.g.
=LEN(A2)
=LEN(A3)
=LEN(A4)
=LEN(A5)
...
=LEN(A199)
=LEN(A200)
=SUM(B2:B200)

works fine.

See demonstration of the problem in cell B12 here: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1273659/+attachment/3962696/+files/Array%20Formula%20Bug.ods

Revision history for this message
In , Elicoten (elicoten) wrote :
Changed in df-libreoffice:
importance: Unknown → Medium
status: Unknown → New
Revision history for this message
In , Mariosv (mariosv) wrote :

Hi elicoten, thanks for reporting

Reproducible with:
Win7x64Ultimate
Version: 4.1.6.0.0+ Build ID: 1c33633ef18274bf384c74c492195519be83c05
until
Version: 4.3.0.0.alpha0+
Build ID: d84ccb39b744457cd47125beb4291c84223d5219
TinderBox: Win-x86@39, Branch:master, Time: 2014-02-22_10:05:06

Last working:
Versión 3.6.7.2 (ID de compilación: e183d5b)

penalvch (penalvch)
summary: - Array formula bug SUM(Len(A2:A200)) adds empty cells
+ Performing {SUM(Len(B7:B11))} against empty cells produces a non-zero
+ result
description: updated
tags: added: amd64 trusty
penalvch (penalvch)
Changed in libreoffice (Ubuntu):
importance: Undecided → Medium
status: New → Triaged
Changed in df-libreoffice:
status: New → Confirmed
Revision history for this message
In , Libreoffice-0 (libreoffice-0) wrote :

I'll take this. This bug was already present in 4.0 in fact.

Revision history for this message
In , Libreoffice-0 (libreoffice-0) wrote :
Revision history for this message
In , Libreoffice-commits (libreoffice-commits) wrote :

Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#75397: Write unit test for this.

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.

Revision history for this message
In , Libreoffice-commits (libreoffice-commits) wrote :

Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#75397: Return an empty string for empty element.

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.

Revision history for this message
In , Libreoffice-0 (libreoffice-0) wrote :
Revision history for this message
In , Libreoffice-commits (libreoffice-commits) wrote :

Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=473e10aa5e1e3c6a4854dec9ff2ea522be283799&h=libreoffice-4-2

fdo#75397: Return an empty string for empty element.

It will be available in LibreOffice 4.2.5.

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.

Revision history for this message
In , Libreoffice-0 (libreoffice-0) wrote :

Fixed.

Revision history for this message
In , Libreoffice-commits (libreoffice-commits) wrote :

Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a226019bb5431ca04cae28fefb6b83e2bb738243&h=libreoffice-4-1

fdo#75397: Return an empty string for empty element.

It will be available in LibreOffice 4.1.7.

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.

Changed in df-libreoffice:
status: Confirmed → Fix Released
Revision history for this message
In , Mariosv (mariosv) wrote :

Thanks Kohei,
Verified with:
Win7x64Ultimate
Version: 4.2.5.0.0+ Build ID: 59906c3d54e6541185f4bf85b1d1c70530198059
   TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-04-30_09:30:13
Version: 4.3.0.0.alpha1+ Build ID: 20778b037c688759a4dc46acb8aeb66c9d1290cc
   TinderBox: Win-x86@39, Branch:master, Time: 2014-05-03_23:16:12

Changed in libreoffice (Ubuntu):
status: Triaged → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.