sc/qa/unit/ucalc_formula2.cxx | 30 ++++++++++++++++++++++++++++++ sc/source/core/data/queryiter.cxx | 17 +++++++++++++---- 2 files changed, 43 insertions(+), 4 deletions(-)
New commits: commit 5934e0cd11af994a095658ebbdf82cf21e419a79 Author: Regina Henschel <[email protected]> AuthorDate: Tue Jan 20 23:13:28 2026 +0100 Commit: Xisco Fauli <[email protected]> CommitDate: Thu Jan 22 12:37:04 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]> (cherry picked from commit 4be8d9afe7ace4ef1289218edb92b9d422507c0f) Reviewed-on: https://gerrit.libreoffice.org/c/core/+/197721 Reviewed-by: Xisco Fauli <[email protected]> (cherry picked from commit 99c906812a5a27b440f4f4e90a2318303bf0bf9c) Reviewed-on: https://gerrit.libreoffice.org/c/core/+/197801 diff --git a/sc/qa/unit/ucalc_formula2.cxx b/sc/qa/unit/ucalc_formula2.cxx index 0416e6c25845..26afdeca757b 100644 --- a/sc/qa/unit/ucalc_formula2.cxx +++ b/sc/qa/unit/ucalc_formula2.cxx @@ -4700,6 +4700,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;
