no ScSortedRangeCache-based query for number-as-string (tdf#149071)

The comparison depends on the cell encountered, which makes it hard
to prepare the sorted data.

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

3 files changed, 104 insertions, 15 deletions

diff --git a/sc/qa/unit/data/functions/statistical/fods/countif.fods b/sc/qa/unit/data/functions/statistical/fods/countif.fods
index 3e77a2ed49c8..41ee8f439791 100644
--- a/sc/qa/unit/data/functions/statistical/fods/countif.fods
+++ b/sc/qa/unit/data/functions/statistical/fods/countif.fods
@@ -4048,16 +4048,54 @@
      <table:table-cell table:style-name="ce58"/>
     </table:table-row>
     <table:table-row table:style-name="ro2">
+     <table:table-cell table:formula="of:=COUNTIF([.I46:.L46];[.F46])" office:value-type="float" office:value="0" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce65" table:formula="of:=ROUND([.A46];12)=ROUND([.B46];12)" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A46])" office:value-type="string" office:string-value="=COUNTIF(I46:L46;&quot;&lt;&gt;&quot;)" calcext:value-type="string">
+      <text:p>=COUNTIF(I46:L46;&quot;&lt;&gt;&quot;)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p><text:s/>numbers entered as string</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>3</text:p>
+     </table:table-cell>
      <table:table-cell table:number-columns-repeated="2"/>
-     <table:table-cell table:style-name="ce64"/>
-     <table:table-cell table:style-name="ce34"/>
-     <table:table-cell table:number-columns-repeated="4"/>
-     <table:table-cell table:style-name="ce20"/>
-     <table:table-cell table:number-columns-repeated="16"/>
-     <table:table-cell table:style-name="ce56"/>
-     <table:table-cell table:style-name="ce58"/>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>3</text:p>
+     </table:table-cell>
     </table:table-row>
-    <table:table-row table:style-name="ro2" table:number-rows-repeated="2">
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:formula="of:=COUNTIF([.I47:.L47];[.F47])" office:value-type="float" office:value="0" calcext:value-type="float">
+      <text:p>0</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="0" calcext:value-type="float">
+      <text:p>0</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce65" table:formula="of:=ROUND([.A47];12)=ROUND([.B47];12)" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:formula="of:=FORMULA([.A47])" office:value-type="string" office:string-value="=COUNTIF(I47:L47;&quot;&lt;&gt;&quot;)" calcext:value-type="string">
+      <text:p>=COUNTIF(I47:L47;&quot;&lt;&gt;&quot;)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p><text:s/>numbers entered as string</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>2</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="2"/>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>1</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
      <table:table-cell/>
      <table:table-cell table:style-name="ce23"/>
      <table:table-cell table:style-name="ce64"/>
diff --git a/sc/qa/unit/data/functions/statistical/fods/countifs.fods b/sc/qa/unit/data/functions/statistical/fods/countifs.fods
index 21f247f7139f..b236a8bebb83 100644
--- a/sc/qa/unit/data/functions/statistical/fods/countifs.fods
+++ b/sc/qa/unit/data/functions/statistical/fods/countifs.fods
@@ -3898,10 +3898,54 @@
      <table:table-cell table:style-name="ce25" table:formula="of:=FORMULA([.A37])" office:value-type="string" office:string-value="=COUNTIFS(AF2:AF7, &quot;&lt;&quot; &amp; AF6,AG2:AG7,&quot;&lt;&quot; &amp; AG4)" calcext:value-type="string">
       <text:p>=COUNTIFS(AF2:AF7, &quot;&lt;&quot; &amp; AF6,AG2:AG7,&quot;&lt;&quot; &amp; AG4)</text:p>
      </table:table-cell>
-     <table:table-cell table:number-columns-repeated="21"/>
-     <table:table-cell table:style-name="ce45"/>
-     <table:table-cell table:style-name="ce46"/>
-     <table:table-cell table:number-columns-repeated="7"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro8">
+     <table:table-cell table:formula="of:=COUNTIFS([.$I$38:.$I$39];[.F38])" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" table:formula="of:=ROUND([.A38];12)=ROUND([.B38];12)" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce25" table:formula="of:=FORMULA([.A38])" office:value-type="string" office:string-value="=COUNTIFS(AF2:AF7, &quot;&lt;&quot; &amp; AF6,AG2:AG7,&quot;&lt;&quot; &amp; AG4)" calcext:value-type="string">
+      <text:p>=COUNTIFS(I38:I39;F38)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>numbers entered as string</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>3</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="2"/>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>3</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro8">
+     <table:table-cell table:formula="of:=COUNTIFS([.$I$38:.$I$39];[.F39])" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>0</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="0" calcext:value-type="float">
+      <text:p>0</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" table:formula="of:=ROUND([.A39];12)=ROUND([.B39];12)" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
+      <text:p>TRUE</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce25" table:formula="of:=FORMULA([.A39])" office:value-type="string" office:string-value="=COUNTIFS(AF2:AF7, &quot;&lt;&quot; &amp; AF6,AG2:AG7,&quot;&lt;&quot; &amp; AG4)" calcext:value-type="string">
+      <text:p>=COUNTIFS(I38:I39;F39)</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>numbers entered as string</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>2</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="2"/>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>1</text:p>
+     </table:table-cell>
     </table:table-row>
     <table:table-row table:style-name="ro6" table:number-rows-repeated="4">
      <table:table-cell table:number-columns-repeated="2"/>
@@ -4335,17 +4379,17 @@
       <calcext:condition calcext:apply-style-name="Untitled1" calcext:value="=0" calcext:base-cell-address="Sheet2.C9"/>
       <calcext:condition calcext:apply-style-name="Untitled2" calcext:value="=1" calcext:base-cell-address="Sheet2.C9"/>
      </calcext:conditional-format>
-     <calcext:conditional-format calcext:target-range-address="Sheet2.C2:Sheet2.C30 Sheet2.C31:Sheet2.C37">
+     <calcext:conditional-format calcext:target-range-address="Sheet2.C2:Sheet2.C30 Sheet2.C31:Sheet2.C39">
       <calcext:condition calcext:apply-style-name="Default" calcext:value="=&quot;&quot;" calcext:base-cell-address="Sheet2.C2"/>
       <calcext:condition calcext:apply-style-name="Untitled1" calcext:value="=0" calcext:base-cell-address="Sheet2.C2"/>
       <calcext:condition calcext:apply-style-name="Untitled2" calcext:value="=1" calcext:base-cell-address="Sheet2.C2"/>
      </calcext:conditional-format>
-     <calcext:conditional-format calcext:target-range-address="Sheet2.C2:Sheet2.C30 Sheet2.C31:Sheet2.C37">
+     <calcext:conditional-format calcext:target-range-address="Sheet2.C2:Sheet2.C30 Sheet2.C31:Sheet2.C39">
       <calcext:condition calcext:apply-style-name="Default" calcext:value="=&quot;&quot;" calcext:base-cell-address="Sheet2.C2"/>
       <calcext:condition calcext:apply-style-name="Untitled1" calcext:value="=0" calcext:base-cell-address="Sheet2.C2"/>
       <calcext:condition calcext:apply-style-name="Untitled2" calcext:value="=1" calcext:base-cell-address="Sheet2.C2"/>
      </calcext:conditional-format>
-     <calcext:conditional-format calcext:target-range-address="Sheet2.C2:Sheet2.C30 Sheet2.C31:Sheet2.C37">
+     <calcext:conditional-format calcext:target-range-address="Sheet2.C2:Sheet2.C30 Sheet2.C31:Sheet2.C39">
       <calcext:condition calcext:apply-style-name="Default" calcext:value="=&quot;&quot;" calcext:base-cell-address="Sheet2.C2"/>
       <calcext:condition calcext:apply-style-name="Untitled1" calcext:value="=0" calcext:base-cell-address="Sheet2.C2"/>
       <calcext:condition calcext:apply-style-name="Untitled2" calcext:value="=1" calcext:base-cell-address="Sheet2.C2"/>
diff --git a/sc/source/core/data/queryiter.cxx b/sc/source/core/data/queryiter.cxx
index f492d917b1eb..aeb310c706b5 100644
--- a/sc/source/core/data/queryiter.cxx
+++ b/sc/source/core/data/queryiter.cxx
@@ -1238,6 +1238,13 @@ static bool CanBeUsedForSorterCache(const ScDocument& rDoc, const ScQueryParam&
         && rParam.GetEntry(0).eOp != SC_GREATER && rParam.GetEntry(0).eOp != SC_GREATER_EQUAL
         && rParam.GetEntry(0).eOp != SC_EQUAL)
         return false;
+    // tdf#149071 - numbers entered as string can be compared both as numbers
+    // and as strings, depending on the cell content, and that makes it hard to pre-sort
+    // the data; such queries are ScQueryEntry::ByValue but have maString set too
+    // (see ScQueryParamBase::FillInExcelSyntax())
+    if(rParam.GetEntry(0).GetQueryItem().meType == ScQueryEntry::ByValue
+        && rParam.GetEntry(0).GetQueryItem().maString.isValid())
+        return false;
     // For unittests allow inefficient caching, in order for the code to be checked.
     static bool inUnitTest = getenv("LO_TESTNAME") != nullptr;
     if(refData == nullptr || refData->Ref1.IsRowRel() || refData->Ref2.IsRowRel())