sc/qa/unit/ucalc.hxx | 1
sc/qa/unit/ucalc_formula.cxx | 71 +++++++++++++++++++++++++++++++++++++++
sc/source/core/tool/compiler.cxx | 29 +++++++++++++++
3 files changed, 101 insertions(+)
New commits:
commit aaf14013a3fa97147f78159361d8b1148b91f5f4
Author: Balazs Varga <balazs.varga.ext...@allotropia.de>
AuthorDate: Mon Jan 27 09:57:25 2025 +0100
Commit: Thorsten Behrens <thorsten.behr...@allotropia.de>
CommitDate: Mon Feb 3 17:20:16 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.
cherry-pick from: 66eecde324aa1687ececc3f0f514358a7f69bc75
Change-Id: I9340b520cd3ac333b63b4aec35a3c647f5d9a119
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/180974
Tested-by: allotropia jenkins <jenk...@allotropia.de>
Reviewed-by: Thorsten Behrens <thorsten.behr...@allotropia.de>
diff --git a/sc/qa/unit/ucalc.hxx b/sc/qa/unit/ucalc.hxx
index 9852a16e7eb6..f89076770fb2 100644
--- a/sc/qa/unit/ucalc.hxx
+++ b/sc/qa/unit/ucalc.hxx
@@ -189,6 +189,7 @@ public:
void testFuncSUM();
void testFuncPRODUCT();
void testFuncSUMPRODUCT();
+ void testFuncSUBTOTAL();
void testFuncSUMXMY2();
void testFuncMIN();
void testFuncN();
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index 1575731f56e7..1a81aa8abf74 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -4685,6 +4685,77 @@ void Test::testFuncSUMPRODUCT()
m_pDoc->DeleteTab(0);
}
+void Test::testFuncSUBTOTAL()
+{
+ m_pDoc->InsertTab(0, "Formula");
+
+ 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, "MyRelative", "$C1:$C$1000", 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),
"=IF(SUBTOTAL(3;MyRelative)=1;"";SUBTOTAL(3;MyRelative))");
+
+ // 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(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);
+}
+
void Test::testFuncSUMXMY2()
{
m_pDoc->InsertTab(0, "Test SumXMY2");
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 1a6833204f0b..a994f5cf1ebf 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -6439,6 +6439,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(aPos);
+ SCCOL nTempStartCol = rRange.aStart.Col();
+ SCROW nTempStartRow = rRange.aStart.Row();
+ SCCOL nTempEndCol = rRange.aEnd.Col();
+ SCROW nTempEndRow = rRange.aEnd.Row();
+ pDoc->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(rRange, aPos);
+ }
+ --ppTok;
+ }
+ }
}
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
