Quelle  ucalc_sort.cxx   Sprache: C

/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
/*
 * This file is part of the LibreOffice project.
 *
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
 */

#include "helper/sorthelper.hxx"
#include "helper/debughelper.hxx"
#include "helper/qahelper.hxx"

#include <postit.hxx>
#include <sortparam.hxx>
#include <dbdata.hxx>
#include <formulacell.hxx>
#include <scopetools.hxx>
#include <globalnames.hxx>
#include <dbdocfun.hxx>
#include <docfunc.hxx>
#include <scitems.hxx>
#include <editutil.hxx>
#include <drwlayer.hxx>
#include <queryiter.hxx>
#include <undomanager.hxx>

#include <editeng/wghtitem.hxx>
#include <editeng/postitem.hxx>
#include <svx/svdocirc.hxx>
#include <svx/svdpage.hxx>
#include <rtl/math.hxx>

using ::std::cerr;
using ::std::endl;

class TestSort : public ScUcalcTestBase
{
protected:
     void testSortRefUpdate4_Impl();
};

CPPUNIT_TEST_FIXTURE(TestSort, testSort)
{
    m_pDoc->InsertTab(0, u"test1"_ustr);

    // We need a drawing layer in order to create caption objects.
    m_pDoc->InitDrawLayer(m_xDocShell.get());

    ScAddress aPos(0,0,0);
    {
        const std::vector<std::vector<const char*>> aData = {
            { "2", "4" },
            { "4", "1" },
            { "1", "2" },
            { "1", "23" },
        };

        clearRange(m_pDoc, ScRange(0, 0, 0, 1, aData.size(), 0));
        ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
    }

    // Insert note in cell B2.
    ScAddress rAddr(1, 1, 0);
    ScPostIt* pNote = m_pDoc->GetOrCreateNote(rAddr);
    pNote->SetText(rAddr, u"Hello"_ustr);
    pNote->SetAuthor(u"Jim Bob"_ustr);

    ScSortParam aSortData;
    aSortData.nCol1 = 1;
    aSortData.nCol2 = 1;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 2;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 1;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;

    m_pDoc->Sort(0, aSortData, false, true, nullptr, nullptr);

    double nVal = m_pDoc->GetValue(1,0,0);
    ASSERT_DOUBLES_EQUAL(1.0, nVal);

    // check that note is also moved after sorting
    pNote = m_pDoc->GetNote(1, 0, 0);
    CPPUNIT_ASSERT(pNote);

    ScRange aDataRange;
    clearRange(m_pDoc, ScRange(0, 0, 0, 1, 9, 0)); // Clear A1:B10.
    {
        // 0 = empty cell
        const std::vector<std::vector<const char*>> aData = {
            { "Title" },
            { nullptr },
            { nullptr },
            { "12" },
            { "b" },
            { "1" },
            { "9" },
            { "123" }
        };

        aDataRange = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
    }

    aSortData.nCol1 = aDataRange.aStart.Col();
    aSortData.nCol2 = aDataRange.aEnd.Col();
    aSortData.nRow1 = aDataRange.aStart.Row();
    aSortData.nRow2 = aDataRange.aEnd.Row();
    aSortData.bHasHeader = true;
    aSortData.maKeyState[0].nField = 0;
    m_pDoc->Sort(0, aSortData, false, true, nullptr, nullptr);

    // Title should stay at the top, numbers should be sorted numerically,
    // numbers always come before strings, and empty cells always occur at the
    // end.
    CPPUNIT_ASSERT_EQUAL(u"Title"_ustr, m_pDoc->GetString(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL(u"1"_ustr, m_pDoc->GetString(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL(u"9"_ustr, m_pDoc->GetString(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL(u"12"_ustr, m_pDoc->GetString(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL(u"123"_ustr, m_pDoc->GetString(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL(u"b"_ustr, m_pDoc->GetString(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL(CELLTYPE_NONE, m_pDoc->GetCellType(aPos));

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortHorizontal)
{
    SortRefUpdateSetter aUpdateSet;

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
    m_pDoc->InsertTab(0, u"Sort"_ustr);

    // Test case from fdo#78079.

    // 0 = empty cell
    const std::vector<std::vector<const char*>> aData = {
        { "table", "has UNIQUE", "Publish to EC2", "flag" },
        { "w2gi.mobilehit", "Yes", "No", "=CONCATENATE(B2;\"-\";C2)" },
        { "w2gi.visitors", "No", "No", "=CONCATENATE(B3;\"-\";C3)" },
        { "w2gi.pagedimension", "Yes", "Yes", "=CONCATENATE(B4;\"-\";C4)" },
    };

    // Insert raw data into A1:D4.
    ScRange aDataRange = insertRangeData(m_pDoc, ScAddress(0,0,0), aData);
    CPPUNIT_ASSERT_EQUAL(u"A1:D4"_ustr, aDataRange.Format(*m_pDoc, ScRefFlags::VALID));

    // Check the formula values.
    CPPUNIT_ASSERT_EQUAL(u"Yes-No"_ustr, m_pDoc->GetString(ScAddress(3,1,0)));
    CPPUNIT_ASSERT_EQUAL(u"No-No"_ustr, m_pDoc->GetString(ScAddress(3,2,0)));
    CPPUNIT_ASSERT_EQUAL(u"Yes-Yes"_ustr, m_pDoc->GetString(ScAddress(3,3,0)));

    // Define A1:D4 as sheet-local anonymous database range.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 3, 3)));

    // Sort A1:D4 horizontally, ascending by row 1.
    ScDBDocFunc aFunc(*m_xDocShell);

    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 3;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 3;
    aSortData.bHasHeader = true;
    aSortData.bByRow = false; // Sort by column (in horizontal direction).
    aSortData.aDataAreaExtras.mbCellFormats = true;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "table", "flag", "has UNIQUE", "Publish to EC2" },
            { "w2gi.mobilehit",     "Yes-No",  "Yes", "No" },
            { "w2gi.visitors",      "No-No",   "No",  "No" },
            { "w2gi.pagedimension", "Yes-Yes", "Yes", "Yes" },
        };

        bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Sorted by column with formula");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=CONCATENATE(C2;\"-\";D2)"_ustr, m_pDoc->GetFormula(1,1,0));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=CONCATENATE(C3;\"-\";D3)"_ustr, m_pDoc->GetFormula(1,2,0));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=CONCATENATE(C4;\"-\";D4)"_ustr, m_pDoc->GetFormula(1,3,0));

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortHorizontalWholeColumn)
{
    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true);
    m_pDoc->InsertTab(0, u"Sort"_ustr);

    // 0 = empty cell
    const std::vector<std::vector<const char*>> aData = {
        { "4", "2", "47", "a", "9" }
    };

    // Insert row data to C1:G1.
    ScRange aSortRange = insertRangeData(m_pDoc, ScAddress(2,0,0), aData);
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(2,0,0)));
    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,0,0)));
    CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(4,0,0)));
    CPPUNIT_ASSERT_EQUAL(u"a"_ustr, m_pDoc->GetString(ScAddress(5,0,0)));
    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(6,0,0)));

    // Extend the sort range to whole column.
    aSortRange.aEnd.SetRow(m_pDoc->MaxRow());

    SCCOL nCol1 = aSortRange.aStart.Col();
    SCCOL nCol2 = aSortRange.aEnd.Col();
    SCROW nRow1 = aSortRange.aStart.Row();
    SCROW nRow2 = aSortRange.aEnd.Row();

    // Define C:G as sheet-local anonymous database range.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, nCol1, nRow1, nCol2, nRow2, false, false)));

    // Sort C:G horizontally ascending by row 1.
    ScDBDocFunc aFunc(*m_xDocShell);

    ScSortParam aSortData;
    aSortData.nCol1 = nCol1;
    aSortData.nCol2 = nCol2;
    aSortData.nRow1 = nRow1;
    aSortData.nRow2 = nRow2;
    aSortData.bHasHeader = false;
    aSortData.bByRow = false; // Sort by column (in horizontal direction).
    aSortData.aDataAreaExtras.mbCellFormats = true;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    // Check the sort result.
    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(3,0,0)));
    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
    CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(5,0,0)));
    CPPUNIT_ASSERT_EQUAL(u"a"_ustr, m_pDoc->GetString(ScAddress(6,0,0)));

    // Undo and check.

    SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
    CPPUNIT_ASSERT(pUndoMgr);

    pUndoMgr->Undo();
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(2,0,0)));
    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(3,0,0)));
    CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(4,0,0)));
    CPPUNIT_ASSERT_EQUAL(u"a"_ustr, m_pDoc->GetString(ScAddress(5,0,0)));
    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(6,0,0)));

    // Redo and check.
    pUndoMgr->Redo();
    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(3,0,0)));
    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
    CPPUNIT_ASSERT_EQUAL(47.0, m_pDoc->GetValue(ScAddress(5,0,0)));
    CPPUNIT_ASSERT_EQUAL(u"a"_ustr, m_pDoc->GetString(ScAddress(6,0,0)));

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortSingleRow)
{
    // This test case is from fdo#80462.

    m_pDoc->InsertTab(0, u"Test"_ustr);

    // Sort range consists of only one row.
    m_pDoc->SetString(ScAddress(0,0,0), u"X"_ustr);
    m_pDoc->SetString(ScAddress(1,0,0), u"Y"_ustr);

    // Define A1:B1 as sheet-local anonymous database range.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 1, 0)));

    // Sort A1:B1 horizontally, ascending by row 1.
    ScDBDocFunc aFunc(*m_xDocShell);

    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 1;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 0;
    aSortData.bHasHeader = true;
    aSortData.bByRow = true;
    aSortData.aDataAreaExtras.mbCellFormats = true;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;

    // Do the sorting.  This should not crash.
    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    // Another test case - single row horizontal sort with header column.
    clearSheet(m_pDoc, 0);

    // A1:G1
    m_pDoc->SetString(ScAddress(0,0,0), u"Header"_ustr);
    m_pDoc->SetValue(ScAddress(1,0,0),  1.0);
    m_pDoc->SetValue(ScAddress(2,0,0), 10.0);
    m_pDoc->SetValue(ScAddress(3,0,0),  3.0);
    m_pDoc->SetValue(ScAddress(4,0,0),  9.0);
    m_pDoc->SetValue(ScAddress(5,0,0), 12.0);
    m_pDoc->SetValue(ScAddress(6,0,0),  2.0);

    // Define A1:G1 as sheet-local anonymous database range.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 6, 0, false, true)));

    // Update the sort data.
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 6;
    aSortData.bByRow = false;
    bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    // Check the result.
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
    CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
    CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(5,0,0)));
    CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(6,0,0)));

    // Undo and check.
    SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
    CPPUNIT_ASSERT(pUndoMgr);
    pUndoMgr->Undo();

    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
    CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(2,0,0)));
    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
    CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
    CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(5,0,0)));
    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(6,0,0)));

    // Redo and check.
    pUndoMgr->Redo();
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(1,0,0)));
    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(2,0,0)));
    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(3,0,0)));
    CPPUNIT_ASSERT_EQUAL( 9.0, m_pDoc->GetValue(ScAddress(4,0,0)));
    CPPUNIT_ASSERT_EQUAL(10.0, m_pDoc->GetValue(ScAddress(5,0,0)));
    CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(6,0,0)));

    m_pDoc->DeleteTab(0);
}

