Luboš, Eike,
Op 21-06-2022 om 15:12 schreef Winfried Donkers:
Am I correct when stating that ScLookupCache currently cannot cope
with the
combinations
eOp SC_GREATER_EQUAL and sorted in ascending order
and
eOp SC_LESSER_EQUAL and sorted in descending order?
As Luboš already said, ScLookupCache just caches results of lookups, to
be able to reuse them for same lookups with further columns. Using the
same for example SC_GREATER_EQUAL operator with different ascending or
descending sort orders should however invalidate the cache for that
position, so you'll probably also need to take the sort order into
account in ScLookupCache::QueryCriteria and ScLookupCache::QueryKey.
That could be accomplished without adding an extra field if the
ScLookupCache::QueryOp simply would have different values for
SC_GREATER_EQUAL ascending or descending, similar for SC_LESS_EQUAL; so
just add them and map accordingly in the
ScLookupCache::QueryCriteria::QueryCriteria() ctor. You'll probably need
to add some flag to ScQueryEntry anyway.
It is the function BinarySearch in sc/source/core/data/queryiter.cxx
that presents 'challenges'.
Currently its search direction (and probably whether the cells are
ordered or not and in which direction) depends on rEntry.eOp.
For MATCH, LOOKUP, HLOOKUP and VLOOKUP this is OK, for XLOOKUP this is
quite different.
XLOOKUP has 4 search modes (forwards and backwards (data not sorted) as
well as ascending and descending (data sorted) and 4 match modes of
which 3 are relevant here ( exact match (SC_EQUAL), exact or less
(SC_LESS_EQUAL, exact or greater (SC_GREATER_EQUAL) and wildcard (
SC_EQUAL with wildcard/regexp enabled)). The search modes and match
modes can be used in any combination.
Incorporating this in BinarySearch will be a lot of work and probably
reduce performance for all other uses than XLOOKUP.
I tend to make an XBinarySearch function that will be called from
BinarySearch when an XLOOKUPIndicator is true. I hope to be able to use
BinarySearch as foundation and expand it for XLOOKUP.
I wonder if this is a wise way to go or whether there are better
alternative solutions?
Winfried
