sc/qa/unit/ucalc_formula.cxx | 72 +++++++++++++++++++++++++++++++++++++++
sc/source/core/tool/compiler.cxx | 29 +++++++++++++++
2 files changed, 101 insertions(+)
New commits:
commit b89047a0f0100fb30121084cf42815aa792c1f88
Author: Balazs Varga <balazs.varga.ext...@allotropia.de>
AuthorDate: Mon Jan 27 09:57:25 2025 +0100
Commit: Balazs Varga <balazs.varga.ext...@allotropia.de>
CommitDate: Mon Feb 3 12:54:01 2025 +0100
tdf#164843 - sc optimize "SubTotal" function's reference ranges
Double references from relative named ranges can point to large
ranges (MAXCOL/MAXROW) and because of that some function evaluation
like SubTotal can be extreamly slow when we call
ScTable::CompileHybridFormula
with these large ranges. Since all the SubTotal functions ignore empty cells
its worth to optimize and trim the double references in SubTotal functions.
Change-Id: I9340b520cd3ac333b63b4aec35a3c647f5d9a119
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/180773
Tested-by: Gabor Kelemen <gabor.kelemen.ext...@allotropia.de>
Tested-by: Jenkins
Reviewed-by: Gabor Kelemen <gabor.kelemen.ext...@allotropia.de>
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index 40d2c6f174de..4dd944fb1774 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -4761,6 +4761,78 @@ CPPUNIT_TEST_FIXTURE(TestFormula, testFuncSUMPRODUCT)
m_pDoc->DeleteTab(0);
}
+CPPUNIT_TEST_FIXTURE(TestFormula, testFuncSUBTOTAL)
+{
+ m_pDoc->InsertTab(0, u"Formula"_ustr);
+
+ sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
+
+ // Fill C1:C1025 with values and insert formulas in D1:D1025 using the
named expression.
+ for (size_t i = 0; i < 1025; i++)
+ m_pDoc->SetValue(ScAddress(2, i, 0), i + 1);
+
+ // Add a named expression for a function.
+ ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
+ CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.",
pGlobalNames);
+ ScRangeData* pName = new ScRangeData(
+ *m_pDoc, u"MyRelative"_ustr, u"$C1:$C$1000"_ustr, ScAddress(2, 999, 0),
+ ScRangeData::Type::Name, formula::FormulaGrammar::GRAM_NATIVE);
+
+ bool bInserted = pGlobalNames->insert(pName);
+ CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
+
+ for (size_t i = 0; i < 1025; i++)
+ m_pDoc->SetString(ScAddress(3, i, 0),
u"=IF(SUBTOTAL(3;MyRelative)=1;"";SUBTOTAL(3;MyRelative))"_ustr);
+
+ // Make sure the results are correct.
+ for (size_t i = 0; i < 1025; i++)
+ {
+ if (i < 999.0)
+ CPPUNIT_ASSERT_EQUAL(26.0, m_pDoc->GetValue(3, i, 0));
+ else
+ CPPUNIT_ASSERT_EQUAL((1000.0 - (i - 999.0)), m_pDoc->GetValue(3,
i, 0));
+ }
+
+ ScRange aTrimedRange(2, 999, 0, 2, 1024, 0);
+ ScRange aValidRange(2, 0, 0, 2, 999, 0);
+ for (size_t i = 0; i < 1025; i++)
+ {
+ ScFormulaCell* pCell = m_pDoc->GetFormulaCell(ScAddress(3, i, 0));
+ ScTokenArray* pCode = pCell->GetCode();
+ sal_uInt16 nLen = pCode->GetCodeLen();
+ FormulaToken** pRPNArray = pCode->GetCode();
+ OUString aCellName = pCell->aPos.GetColRowString();
+
+ for (sal_uInt16 nIdx = 0; nIdx < nLen; ++nIdx)
+ {
+ FormulaToken* pTok = pRPNArray[nIdx];
+ if (pTok && pTok->GetType() == svDoubleRef)
+ {
+ ScRange aRange = pTok->GetDoubleRef()->toAbs(*m_pDoc,
ScAddress(3, i, 0));
+ if (i < 999)
+ {
+ CPPUNIT_ASSERT_EQUAL_MESSAGE(OUString("Double ref is
incorrectly trimmed in: " + aCellName).toUtf8().getStr(),
+ aRange, aTrimedRange);
+ // Without the trim it would failed with
+ // assertion failed
+ // - Expression: aRange == aTrimedRange
+ // - Double ref is incorrectly trimmed in : D1
+ // ScRange aTrimmableRange(2, 999, 0, 0, 1048575, 0);
+ }
+ else
+ {
+ CPPUNIT_ASSERT_EQUAL_MESSAGE(OUString("Double ref is
incorrectly trimmed in: " + aCellName).toUtf8().getStr(),
+ aRange, aValidRange);
+ }
+ }
+ }
+ if (i >= 999)
+ aValidRange.aStart.IncRow();
+ }
+
+ m_pDoc->DeleteTab(0);
+}
+
CPPUNIT_TEST_FIXTURE(TestFormula, testFuncSUMXMY2)
{
m_pDoc->InsertTab(0, u"Test SumXMY2"_ustr);
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 90f67c3eec22..07d59de2df16 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -6817,6 +6817,35 @@ void ScCompiler::AnnotateTrimOnDoubleRefs()
--ppTok;
}
}
+ else if (eOpCode == ocSubTotal)
+ {
+ // tdf#164843: Double references from relative named ranges can point
to large
+ // ranges (MAXCOL/MAXROW) and because of that some function evaluation
+ // like SubTotal can be extreamly slow when we call
ScTable::CompileHybridFormula
+ // with these large ranges. Since all the SubTotal functions ignore
empty cells
+ // its worth to optimize and trim the double references in SubTotal
functions.
+ FormulaToken** ppTok = pCode - 2;
+ while (*ppTok)
+ {
+ FormulaToken* pTok = *ppTok;
+ if (pTok->GetType() == svDoubleRef)
+ {
+ ScComplexRefData* pRefData = pTok->GetDoubleRef();
+ // no need to set pRefData->SetTrimToData(true); because we
already trim here
+ ScRange rRange = pRefData->toAbs(rDoc, aPos);
+ SCCOL nTempStartCol = rRange.aStart.Col();
+ SCROW nTempStartRow = rRange.aStart.Row();
+ SCCOL nTempEndCol = rRange.aEnd.Col();
+ SCROW nTempEndRow = rRange.aEnd.Row();
+ rDoc.ShrinkToDataArea(rRange.aStart.Tab(), nTempStartCol,
nTempStartRow, nTempEndCol, nTempEndRow);
+ rRange.aStart.Set(nTempStartCol, nTempStartRow,
rRange.aStart.Tab());
+ rRange.aEnd.Set(nTempEndCol, nTempEndRow, rRange.aEnd.Tab());
+ rRange.PutInOrder();
+ pRefData->SetRange(rDoc.GetSheetLimits(), rRange, aPos);
+ }
+ --ppTok;
+ }
+ }
}
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
