Quelle  ucalc_formula.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/debughelper.hxx"
#include "helper/qahelper.hxx"
#include <scopetools.hxx>
#include <formulacell.hxx>
#include <docfunc.hxx>
#include <inputopt.hxx>
#include <tokenstringcontext.hxx>
#include <refupdatecontext.hxx>
#include <dbdata.hxx>
#include <validat.hxx>
#include <scitems.hxx>
#include <docpool.hxx>
#include <scmod.hxx>
#include <undomanager.hxx>

#include <formula/vectortoken.hxx>
#include <svl/intitem.hxx>

#include <memory>
#include <algorithm>
#include <vector>

using namespace formula;
using ::std::cerr;
using ::std::endl;

namespace {
void setExpandRefs(bool bExpand)
{
    ScModule* pMod = ScModule::get();
    ScInputOptions aOpt = pMod->GetInputOptions();
    aOpt.SetExpandRefs(bExpand);
    pMod->SetInputOptions(aOpt);
}

void testFormulaRefUpdateNameCopySheetCheckTab( const ScDocument* pDoc, SCTAB nTab, bool bCheckNames )
{
    if (bCheckNames)
    {
        const ScRangeData* pName;
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"GLOBAL"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name GLOBAL should exist", pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"LOCAL"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name LOCAL should exist", pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"GLOBAL_GLOBAL"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name GLOBAL_GLOBAL should exist", pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"GLOBAL_LOCAL"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name GLOBAL_LOCAL should exist", pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"GLOBAL_UNUSED"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name GLOBAL_UNUSED should exist", pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"GLOBAL_UNUSED_NOREF"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name GLOBAL_UNUSED_NOREF should not exist", !pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"LOCAL_GLOBAL"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name LOCAL_GLOBAL should exist", pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"LOCAL_LOCAL"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name LOCAL_LOCAL should exist", pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"LOCAL_UNUSED"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name LOCAL_UNUSED should exist", pName);
        pName = pDoc->GetRangeName(nTab)->findByUpperName(u"LOCAL_UNUSED_NOREF"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Sheet-local name LOCAL_UNUSED_NOREF should exist", pName);
    }

    ScAddress aPos(0,0,0);
    aPos.SetRow(0);
    aPos.SetTab(nTab);
    int nSheet = nTab + 1;
    CPPUNIT_ASSERT_EQUAL( 1.0 * nSheet, pDoc->GetValue(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL( 11.0 * nSheet, pDoc->GetValue(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL( 100.0 * nSheet, pDoc->GetValue(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL( 11000.0 * nSheet, pDoc->GetValue(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL( 10000.0 * nSheet, pDoc->GetValue(aPos));
    aPos.IncRow();
    CPPUNIT_ASSERT_EQUAL( 1100000.0 * nSheet, pDoc->GetValue(aPos));
}


}

class TestFormula : public ScUcalcTestBase
{
};

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaCreateStringFromTokens)
{
    // Insert sheets.
    m_pDoc->InsertTab(0, u"Test"_ustr);
    m_pDoc->InsertTab(1, u"Kevin's Data"_ustr);
    m_pDoc->InsertTab(2, u"Past Data"_ustr);
    m_pDoc->InsertTab(3, u"2013"_ustr);

    // Insert named ranges.
    static const struct {
        bool bGlobal;
        const char* pName;
        const char* pExpr;
    } aNames[] = {
        { true, "x", "Test.H1" },
        { true, "y", "Test.H2" },
        { true, "z", "Test.H3" },

        { false, "sheetx", "Test.J1" }
    };

    ScRangeName* pGlobalNames = m_pDoc->GetRangeName();
    ScRangeName* pSheetNames = m_pDoc->GetRangeName(0);
    CPPUNIT_ASSERT_MESSAGE("Failed to obtain global named expression object.", pGlobalNames);
    CPPUNIT_ASSERT_MESSAGE("Failed to obtain sheet-local named expression object.", pSheetNames);

    for (size_t i = 0; i < SAL_N_ELEMENTS(aNames); ++i)
    {
        ScRangeData* pName = new ScRangeData(
            *m_pDoc, OUString::createFromAscii(aNames[i].pName), OUString::createFromAscii(aNames[i].pExpr),
            ScAddress(0,0,0), ScRangeData::Type::Name, formula::FormulaGrammar::GRAM_NATIVE);

        if (aNames[i].bGlobal)
        {
            bool bInserted = pGlobalNames->insert(pName);
            CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
        }
        else
        {
            bool bInserted = pSheetNames->insert(pName);
            CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bInserted);
        }
    }

    // Insert DB ranges.
    static const struct {
        const char* pName;
        SCTAB nTab;
        SCCOL nCol1;
        SCROW nRow1;
        SCCOL nCol2;
        SCROW nRow2;
    } aDBs[] = {
        { "Table1", 0, 0, 0, 10, 10 },
        { "Table2", 1, 0, 0, 10, 10 },
        { "Table3", 2, 0, 0, 10, 10 }
    };

    ScDBCollection* pDBs = m_pDoc->GetDBCollection();
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch DB collection object.", pDBs);

    for (size_t i = 0; i < SAL_N_ELEMENTS(aDBs); ++i)
    {
        std::unique_ptr<ScDBData> pData( new ScDBData(
            OUString::createFromAscii(
                aDBs[i].pName), aDBs[i].nTab, aDBs[i].nCol1, aDBs[i].nRow1, aDBs[i].nCol2,aDBs[i].nRow2) );
        bool bInserted = pDBs->getNamedDBs().insert(std::move(pData));
        CPPUNIT_ASSERT_MESSAGE(
            OString(
                OString::Concat("Failed to insert \"") + aDBs[i].pName + "\"").getStr(),
            bInserted);
    }

    const char* aTests[] = {
        "1+2",
        "SUM(A1:A10;B1:B10;C5;D6)",
        "IF(Test.B10<>10;\"Good\";\"Bad\")",
        "AVERAGE('2013'.B10:C20)",
        "'Kevin''s Data'.B10",
        "'Past Data'.B1+'2013'.B2*(1+'Kevin''s Data'.C10)",
        "x+y*z", // named ranges
        "SUM(sheetx;x;y;z)", // sheet local and global named ranges mixed
        "MAX(Table1)+MIN(Table2)*SUM(Table3)", // database ranges
        "{1;TRUE;3|FALSE;5;\"Text\"|;;}", // inline matrix
        "SUM('file:///path/to/fake.file'#$Sheet.A1:B10)",
    };
    (void) aTests;

    sc::TokenStringContext aCxt(*m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH);

    // Artificially add external reference data after the context object is
    // initialized.
    aCxt.maExternalFileNames.emplace_back("file:///path/to/fake.file");
    std::vector<OUString> aExtTabNames;
    aExtTabNames.emplace_back("Sheet");
    aCxt.maExternalCachedTabNames.emplace(0, aExtTabNames);

    ScAddress aPos(0,0,0);

    for (size_t i = 0; i < SAL_N_ELEMENTS(aTests); ++i)
    {
#if 0
        OUString aFormula = OUString::createFromAscii(aTests[i]);
#endif
        ScCompiler aComp(*m_pDoc, aPos, FormulaGrammar::GRAM_ENGLISH);
#if 0 // TODO: This call to CompileString() causes the cppunittester to somehow fail on Windows.
        pArray.reset(aComp.CompileString(aFormula));
        CPPUNIT_ASSERT_MESSAGE("Failed to compile formula string.", pArray.get());

        OUString aCheck = pArray->CreateString(aCxt, aPos);
        CPPUNIT_ASSERT_EQUAL(aFormula, aCheck);
#endif
    }

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

namespace {

bool isEmpty( const formula::VectorRefArray& rArray, size_t nPos )
{
    if (rArray.mpStringArray)
    {
        if (rArray.mpStringArray[nPos])
            return false;
    }

    if (rArray.mpNumericArray)
        return std::isnan(rArray.mpNumericArray[nPos]);
    else
        return true;
}

bool equals( const formula::VectorRefArray& rArray, size_t nPos, double fVal )
{
    if (rArray.mpStringArray && rArray.mpStringArray[nPos])
        // This is a string cell.
        return false;

    return rArray.mpNumericArray && rArray.mpNumericArray[nPos] == fVal;
}

bool equals( const formula::VectorRefArray& rArray, size_t nPos, const OUString& rVal )
{
    if (!rArray.mpStringArray)
        return false;

    bool bEquals = OUString(rArray.mpStringArray[nPos]).equalsIgnoreAsciiCase(rVal);
    if (!bEquals)
    {
        cerr << "Expected: " << rVal.toAsciiUpperCase() << " (upcased)" << endl;
        cerr << "Actual: " << OUString(rArray.mpStringArray[nPos]) << " (upcased)" << endl;
    }
    return bEquals;
}

}

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaParseReference)
{
    OUString aTab1(u"90's Music"_ustr), aTab2(u"90's and 70's"_ustr), aTab3(u"All Others"_ustr), aTab4(u"NoQuote"_ustr);
    m_pDoc->InsertTab(0, u"Dummy"_ustr); // just to shift the sheet indices...
    m_pDoc->InsertTab(1, aTab1); // name with a single quote.
    m_pDoc->InsertTab(2, aTab2); // name with 2 single quotes.
    m_pDoc->InsertTab(3, aTab3); // name without single quotes.
    m_pDoc->InsertTab(4, aTab4); // name that doesn't require to be quoted.

    OUString aTabName;
    m_pDoc->GetName(1, aTabName);
    CPPUNIT_ASSERT_EQUAL(aTab1, aTabName);
    m_pDoc->GetName(2, aTabName);
    CPPUNIT_ASSERT_EQUAL(aTab2, aTabName);
    m_pDoc->GetName(3, aTabName);
    CPPUNIT_ASSERT_EQUAL(aTab3, aTabName);
    m_pDoc->GetName(4, aTabName);
    CPPUNIT_ASSERT_EQUAL(aTab4, aTabName);

    // Make sure the formula input and output match.
    {
        const char* aChecks[] = {
            "='90''s Music'.B12",
            "='90''s and 70''s'.$AB$100",
            "='All Others'.Z$100",
            "=NoQuote.$C111"
        };

        for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
        {
            // Use the 'Dummy' sheet for this.
            m_pDoc->SetString(ScAddress(0,0,0), OUString::createFromAscii(aChecks[i]));
            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString::createFromAscii(aChecks[i]), m_pDoc->GetFormula(0,0,0));
        }
    }

    ScAddress aPos;
    ScAddress::ExternalInfo aExtInfo;
    ScRefFlags nRes = aPos.Parse(u"'90''s Music'.D10"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(1), aPos.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(3), aPos.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(9), aPos.Row());
    CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);

    nRes = aPos.Parse(u"'90''s and 70''s'.C100"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(2), aPos.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(2), aPos.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(99), aPos.Row());
    CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);

    nRes = aPos.Parse(u"'All Others'.B3"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(3), aPos.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), aPos.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(2), aPos.Row());
    CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);

    nRes = aPos.Parse(u"NoQuote.E13"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO, &aExtInfo);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(4), aPos.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(4), aPos.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(12), aPos.Row());
    CPPUNIT_ASSERT_MESSAGE("This is not an external address.", !aExtInfo.mbExternal);

    ScRange aRange;

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u":B"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u"B:"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u":B2"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u"B2:"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u":2"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u"2:"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u":2B"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u"2B:"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u"abc_foo:abc_bar"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u"B1:B2~C1"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Should fail to parse.", !(nRes & ScRefFlags::VALID));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u"B:B"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(0), aRange.aStart.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), aRange.aStart.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), aRange.aStart.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(0), aRange.aEnd.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), aRange.aEnd.Col());
    CPPUNIT_ASSERT_EQUAL(m_pDoc->MaxRow(), aRange.aEnd.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                 ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                         ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::ZERO),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_ABS | ScRefFlags::COL2_ABS)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS)));

    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(u"2:2"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(0), aRange.aStart.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), aRange.aStart.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), aRange.aStart.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(0), aRange.aEnd.Tab());
    CPPUNIT_ASSERT_EQUAL(m_pDoc->MaxCol(), aRange.aEnd.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), aRange.aEnd.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                 ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                         ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::ZERO),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::COL_ABS | ScRefFlags::COL2_ABS),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_ABS | ScRefFlags::COL2_ABS)));

    nRes = aRange.Parse(u"NoQuote.B:C"_ustr, *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(4), aRange.aStart.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), aRange.aStart.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), aRange.aStart.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(4), aRange.aEnd.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(2), aRange.aEnd.Col());
    CPPUNIT_ASSERT_EQUAL(m_pDoc->MaxRow(), aRange.aEnd.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                 ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                         ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::ZERO),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_ABS | ScRefFlags::COL2_ABS)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS)));

    // Both rows at sheet bounds and relative => convert to absolute => entire column reference.
    aRange.aStart.SetTab(0);
    nRes = aRange.Parse(m_pDoc->MaxRow() == MAXROW ? u"B1:B1048576"_ustr
                                                   : u"B1:B16777216"_ustr,
                        *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(0), aRange.aStart.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), aRange.aStart.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(0), aRange.aStart.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(0), aRange.aEnd.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(1), aRange.aEnd.Col());
    CPPUNIT_ASSERT_EQUAL(m_pDoc->MaxRow(), aRange.aEnd.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                 ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                         ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::ZERO),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_ABS | ScRefFlags::COL2_ABS)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS)));

    // Both columns at sheet bounds and relative => convert to absolute => entire row reference.
    aRange.aStart.SetTab(0);
    nRes = aRange.Parse("A2:" + m_pDoc->MaxColAsString() + "2", *m_pDoc, formula::FormulaGrammar::CONV_OOO);
    CPPUNIT_ASSERT_MESSAGE("Failed to parse.", (nRes & ScRefFlags::VALID));
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(0), aRange.aStart.Tab());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCCOL>(0), aRange.aStart.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), aRange.aStart.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCTAB>(0), aRange.aEnd.Tab());
    CPPUNIT_ASSERT_EQUAL(m_pDoc->MaxCol(), aRange.aEnd.Col());
    CPPUNIT_ASSERT_EQUAL(static_cast<SCROW>(1), aRange.aEnd.Row());
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                 ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_VALID | ScRefFlags::ROW_VALID | ScRefFlags::TAB_VALID |
                                                         ScRefFlags::COL2_VALID | ScRefFlags::ROW2_VALID | ScRefFlags::TAB2_VALID)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::ZERO),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS)));
    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(ScRefFlags::COL_ABS | ScRefFlags::COL2_ABS),
                         static_cast<sal_uInt16>(nRes & (ScRefFlags::COL_ABS | ScRefFlags::COL2_ABS)));

    // Check for reference input conversion to and display string of entire column/row.
    {
        const char* row1048576Checks[][2] = {
            { "=B:B",           "=B:B" },
            { "=B1:B1048576",   "=B:B" },
            { "=B1:B$1048576",  "=B1:B$1048576" },
            { "=B$1:B1048576",  "=B$1:B1048576" },
            { "=B$1:B$1048576", "=B:B" }
        };
        const char* row16777216Checks[][2] = {
            { "=B:B",           "=B:B" },
            { "=B1:B16777216",   "=B:B" },
            { "=B1:B$16777216",  "=B1:B$16777216" },
            { "=B$1:B16777216",  "=B$1:B16777216" },
            { "=B$1:B$16777216", "=B:B" }
        };
        const char* col1024Checks[][2] = {
            { "=2:2",           "=2:2" },
            { "=A2:AMJ2",       "=2:2" },
            { "=A2:$AMJ2",      "=A2:$AMJ2" },
            { "=$A2:AMJ2",      "=$A2:AMJ2" },
            { "=$A2:$AMJ2",     "=2:2" }
        };
        const char* col16384Checks[][2] = {
            { "=2:2",           "=2:2" },
            { "=A2:XFD2",       "=2:2" },
            { "=A2:$XFD2",      "=A2:$XFD2" },
            { "=$A2:XFD2",      "=$A2:XFD2" },
            { "=$A2:$XFD2",     "=2:2" }
        };

        if (m_pDoc->MaxRow() == 1048575)
        {
            for (const auto& check : row1048576Checks)
            {
                // Use the 'Dummy' sheet for this.
                m_pDoc->SetString(ScAddress(0,0,0), OUString::createFromAscii(check[0]));
                CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString::createFromAscii(check[1]), m_pDoc->GetFormula(0,0,0));
            }
        }
        else
        {
            CPPUNIT_ASSERT_EQUAL(SCROW(16777215), m_pDoc->MaxRow());
            for (const auto& check : row16777216Checks)
            {
                m_pDoc->SetString(ScAddress(0,0,0), OUString::createFromAscii(check[0]));
                CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString::createFromAscii(check[1]), m_pDoc->GetFormula(0,0,0));
            }
        }
        if (m_pDoc->MaxCol() == 1023)
        {
            for (const auto& check : col1024Checks)
            {
                m_pDoc->SetString(ScAddress(0,0,0), OUString::createFromAscii(check[0]));
                CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString::createFromAscii(check[1]), m_pDoc->GetFormula(0,0,0));
            }
        }
        else
        {
            CPPUNIT_ASSERT_EQUAL(SCCOL(16383), m_pDoc->MaxCol());
            for (const auto& check : col16384Checks)
            {
                m_pDoc->SetString(ScAddress(0,0,0), OUString::createFromAscii(check[0]));
                CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong formula", OUString::createFromAscii(check[1]), m_pDoc->GetFormula(0,0,0));
            }
        }
    }

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

