Bug 149071 - SUMIFS generates wrong result
Summary: SUMIFS generates wrong result
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) alpha0+
Hardware: All Linux (All)
: high critical
Assignee: Not Assigned
Whiteboard: target:7.4.0
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Function-Sum
  Show dependency treegraph
Reported: 2022-05-13 12:33 UTC by Kevin Suo
Modified: 2022-05-15 08:51 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

test-sumifs.ods (38.95 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-05-13 12:33 UTC, Kevin Suo

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2022-05-13 12:33:56 UTC
Created attachment 180095 [details]

In the attached ods file, sheet "pivot" contains the Debit Amount and Credit Amount for each General Ledger account, and sheet "TB" tries to get the Debit/Credit totals in order to generate a Trial Balance, using SUMIFS formula.

If one modifies the Debit Amount or Credit Amount of any single cell in sheet "pivot", the Debit/Credit amount in TB will become 0 for the whole column.

The test ods document was generated using LibreOffice 7.3. It works OK in 7.3, thus this is a critical regression.

Steps to Reproduce:
1. Open the attached test ods file with current master.
Observe that in the highlighted range in sheet "TB", the SUMIFS results are correctly shown.
2. Go to sheet "pivot", modify the value in any single cell (e.g. change the value in D5 to 1).

Current Result:
The whole SUMIFS result range in sheet "TB" becomes zero!

Expected Result:
SUMIFS result should be correctly calculated.
i.e., if I change e.g. pivot.D5 to 1, then in sheet TB the result for account 1405001006 should be 1 and all other accounts should remain unchanged.

Version: / LibreOffice Community
Build ID: 9391e9fb99a87e71897e29da79eba084682fdaa5
CPU threads: 8; OS: Linux 5.17; UI render: default; VCL: gtk3
Locale: zh-CN (zh_CN.UTF-8); UI: zh-CN
Build Platform: Fedora34@X64, Branch:master, bibisect-linux-64-7.4-CN
Calc: threaded

Fedora 35.
Comment 1 Kevin Suo 2022-05-13 12:44:23 UTC
# bad: 2022-05-11 11:48:37 source-hash-e98a22b4e
# good: 2022-05-11 11:48:14 source-hash-eaa2b5054

# first bad commit: 2022-05-11 11:48:37 source-hash-e98a22b4e

This range contains only one commit thus it is bisected to:

author	Luboš Luňák 2022-05-06 20:50:32 +0200
committer	Luboš Luňák	2022-05-11 11:48:37 +0200
commit e98a22b4e4f9c5a9249e634dd0489d30d9de2bb1 (patch)
use ScSortedRangeCache also for generic queries

Adding cc to Luboš Luňák, could you please take a look? Thanks.
Comment 2 raal 2022-05-13 18:32:04 UTC
Confirm with Version: / LibreOffice Community
Build ID: 75f7e057039aaa49558e22d18cad651d11589da9
CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3
Locale: cs-CZ (cs_CZ.UTF-8); UI: en-US
Calc: threaded Jumbo
Comment 3 Commit Notification 2022-05-15 08:51:21 UTC
Luboš Luňák committed a patch related to this issue.
It has been pushed to "master":


no ScSortedRangeCache-based query for number-as-string (tdf#149071)

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:

Affected users are encouraged to test the fix and report feedback.