Need help with ScLookupCache

[Winfried Donkers]

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