unit test for sheet and in-range range references after sort, fdo#79441

Change-Id: I09327866a92b1f53a7c0a76c38cf79856390d69a

2 files changed, 142 insertions, 0 deletions

diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index ee5a0d856f0d..20b7ba755290 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -5695,6 +5695,146 @@ void Test::testSortRefUpdate3()
     m_pDoc->DeleteTab(0);
 }
 
+// Derived from fdo#79441 https://bugs.freedesktop.org/attachment.cgi?id=100144
+// testRefInterne.ods
+void Test::testSortRefUpdate4()
+{
+    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
+    m_pDoc->InsertTab(0, "Sort");
+    m_pDoc->InsertTab(1, "Lesson1");
+    m_pDoc->InsertTab(2, "Lesson2");
+
+    ScRange aLesson1Range;
+    {
+        const char* aData[][2] = {
+            { "Name", "Note" },
+            { "Student1", "1" },
+            { "Student2", "2" },
+            { "Student3", "3" },
+            { "Student4", "4" },
+            { "Student5", "5" },
+        };
+
+        SCTAB nTab = 1;
+        ScAddress aPos(0,0,nTab);
+        clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
+        aLesson1Range = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
+        CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aLesson1Range.aStart == aPos);
+    }
+
+    ScRange aLesson2Range;
+    {
+        const char* aData[][2] = {
+            { "Name", "Note" },
+            { "=Lesson1.A2", "3" },
+            { "=Lesson1.A3", "4" },
+            { "=Lesson1.A4", "9" },
+            { "=Lesson1.A5", "6" },
+            { "=Lesson1.A6", "3" },
+        };
+
+        SCTAB nTab = 2;
+        ScAddress aPos(0,0,nTab);
+        clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
+        aLesson2Range = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
+        CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aLesson2Range.aStart == aPos);
+    }
+
+    ScRange aSortRange;
+    {
+        const char* aData[][4] = {
+            { "Name", "Lesson1", "Lesson2", "Average" },
+            { "=Lesson1.A2", "=Lesson1.B2", "=Lesson2.B2", "=AVERAGE(B2:C2)" },
+            { "=Lesson1.A3", "=Lesson1.B3", "=Lesson2.B3", "=AVERAGE(B3:C3)" },
+            { "=Lesson1.A4", "=Lesson1.B4", "=Lesson2.B4", "=AVERAGE(B4:C4)" },
+            { "=Lesson1.A5", "=Lesson1.B5", "=Lesson2.B5", "=AVERAGE(B5:C5)" },
+            { "=Lesson1.A6", "=Lesson1.B6", "=Lesson2.B6", "=AVERAGE(B6:C6)" },
+        };
+
+        SCTAB nTab = 0;
+        ScAddress aPos(0,0,nTab);
+        clearRange(m_pDoc, ScRange(0, 0, nTab, 1, SAL_N_ELEMENTS(aData), nTab));
+        aSortRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
+        CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aSortRange.aStart == aPos);
+    }
+
+    ScDBDocFunc aFunc(getDocShell());
+
+    // Sort A1:D6.
+    m_pDoc->SetAnonymousDBData( 0, new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
+                aSortRange.aStart.Col(), aSortRange.aStart.Row(), aSortRange.aEnd.Col(), aSortRange.aEnd.Row()));
+
+    // Sort A1:D6 by column D (Average, with a row header).
+    ScSortParam aSortData;
+    aSortData.nCol1 = aSortRange.aStart.Col();
+    aSortData.nCol2 = aSortRange.aEnd.Col();
+    aSortData.nRow1 = aSortRange.aStart.Row();
+    aSortData.nRow2 = aSortRange.aEnd.Row();
+    aSortData.bHasHeader = true;
+    aSortData.maKeyState[0].bDoSort = true;         // sort on
+    aSortData.maKeyState[0].nField = 3;             // Average
+    aSortData.maKeyState[0].bAscending = false;     // descending
+    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
+    CPPUNIT_ASSERT(bSorted);
+
+    // Check the sorted values.
+    m_pDoc->CalcAll();
+    CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
+    CPPUNIT_ASSERT_EQUAL(OUString("Student3"), m_pDoc->GetString(ScAddress(0,1,0)));
+    CPPUNIT_ASSERT_EQUAL(OUString("Student4"), m_pDoc->GetString(ScAddress(0,2,0)));
+    CPPUNIT_ASSERT_EQUAL(OUString("Student5"), m_pDoc->GetString(ScAddress(0,3,0)));
+    CPPUNIT_ASSERT_EQUAL(OUString("Student2"), m_pDoc->GetString(ScAddress(0,4,0)));
+    CPPUNIT_ASSERT_EQUAL(OUString("Student1"), m_pDoc->GetString(ScAddress(0,5,0)));
+    CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,1,0)));
+    CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
+    CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,3,0)));
+    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,4,0)));
+    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,5,0)));
+
+    // Make sure the formula cells have been adjusted correctly.
+    const char* aCheck[][4] = {
+        // Name          Lesson1       Lesson2       Average
+        { "Lesson1.A4", "Lesson1.B4", "Lesson2.B4", "AVERAGE(B2:C2)" },
+        { "Lesson1.A5", "Lesson1.B5", "Lesson2.B5", "AVERAGE(B3:C3)" },
+        { "Lesson1.A6", "Lesson1.B6", "Lesson2.B6", "AVERAGE(B4:C4)" },
+        { "Lesson1.A3", "Lesson1.B3", "Lesson2.B3", "AVERAGE(B5:C5)" },
+        { "Lesson1.A2", "Lesson1.B2", "Lesson2.B2", "AVERAGE(B6:C6)" },
+    };
+    for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aCheck)); ++nRow)
+    {
+        for (SCCOL nCol=0; nCol < 4; ++nCol)
+        {
+            if (!checkFormula(*m_pDoc, ScAddress(nCol,nRow+1,0), aCheck[nRow][nCol]))
+                CPPUNIT_FAIL(OString("Wrong formula in " + OString('A'+nCol) + OString::number(nRow+2) + ".").getStr());
+        }
+    }
+
+    // Undo and check the result.
+    SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
+    pUndoMgr->Undo();
+    m_pDoc->CalcAll();
+    CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
+    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,1,0)));
+    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,2,0)));
+    CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,3,0)));
+    CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,4,0)));
+    CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,5,0)));
+
+    // Redo and check the result.
+    pUndoMgr->Redo();
+    m_pDoc->CalcAll();
+    CPPUNIT_ASSERT_EQUAL(OUString("Name"), m_pDoc->GetString(ScAddress(0,0,0)));
+    CPPUNIT_ASSERT_EQUAL( 6.0, m_pDoc->GetValue(ScAddress(3,1,0)));
+    CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
+    CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,3,0)));
+    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,4,0)));
+    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(3,5,0)));
+
+    m_pDoc->DeleteTab(2);
+    m_pDoc->DeleteTab(1);
+    m_pDoc->DeleteTab(0);
+}
+
 void Test::testSortOutOfPlaceResult()
 {
     m_pDoc->InsertTab(0, "Sort");
diff --git a/sc/qa/unit/ucalc.hxx b/sc/qa/unit/ucalc.hxx
index e4a6b8c0b000..80c46bf4de34 100644
--- a/sc/qa/unit/ucalc.hxx
+++ b/sc/qa/unit/ucalc.hxx
@@ -354,6 +354,7 @@ public:
     void testSortRefUpdate();
     void testSortRefUpdate2();
     void testSortRefUpdate3();
+    void testSortRefUpdate4();
     void testSortOutOfPlaceResult();
     void testSortPartialFormulaGroup();
     void testShiftCells();
@@ -533,6 +534,7 @@ public:
     CPPUNIT_TEST(testSortRefUpdate);
     CPPUNIT_TEST(testSortRefUpdate2);
     CPPUNIT_TEST(testSortRefUpdate3);
+    CPPUNIT_TEST(testSortRefUpdate4);
     CPPUNIT_TEST(testSortOutOfPlaceResult);
     CPPUNIT_TEST(testSortPartialFormulaGroup);
     CPPUNIT_TEST(testShiftCells);