sc/qa/unit/data/functions/statistical/fods/countif.fods | 54 +++++++++++--
sc/qa/unit/data/functions/statistical/fods/countifs.fods | 58 +++++++++++++--
sc/source/core/data/queryiter.cxx | 9 ++
3 files changed, 106 insertions(+), 15 deletions(-)
New commits:
commit 8f61250bf9804ed54e7d960f1446a40b29d1a122
Author: Luboš Luňák <l.lu...@collabora.com>
AuthorDate: Fri May 13 17:01:32 2022 +0200
Commit: Luboš Luňák <l.lu...@collabora.com>
CommitDate: Sun May 15 10:50:15 2022 +0200
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.lu...@collabora.com>
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;"<>")"
calcext:value-type="string">
+ <text:p>=COUNTIF(I46:L46;"<>")</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;"<>")"
calcext:value-type="string">
+ <text:p>=COUNTIF(I47:L47;"<>")</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, "<" &
AF6,AG2:AG7,"<" & AG4)" calcext:value-type="string">
<text:p>=COUNTIFS(AF2:AF7, "<" &
AF6,AG2:AG7,"<" & 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, "<" &
AF6,AG2:AG7,"<" & 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, "<" &
AF6,AG2:AG7,"<" & 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="=""" 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="=""" 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="=""" 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())
commit 3165328258c2d7031801d6074611f82b59bec8b5
Author: Luboš Luňák <l.lu...@collabora.com>
AuthorDate: Fri May 13 15:31:28 2022 +0200
Commit: Luboš Luňák <l.lu...@collabora.com>
CommitDate: Sun May 15 10:50:00 2022 +0200
ScSortedRangeCache-based query doesn't work with mbRangeLookup
Change-Id: I9ce8e3547fc261e92e1330df4c054c3efe532d61
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/134286
Tested-by: Jenkins
Reviewed-by: Luboš Luňák <l.lu...@collabora.com>
diff --git a/sc/source/core/data/queryiter.cxx
b/sc/source/core/data/queryiter.cxx
index ac8c791710e2..f492d917b1eb 100644
--- a/sc/source/core/data/queryiter.cxx
+++ b/sc/source/core/data/queryiter.cxx
@@ -1229,6 +1229,8 @@ static bool CanBeUsedForSorterCache(const ScDocument&
rDoc, const ScQueryParam&
return false;
if(rParam.bHasHeader)
return false;
+ if(rParam.mbRangeLookup)
+ return false;
if(rParam.GetEntry(0).GetQueryItem().meType == ScQueryEntry::ByString
&& !ScQueryEvaluator::isMatchWholeCell(rDoc, rParam.GetEntry(0)))
return false; // substring matching cannot be sorted