// regression test of fdo#53814, sorting doesn't work as expected
// if cells in the sort are referenced by formulas
CPPUNIT_TEST_FIXTURE(TestSort, testSortWithFormulaRefs)
{
    SortRefUpdateSetter aUpdateSet;

    m_pDoc->InsertTab(0, u"List1"_ustr);
    m_pDoc->InsertTab(1, u"List2"_ustr);

    const char* aFormulaData[6] = {
        "=IF($List1.A2<>\"\";$List1.A2;\"\")",
        "=IF($List1.A3<>\"\";$List1.A3;\"\")",
        "=IF($List1.A4<>\"\";$List1.A4;\"\")",
        "=IF($List1.A5<>\"\";$List1.A5;\"\")",
        "=IF($List1.A6<>\"\";$List1.A6;\"\")",
        "=IF($List1.A7<>\"\";$List1.A7;\"\")",
    };

    const char* const aTextData[4] = {
        "bob",
        "tim",
        "brian",
        "larry",
    };

    const char* aResults[6] = {
        "bob",
        "brian",
        "larry",
        "tim",
        "",
        "",
    };

    // Insert data to sort in A2:A5 on the 1st sheet.
    for (SCROW i = 1; i <= 4; ++i)
        m_pDoc->SetString( 0, i, 0, OUString::createFromAscii(aTextData[i-1]) );

    // Insert formulas in A1:A6 on the 2nd sheet.
    for (size_t i = 0; i < std::size(aFormulaData); ++i)
        m_pDoc->SetString( 0, i, 1, OUString::createFromAscii(aFormulaData[i]) );

    // Sort data in A2:A8 on the 1st sheet. No column header.
    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 0;
    aSortData.nRow1 = 1;
    aSortData.nRow2 = 7;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;

    m_pDoc->Sort(0, aSortData, false, true, nullptr, nullptr);

    for (size_t i = 0; i < std::size(aResults); ++i)
    {
        OUString sResult = m_pDoc->GetString(0, i + 1, 0);
        CPPUNIT_ASSERT_EQUAL( OUString::createFromAscii( aResults[i] ), sResult );
    }
    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortWithStrings)
{
    m_pDoc->InsertTab(0, u"Test"_ustr);

    ScFieldEditEngine& rEE = m_pDoc->GetEditEngine();
    rEE.SetTextCurrentDefaults(u"Val1"_ustr);
    m_pDoc->SetString(ScAddress(1,1,0), u"Header"_ustr);
    m_pDoc->SetString(ScAddress(1,2,0), u"Val2"_ustr);
    m_pDoc->SetEditText(ScAddress(1,3,0), rEE.CreateTextObject());

    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(1,1,0)));
    CPPUNIT_ASSERT_EQUAL(u"Val2"_ustr, m_pDoc->GetString(ScAddress(1,2,0)));
    CPPUNIT_ASSERT_EQUAL(u"Val1"_ustr, m_pDoc->GetString(ScAddress(1,3,0)));

    ScSortParam aParam;
    aParam.nCol1 = 1;
    aParam.nCol2 = 1;
    aParam.nRow1 = 1;
    aParam.nRow2 = 3;
    aParam.bHasHeader = true;
    aParam.maKeyState[0].bDoSort = true;
    aParam.maKeyState[0].bAscending = true;
    aParam.maKeyState[0].nField = 1;
    aParam.maKeyState[0].aColorSortMode = ScColorSortMode::None;

    m_pDoc->Sort(0, aParam, false, true, nullptr, nullptr);

    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(1,1,0)));
    CPPUNIT_ASSERT_EQUAL(u"Val1"_ustr, m_pDoc->GetString(ScAddress(1,2,0)));
    CPPUNIT_ASSERT_EQUAL(u"Val2"_ustr, m_pDoc->GetString(ScAddress(1,3,0)));

    aParam.maKeyState[0].bAscending = false;

    m_pDoc->Sort(0, aParam, false, true, nullptr, nullptr);

    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(1,1,0)));
    CPPUNIT_ASSERT_EQUAL(u"Val2"_ustr, m_pDoc->GetString(ScAddress(1,2,0)));
    CPPUNIT_ASSERT_EQUAL(u"Val1"_ustr, m_pDoc->GetString(ScAddress(1,3,0)));

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortInFormulaGroup)
{
    SortRefUpdateSetter aUpdateSet;

    static const struct {
        SCCOL nCol;
        SCROW nRow;
        const char *pData;
    } aEntries[] = {
        { 0, 0, "3" },   { 1, 0, "=A1" },
        { 0, 1, "1" },   { 1, 1, "=A2" },
        { 0, 2, "20" },  { 1, 2, "=A3" },
        { 0, 3, "10" },  { 1, 3, "=A4+1" }, // swap across groups
        { 0, 4, "2"  },  { 1, 4, "=A5+1" },
        { 0, 5, "101" }, { 1, 5, "=A6" }, // swap inside contiguous group
        { 0, 6, "100" }, { 1, 6, "=A7" },
        { 0, 7, "102" }, { 1, 7, "=A8" },
        { 0, 8, "104" }, { 1, 8, "=A9" },
        { 0, 9, "103" }, { 1, 9, "=A10" },
    };

    m_pDoc->InsertTab(0, u"sorttest"_ustr);

    for ( auto const & i: aEntries )
        m_pDoc->SetString( i.nCol, i.nRow, 0,
                           OUString::createFromAscii( i.pData) );

    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 1;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 9;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;

    m_pDoc->Sort(0, aSortData, false, true, nullptr, nullptr);

    static const double aResults[] = {
        1.0,   1.0,
        2.0,   3.0,
        3.0,   3.0,
        10.0,  11.0,
        20.0,  20.0,
        100.0, 100.0,
        101.0, 101.0,
        102.0, 102.0,
        103.0, 103.0,
        104.0, 104.0
    };

    for ( SCROW i = 0; i < SCROW(std::size( aEntries )); ++i )
    {
        double val = m_pDoc->GetValue( aEntries[i].nCol, aEntries[i].nRow, 0 );
        CPPUNIT_ASSERT_MESSAGE("Mis-matching value after sort.",
                               rtl::math::approxEqual(val, aResults[i]));
    }

    m_pDoc->DeleteTab( 0 );
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortWithCellFormats)
{
    struct
    {
        bool isBold( const ScPatternAttr* pPat ) const
        {
            if (!pPat)
            {
                cerr << "Pattern is NULL!" << endl;
                return false;
            }

            const SfxPoolItem* pItem = nullptr;
            if (!pPat->GetItemSet().HasItem(ATTR_FONT_WEIGHT, &pItem))
            {
                cerr << "Pattern does not have a font weight item, but it should." << endl;
                return false;
            }

            CPPUNIT_ASSERT(pItem);

            if (static_cast<const SvxWeightItem*>(pItem)->GetEnumValue() != WEIGHT_BOLD)
            {
                cerr << "Font weight should be bold." << endl;
                return false;
            }

            return true;
        }

        bool isItalic( const ScPatternAttr* pPat ) const
        {
            if (!pPat)
            {
                cerr << "Pattern is NULL!" << endl;
                return false;
            }

            const SfxPoolItem* pItem = nullptr;
            if (!pPat->GetItemSet().HasItem(ATTR_FONT_POSTURE, &pItem))
            {
                cerr << "Pattern does not have a font posture item, but it should." << endl;
                return false;
            }

            CPPUNIT_ASSERT(pItem);

            if (static_cast<const SvxPostureItem*>(pItem)->GetEnumValue() != ITALIC_NORMAL)
            {
                cerr << "Italic should be applied.." << endl;
                return false;
            }

            return true;
        }

        bool isNormal( const ScPatternAttr* pPat ) const
        {
            if (!pPat)
            {
                cerr << "Pattern is NULL!" << endl;
                return false;
            }

            const SfxPoolItem* pItem = nullptr;
            if (pPat->GetItemSet().HasItem(ATTR_FONT_WEIGHT, &pItem))
            {
                // Check if the font weight is applied.
                if (static_cast<const SvxWeightItem*>(pItem)->GetEnumValue() == WEIGHT_BOLD)
                {
                    cerr << "This cell is bold, but shouldn't." << endl;
                    return false;
                }
            }

            if (pPat->GetItemSet().HasItem(ATTR_FONT_POSTURE, &pItem))
            {
                // Check if the italics is applied.
                if (static_cast<const SvxPostureItem*>(pItem)->GetEnumValue() == ITALIC_NORMAL)
                {
                    cerr << "This cell is italic, but shouldn't." << endl;
                    return false;
                }
            }

            return true;
        }

    } aCheck;

    m_pDoc->InsertTab(0, u"Test"_ustr);

    // Insert some values into A1:A4.
    m_pDoc->SetString(ScAddress(0,0,0), u"Header"_ustr);
    m_pDoc->SetString(ScAddress(0,1,0), u"Normal"_ustr);
    m_pDoc->SetString(ScAddress(0,2,0), u"Bold"_ustr);
    m_pDoc->SetString(ScAddress(0,3,0), u"Italic"_ustr);

    // Set A3 bold and A4 italic.
    const ScPatternAttr* pPat = m_pDoc->GetPattern(ScAddress(0,2,0));
    CPPUNIT_ASSERT(pPat);
    {
        ScPatternAttr aNewPat(*pPat);
        SfxItemSet& rSet = aNewPat.GetItemSet();
        rSet.Put(SvxWeightItem(WEIGHT_BOLD, ATTR_FONT_WEIGHT));
        m_pDoc->ApplyPattern(0, 2, 0, aNewPat);

        // Make sure it's really in.
        bool bGood = aCheck.isBold(m_pDoc->GetPattern(ScAddress(0,2,0)));
        CPPUNIT_ASSERT_MESSAGE("A3 is not bold but it should.", bGood);
    }

    pPat = m_pDoc->GetPattern(ScAddress(0,3,0));
    CPPUNIT_ASSERT(pPat);
    {
        ScPatternAttr aNewPat(*pPat);
        SfxItemSet& rSet = aNewPat.GetItemSet();
        rSet.Put(SvxPostureItem(ITALIC_NORMAL, ATTR_FONT_POSTURE));
        m_pDoc->ApplyPattern(0, 3, 0, aNewPat);

        bool bGood = aCheck.isItalic(m_pDoc->GetPattern(ScAddress(0,3,0)));
        CPPUNIT_ASSERT_MESSAGE("A4 is not italic but it should.", bGood);
    }

    // Define A1:A4 as sheet-local anonymous database range, else sort wouldn't run.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 3)));

    // Sort A1:A4 ascending with cell formats.
    ScDBDocFunc aFunc(*m_xDocShell);

    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 0;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 3;
    aSortData.bHasHeader = true;
    aSortData.aDataAreaExtras.mbCellFormats = true;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
    bool bSorted = aFunc.Sort(0, aSortData, true, false, true);
    CPPUNIT_ASSERT(bSorted);

    // Check the sort result.
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    CPPUNIT_ASSERT_EQUAL(u"Bold"_ustr,   m_pDoc->GetString(ScAddress(0,1,0)));
    CPPUNIT_ASSERT_EQUAL(u"Italic"_ustr, m_pDoc->GetString(ScAddress(0,2,0)));
    CPPUNIT_ASSERT_EQUAL(u"Normal"_ustr, m_pDoc->GetString(ScAddress(0,3,0)));

    // A2 should be bold now.
    bool bBold = aCheck.isBold(m_pDoc->GetPattern(ScAddress(0,1,0)));
    CPPUNIT_ASSERT_MESSAGE("A2 should be bold after the sort.", bBold);

    // and A3 should be italic.
    bool bItalic = aCheck.isItalic(m_pDoc->GetPattern(ScAddress(0,2,0)));
    CPPUNIT_ASSERT_MESSAGE("A3 should be italic.", bItalic);

    // A4 should have neither bold nor italic.
    bool bNormal = aCheck.isNormal(m_pDoc->GetPattern(ScAddress(0,3,0)));
    CPPUNIT_ASSERT_MESSAGE("A4 should be neither bold nor italic.", bNormal);

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortRefUpdate)
{
    SortTypeSetter aSortTypeSet(true);

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
    FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);

    m_pDoc->InsertTab(0, u"Sort"_ustr);

    // Set values to sort in column A.
    m_pDoc->SetString(ScAddress(0,0,0), u"Header"_ustr);

    double aValues[] = { 4.0, 36.0, 14.0, 29.0, 98.0, 78.0, 0.0, 99.0, 1.0 };
    size_t nCount = std::size(aValues);
    for (size_t i = 0; i < nCount; ++i)
        m_pDoc->SetValue(ScAddress(0,i+1,0), aValues[i]);

    // Set formulas to reference these values in column C.
    m_pDoc->SetString(ScAddress(2,0,0), u"Formula"_ustr);
    for (size_t i = 0; i < nCount; ++i)
        m_pDoc->SetString(ScAddress(2,1+i,0), u"=RC[-2]"_ustr);

    // Check the values in column C.
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aValues[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
    }

    ScDBDocFunc aFunc(*m_xDocShell);

    // Define A1:A10 as sheet-local anonymous database range, else sort wouldn't run.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 9)));

    // Sort A1:A10 (with a header row).
    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 0;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 9;
    aSortData.bHasHeader = true;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    double aSorted[] = { 0.0, 1.0, 4.0, 14.0, 29.0, 36.0, 78.0, 98.0, 99.0 };

    // Check the sort result.
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aSorted[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
    }

    // Sorting should not alter the values in column C.
    m_pDoc->CalcAll(); // just in case...
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aValues[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
    }

    // C2 should now point to A4.
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in C2!", u"=R[2]C[-2]"_ustr, m_pDoc->GetFormula(2,1,0));

    // Undo the sort.
    SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
    pUndoMgr->Undo();

    // Check the undo result.
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aValues[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
    }

    // Values in column C should still be unaltered.
    m_pDoc->CalcAll(); // just in case...
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aValues[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
    }

    // C2 should now point to A2.
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in C2!", u"=RC[-2]"_ustr, m_pDoc->GetFormula(2,1,0));

    // Redo.
    pUndoMgr->Redo();

    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aSorted[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
    }

    // Sorting should not alter the values in column C.
    m_pDoc->CalcAll(); // just in case...
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aValues[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0)));
    }

    // C2 should now point to A4.
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in C2!", u"=R[2]C[-2]"_ustr, m_pDoc->GetFormula(2,1,0));

    // Undo again.
    pUndoMgr->Undo();

    // Formulas in column C should all be "RC[-2]" again.
    for (size_t i = 0; i < nCount; ++i)
        m_pDoc->SetString(ScAddress(2,1+i,0), u"=RC[-2]"_ustr);

    // Turn off reference update on sort.
    SortTypeSetter::changeTo(false);

    bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    // Check the sort result again.
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aSorted[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(0,i+1,0)));
    }

    // Formulas in column C should all remain "RC[-2]".
    for (size_t i = 0; i < nCount; ++i)
        m_pDoc->SetString(ScAddress(2,1+i,0), u"=RC[-2]"_ustr);

    // The values in column C should now be the same as sorted values in column A.
    m_pDoc->CalcAll(); // just in case...
    for (size_t i = 0; i < nCount; ++i)
    {
        double fCheck = aSorted[i];
        CPPUNIT_ASSERT_EQUAL(fCheck, m_pDoc->GetValue(ScAddress(2,i+1,0))); // column C
    }

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortRefUpdate2)
{
    SortRefUpdateSetter aUpdateSet;

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
    FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);

    m_pDoc->InsertTab(0, u"Sort"_ustr);

    // Set up the sheet.
    const char* aData[][2] = {
        { "F1", "F2" },
        { "9", "=RC[-1]" },
        { "2", "=RC[-1]" },
        { "6", "=RC[-1]" },
        { "4", "=RC[-1]" },
        { nullptr, nullptr } // terminator
    };

    for (SCROW i = 0; aData[i][0]; ++i)
    {
        m_pDoc->SetString(0, i, 0, OUString::createFromAscii(aData[i][0]));
        m_pDoc->SetString(1, i, 0, OUString::createFromAscii(aData[i][1]));
    }

    // Check the values in B2:B5.
    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,1,0)));
    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
    CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,4,0)));

    ScDBDocFunc aFunc(*m_xDocShell);

    // Define A1:B5 as sheet-local anonymous database range, else sort wouldn't run.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 1, 4)));

    // Sort A1:B5 by column A (with a row header).
    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 1;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 4;
    aSortData.bHasHeader = true;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    // Check the sort result in column A.
    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(0,1,0)));
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(0,2,0)));
    CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(0,3,0)));
    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(0,4,0)));

    // and column B.
    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,2,0)));
    CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,4,0)));

    // Formulas in column B should still point to their respective left neighbor cell.
    for (SCROW i = 1; i <= 4; ++i)
    {
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=RC[-1]"_ustr, m_pDoc->GetFormula(1,i,0));
    }

    // Undo and check the result in column B.
    SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
    pUndoMgr->Undo();

    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,1,0)));
    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,2,0)));
    CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,4,0)));

    // and redo.
    pUndoMgr->Redo();

    CPPUNIT_ASSERT_EQUAL(2.0, m_pDoc->GetValue(ScAddress(1,1,0)));
    CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(ScAddress(1,2,0)));
    CPPUNIT_ASSERT_EQUAL(6.0, m_pDoc->GetValue(ScAddress(1,3,0)));
    CPPUNIT_ASSERT_EQUAL(9.0, m_pDoc->GetValue(ScAddress(1,4,0)));

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortRefUpdate3)
{
    SortRefUpdateSetter aUpdateSet;

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
    m_pDoc->InsertTab(0, u"Sort"_ustr);

    const char* pData[] = {
        "Header",
        "1",
        "=A2+10",
        "2",
        "=A4+10",
        "=A2+A4",
        nullptr // terminator
    };

    for (SCROW i = 0; pData[i]; ++i)
        m_pDoc->SetString(ScAddress(0,i,0), OUString::createFromAscii(pData[i]));

    // Check the initial values.
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
    CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,2,0)));
    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
    CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,4,0)));
    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,5,0)));

    ScDBDocFunc aFunc(*m_xDocShell);

    // Sort A1:A6.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 0, 5)));

    // Sort A1:A6 by column A (with a row header).
    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 0;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 5;
    aSortData.bHasHeader = true;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    // Check the sorted values.
    m_pDoc->CalcAll();
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,2,0)));
    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,3,0)));
    CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,4,0)));
    CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,5,0)));

    // Make sure the formula cells have been adjusted correctly.
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in A4.", u"=A2+A3"_ustr, m_pDoc->GetFormula(0,3,0));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in A5.", u"=A2+10"_ustr, m_pDoc->GetFormula(0,4,0));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula in A6.", u"=A3+10"_ustr, m_pDoc->GetFormula(0,5,0));

    // Undo and check the result.
    SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
    pUndoMgr->Undo();
    m_pDoc->CalcAll();
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
    CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,2,0)));
    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,3,0)));
    CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,4,0)));
    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,5,0)));

    // Redo and check the result.
    pUndoMgr->Redo();
    m_pDoc->CalcAll();
    CPPUNIT_ASSERT_EQUAL(u"Header"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
    CPPUNIT_ASSERT_EQUAL( 1.0, m_pDoc->GetValue(ScAddress(0,1,0)));
    CPPUNIT_ASSERT_EQUAL( 2.0, m_pDoc->GetValue(ScAddress(0,2,0)));
    CPPUNIT_ASSERT_EQUAL( 3.0, m_pDoc->GetValue(ScAddress(0,3,0)));
    CPPUNIT_ASSERT_EQUAL(11.0, m_pDoc->GetValue(ScAddress(0,4,0)));
    CPPUNIT_ASSERT_EQUAL(12.0, m_pDoc->GetValue(ScAddress(0,5,0)));

    m_pDoc->DeleteTab(0);
}

