sc/qa/unit/ucalc_formula2.cxx | 30 ++++++++++++++++++++++++++++++ sc/source/core/data/queryiter.cxx | 17 +++++++++++++---- 2 files changed, 43 insertions(+), 4 deletions(-)
New commits: commit 4be8d9afe7ace4ef1289218edb92b9d422507c0f Author: Regina Henschel <[email protected]> AuthorDate: Tue Jan 20 23:13:28 2026 +0100 Commit: Regina Henschel <[email protected]> CommitDate: Wed Jan 21 10:32:09 2026 +0100 tdf#170295 no reduced range if look for empty cells It solves tdf#170388 as well. If a query is horizontal, then the range the user has specified in the function is reduced to the columns, that are actually used in the sheet. This 'actually used columns' is a global known value of the sheet and need not be newly calculated for evaluating the function. This behavior is meaningful for performance reasons, for not to compare with cells known to be empty. But the query will return a wrong value if it queries for empty cells and the query range covers empty cells, that belong to columns that are not used. The patch distinguishes now between looking for empty cells or not, and reduces the range only in case of not looking for empty cells. This solves the wrong results of XLOOKUP(;range;...), and COUNTIF with criterion '=' and '<>'. The patch does not fix tdf#159544 because the implementation of COUNTIFS uses a different mechanism to reduce the query ranges. Change-Id: I944072dbe6ecbbf47ea1c1f683ad3ec4930338b4 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/197703 Tested-by: Jenkins Reviewed-by: Regina Henschel <[email protected]> diff --git a/sc/qa/unit/ucalc_formula2.cxx b/sc/qa/unit/ucalc_formula2.cxx index 5ab27fab9e8e..146b2b1835b4 100644 --- a/sc/qa/unit/ucalc_formula2.cxx +++ b/sc/qa/unit/ucalc_formula2.cxx @@ -4699,6 +4699,36 @@ CPPUNIT_TEST_FIXTURE(TestFormula2, testRegexForXLOOKUP) m_pDoc->DeleteTab(0); } +CPPUNIT_TEST_FIXTURE(TestFormula2, testHoriQueryEmptyCell) +{ + //Tests for fix for tdf#170388 and tdf#170295 + m_pDoc->InsertTab(0, u"Test"_ustr); + m_pDoc->SetString(0, 0, 0, u"x"_ustr); // col, row, tab + m_pDoc->SetString(1, 0, 0, u"y"_ustr); + m_pDoc->SetString(2, 0, 0, u"z"_ustr); + + // Count empty cells in range A1:H1 + m_pDoc->SetFormula(ScAddress(0, 2, 0), "=COUNTIF(A1:H1;\"=\")", + formula::FormulaGrammar::GRAM_NATIVE_UI); + // Without fix, count was 0 + CPPUNIT_ASSERT_EQUAL_MESSAGE("COUNTIF equal empty", 5.0, m_pDoc->GetValue(ScAddress(0, 2, 0))); + + // Get address of first empty cell + m_pDoc->SetFormula(ScAddress(0, 3, 0), "=CELL(\"ADDRESS\"; XLOOKUP(;A1:H1;A1:H1))", + formula::FormulaGrammar::GRAM_NATIVE_UI); + // Without fix, reference was #N/A + CPPUNIT_ASSERT_EQUAL_MESSAGE("XLOOKUP empty", u"$D$1"_ustr, + m_pDoc->GetString(ScAddress(0, 3, 0))); + + // criterion <> counts empty cells too. + m_pDoc->SetFormula(ScAddress(0, 4, 0), "=COUNTIF(A1:H1;\"<>y\")", + formula::FormulaGrammar::GRAM_NATIVE_UI); + // Without fix, count was 2 + CPPUNIT_ASSERT_EQUAL_MESSAGE("COUNTIF not equal", 7.0, m_pDoc->GetValue(ScAddress(0, 4, 0))); + + m_pDoc->DeleteTab(0); +} + CPPUNIT_PLUGIN_IMPLEMENT(); /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/core/data/queryiter.cxx b/sc/source/core/data/queryiter.cxx index 0334dbab42d4..6c6b638de8d7 100644 --- a/sc/source/core/data/queryiter.cxx +++ b/sc/source/core/data/queryiter.cxx @@ -128,8 +128,11 @@ void ScQueryCellIteratorBase< accessType, queryType >::PerformQuery() if (!mbReverseSearch) { ++nCol; - if (nCol > maParam.nCol2 || nCol >= rDoc.maTabs[nTab]->GetAllocatedColumnsCount()) + if (nCol > maParam.nCol2) return; + else if (!rItem.mbMatchEmpty + && nCol >= rDoc.maTabs[nTab]->GetAllocatedColumnsCount()) + return; } else { @@ -142,7 +145,10 @@ void ScQueryCellIteratorBase< accessType, queryType >::PerformQuery() AdvanceQueryParamEntryField(); nFirstQueryField = rEntry.nField; } - pCol = &(rDoc.maTabs[nTab])->aCol[nCol]; + if (rItem.mbMatchEmpty) + pCol = rDoc.maTabs[nTab]->FetchColumn(nCol); + else + pCol = &(rDoc.maTabs[nTab])->aCol[nCol]; } while (!rItem.mbMatchEmpty && pCol->IsEmptyData()); @@ -1743,8 +1749,11 @@ sal_uInt64 ScCountIfCellIterator< accessType >::GetCount() // Keep Entry.nField in iterator on column change SetAdvanceQueryParamEntryField( true ); assert(nTab < rDoc.GetTableCount() && "try to access index out of bounds, FIX IT"); - maParam.nCol1 = rDoc.ClampToAllocatedColumns(nTab, maParam.nCol1); - maParam.nCol2 = rDoc.ClampToAllocatedColumns(nTab, maParam.nCol2); + if (!maParam.GetEntry(0).GetQueryItem().mbMatchEmpty) + { + maParam.nCol1 = rDoc.ClampToAllocatedColumns(nTab, maParam.nCol1); + maParam.nCol2 = rDoc.ClampToAllocatedColumns(nTab, maParam.nCol2); + } nCol = maParam.nCol1; InitPos(); countIfCount = 0;