CPPUNIT_TEST_FIXTURE(TestFormula, testFetchVectorRefArray)
{
    m_pDoc->InsertTab(0, u"Test"_ustr);

    // All numeric cells in Column A.
    m_pDoc->SetValue(ScAddress(0,0,0), 1);
    m_pDoc->SetValue(ScAddress(0,1,0), 2);
    m_pDoc->SetValue(ScAddress(0,2,0), 3);
    m_pDoc->SetValue(ScAddress(0,3,0), 4);

    formula::VectorRefArray aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 4);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array is expected to be numeric cells only.", !aArray.mpStringArray);
    CPPUNIT_ASSERT_EQUAL(1.0, aArray.mpNumericArray[0]);
    CPPUNIT_ASSERT_EQUAL(2.0, aArray.mpNumericArray[1]);
    CPPUNIT_ASSERT_EQUAL(3.0, aArray.mpNumericArray[2]);
    CPPUNIT_ASSERT_EQUAL(4.0, aArray.mpNumericArray[3]);

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 5);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array is expected to be numeric cells only.", !aArray.mpStringArray);
    CPPUNIT_ASSERT_EQUAL(1.0, aArray.mpNumericArray[0]);
    CPPUNIT_ASSERT_EQUAL(2.0, aArray.mpNumericArray[1]);
    CPPUNIT_ASSERT_EQUAL(3.0, aArray.mpNumericArray[2]);
    CPPUNIT_ASSERT_EQUAL(4.0, aArray.mpNumericArray[3]);
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));

    // All string cells in Column B.  Note that the fetched string arrays are
    // only to be compared case-insensitively.  Right now, we use upper cased
    // strings to achieve case-insensitive-ness, but that may change. So,
    // don't count on that.
    m_pDoc->SetString(ScAddress(1,0,0), u"Andy"_ustr);
    m_pDoc->SetString(ScAddress(1,1,0), u"Bruce"_ustr);
    m_pDoc->SetString(ScAddress(1,2,0), u"Charlie"_ustr);
    m_pDoc->SetString(ScAddress(1,3,0), u"David"_ustr);
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,0,0), 5);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array is expected to be string cells only.", !aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, u"Andy"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, u"Bruce"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, u"Charlie"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, u"David"_ustr));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));

    // Mixture of numeric, string, and empty cells in Column C.
    m_pDoc->SetString(ScAddress(2,0,0), u"Header"_ustr);
    m_pDoc->SetValue(ScAddress(2,1,0), 11);
    m_pDoc->SetValue(ScAddress(2,2,0), 12);
    m_pDoc->SetValue(ScAddress(2,3,0), 13);
    m_pDoc->SetString(ScAddress(2,5,0), u"=SUM(C2:C4)"_ustr);
    m_pDoc->CalcAll();

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(2,0,0), 7);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpStringArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, u"Header"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 11));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 12));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 13));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 4));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 36));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 6));

    // Mixed type again in Column D, but it starts with a numeric cell.
    m_pDoc->SetValue(ScAddress(3,0,0), 10);
    m_pDoc->SetString(ScAddress(3,1,0), u"Below 10"_ustr);
    // Leave 2 empty cells.
    m_pDoc->SetValue(ScAddress(3,4,0), 11);
    m_pDoc->SetString(ScAddress(3,5,0), u"=12"_ustr);
    m_pDoc->SetString(ScAddress(3,6,0), u"=13"_ustr);
    m_pDoc->SetString(ScAddress(3,7,0), u"=CONCATENATE(\"A\";\"B\";\"C\")"_ustr);
    m_pDoc->CalcAll();

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(3,0,0), 8);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpStringArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 10));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, u"Below 10"_ustr));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 2));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 3));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 11));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 12));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 6, 13));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 7, u"ABC"_ustr));

    // Column E consists of formula cells whose results are all numeric.
    for (SCROW i = 0; i <= 6; ++i)
        m_pDoc->SetString(ScAddress(4,i,0), u"=ROW()"_ustr);
    m_pDoc->CalcAll();

    // Leave row 7 empty.
    m_pDoc->SetString(ScAddress(4,8,0), u"Andy"_ustr);
    m_pDoc->SetValue(ScAddress(4,9,0), 123);

    // This array fits within a single formula block.
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,0,0), 5);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should be purely numeric.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should be purely numeric.", !aArray.mpStringArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 2));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 3));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 4));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 5));

    // This array spans over multiple blocks.
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,0,0), 11);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should have both numeric and string arrays.", aArray.mpStringArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 2));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 3));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 4));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 5));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 5, 6));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 6, 7));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 7));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 8, u"Andy"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 9, 123));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 10));

    // Hit the cache but at a different start row.
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,2,0), 3);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 3));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 4));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 5));

    // Column F begins with empty rows at the top.
    m_pDoc->SetValue(ScAddress(5,2,0), 1.1);
    m_pDoc->SetValue(ScAddress(5,3,0), 1.2);
    m_pDoc->SetString(ScAddress(5,4,0), u"=2*8"_ustr);
    m_pDoc->CalcAll();

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,2,0), 4);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 0, 1.1));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 1, 1.2));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 16));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 3));

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,0,0), 3);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 0));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 1));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 1.1));

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,0,0), 10);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should at least have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 0));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 1));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 2, 1.1));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 3, 1.2));
    CPPUNIT_ASSERT_MESSAGE("Unexpected numeric cell.", equals(aArray, 4, 16));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 5));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 6));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 7));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 8));
    CPPUNIT_ASSERT_MESSAGE("This should be empty.", isEmpty(aArray, 9));

    // Get the array for F3:F4. This array should only consist of numeric array.
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(5,2,0), 3);
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);

    // Column G consists only of strings.
    m_pDoc->SetString(ScAddress(6,0,0), u"Title"_ustr);
    m_pDoc->SetString(ScAddress(6,1,0), u"foo"_ustr);
    m_pDoc->SetString(ScAddress(6,2,0), u"bar"_ustr);
    m_pDoc->SetString(ScAddress(6,3,0), u"foo"_ustr);
    m_pDoc->SetString(ScAddress(6,4,0), u"baz"_ustr);
    m_pDoc->SetString(ScAddress(6,5,0), u"quack"_ustr);
    m_pDoc->SetString(ScAddress(6,6,0), u"beep"_ustr);
    m_pDoc->SetString(ScAddress(6,7,0), u"kerker"_ustr);

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(6,1,0), 4); // G2:G5
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a numeric array.", !aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should have a string array.", aArray.mpStringArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, u"foo"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, u"bar"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, u"foo"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, u"baz"_ustr));

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(6,2,0), 4); // G3:G6
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a numeric array.", !aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should have a string array.", aArray.mpStringArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, u"bar"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, u"foo"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, u"baz"_ustr));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 3, u"quack"_ustr));

    // Column H starts with formula cells.
    for (SCROW i = 0; i < 10; ++i)
        m_pDoc->SetString(ScAddress(7,i,0), u"=ROW()"_ustr);

    m_pDoc->CalcAll();
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(7,3,0), 3); // H4:H6
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, 4.0));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 1, 5.0));
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 2, 6.0));

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(7,4,0), 10); // H5:H15
    CPPUNIT_ASSERT_MESSAGE("Failed to fetch vector ref array.", aArray.isValid());
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    CPPUNIT_ASSERT_MESSAGE("Unexpected string cell.", equals(aArray, 0, 5.0));

    // Clear everything and start over.
    clearRange(m_pDoc, ScRange(0,0,0,m_pDoc->MaxCol(),m_pDoc->MaxRow(),0));
    m_pDoc->PrepareFormulaCalc();

    // Totally empty range in a totally empty column (Column A).
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(0,0,0), 3); // A1:A3
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[0]));
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[1]));
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[2]));

    // Totally empty range in a non-empty column (Column B).
    m_pDoc->SetString(ScAddress(1,10,0), u"Some text"_ustr); // B11
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,0,0), 3); // B1:B3
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[0]));
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[1]));
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[2]));

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,12,0), 3); // B13:B15
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[0]));
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[1]));
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[2]));

    // These values come from a cache because of the call above.
    aArray = m_pDoc->FetchVectorRefArray(ScAddress(1,1,0), 3); // B2:B4
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[0]));
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[1]));
    CPPUNIT_ASSERT(std::isnan(aArray.mpNumericArray[2]));

    // The column begins with a string header at row 1 (Column C).
    m_pDoc->SetString(ScAddress(2,0,0), u"MyHeader"_ustr);
    for (SCROW i = 1; i <= 9; ++i) // rows 2-10 are numeric.
        m_pDoc->SetValue(ScAddress(2,i,0), i);

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(2,1,0), 9); // C2:C10
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    for (size_t i = 0; i < 9; ++i)
        CPPUNIT_ASSERT_EQUAL(double(i+1), aArray.mpNumericArray[i]);

    // The column begins with a number, followed by a string then followed by
    // a block of numbers (Column D).
    m_pDoc->SetValue(ScAddress(3,0,0), 0.0);
    m_pDoc->SetString(ScAddress(3,1,0), u"Some string"_ustr);
    for (SCROW i = 2; i <= 9; ++i) // rows 3-10 are numeric.
        m_pDoc->SetValue(ScAddress(3,i,0), i);

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(3,2,0), 8); // D3:D10
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    for (size_t i = 0; i < 8; ++i)
        CPPUNIT_ASSERT_EQUAL(double(i+2), aArray.mpNumericArray[i]);

    // The column begins with a formula, followed by a string then followed by
    // a block of numbers (Column E).
    m_pDoc->SetString(ScAddress(4,0,0), u"=1*2"_ustr);
    m_pDoc->SetString(ScAddress(4,1,0), u"Some string"_ustr);
    for (SCROW i = 2; i <= 9; ++i) // rows 3-10 are numeric.
        m_pDoc->SetValue(ScAddress(4,i,0), i*2);

    aArray = m_pDoc->FetchVectorRefArray(ScAddress(4,2,0), 8); // E3:E10
    CPPUNIT_ASSERT_MESSAGE("Array should have a numeric array.", aArray.mpNumericArray);
    CPPUNIT_ASSERT_MESSAGE("Array should NOT have a string array.", !aArray.mpStringArray);
    for (size_t i = 0; i < 8; ++i)
        CPPUNIT_ASSERT_EQUAL(double((i+2)*2), aArray.mpNumericArray[i]);

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestFormula, testGroupConverter3D)
{
    m_pDoc->InsertTab(0, u"Test"_ustr);
    m_pDoc->InsertTab(1, u"Test2"_ustr);

    m_pDoc->SetValue(1, 0, 0, 1.0);
    m_pDoc->SetValue(1, 0, 1, 2.0);

    for (SCROW nRow = 0; nRow < 200; ++nRow)
    {
        OUString aFormula = "=SUM(Test.B" + OUString::number(nRow+1) + ":Test2.B" + OUString::number(nRow+1) + ")";
        m_pDoc->SetString(0, nRow, 0, aFormula);
    }

    double nVal = m_pDoc->GetValue(0, 0, 0);
    CPPUNIT_ASSERT_EQUAL(3.0, nVal);

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

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaTokenEquality)
{
    struct FormulaTokenEqualityTest
    {
        const char* mpFormula1;
        const char* mpFormula2;
        bool mbEqual;
    };

    static const FormulaTokenEqualityTest aTests[] = {
        { "R1C2", "R1C2", true },
        { "R1C2", "R1C3", false },
        { "R1C2", "R2C2", false },
        { "RC2",  "RC[1]", false },
        { "R1C2:R10C2", "R1C2:R10C2", true },
        { "R1C2:R10C2", "R1C2:R11C2", false },
        { "1", "2", false },
        { "RC[1]+1.2", "RC[1]+1.2", true },
        { "RC[1]*0.2", "RC[1]*0.5", false },
        { "\"Test1\"", "\"Test2\"", false },
        { "\"Test\"", "\"Test\"", true },
        { "CONCATENATE(\"Test1\")", "CONCATENATE(\"Test1\")", true },
        { "CONCATENATE(\"Test1\")", "CONCATENATE(\"Test2\")", false },
    };

    formula::FormulaGrammar::Grammar eGram = formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1;
    for (size_t i = 0; i < SAL_N_ELEMENTS(aTests); ++i)
    {
        ScFormulaCell aCell1(*m_pDoc, ScAddress(), OUString::createFromAscii(aTests[i].mpFormula1), eGram);
        ScFormulaCell aCell2(*m_pDoc, ScAddress(), OUString::createFromAscii(aTests[i].mpFormula2), eGram);

        ScFormulaCell::CompareState eComp = aCell1.CompareByTokenArray(aCell2);
        if (aTests[i].mbEqual)
        {
            if (eComp == ScFormulaCell::NotEqual)
            {
                std::ostringstream os;
                os << "These two formulas should be evaluated equal: '"
                    << aTests[i].mpFormula1 << "' vs '" << aTests[i].mpFormula2 << "'" << endl;
                CPPUNIT_FAIL(os.str());
            }
        }
        else
        {
            if (eComp != ScFormulaCell::NotEqual)
            {
                std::ostringstream os;
                os << "These two formulas should be evaluated non-equal: '"
                    << aTests[i].mpFormula1 << "' vs '" << aTests[i].mpFormula2 << "'" << endl;
                CPPUNIT_FAIL(os.str());
            }
        }
    }
}

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaRefData)
{
    std::unique_ptr<ScDocument> pDoc = std::make_unique<ScDocument>();

    ScAddress aAddr(4,5,3), aPos(2,2,2);
    ScSingleRefData aRef;
    aRef.InitAddress(aAddr);
    CPPUNIT_ASSERT_MESSAGE("Wrong ref data state.", !aRef.IsRowRel());
    CPPUNIT_ASSERT_MESSAGE("Wrong ref data state.", !aRef.IsColRel());
    CPPUNIT_ASSERT_MESSAGE("Wrong ref data state.", !aRef.IsTabRel());
    CPPUNIT_ASSERT_EQUAL(SCCOL(4), aRef.Col());
    CPPUNIT_ASSERT_EQUAL(SCROW(5), aRef.Row());
    CPPUNIT_ASSERT_EQUAL(SCTAB(3), aRef.Tab());

    aRef.SetRowRel(true);
    aRef.SetColRel(true);
    aRef.SetTabRel(true);
    aRef.SetAddress(pDoc->GetSheetLimits(), aAddr, aPos);
    CPPUNIT_ASSERT_EQUAL(SCCOL(2), aRef.Col());
    CPPUNIT_ASSERT_EQUAL(SCROW(3), aRef.Row());
    CPPUNIT_ASSERT_EQUAL(SCTAB(1), aRef.Tab());

    // Test extension of range reference.

    ScComplexRefData aDoubleRef;
    aDoubleRef.InitRange(ScRange(2,2,0,4,4,0));

    aRef.InitAddress(ScAddress(6,5,0));

    aDoubleRef.Extend(pDoc->GetSheetLimits(), aRef, ScAddress());
    ScRange aTest = aDoubleRef.toAbs(*pDoc, ScAddress());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong start position of extended range.", ScAddress(2,2,0), aTest.aStart);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong end position of extended range.", ScAddress(6,5,0), aTest.aEnd);

    ScComplexRefData aDoubleRef2;
    aDoubleRef2.InitRangeRel(*pDoc, ScRange(1,2,0,8,6,0), ScAddress(5,5,0));
    aDoubleRef.Extend(pDoc->GetSheetLimits(), aDoubleRef2, ScAddress(5,5,0));
    aTest = aDoubleRef.toAbs(*pDoc, ScAddress(5,5,0));

    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong start position of extended range.", ScAddress(1,2,0), aTest.aStart);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong end position of extended range.", ScAddress(8,6,0), aTest.aEnd);
}

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaCompiler)
{
    static const struct {
        const char* pInput; FormulaGrammar::Grammar eInputGram;
        const char* pOutput; FormulaGrammar::Grammar eOutputGram;
    } aTests[] = {
        { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "[.B1]-[.$C2]+[.D$3]-[.$E$4]", FormulaGrammar::GRAM_ODFF },
        { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE },
        { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE_XL_A1 },
        { "=B1-$C2+D$3-$E$4", FormulaGrammar::GRAM_NATIVE, "RC[1]-R[1]C3+R3C[3]-R4C5", FormulaGrammar::GRAM_NATIVE_XL_R1C1 },
    };

    for (size_t i = 0; i < SAL_N_ELEMENTS(aTests); ++i)
    {
        std::unique_ptr<ScTokenArray> pArray = compileFormula(m_pDoc, OUString::createFromAscii(aTests[i].pInput), aTests[i].eInputGram);
        CPPUNIT_ASSERT_MESSAGE("Token array shouldn't be NULL!", pArray);

        ScCompiler aComp(*m_pDoc, ScAddress(), *pArray, aTests[i].eOutputGram);
        OUStringBuffer aBuf;
        aComp.CreateStringFromTokenArray(aBuf);
        OUString aFormula = aBuf.makeStringAndClear();

        CPPUNIT_ASSERT_EQUAL(OUString::createFromAscii(aTests[i].pOutput), aFormula);
    }
}

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaCompilerJumpReordering)
{
    struct TokenCheck
    {
        OpCode meOp;
        StackVar meType;
    };

    {
        // Compile formula string first.
        std::unique_ptr<ScTokenArray> pCode(compileFormula(m_pDoc, u"=IF(B1;12;\"text\")"_ustr));
        CPPUNIT_ASSERT(pCode);

        // Then generate RPN tokens.
        ScCompiler aCompRPN(*m_pDoc, ScAddress(), *pCode, FormulaGrammar::GRAM_NATIVE);
        aCompRPN.CompileTokenArray();

        // RPN tokens should be ordered: B1, ocIf, C1, ocSep, D1, ocClose.
        static const TokenCheck aCheckRPN[] =
        {
            { ocPush,  svSingleRef },
            { ocIf,    svUnknown   }, // type is context dependent, don't test it
            { ocPush,  svDouble    },
            { ocSep,   svSep       },
            { ocPush,  svString    },
            { ocClose, svSep       },
        };

        sal_uInt16 nLen = pCode->GetCodeLen();
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong RPN token count.", static_cast<sal_uInt16>(SAL_N_ELEMENTS(aCheckRPN)), nLen);

        FormulaToken** ppTokens = pCode->GetCode();
        for (sal_uInt16 i = 0; i < nLen; ++i)
        {
            const FormulaToken* p = ppTokens[i];
            CPPUNIT_ASSERT_EQUAL(aCheckRPN[i].meOp, p->GetOpCode());
            if (aCheckRPN[i].meOp != ocIf )
                CPPUNIT_ASSERT_EQUAL(static_cast<int>(aCheckRPN[i].meType), static_cast<int>(p->GetType()));
        }

        // Generate RPN tokens again, but this time no jump command reordering.
        pCode->DelRPN();
        ScCompiler aCompRPN2(*m_pDoc, ScAddress(), *pCode, FormulaGrammar::GRAM_NATIVE);
        aCompRPN2.EnableJumpCommandReorder(false);
        aCompRPN2.CompileTokenArray();

        static const TokenCheck aCheckRPN2[] =
        {
            { ocPush,  svSingleRef },
            { ocPush,  svDouble    },
            { ocPush,  svString    },
            { ocIf,    svUnknown   }, // type is context dependent, don't test it
        };

        nLen = pCode->GetCodeLen();
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong RPN token count.", static_cast<sal_uInt16>(SAL_N_ELEMENTS(aCheckRPN2)), nLen);
        ppTokens = pCode->GetCode();
        for (sal_uInt16 i = 0; i < nLen; ++i)
        {
            const FormulaToken* p = ppTokens[i];
            CPPUNIT_ASSERT_EQUAL(aCheckRPN2[i].meOp, p->GetOpCode());
            if (aCheckRPN[i].meOp == ocPush)
                CPPUNIT_ASSERT_EQUAL(static_cast<int>(aCheckRPN2[i].meType), static_cast<int>(p->GetType()));
        }
    }
}

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaCompilerImplicitIntersection2Param)
{
    struct TestCaseFormula
    {
        OUString  aFormula;
        ScAddress aCellAddress;
        ScRange   aSumRange;
        bool      bStartColRel;  // SumRange-StartCol
        bool      bEndColRel;    // SumRange-EndCol
    };

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

    {
        TestCaseFormula aTestCases[] =
        {
            // Formula, FormulaCellAddress, SumRange with Implicit Intersection

            // Sumrange is single cell, address is abs
            {
                u"=SUMIF($B$2:$B$10;F2;$D$5)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                false,
                false
            },

            // Sumrange is single cell, address is relative
            {
                u"=SUMIF($B$2:$B$10;F2;D5)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                true,
                true
            },

            // Baserange(abs,abs), Sumrange(abs,abs)
            {
                u"=SUMIF($B$2:$B$10;F2;$D$5:$D$10)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                false,
                false
            },

            // Baserange(abs,rel), Sumrange(abs,abs)
            {
                u"=SUMIF($B$2:B10;F2;$D$5:$D$10)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                false,
                false
            },

            // Baserange(rel,abs), Sumrange(abs,abs)
            {
                u"=SUMIF(B2:$B$10;F2;$D$5:$D$10)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                false,
                false
            },

            // Baserange(rel,rel), Sumrange(abs,abs)
            {
                u"=SUMIF(B2:B10;F2;$D$5:$D$10)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                false,
                false
            },

            // Baserange(abs,abs), Sumrange(abs,rel)
            {
                u"=SUMIF($B$2:$B$10;F2;$D$5:D10)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                false,
                true
            },

            // Baserange(abs,abs), Sumrange(rel,abs)
            {
                u"=SUMIF($B$2:$B$10;F2;D5:$D$10)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                true,
                false
            },

            // Baserange(abs,abs), Sumrange(rel,rel)
            {
                u"=SUMIF($B$2:$B$10;F2;D5:D10)"_ustr,
                ScAddress(7, 5, 0),
                ScRange( ScAddress(3, 4, 0), ScAddress(3, 12, 0) ),
                true,
                true
            }
        };

        for (const auto& rCase : aTestCases)
        {
            m_pDoc->SetString(rCase.aCellAddress, rCase.aFormula);
            const ScFormulaCell* pCell = m_pDoc->GetFormulaCell(rCase.aCellAddress);
            const ScTokenArray* pCode = pCell->GetCode();
            CPPUNIT_ASSERT(pCode);

            sal_uInt16 nLen = pCode->GetCodeLen();
            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong RPN token count.", static_cast<sal_uInt16>(4), nLen);

            FormulaToken** ppTokens = pCode->GetCode();

            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong type of token(first argument to SUMIF)", svDoubleRef, ppTokens[0]->GetType());
            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong type of token(third argument to SUMIF)", svDoubleRef, ppTokens[2]->GetType());

            ScComplexRefData aSumRangeData = *ppTokens[2]->GetDoubleRef();
            ScRange aSumRange = aSumRangeData.toAbs(*m_pDoc, rCase.aCellAddress);
            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong sum-range in RPN array", rCase.aSumRange, aSumRange);

            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong IsRel type for start column address in sum-range", rCase.bStartColRel, aSumRangeData.Ref1.IsColRel());
            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong IsRel type for end column address in sum-range", rCase.bEndColRel, aSumRangeData.Ref2.IsColRel());
        }
    }
}

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaCompilerImplicitIntersection1ParamNoChange)
{
    struct TestCaseFormulaNoChange
    {
        OUString  aFormula;
        ScAddress aCellAddress;
        bool      bMatrixFormula;
        bool      bForcedArray;
    };

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

    {
        ScAddress aStartAddr(4, 5, 0);
        TestCaseFormulaNoChange aCasesNoChange[] =
        {
            {
                u"=COS(A$2:A$100)"_ustr,  // No change because of abs col ref.
                aStartAddr,
                false,
                false
            },
            {
                u"=COS($A7:$A100)"_ustr,  // No intersection
                aStartAddr,
                false,
                false
            },
            {
                u"=COS($A5:$C7)"_ustr,   // No intersection 2-D range
                aStartAddr,
                false,
                false
            },
            {
                u"=SUMPRODUCT(COS(A6:A10))"_ustr,  // COS() in forced array mode
                aStartAddr,
                false,
                true
            },
            {
                u"=COS(A6:A10)"_ustr,  // Matrix formula
                aStartAddr,
                true,
                false
            }
        };

        for (const auto& rCase : aCasesNoChange)
        {
            if (rCase.bMatrixFormula)
            {
                ScMarkData aMark(m_pDoc->GetSheetLimits());
                aMark.SelectOneTable(0);
                SCCOL nColStart = rCase.aCellAddress.Col();
                SCROW nRowStart = rCase.aCellAddress.Row();
                m_pDoc->InsertMatrixFormula(nColStart, nRowStart, nColStart, nRowStart + 4,
                                            aMark, rCase.aFormula);
            }
            else
                m_pDoc->SetString(rCase.aCellAddress, rCase.aFormula);

            const ScFormulaCell* pCell = m_pDoc->GetFormulaCell(rCase.aCellAddress);
            const ScTokenArray* pCode = pCell->GetCode();
            CPPUNIT_ASSERT(pCode);

            sal_uInt16 nRPNLen = pCode->GetCodeLen();
            sal_uInt16 nRawLen = pCode->GetLen();
            sal_uInt16 nRawArgPos;
            if (rCase.bForcedArray)
            {
                nRawArgPos = 4;
                CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong raw token count.", static_cast<sal_uInt16>(7), nRawLen);
                CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong RPN token count.", static_cast<sal_uInt16>(3), nRPNLen);
            }
            else
            {
                nRawArgPos = 2;
                CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong raw token count.", static_cast<sal_uInt16>(4), nRawLen);
                CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong RPN token count.", static_cast<sal_uInt16>(2), nRPNLen);
            }

            FormulaToken** ppRawTokens = pCode->GetArray();
            FormulaToken** ppRPNTokens = pCode->GetCode();

            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong type of raw token(argument to COS)", svDoubleRef, ppRawTokens[nRawArgPos]->GetType());
            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong type of RPN token(argument to COS)", svDoubleRef, ppRPNTokens[0]->GetType());

            ScComplexRefData aArgRangeRaw = *ppRawTokens[nRawArgPos]->GetDoubleRef();
            ScComplexRefData aArgRangeRPN = *ppRPNTokens[0]->GetDoubleRef();
            bool bRawMatchRPNToken(aArgRangeRaw == aArgRangeRPN);
            CPPUNIT_ASSERT_MESSAGE("raw arg token and RPN arg token contents do not match", bRawMatchRPNToken);
        }
    }
}

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaCompilerImplicitIntersection1ParamWithChange)
{
    struct TestCaseFormula
    {
        OUString  aFormula;
        ScAddress aCellAddress;
        ScAddress aArgAddr;
    };

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

    {
        ScAddress aStartAddr(10, 5, 0);
        TestCaseFormula aCasesWithChange[] =
        {
            {
                u"=COS($A6:$A100)"_ustr,  // Corner case with intersection
                aStartAddr,
                ScAddress(0, 5, 0)
            },
            {
                u"=COS($A2:$A6)"_ustr,    // Corner case with intersection
                aStartAddr,
                ScAddress(0, 5, 0)
            },
            {
                u"=COS($A2:$A100)"_ustr,    // Typical 1D case
                aStartAddr,
                ScAddress(0, 5, 0)
            },
            {
                u"=COS($Formula.$A1:$C3)"_ustr,      // 2D corner case
                ScAddress(0, 0, 1),                      // Formula in sheet 1
                ScAddress(0, 0, 0)
            },
            {
                u"=COS($Formula.$A1:$C3)"_ustr,      // 2D corner case
                ScAddress(0, 2, 1),                      // Formula in sheet 1
                ScAddress(0, 2, 0)
            },
            {
                u"=COS($Formula.$A1:$C3)"_ustr,      // 2D corner case
                ScAddress(2, 0, 1),                      // Formula in sheet 1
                ScAddress(2, 0, 0)
            },
            {
                u"=COS($Formula.$A1:$C3)"_ustr,      // 2D corner case
                ScAddress(2, 2, 1),                      // Formula in sheet 1
                ScAddress(2, 2, 0)
            },
            {
                u"=COS($Formula.$A1:$C3)"_ustr,      // Typical 2D case
                ScAddress(1, 1, 1),                      // Formula in sheet 1
                ScAddress(1, 1, 0)
            }
        };

        for (const auto& rCase : aCasesWithChange)
        {
            m_pDoc->SetString(rCase.aCellAddress, rCase.aFormula);

            const ScFormulaCell* pCell = m_pDoc->GetFormulaCell(rCase.aCellAddress);
            const ScTokenArray* pCode = pCell->GetCode();
            CPPUNIT_ASSERT(pCode);

            sal_uInt16 nRPNLen = pCode->GetCodeLen();
            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong RPN token count.", static_cast<sal_uInt16>(2), nRPNLen);

            FormulaToken** ppRPNTokens = pCode->GetCode();

            CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong type of RPN token(argument to COS)", svSingleRef, ppRPNTokens[0]->GetType());

            ScSingleRefData aArgAddrRPN = *ppRPNTokens[0]->GetSingleRef();
            ScAddress aArgAddrActual = aArgAddrRPN.toAbs(*m_pDoc, rCase.aCellAddress);
            CPPUNIT_ASSERT_EQUAL_MESSAGE("Computed implicit intersection singleref is wrong", rCase.aArgAddr, aArgAddrActual);
        }
    }
}

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

    m_pDoc->SetString(ScAddress(1,2,0), u"=COS(A1:A5)"_ustr); // B3
    m_pDoc->SetString(ScAddress(1,3,0), u"=COS(A1:A5)"_ustr); // B4

    // Implicit intersection optimization in ScCompiler::HandleIIOpCode() internally changes
    // these to "=COS(A3)" and "=COS(A4)", but these shouldn't be merged into a formula group,
    // otherwise B4's formula would then be "=COS(A2:A6)".
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula in B3 has changed.", u"=COS(A1:A5)"_ustr, m_pDoc->GetFormula(1,2,0));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Formula in B4 has changed.", u"=COS(A1:A5)"_ustr, m_pDoc->GetFormula(1,3,0));

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestFormula, testFormulaCompilerImplicitIntersectionOperators)
{
    struct TestCase
    {
        OUString formula[3];
        double result[3];
    };

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

    m_pDoc->SetValue(2, 0, 0, 5); // C1
    m_pDoc->SetValue(2, 1, 0, 4); // C2
    m_pDoc->SetValue(2, 2, 0, 3); // C3
    m_pDoc->SetValue(3, 0, 0, 1); // D1
    m_pDoc->SetValue(3, 1, 0, 2); // D2
    m_pDoc->SetValue(3, 2, 0, 3); // D3

    TestCase tests[] =
    {
        { u"=C:C/D:D"_ustr, u"=C:C/D:D"_ustr, u"=C:C/D:D"_ustr, 5, 2, 1 },
        { u"=C1:C2/D1:D2"_ustr, u"=C2:C3/D2:D3"_ustr, u"=C3:C4/D3:D4"_ustr, 5, 2, 1 }
    };

    for (const TestCase& test : tests)
    {
        for(int i = 0; i < 2; ++i )
            m_pDoc->SetString(ScAddress(4,i,0), test.formula[i]); // E1-3
        for(int i = 0; i < 2; ++i )
            CPPUNIT_ASSERT_EQUAL_MESSAGE(OUString( test.formula[i] + " result incorrect in row " + OUString::number(i+1)).toUtf8().getStr(),
                test.result[i], m_pDoc->GetValue(ScAddress(4,i,0)));
    }

    m_pDoc->DeleteTab(0);
}

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

    constexpr sal_Int32 nCols = 2;
    constexpr sal_Int32 nRows = 5;

    // Values in A1:B5
    static constexpr sal_Int32 aMat[nRows][nCols] = {
        {4, 50},
        {5, 30},
        {4, 40},
        {0, 70},
        {5, 90}
    };

    for (sal_Int32 nCol = 0; nCol < nCols; ++nCol)
    {
        for (sal_Int32 nRow = 0; nRow < nRows; ++nRow)
            m_pDoc->SetValue(nCol, nRow, 0, aMat[nRow][nCol]);
    }

    m_pDoc->SetValue(2, 0, 0, 4); // C1 = 4
    m_pDoc->SetValue(3, 0, 0, 5); // D1 = 5

    ScMarkData aMark(m_pDoc->GetSheetLimits());
    aMark.SelectOneTable(0);

    struct TestCase
    {
        OUString aFormula;
        ScRange aTrimmableRange;
        double fResult;
        bool bMatrixFormula;
    };

    constexpr sal_Int32 nTestCases = 5;
    TestCase aTestCases[nTestCases] = {
        {
            u"=SUM(IF($C$1=A:A;B:B)/10*D1)"_ustr,
            ScRange(0, 0, 0, 0, 1048575, 0),
            45.0,
            true
        },

        {
            u"=SUM(IF(A:A=5;B:B)/10*D1)"_ustr,
            ScRange(0, 0, 0, 0, 1048575, 0),
            60.0,
            true
        },

        {
            u"=SUM(IF($C$1=A:A;B:B;B:B)/10*D1)"_ustr,  // IF has else clause
--> --------------------

--> maximum size reached

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