bring back optimized calc querying by value (tdf#133878)

When filtering, if the filtered value was a number, Calc used
to convert the string value from the autofilter dialog to a number,
since querying by numerical value is much faster then comparing
strings. Commit 42e472b5870278058537d43d03d457dc80b16166 tried
to fix a problem with filtering time values (that are internally
also represented as a float) by moving this optimization elsewhere,
but that had 2 problems: 1) the problem was with the time value
getting rounded to seconds for display and then converting back
to float value for querying, so the move would not have fixed
anything, 2) the move "fixed" the problem by essentially disabling
the optimization (the new code doesn't get called with the doc
from tdf#105629).
This commit reverts that (actually, it does the optimization in both
places, since at least the unittest requires it in the other place,
and it seems to make sense to keep it there too). As a fix
for the original problem, this this optimization is allowed
only for number formats where this problem should not occur.

Change-Id: I2fb56dea9577c59b870c29e26bb5f09ef9ddec82
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/106594
Tested-by: Jenkins
Reviewed-by: Kohei Yoshida <kohei@libreoffice.org>
Reviewed-by: Luboš Luňák <l.lunak@collabora.com>
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/114530
Tested-by: Gabor Kelemen <kelemen.gabor2@nisz.hu>
Reviewed-by: Gabor Kelemen <kelemen.gabor2@nisz.hu>

1 files changed, 40 insertions, 8 deletions

diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index 3e03ae111998..3ac587fe1580 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -2978,6 +2978,29 @@ void ScTable::TopTenQuery( ScQueryParam& rParam )
 
 namespace {
 
+bool CanOptimizeQueryStringToNumber( SvNumberFormatter* pFormatter, sal_uInt32 nFormatIndex )
+{
+    // tdf#105629: ScQueryEntry::ByValue queries are faster than ScQueryEntry::ByString.
+    // The problem with this optimization is that the autofilter dialog apparently converts
+    // the value to text and then converts that back to a number for filtering.
+    // If that leads to any change of value (such as when time is rounded to seconds),
+    // even matching values will be filtered out. Therefore query by value only for formats
+    // where no such change should occur.
+    if(const SvNumberformat* pEntry = pFormatter->GetEntry(nFormatIndex))
+    {
+        switch(pEntry->GetType())
+        {
+        case SvNumFormatType::NUMBER:
+        case SvNumFormatType::FRACTION:
+        case SvNumFormatType::SCIENTIFIC:
+            return true;
+        default:
+            break;
+        }
+    }
+    return false;
+}
+
 class PrepareQueryItem
 {
     const ScDocument& mrDoc;
@@ -2986,15 +3009,26 @@ public:
 
     void operator() (ScQueryEntry::Item& rItem)
     {
-        // Double-check if the query by date is really appropriate.
-
-        if (rItem.meType != ScQueryEntry::ByDate)
+        if (rItem.meType != ScQueryEntry::ByString && rItem.meType != ScQueryEntry::ByDate)
             return;
 
         sal_uInt32 nIndex = 0;
         bool bNumber = mrDoc.GetFormatTable()->
             IsNumberFormat(rItem.maString.getString(), nIndex, rItem.mfVal);
 
+        // Advanced Filter creates only ByString queries that need to be
+        // converted to ByValue if appropriate. rItem.mfVal now holds the value
+        // if bNumber==true.
+
+        if (rItem.meType == ScQueryEntry::ByString)
+        {
+            if (bNumber && CanOptimizeQueryStringToNumber( mrDoc.GetFormatTable(), nIndex ))
+                rItem.meType = ScQueryEntry::ByValue;
+            return;
+        }
+
+        // Double-check if the query by date is really appropriate.
+
         if (bNumber && ((nIndex % SV_COUNTRY_LANGUAGE_OFFSET) != 0))
         {
             const SvNumberformat* pEntry = mrDoc.GetFormatTable()->GetEntry(nIndex);
@@ -3360,19 +3394,17 @@ bool ScTable::CreateQueryParam(SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow
 
     SvNumberFormatter* pFormatter = pDocument->GetFormatTable();
     nCount = rQueryParam.GetEntryCount();
-
     if (bValid)
     {
-        //  bQueryByString must be set
+        //  query type must be set
         for (i=0; i < nCount; i++)
         {
             ScQueryEntry::Item& rItem = rQueryParam.GetEntry(i).GetQueryItem();
-
             sal_uInt32 nIndex = 0;
             bool bNumber = pFormatter->IsNumberFormat(
                 rItem.maString.getString(), nIndex, rItem.mfVal);
-
-            rItem.meType = bNumber ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
+            rItem.meType = bNumber && CanOptimizeQueryStringToNumber( pFormatter, nIndex )
+                ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
         }
     }
     else