// Derived from fdo#79441 https://bugs.freedesktop.org/attachment.cgi?id=100144
// testRefInterne.ods
CPPUNIT_TEST_FIXTURE(TestSort, testSortRefUpdate4)
{
    // This test has to work in both update reference modes.
    {
        SortRefNoUpdateSetter aUpdateSet;
        testSortRefUpdate4_Impl();
    }
    {
        SortRefUpdateSetter aUpdateSet;
        testSortRefUpdate4_Impl();
    }
}

void TestSort::testSortRefUpdate4_Impl()
{
    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
    m_pDoc->InsertTab(0, u"Sort"_ustr);
    m_pDoc->InsertTab(1, u"Lesson1"_ustr);
    m_pDoc->InsertTab(2, u"Lesson2"_ustr);

    {
        const std::vector<std::vector<const char*>> aData = {
            { "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, aData.size(), nTab));
        ScRange aLesson1Range = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aLesson1Range.aStart);
    }

    {
        const std::vector<std::vector<const char*>> aData = {
            { "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, aData.size(), nTab));
        ScRange aLesson2Range = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aLesson2Range.aStart);
    }

    ScRange aSortRange;
    {
        const std::vector<std::vector<const char*>> aData = {
            { "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, aData.size(), nTab));
        aSortRange = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aSortRange.aStart);
    }

    ScDBDocFunc aFunc(*m_xDocShell);

    // 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
        aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;

        m_pDoc->SetAnonymousDBData( 0, std::unique_ptr<ScDBData>(new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
                    aSortData.nCol1, aSortData.nRow1, aSortData.nCol2, aSortData.nRow2)));

        bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
        CPPUNIT_ASSERT(bSorted);

        // Check the sorted values.
        m_pDoc->CalcAll();
        CPPUNIT_ASSERT_EQUAL(u"Name"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student3"_ustr, m_pDoc->GetString(ScAddress(0,1,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student4"_ustr, m_pDoc->GetString(ScAddress(0,2,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student5"_ustr, m_pDoc->GetString(ScAddress(0,3,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student2"_ustr, m_pDoc->GetString(ScAddress(0,4,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student1"_ustr, 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)
            {
                CPPUNIT_ASSERT_EQUAL_MESSAGE(OString("Wrong formula in " + OStringChar(char('A'+nCol)) + OString::number(nRow+2) + ".").getStr(), OUString::createFromAscii(aCheck[nRow][nCol]), m_pDoc->GetFormula(nCol,nRow+1,0));
            }
        }

        // Undo and check the result.
        SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
        pUndoMgr->Undo();
        m_pDoc->CalcAll();
        CPPUNIT_ASSERT_EQUAL(u"Name"_ustr, 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(u"Name"_ustr, 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)));
    }

    // Sort A2:AMJ6 by column A (Name, without header).
    {
        ScSortParam aSortData;
        aSortData.nCol1 = 0;
        aSortData.nCol2 = m_pDoc->MaxCol();
        aSortData.nRow1 = aSortRange.aStart.Row()+1;
        aSortData.nRow2 = aSortRange.aEnd.Row();
        aSortData.bHasHeader = false;
        aSortData.maKeyState[0].bDoSort = true;         // sort on
        aSortData.maKeyState[0].nField = 0;             // Name
        aSortData.maKeyState[0].bAscending = false;     // descending
        aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;

        m_pDoc->SetAnonymousDBData( 0, std::unique_ptr<ScDBData>(new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
                    aSortData.nCol1, aSortData.nRow1, aSortData.nCol2, aSortData.nRow2)));

        bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
        CPPUNIT_ASSERT(bSorted);

        // Check the sorted values.
        m_pDoc->CalcAll();
        CPPUNIT_ASSERT_EQUAL(u"Name"_ustr, m_pDoc->GetString(ScAddress(0,0,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student5"_ustr, m_pDoc->GetString(ScAddress(0,1,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student4"_ustr, m_pDoc->GetString(ScAddress(0,2,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student3"_ustr, m_pDoc->GetString(ScAddress(0,3,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student2"_ustr, m_pDoc->GetString(ScAddress(0,4,0)));
        CPPUNIT_ASSERT_EQUAL(u"Student1"_ustr, m_pDoc->GetString(ScAddress(0,5,0)));
        CPPUNIT_ASSERT_EQUAL( 4.0, m_pDoc->GetValue(ScAddress(3,1,0)));
        CPPUNIT_ASSERT_EQUAL( 5.0, m_pDoc->GetValue(ScAddress(3,2,0)));
        CPPUNIT_ASSERT_EQUAL( 6.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.A6", "=Lesson1.B6", "=Lesson2.B6", "=AVERAGE(B2:C2)" },
            { "=Lesson1.A5", "=Lesson1.B5", "=Lesson2.B5", "=AVERAGE(B3:C3)" },
            { "=Lesson1.A4", "=Lesson1.B4", "=Lesson2.B4", "=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)
            {
                CPPUNIT_ASSERT_EQUAL_MESSAGE(OString("Wrong formula in " + OStringChar(char('A'+nCol)) + OString::number(nRow+2) + ".").getStr(), OUString::createFromAscii(aCheck[nRow][nCol]), m_pDoc->GetFormula(nCol,nRow+1,0));
            }
        }
    }

    m_pDoc->DeleteTab(2);
    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

// Make sure the refupdate works also with volatile cells, see fdo#83067
/* FIXME: this test is not roll-over-midnight safe and will fail then! We may
 * want to have something different, but due to the nature of volatile
 * functions it's not that easy to come up with something reproducible staying
 * stable over sorts... ;-)  Check for time and don't run test a few seconds
 * before midnight, ermm... */
CPPUNIT_TEST_FIXTURE(TestSort, testSortRefUpdate5)
{
    SortRefUpdateSetter aUpdateSet;

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
    m_pDoc->InsertTab(0, u"Sort"_ustr);

    double aValCheck[][3] = {
        // Result, Unsorted order, Sorted result.
        { 0, 4, 0 },
        { 0, 1, 0 },
        { 0, 3, 0 },
        { 0, 2, 0 },
    };
    ScRange aSortRange;
    {
        const std::vector<std::vector<const char*>> aData = {
            { "Date", "Volatile", "Order" },
            { "1999-05-05", "=TODAY()-$A2", "4" },
            { "1994-10-18", "=TODAY()-$A3", "1" },
            { "1996-06-30", "=TODAY()-$A4", "3" },
            { "1995-11-21", "=TODAY()-$A5", "2" },
        };

        SCTAB nTab = 0;
        ScAddress aPos(0,0,nTab);
        clearRange(m_pDoc, ScRange(0, 0, nTab, 2, aData.size(), nTab));
        aSortRange = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aSortRange.aStart);

        // Actual results and expected sorted results.
        for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
        {
            double fVal = m_pDoc->GetValue(ScAddress(1,nRow+1,0));
            aValCheck[nRow][0] = fVal;
            aValCheck[static_cast<size_t>(aValCheck[nRow][1])-1][2] = fVal;
        }
    }

    ScDBDocFunc aFunc(*m_xDocShell);

    // Sort A1:B5.
    m_pDoc->SetAnonymousDBData( 0, std::unique_ptr<ScDBData>(new ScDBData( STR_DB_LOCAL_NONAME, aSortRange.aStart.Tab(),
                aSortRange.aStart.Col(), aSortRange.aStart.Row(), aSortRange.aEnd.Col(), aSortRange.aEnd.Row())));

    // Sort by column A.
    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 = 0;             // Date
    aSortData.maKeyState[0].bAscending = true;      // ascending
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    // Check the sorted values.
    m_pDoc->CalcAll();
    for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
    {
        size_t i = static_cast<size_t>(m_pDoc->GetValue(ScAddress(2,nRow+1,0)));    // order 1..4
        CPPUNIT_ASSERT_EQUAL( static_cast<size_t>(nRow+1), i);
        CPPUNIT_ASSERT_EQUAL( aValCheck[i-1][2], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
    }

    // Make sure the formula cells have been adjusted correctly.
    const char* aFormulaCheck[] = {
        // Volatile
        "=TODAY()-$A2",
        "=TODAY()-$A3",
        "=TODAY()-$A4",
        "=TODAY()-$A5",
    };
    for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aFormulaCheck)); ++nRow)
    {
        CPPUNIT_ASSERT_EQUAL_MESSAGE(OString("Wrong formula in B" + OString::number(nRow+2) + ".").getStr(), OUString::createFromAscii(aFormulaCheck[nRow]), m_pDoc->GetFormula(1,nRow+1,0));
    }

    // Undo and check the result.
    SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
    pUndoMgr->Undo();
    m_pDoc->CalcAll();
    for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
    {
        CPPUNIT_ASSERT_EQUAL( aValCheck[nRow][0], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
        CPPUNIT_ASSERT_EQUAL( aValCheck[nRow][1], m_pDoc->GetValue(ScAddress(2,nRow+1,0)));
    }

    // Redo and check the result.
    pUndoMgr->Redo();
    m_pDoc->CalcAll();
    for (SCROW nRow=0; nRow < static_cast<SCROW>(SAL_N_ELEMENTS(aValCheck)); ++nRow)
    {
        size_t i = static_cast<size_t>(m_pDoc->GetValue(ScAddress(2,nRow+1,0)));    // order 1..4
        CPPUNIT_ASSERT_EQUAL( static_cast<size_t>(nRow+1), i);
        CPPUNIT_ASSERT_EQUAL( aValCheck[i-1][2], m_pDoc->GetValue(ScAddress(1,nRow+1,0)));
    }

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestSort, testSortRefUpdate6)
{
    SortRefNoUpdateSetter aUpdateSet;

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
    m_pDoc->InsertTab(0, u"Sort"_ustr);

    const std::vector<std::vector<const char*>> aData = {
        { "Order", "Value", "1" },
        { "9", "1", "=C1+B2" },
        { "1", "2", "=C2+B3" },
        { "8", "3", "=C3+B4" },
    };

    ScAddress aPos(0,0,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL(aPos, aDataRange.aStart);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Order", "Value", "1" },
            { "9", "1", "2" },
            { "1", "2", "4" },
            { "8", "3", "7" },
        };

        bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Initial value");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    ScDBDocFunc aFunc(*m_xDocShell);

    // Sort A1:C4.
    m_pDoc->SetAnonymousDBData(
        0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 2, 3)));

    // Sort A1:A6 by column A (with a row header).
    ScSortParam aSortData;
    aSortData.nCol1 = 0;
    aSortData.nCol2 = 2;
    aSortData.nRow1 = 0;
    aSortData.nRow2 = 3;
    aSortData.bHasHeader = true;
    aSortData.maKeyState[0].bDoSort = true;
    aSortData.maKeyState[0].nField = 0;
    aSortData.maKeyState[0].bAscending = true;
    aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
    bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
    CPPUNIT_ASSERT(bSorted);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Order", "Value", "1" },
            { "1", "2", "3" },
            { "8", "3", "6" },
            { "9", "1", "7" },
        };

        bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Sorted without reference update");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Make sure that the formulas in C2:C4 are not adjusted.
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=C1+B2"_ustr, m_pDoc->GetFormula(2,1,0));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=C2+B3"_ustr, m_pDoc->GetFormula(2,2,0));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=C3+B4"_ustr, m_pDoc->GetFormula(2,3,0));

    // Undo and check.
    SfxUndoManager* pUndoMgr = m_pDoc->GetUndoManager();
    CPPUNIT_ASSERT(pUndoMgr);

    pUndoMgr->Undo();

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Order", "Value", "1" },
            { "9", "1", "2" },
            { "1", "2", "4" },
            { "8", "3", "7" },
        };

        bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "After undo");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Redo and check.
    pUndoMgr->Redo();
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Order", "Value", "1" },
            { "1", "2", "3" },
            { "8", "3", "6" },
            { "9", "1", "7" },
        };

        bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "After redo");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Change the value of C1 and make sure the formula broadcasting chain still works.
    ScDocFunc& rFunc = m_xDocShell->GetDocFunc();
    rFunc.SetValueCell(ScAddress(2,0,0), 11.0, false);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Order", "Value", "11" },
            { "1", "2", "13" },
            { "8", "3", "16" },
            { "9", "1", "17" },
        };

        bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Change the header value");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Undo and check.
    pUndoMgr->Undo();
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Order", "Value", "1" },
            { "1", "2", "3" },
            { "8", "3", "6" },
            { "9", "1", "7" },
        };

        bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "After undo of header value change");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    m_pDoc->DeleteTab(0);
}

