fix ByValue lookups with ScSortedRangeCache

My fix for tdf#149071 actually disabled the optimization for all
ByValue lookups, because in fact all such lookups have maString set.
So lookups where the cells are a mix of numeric and string values
need different handling. A simple solution is detecting such a mix
when collecting the values for ScSortedRangeCache and disabling
the optimization in such a case. But it turns out that queries
containing such a mix are not that rare, as documents may e.g.
do COUNTIF($C:$C) where the given column has numeric values that
start with a textual header. So bail out only if the string cell
actually could affect the numeric query.
Also fix ScSortedRangeCache usage depending on query parameters,
different instances are needed for e.g. different ScQueryOp,
because the ScQueryEvaluator functions may return different
results (isQueryByString() is automatically true for SC_EQUAL).

Change-Id: Ib4565cbf6194e7c525c4d10d00b1c31707952a79
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/136403
Tested-by: Jenkins
Reviewed-by: Luboš Luňák <l.lunak@collabora.com>

1 files changed, 5 insertions, 3 deletions

diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index dbffd939d53d..957d93005375 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5803,7 +5803,8 @@ void ScInterpreter::ScCountIf()
             }
             else
             {
-                if(ScCountIfCellIteratorSortedCache::CanBeUsed(mrDoc, rParam, pMyFormulaCell, refData))
+                if(ScCountIfCellIteratorSortedCache::CanBeUsed(mrDoc, rParam, nTab1, pMyFormulaCell,
+                        refData, mrContext))
                 {
                     ScCountIfCellIteratorSortedCache aCellIter(mrDoc, mrContext, nTab1, rParam, false);
                     fCount += aCellIter.GetCount();
@@ -6198,7 +6199,8 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf
             }
             else
             {
-                if( ScQueryCellIteratorSortedCache::CanBeUsed( mrDoc, rParam, pMyFormulaCell, refData ))
+                if( ScQueryCellIteratorSortedCache::CanBeUsed( mrDoc, rParam, nTab1, pMyFormulaCell,
+                        refData, mrContext ))
                 {
                     ScQueryCellIteratorSortedCache aCellIter(mrDoc, mrContext, nTab1, rParam, false);
                     // Increment Entry.nField in iterator when switching to next column.
@@ -10045,7 +10047,7 @@ static bool lcl_LookupQuery( ScAddress & o_rResultPos, ScDocument& rDoc, ScInter
     }
     else // EQUAL
     {
-        if( ScQueryCellIteratorSortedCache::CanBeUsed( rDoc, rParam, cell, refData ))
+        if( ScQueryCellIteratorSortedCache::CanBeUsed( rDoc, rParam, rParam.nTab, cell, refData, rContext ))
         {
             ScQueryCellIteratorSortedCache aCellIter( rDoc, rContext, rParam.nTab, rParam, false);
             if (aCellIter.GetFirst())