https://bugs.documentfoundation.org/show_bug.cgi?id=171581
--- Comment #1 from [email protected] --- IA says: Yes. I dug into the LibreOffice Calc code path, and there is a plausible fix. The key clue is in sc/source/core/data/queryevaluator.cxx: when a function like COUNTIF supplies a numeric criterion directly, the evaluator may still enter the string-comparison path for =/<>, but it explicitly refuses to match string cells if rItem.maString is empty. There is even an old comment saying that for functions such as match, countif, sumif, vlookup, hlookup, lookup, “the query value is assigned directly, and the string is empty”, so string matches are intentionally skipped in that case. That matches your symptom very well: with C3 ('123), the criterion goes through FillInExcelSyntax(...), which populates the query entry from text syntax; with C4 (123), COUNTIF sets only rItem.meType = ByValue and rItem.mfVal = fVal, but does not populate rItem.maString; later, for SC_EQUAL, the evaluator can compare by string too, but if maString is empty it bails out and does not match text cells. So the asymmetry is very likely here. Where I would patch it Two places in sc/source/core/tool/interpr1.cxx: ScInterpreter::ScCountIf() ScInterpreter::IterateParametersIfs() Both have the same pattern: if (!bIsString) { rItem.meType = ScQueryEntry::ByValue; rItem.mfVal = fVal; rEntry.eOp = SC_EQUAL; } else { rParam.FillInExcelSyntax(...); ... } That is the branch I would change. Minimal candidate patch I would try this first: if (!bIsString) { rItem.meType = ScQueryEntry::ByValue; rItem.mfVal = fVal; rEntry.eOp = SC_EQUAL; // Also provide a string representation so that SC_EQUAL/SC_NOT_EQUAL // can match text cells containing the same numeric text, just like when // the criterion comes from a text cell and goes through FillInExcelSyntax(). rItem.maString = mrDoc.GetSharedStringPool().intern(OUString::number(fVal)); } else { rParam.FillInExcelSyntax(mrDoc.GetSharedStringPool(), aString.getString(), 0, &mrContext); if (rItem.meType == ScQueryEntry::ByString) rParam.eSearchType = DetectSearchType(rItem.maString.getString(), mrDoc); } and the same change in the analogous numeric branch inside IterateParametersIfs(). Why this is the likely fix Because the evaluator already has logic that allows ByValue items to participate in string matching on non-numeric cells for SC_EQUAL, but it relies on rItem.maString being present. That is visible here: SC_EQUAL and SC_NOT_EQUAL are treated as text-match operators too; for non-numeric cells, the fast/generic string path may include ByValue items; but if rItem.meType != ByString && rItem.maString.isEmpty(), it explicitly returns no match. So the bug is not that the evaluator cannot do the comparison. It is that COUNTIF does not feed it the string form of the numeric criterion. Caveat I have not built LibreOffice here, so I cannot promise that OUString::number(fVal) is the final correct representation for all locales and all floating-point edge cases. That is the weak point of the minimal patch. For integers like your 123, it is very likely fine. For decimals, scientific notation, locale-specific separators, and “value-as-shown” cases, a more robust conversion may be needed. More robust variant Instead of OUString::number(fVal), the ideal patch would use the same canonical criterion-text representation that Calc already expects for numeric comparisons, then intern that into maString. If you want to make the patch safer, I would inspect the numeric-formatting helpers used around query parsing and matching, especially in: sc/source/core/tool/queryparam.cxx (FillInExcelSyntax) sc/source/core/data/queryevaluator.cxx (compareByValue, compareByString) What I would submit as a first patch If you want something pragmatic for the bug report or Gerrit, I would send this as the first candidate: diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx --- a/sc/source/core/tool/interpr1.cxx +++ b/sc/source/core/tool/interpr1.cxx @@ -19348,6 +19348,11 @@ if (!bIsString) { rItem.meType = ScQueryEntry::ByValue; rItem.mfVal = fVal; rEntry.eOp = SC_EQUAL; + // Keep a textual form too, so COUNTIF/COUNTIFS equality checks + // can match text cells containing the same numeric text. + if (rItem.maString.isEmpty()) + rItem.maString = + mrDoc.GetSharedStringPool().intern(OUString::number(fVal)); } else { @@ -19048,6 +19048,11 @@ if (!bIsString) { rItem.meType = ScQueryEntry::ByValue; rItem.mfVal = fVal; rEntry.eOp = SC_EQUAL; + // Keep a textual form too, so COUNTIF equality checks + // can match text cells containing the same numeric text. + if (rItem.maString.isEmpty()) + rItem.maString = + mrDoc.GetSharedStringPool().intern(OUString::number(fVal)); } else { I would describe it like this: Populate ScQueryEntry::Item::maString for numeric COUNTIF/COUNTIFS equality criteria, so mixed text/numeric matches behave symmetrically for = comparisons. What I would test after patching At minimum: '123 and 123 with COUNTIF(range;C3) and COUNTIF(range;C4) → both 2 same with COUNTIFS decimals like '1.5 and 1.5 negative numbers 0 leading zeros: '0123 vs 123 — here you must decide whether matching is desirable scientific notation text like '1E3 vs 1000 That last group matters because the patch may improve your bug but also change semantics for numeric-looking text in edge cases. -- You are receiving this mail because: You are the assignee for the bug.
