summaryrefslogtreecommitdiff
path: root/sc
diff options
context:
space:
mode:
authorEike Rathke <erack@redhat.com>2013-11-27 23:43:09 +0100
committerKohei Yoshida <libreoffice@kohei.us>2013-12-03 09:39:36 -0600
commit2259f4a6a93a0dd14e961aa6eb33f49752fc86dc (patch)
tree5f1dd5d8d0a443621cb3f09c904afb2e687e6339 /sc
parent0f4a452be3342d0206d6724faf170fa2786a7777 (diff)
resolved fdo#71589 reimplemented horizontal range lookup
Regression introduced with ebdd9c300718bce454ef56a31d5d8fb699fc1822 (first eaea417bfdf8d06df2b7f2e42c904c32ce77e871) that removed the bMixedComparison member from ScQueryParam under the false assumption that is was only used to emulate a legacy Excel behavior. In fact it was also needed to do the at least horizontal range lookup in sorted mixed data, though didn't evaluate exactly the same conditions as Excel and defined in ODFF. Reimplemented a similar behavior for the new code structures but this time also checking for the additional condtion that a query ByString does not return the last numeric result and vice versa, which previously was missing. (cherry picked from commit f0701470858f57a855ba57c0c2283e52953db327) Conflicts: sc/source/core/data/dociter.cxx Backported. Change-Id: I46061777879ba5301bfcaca2d50cf87a994f93f2 Reviewed-on: https://gerrit.libreoffice.org/6839 Reviewed-by: Kohei Yoshida <libreoffice@kohei.us> Tested-by: Kohei Yoshida <libreoffice@kohei.us>
Diffstat (limited to 'sc')
-rw-r--r--sc/inc/queryparam.hxx1
-rw-r--r--sc/source/core/data/dociter.cxx62
-rw-r--r--sc/source/core/data/table3.cxx42
-rw-r--r--sc/source/core/tool/queryparam.cxx5
4 files changed, 107 insertions, 3 deletions
diff --git a/sc/inc/queryparam.hxx b/sc/inc/queryparam.hxx
index 26d2d1b2a087..445c23c06aac 100644
--- a/sc/inc/queryparam.hxx
+++ b/sc/inc/queryparam.hxx
@@ -36,6 +36,7 @@ struct ScQueryParamBase
bool bCaseSens;
bool bRegExp;
bool bDuplicate;
+ bool mbRangeLookup; ///< for spreadsheet functions like MATCH, LOOKUP, HLOOKUP, VLOOKUP
virtual ~ScQueryParamBase();
diff --git a/sc/source/core/data/dociter.cxx b/sc/source/core/data/dociter.cxx
index f8ba16f5f433..e050e5816c5a 100644
--- a/sc/source/core/data/dociter.cxx
+++ b/sc/source/core/data/dociter.cxx
@@ -1325,6 +1325,17 @@ bool ScQueryCellIterator::FindEqualOrSortedLastInRange( SCCOL& nFoundCol,
SCROW& nFoundRow, bool bSearchForEqualAfterMismatch,
bool bIgnoreMismatchOnLeadingStringsP )
{
+ // Set and automatically reset mpParam->mbRangeLookup when returning. We
+ // could use comphelper::FlagRestorationGuard, but really, that one is
+ // overengineered for this simple purpose here.
+ struct BoolResetter
+ {
+ bool& mr;
+ bool mb;
+ BoolResetter( bool& r, bool b ) : mr(r), mb(r) { r = b; }
+ ~BoolResetter() { mr = mb; }
+ } aRangeLookupResetter( mpParam->mbRangeLookup, true);
+
nFoundCol = MAXCOL+1;
nFoundRow = MAXROW+1;
SetStopOnMismatch( true ); // assume sorted keys
@@ -1333,7 +1344,22 @@ bool ScQueryCellIterator::FindEqualOrSortedLastInRange( SCCOL& nFoundCol,
bool bRegExp = mpParam->bRegExp && mpParam->GetEntry(0).GetQueryItem().meType == ScQueryEntry::ByString;
bool bBinary = !bRegExp && mpParam->bByRow && (mpParam->GetEntry(0).eOp ==
SC_LESS_EQUAL || mpParam->GetEntry(0).eOp == SC_GREATER_EQUAL);
- if (bBinary ? (BinarySearch() ? GetThis() : 0) : GetFirst())
+ bool bFound = false;
+ if (bBinary)
+ {
+ if (BinarySearch())
+ {
+ // BinarySearch() already positions correctly and only needs real
+ // query comparisons afterwards, skip the verification check below.
+ mpParam->mbRangeLookup = false;
+ bFound = GetThis();
+ }
+ }
+ else
+ {
+ bFound = GetFirst();
+ }
+ if (bFound)
{
// First equal entry or last smaller than (greater than) entry.
SCSIZE nColRowSave;
@@ -1351,9 +1377,43 @@ bool ScQueryCellIterator::FindEqualOrSortedLastInRange( SCCOL& nFoundCol,
{
// Step back to last in range and adjust position markers for
// GetNumberFormat() or similar.
+ SCCOL nColDiff = nCol - nFoundCol;
nCol = nFoundCol;
nRow = nFoundRow;
nColRow = nColRowSave;
+ if (mpParam->mbRangeLookup)
+ {
+ // Verify that the found entry does not only fulfill the range
+ // lookup but also the real query, i.e. not numeric was found
+ // if query is ByString and vice versa.
+ mpParam->mbRangeLookup = false;
+ // Step back the last field advance if GetNext() did one.
+ if (bAdvanceQuery && nColDiff)
+ {
+ SCSIZE nEntries = mpParam->GetEntryCount();
+ for (SCSIZE j=0; j < nEntries; ++j)
+ {
+ ScQueryEntry& rEntry = mpParam->GetEntry( j );
+ if (rEntry.bDoQuery)
+ {
+ if (rEntry.nField - nColDiff >= 0)
+ rEntry.nField -= nColDiff;
+ else
+ {
+ assert(!"FindEqualOrSortedLastInRange: rEntry.nField -= nColDiff < 0");
+ }
+ }
+ else
+ break; // for
+ }
+ }
+ // Check it.
+ if (!GetThis())
+ {
+ nFoundCol = MAXCOL+1;
+ nFoundRow = MAXROW+1;
+ }
+ }
}
}
if ( IsEqualConditionFulfilled() )
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index b61be80ce35b..73f952ddc9e0 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -1519,6 +1519,41 @@ public:
return std::pair<bool,bool>(bOk, bTestEqual);
}
+
+ // To be called only if both isQueryByValue() and isQueryByString()
+ // returned false and range lookup is wanted! In range lookup comparison
+ // numbers are less than strings. Nothing else is compared.
+ std::pair<bool,bool> compareByRangeLookup(
+ ScBaseCell* pCell, SCCOL nCol, SCROW nRow,
+ const ScQueryEntry& rEntry, const ScQueryEntry::Item& rItem)
+ {
+ bool bTestEqual = false;
+
+ if (rItem.meType == ScQueryEntry::ByString && rEntry.eOp != SC_LESS && rEntry.eOp != SC_LESS_EQUAL)
+ return std::pair<bool,bool>(false, bTestEqual);
+
+ if (rItem.meType != ScQueryEntry::ByString && rEntry.eOp != SC_GREATER && rEntry.eOp != SC_GREATER_EQUAL)
+ return std::pair<bool,bool>(false, bTestEqual);
+
+ if (pCell)
+ {
+ if (rItem.meType == ScQueryEntry::ByString)
+ {
+ if (pCell->GetCellType() == CELLTYPE_FORMULA && static_cast<ScFormulaCell*>(pCell)->GetErrCode())
+ // Error values are compared as string.
+ return std::pair<bool,bool>(false, bTestEqual);
+
+ return std::pair<bool,bool>(pCell->HasValueData(), bTestEqual);
+ }
+
+ return std::pair<bool,bool>(!pCell->HasValueData(), bTestEqual);
+ }
+
+ if (rItem.meType == ScQueryEntry::ByString)
+ return std::pair<bool,bool>(mrTab.HasValueData(nCol, nRow), bTestEqual);
+
+ return std::pair<bool,bool>(!mrTab.HasValueData(nCol, nRow), bTestEqual);
+ }
};
}
@@ -1582,6 +1617,13 @@ bool ScTable::ValidQuery(
aRes.first |= aThisRes.first;
aRes.second |= aThisRes.second;
}
+ else if (rParam.mbRangeLookup)
+ {
+ std::pair<bool,bool> aThisRes =
+ aEval.compareByRangeLookup(pCell, nCol, nRow, rEntry, *itr);
+ aRes.first |= aThisRes.first;
+ aRes.second |= aThisRes.second;
+ }
if (aRes.first && aRes.second)
break;
diff --git a/sc/source/core/tool/queryparam.cxx b/sc/source/core/tool/queryparam.cxx
index 826ea3e3981e..781816a3d9e5 100644
--- a/sc/source/core/tool/queryparam.cxx
+++ b/sc/source/core/tool/queryparam.cxx
@@ -61,7 +61,8 @@ ScQueryParamBase::ScQueryParamBase() :
bInplace(true),
bCaseSens(false),
bRegExp(false),
- bDuplicate(false)
+ bDuplicate(false),
+ mbRangeLookup(false)
{
for (size_t i = 0; i < MAXQUERY; ++i)
maEntries.push_back(new ScQueryEntry);
@@ -69,7 +70,7 @@ ScQueryParamBase::ScQueryParamBase() :
ScQueryParamBase::ScQueryParamBase(const ScQueryParamBase& r) :
bHasHeader(r.bHasHeader), bByRow(r.bByRow), bInplace(r.bInplace), bCaseSens(r.bCaseSens),
- bRegExp(r.bRegExp), bDuplicate(r.bDuplicate),
+ bRegExp(r.bRegExp), bDuplicate(r.bDuplicate), mbRangeLookup(r.mbRangeLookup),
maEntries(r.maEntries)
{
}