// fdo#86762 check that broadcasters are sorted correctly and empty cell is
// broadcasted.
CPPUNIT_TEST_FIXTURE(TestSort, testSortBroadcaster)
{
    SortRefNoUpdateSetter aUpdateSet;

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
    m_pDoc->InsertTab(0, u"Sort"_ustr);

    {
        const std::vector<std::vector<const char*>> aData = {
            { "1",   nullptr, nullptr, "=B1", "=$B$1", "=SUM(A1:B1)", "=SUM($A$1:$B$1)" },
            { "2", "8", nullptr, "=B2", "=$B$2", "=SUM(A2:B2)", "=SUM($A$2:$B$2)" },
        };

        ScAddress aPos(0,0,0);
        ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL(aPos, aDataRange.aStart);

        {
            // Expected output table content.  0 = empty cell
            std::vector<std::vector<const char*>> aOutputCheck = {
                { "1",   nullptr, nullptr, "0", "0",  "1",  "1" },
                { "2", "8", nullptr, "8", "8", "10", "10" },
            };

            bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Initial value");
            CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
        }

        // Sort A1:B2.
        m_pDoc->SetAnonymousDBData(
                0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 0, 1, 1)));

        ScDBDocFunc aFunc(*m_xDocShell);

        // Sort A1:B2 by column A descending.
        ScSortParam aSortData;
        aSortData.nCol1 = 0;
        aSortData.nCol2 = 1;
        aSortData.nRow1 = 0;
        aSortData.nRow2 = 1;
        aSortData.bHasHeader = false;
        aSortData.bByRow = true;
        aSortData.maKeyState[0].bDoSort = true;
        aSortData.maKeyState[0].nField = 0;
        aSortData.maKeyState[0].bAscending = false;
        aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
        bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
        CPPUNIT_ASSERT(bSorted);

        {
            // Expected output table content.  0 = empty cell
            std::vector<std::vector<const char*>> aOutputCheck = {
                { "2", "8", nullptr, "8", "8", "10", "10" },
                { "1",   nullptr, nullptr, "0", "0",  "1",  "1" },
            };

            bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Sorted without reference update");
            CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
        }

        // Make sure that the formulas in D1:G2 are not adjusted.
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=B1"_ustr, m_pDoc->GetFormula(3,0,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=B2"_ustr, m_pDoc->GetFormula(3,1,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=$B$1"_ustr, m_pDoc->GetFormula(4,0,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=$B$2"_ustr, m_pDoc->GetFormula(4,1,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=SUM(A1:B1)"_ustr, m_pDoc->GetFormula(5,0,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=SUM(A2:B2)"_ustr, m_pDoc->GetFormula(5,1,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=SUM($A$1:$B$1)"_ustr, m_pDoc->GetFormula(6,0,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=SUM($A$2:$B$2)"_ustr, m_pDoc->GetFormula(6,1,0));

        // Enter new value and check that it is broadcasted. First in empty cell.
        m_pDoc->SetString(1,1,0, u"16"_ustr);
        double nVal = m_pDoc->GetValue(3,1,0);
        ASSERT_DOUBLES_EQUAL( 16.0, nVal);
        nVal = m_pDoc->GetValue(4,1,0);
        ASSERT_DOUBLES_EQUAL( 16.0, nVal);
        nVal = m_pDoc->GetValue(5,1,0);
        ASSERT_DOUBLES_EQUAL( 17.0, nVal);
        nVal = m_pDoc->GetValue(6,1,0);
        ASSERT_DOUBLES_EQUAL( 17.0, nVal);

        // Enter new value and check that it is broadcasted. Now overwriting data.
        m_pDoc->SetString(1,0,0, u"32"_ustr);
        nVal = m_pDoc->GetValue(3,0,0);
        ASSERT_DOUBLES_EQUAL( 32.0, nVal);
        nVal = m_pDoc->GetValue(4,0,0);
        ASSERT_DOUBLES_EQUAL( 32.0, nVal);
        nVal = m_pDoc->GetValue(5,0,0);
        ASSERT_DOUBLES_EQUAL( 34.0, nVal);
        nVal = m_pDoc->GetValue(6,0,0);
        ASSERT_DOUBLES_EQUAL( 34.0, nVal);
    }

    // The same for sort by column. Start data at A5.

    {
        const std::vector<std::vector<const char*>> aData = {
            { "1", "2" },
            {   nullptr, "8" },
            { nullptr, nullptr },
            { "=A6",             "=B6" },
            { "=$A$6",           "=$B$6" },
            { "=SUM(A5:A6)",     "=SUM(B5:B6)" },
            { "=SUM($A$5:$A$6)", "=SUM($B$5:$B$6)" },
        };

        ScAddress aPos(0,4,0);
        ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL(aPos, aDataRange.aStart);

        {
            // Expected output table content.  0 = empty cell
            std::vector<std::vector<const char*>> aOutputCheck = {
                { "1", "2" },
                {   nullptr, "8" },
                { nullptr, nullptr },
                { "0",  "8" },
                { "0",  "8" },
                { "1", "10" },
                { "1", "10" },
            };

            bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Initial value");
            CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
        }

        // Sort A5:B6.
        m_pDoc->SetAnonymousDBData(
                0, std::unique_ptr<ScDBData>(new ScDBData(STR_DB_LOCAL_NONAME, 0, 0, 4, 1, 5)));

        ScDBDocFunc aFunc(*m_xDocShell);

        // Sort A5:B6 by row 5 descending.
        ScSortParam aSortData;
        aSortData.nCol1 = 0;
        aSortData.nCol2 = 1;
        aSortData.nRow1 = 4;
        aSortData.nRow2 = 5;
        aSortData.bHasHeader = false;
        aSortData.bByRow = false;
        aSortData.maKeyState[0].bDoSort = true;
        aSortData.maKeyState[0].nField = 0;
        aSortData.maKeyState[0].bAscending = false;
        aSortData.maKeyState[0].aColorSortMode = ScColorSortMode::None;
        bool bSorted = aFunc.Sort(0, aSortData, true, true, true);
        CPPUNIT_ASSERT(bSorted);

        {
            // Expected output table content.  0 = empty cell
            std::vector<std::vector<const char*>> aOutputCheck = {
                { "2", "1" },
                { "8",   nullptr },
                { nullptr, nullptr },
                { "8",  "0" },
                { "8",  "0" },
                { "10", "1" },
                { "10", "1" },
            };

            bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Sorted without reference update");
            CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
        }

        // Make sure that the formulas in A8:B11 are not adjusted.
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=A6"_ustr, m_pDoc->GetFormula(0,7,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=B6"_ustr, m_pDoc->GetFormula(1,7,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=$A$6"_ustr, m_pDoc->GetFormula(0,8,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=$B$6"_ustr, m_pDoc->GetFormula(1,8,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=SUM(A5:A6)"_ustr, m_pDoc->GetFormula(0,9,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=SUM(B5:B6)"_ustr, m_pDoc->GetFormula(1,9,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=SUM($A$5:$A$6)"_ustr, m_pDoc->GetFormula(0,10,0));
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula!", u"=SUM($B$5:$B$6)"_ustr, m_pDoc->GetFormula(1,10,0));

        // Enter new value and check that it is broadcasted. First in empty cell.
        m_pDoc->SetString(1,5,0, u"16"_ustr);
        double nVal = m_pDoc->GetValue(1,7,0);
        ASSERT_DOUBLES_EQUAL(16.0, nVal);
        nVal = m_pDoc->GetValue(1,8,0);
        ASSERT_DOUBLES_EQUAL(16.0, nVal);
        nVal = m_pDoc->GetValue(1,9,0);
        ASSERT_DOUBLES_EQUAL(17.0, nVal);
        nVal = m_pDoc->GetValue(1,10,0);
        ASSERT_DOUBLES_EQUAL(17.0, nVal);

        // Enter new value and check that it is broadcasted. Now overwriting data.
        m_pDoc->SetString(0,5,0, u"32"_ustr);
        nVal = m_pDoc->GetValue(0,7,0);
        ASSERT_DOUBLES_EQUAL(32.0, nVal);
        nVal = m_pDoc->GetValue(0,8,0);
        ASSERT_DOUBLES_EQUAL(32.0, nVal);
        nVal = m_pDoc->GetValue(0,9,0);
        ASSERT_DOUBLES_EQUAL(34.0, nVal);
        nVal = m_pDoc->GetValue(0,10,0);
        ASSERT_DOUBLES_EQUAL(34.0, nVal);
    }

    m_pDoc->DeleteTab(0);
}

// tdf#99417 check that formulas are tracked that *only* indirectly depend on
// sorted data and no other broadcasting than BroadcastBroadcasters is
// involved (for which this test can not be included in testSortBroadcaster()).
CPPUNIT_TEST_FIXTURE(TestSort, testSortBroadcastBroadcaster)
{
    SortRefNoUpdateSetter aUpdateSet;

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
    m_pDoc->InsertTab(0, u"Sort"_ustr);

    {
        const std::vector<std::vector<const char*>> aData = {
            { "1", "=A1", "=B1" },
            { "2", "=A2", "=B2" },
        };

        ScAddress aPos(0,0,0);
        ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
        CPPUNIT_ASSERT_EQUAL(aPos, aDataRange.aStart);

        {
            // Expected output table content.  0 = empty cell
            std::vector<std::vector<const char*>> aOutputCheck = {
                { "1", "1", "1" },
                { "2", "2", "2" },
            };

            bool bSuccess = checkOutput(m_pDoc, aDataRange, aOutputCheck, "Initial value");
            CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
        }

        // Sort A1:A2.
        m_pDoc->SetAnonymousDBData(
--> --------------------

--> maximum size reached

--> --------------------