This is an automated email from the ASF dual-hosted git repository. jimjag pushed a commit to branch openformula in repository https://gitbox.apache.org/repos/asf/openoffice.git
commit 31ad94abb5cc8b22bda08b7841de5606d5d474f1 Author: Jim Jagielski <[email protected]> AuthorDate: Fri Nov 13 10:06:08 2026 -0500 sc: count every matching record in DCOUNT and DCOUNTA without a field Records with an empty cell in a criteria column were missed. --- main/sc/inc/dociter.hxx | 5 +++++ main/sc/source/core/data/dociter.cxx | 32 ++++++++++++++++++++++++++++++++ main/sc/source/core/tool/interpr1.cxx | 34 ++++++++++------------------------ test/odf-validate/calcval-cases14.tsv | 17 +++++++++++++++++ test/odf-validate/calcval.py | 7 +++++++ 5 files changed, 71 insertions(+), 24 deletions(-) diff --git a/main/sc/inc/dociter.hxx b/main/sc/inc/dociter.hxx index 078e630bdb..15d5927928 100644 --- a/main/sc/inc/dociter.hxx +++ b/main/sc/inc/dociter.hxx @@ -155,6 +155,7 @@ private: virtual bool getCurrent(Value& rValue) = 0; virtual bool getFirst(Value& rValue) = 0; virtual bool getNext(Value& rValue) = 0; + virtual SCSIZE countRecords() = 0; protected: const ScDBQueryDataIterator* mpParent; }; @@ -167,6 +168,7 @@ private: virtual bool getCurrent(Value& rValue); virtual bool getFirst(Value& rValue); virtual bool getNext(Value& rValue); + virtual SCSIZE countRecords(); private: ScDBQueryParamInternal* mpParam; @@ -191,6 +193,7 @@ private: virtual bool getCurrent(Value& rValue); virtual bool getFirst(Value& rValue); virtual bool getNext(Value& rValue); + virtual SCSIZE countRecords(); private: bool isValidQuery(SCROW mnRow, const ScMatrix& rMat) const; @@ -210,6 +213,8 @@ public: bool GetFirst(Value& rValue); /// Does NOT reset rValue if no value found! bool GetNext(Value& rValue); + /// Number of records matching the query, whatever their cells contain. + SCSIZE CountRecords(); }; // ============================================================================ diff --git a/main/sc/source/core/data/dociter.cxx b/main/sc/source/core/data/dociter.cxx index 388fb255e7..98d866e94f 100644 --- a/main/sc/source/core/data/dociter.cxx +++ b/main/sc/source/core/data/dociter.cxx @@ -682,6 +682,21 @@ bool ScDBQueryDataIterator::DataAccessInternal::getNext(Value& rValue) return getCurrent(rValue); } +SCSIZE ScDBQueryDataIterator::DataAccessInternal::countRecords() +{ + // A record can match with an empty cell in a query column ("=", "<>5"), + // getCurrent() only visits rows that have a cell in column nCol. + ScQueryParam aParam(*mpParam); + SCROW nFirstRow = mpParam->bHasHeader ? mpParam->nRow1 + 1 : mpParam->nRow1; + SCSIZE nCount = 0; + for (SCROW nR = nFirstRow; nR <= mpParam->nRow2; ++nR) + { + if (ScDBQueryDataIterator::IsQueryValid(*mpDoc, aParam, nTab, nR, NULL)) + ++nCount; + } + return nCount; +} + // ---------------------------------------------------------------------------- ScDBQueryDataIterator::DataAccessMatrix::DataAccessMatrix(const ScDBQueryDataIterator* pParent, ScDBQueryParamMatrix* pParam) : @@ -737,6 +752,18 @@ bool ScDBQueryDataIterator::DataAccessMatrix::getNext(Value& rValue) return getCurrent(rValue); } +SCSIZE ScDBQueryDataIterator::DataAccessMatrix::countRecords() +{ + const ScMatrix& rMat = *mpParam->mpMatrix; + SCSIZE nCount = 0; + for (SCROW nR = mpParam->bHasHeader ? 1 : 0; nR < mnRows; ++nR) + { + if (isValidQuery(nR, rMat)) + ++nCount; + } + return nCount; +} + namespace { bool lcl_isQueryByValue(const ScQueryEntry& rEntry, const ScMatrix& rMat, SCSIZE nCol, SCSIZE nRow) @@ -958,6 +985,11 @@ bool ScDBQueryDataIterator::GetNext(Value& rValue) return mpData->getNext(rValue); } +SCSIZE ScDBQueryDataIterator::CountRecords() +{ + return mpData->countRecords(); +} + // ============================================================================ ScCellIterator::ScCellIterator( ScDocument* pDocument, diff --git a/main/sc/source/core/tool/interpr1.cxx b/main/sc/source/core/tool/interpr1.cxx index 78a4f6216a..da1655752c 100644 --- a/main/sc/source/core/tool/interpr1.cxx +++ b/main/sc/source/core/tool/interpr1.cxx @@ -6876,30 +6876,10 @@ void ScInterpreter::ScDBCount() if (pQueryParam.get()) { sal_uLong nCount = 0; - if ( bMissingField && pQueryParam->GetType() == ScDBQueryParamBase::INTERNAL ) - { // count all matching records - // TODO: currently the QueryIterators only return cell pointers of - // existing cells, so if a query matches an empty cell there's - // nothing returned, and therefor not counted! - // Since this has ever been the case and this code here only came - // into existence to fix #i6899 and it never worked before we'll - // have to live with it until we reimplement the iterators to also - // return empty cells, which would mean to adapt all callers of - // iterators. - ScDBQueryParamInternal* p = static_cast<ScDBQueryParamInternal*>(pQueryParam.get()); - SCTAB nTab = p->nTab; - // ScQueryCellIterator doesn't make use of ScDBQueryParamBase::mnField, - // so the source range has to be restricted, like before the introduction - // of ScDBQueryParamBase. - p->nCol1 = p->nCol2 = p->mnField; - ScQueryCellIterator aCellIter( pDok, nTab, *p); - if ( aCellIter.GetFirst() ) - { - do - { - nCount++; - } while ( aCellIter.GetNext() ); - } + if ( bMissingField ) + { // count all matching records, whatever their cells contain + ScDBQueryDataIterator aRecIter( pDok, pQueryParam.release()); + nCount = aRecIter.CountRecords(); } else { // count only matching records with a value in the "result" field @@ -6930,6 +6910,12 @@ void ScInterpreter::ScDBCount2() if (pQueryParam.get()) { sal_uLong nCount = 0; + if ( bMissingField ) + { // count all matching records, whatever their cells contain + ScDBQueryDataIterator aRecIter( pDok, pQueryParam.release()); + PushDouble( aRecIter.CountRecords() ); + return; + } pQueryParam->mbSkipString = false; ScDBQueryDataIterator aValIter( pDok, pQueryParam.release()); ScDBQueryDataIterator::Value aValue; diff --git a/test/odf-validate/calcval-cases14.tsv b/test/odf-validate/calcval-cases14.tsv index a40f566121..9266fb4e6d 100644 --- a/test/odf-validate/calcval-cases14.tsv +++ b/test/odf-validate/calcval-cases14.tsv @@ -164,6 +164,23 @@ COUNTBLANK.empty =COUNTBLANK(D6:D7) cell 2 6.13.8 COUNTBLANK: truly empty cells COUNTBLANK.emptystr =COUNTBLANK(AM3) cell 1 6.13.8 a cell whose formula returns "" (AM3): implementation-defined whether it is blank; AOO counts it DATE.pre1583 =DATE(1500;1;1) cell #ERR 6.10.2 DATE(1500;1;1) outside constraints (Year>=1904); impl-defined/diagnostic per ODF 1.4 — NOT mandatory #ERR. THIS CALCVAL ASSERTION IS AOO-SPECIFIC DIAGNOSTIC ONLY (observed #VALUE!), not an ODF conformance assertion. SYN-001-criteria =DCOUNT(R1:T6;;V1:V2) cell 4 6.x Criteria syntax §4.11.11 via DCOUNT against existing R1:T6 database and V1:V2 criteria fixtures (Age>=30 → B,C,D,E) +DB.DCOUNT.nf.ne =DCOUNT(R1:T6;;AP1:AP2) cell 4 6.9.3 Field omitted: count of the records that satisfy the criteria. AP1:AP2 Score <>5; 4.11.8: <> with a value matches everything else, empty cells included: B,C,D,E (D's Score is empty) +DB.DCOUNTA.nf.ne =DCOUNTA(R1:T6;;AP1:AP2) cell 4 6.9.4 Field omitted: same record count as DCOUNT: B,C,D,E +DB.DCOUNT.f.ne =DCOUNT(R1:T6;"Score";AP1:AP2) cell 2 6.9.3 with a field: COUNT over Score of B,C,D,E (n/a,15,empty,25) = 2 +DB.DCOUNTA.f.ne =DCOUNTA(R1:T6;"Score";AP1:AP2) cell 3 6.9.4 with a field: COUNTA over Score of B,C,D,E (n/a,15,empty,25) = 3; the empty cell is not counted +DB.DCOUNT.nf.eq =DCOUNT(R1:T6;;AQ1:AQ2) cell 1 6.9.3 Field omitted; AQ1:AQ2 Score "="; 4.11.8: = with an empty value matches empty cells: D +DB.DCOUNTA.nf.eq =DCOUNTA(R1:T6;;AQ1:AQ2) cell 1 6.9.4 Field omitted; Score "=" matches D +DB.DCOUNT.f.eq =DCOUNT(R1:T6;"Score";AQ1:AQ2) cell 0 6.9.3 with a field: D matches but its Score is empty, COUNT 0 +DB.DCOUNT.nf.and =DCOUNT(R1:T6;;AR1:AS2) cell 1 6.9.3/4.11.11 one criteria row, all expressions must match: Score "=" and Age >=30: D +DB.DCOUNT.nf.and2 =DCOUNT(R1:T6;;AT1:AU2) cell 1 Same criteria as DB.DCOUNT.nf.and with the columns swapped (Age first): D; the result must not depend on column order +DB.DCOUNTA.nf.and =DCOUNTA(R1:T6;;AR1:AS2) cell 1 6.9.4 Field omitted; Score "=" and Age >=30: D +DB.DCOUNT.nf.or =DCOUNT(R1:T6;;AV1:AW3) cell 2 6.9.3/4.11.11 two criteria rows (or): Score 25, or Age 50: E and D (D's Score is empty) +DB.DCOUNTA.nf.or =DCOUNTA(R1:T6;;AV1:AW3) cell 2 6.9.4 Field omitted; Score 25 or Age 50: E and D +DB.DCOUNT.f.or =DCOUNT(R1:T6;"Age";AV1:AW3) cell 2 6.9.3 with a field: COUNT over Age of D and E = 2 +DB.DCOUNT.nf.inline =DCOUNT({"Name";"Age";"Score"|"A";20;5|"B";30;"n/a"|"C";40;15|"D";50;"x"|"E";60;25};;Y1:Y2) cell 1 6.9.3 Field omitted, inline array as the database (4.11.9 requires only ranges; AOO accepts arrays): Name A matches one record +DB.DCOUNTA.nf.inline =DCOUNTA({"Name";"Age";"Score"|"A";20;5|"B";30;"n/a"|"C";40;15|"D";50;"x"|"E";60;25};;Y1:Y2) cell 1 6.9.4 Field omitted, inline array as the database: Name A matches one record +DB.DCOUNT.nf.nocrit =DCOUNT(AX11:AY14;;AZ11:AZ12) cell - Field omitted, criteria cell empty (AZ12). 4.11.11 reads an empty criterion as 0 (no match); AOO ignores empty criteria (known deviation, see DCOUNT.emptycrit). Record only +DB.DCOUNT.f.nocrit =DCOUNT(AX11:AY14;"V";AZ11:AZ12) cell - Same as DB.DCOUNT.nf.nocrit with field V. Record only BITAND.3params =BITAND(6;3;1) cell #ERR 6.6.2 BITAND exactly 2 params enforced # OpenFormula Small Group: basic limits, syntax, operators and conversions (ODF 1.4 Part 4 sections 3.7, 5, 6.3, 6.4). diff --git a/test/odf-validate/calcval.py b/test/odf-validate/calcval.py index 4262c9ce8c..64e7439416 100644 --- a/test/odf-validate/calcval.py +++ b/test/odf-validate/calcval.py @@ -143,6 +143,13 @@ def setup_data(doc): for addr, f in (("AM1", "=NA()"), ("AM2", "=1/0"), ("AM3", '=""'), ("AN1", "5"), ("AN2", "=NA()"), ("AN4", "=NA()"), ("AN5", "7"), ("AN6", "=1/0")): sh.getCellRangeByName(addr).setFormula(f) sh.getCellRangeByName("AN3").setString("x") + put("AP1", [["Score"], ["<>5"]]) + put("AQ1", [["Score"], ["="]]) + put("AR1", [["Score", "Age"], ["=", ">=30"]]) + put("AT1", [["Age", "Score"], [">=30", "="]]) + put("AV1", [["Score", "Age"], ["25", None], [None, "50"]]) + put("AX11", [["K", "V"], ["a", 1], ["b", 2], ["c", 3]]) + put("AZ11", [["K"], [None]]) return sh
