tdf#142910 sc filter: fix "greater than" or "smaller than" etc

Filter "greater than" or "smaller than" (>, <, >=, <=)
conditions according to the cell number format.

Regression from commit: d5c2584bf36d21580db677b231c57f99f49aa2cb
(Related: tdf#140968 avoid duplicated filter values)

Follow-up to commit: 1f755525189884e4b2824889a6b9dea8933402db
(tdf#142402 sc UI: store formatted values in standard filter)

Clean-up for commit: d5c2584bf36d21580db677b231c57f99f49aa2cb
(Related: tdf#140968 avoid duplicated filter values)

Change-Id: I1284892398c9964ca5407b4d617a617f20341107
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/118272
Tested-by: Jenkins
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>
Signed-off-by: Xisco Fauli <xiscofauli@libreoffice.org>
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/118593

14 files changed, 122 insertions, 135 deletions

diff --git a/sc/inc/queryentry.hxx b/sc/inc/queryentry.hxx
index 1855744f78f7..94ea761c1239 100644
--- a/sc/inc/queryentry.hxx
+++ b/sc/inc/queryentry.hxx
@@ -48,10 +48,9 @@ struct SC_DLLPUBLIC ScQueryEntry
         double        mfVal;
         svl::SharedString maString;
         bool              mbMatchEmpty;
-        bool              mbFormattedValue;
         Color maColor;
 
-        Item() : meType(ByValue), mfVal(0.0), mbMatchEmpty(false), mbFormattedValue(false) {}
+        Item() : meType(ByValue), mfVal(0.0), mbMatchEmpty(false) {}
 
         bool operator== (const Item& r) const;
     };
diff --git a/sc/inc/typedstrdata.hxx b/sc/inc/typedstrdata.hxx
index 50a7effea87e..7c7b1c7e45d4 100644
--- a/sc/inc/typedstrdata.hxx
+++ b/sc/inc/typedstrdata.hxx
@@ -24,14 +24,14 @@ public:
         Header   = 4
     };
 
-    ScTypedStrData( const OUString& rStr, double nVal = 0.0, StringType eType = Standard,
-                    bool bDate = false, bool mbIsFormatted = false, bool bDuplicated = false );
+    ScTypedStrData( const OUString& rStr, double fVal = 0.0, double fRVal = 0.0, StringType eType = Standard,
+                    bool bDate = false );
 
     bool IsDate() const { return mbIsDate;}
     const OUString& GetString() const { return maStrValue;}
     StringType GetStringType() const { return meStrType;}
     double GetValue() const { return mfValue; }
-    bool IsDuplicated() const { return mbIsDuplicated; }
+    double GetRoundedValue() const { return mfRoundedValue; }
 
     struct LessCaseSensitive
     {
@@ -58,10 +58,9 @@ public:
 private:
     OUString maStrValue;
     double mfValue;
+    double mfRoundedValue; // rounded value by format code
     StringType meStrType;
     bool   mbIsDate;
-    bool   mbIsFormatted; // true if the cell value is a formatted filter value
-    bool   mbIsDuplicated; // true if the cell has a formatted filter value and has at least one duplicate formatted value.
 };
 
 class FindTypedStrData
diff --git a/sc/qa/uitest/autofilter/autofilter.py b/sc/qa/uitest/autofilter/autofilter.py
index 060ada717844..7c8078c702c5 100644
--- a/sc/qa/uitest/autofilter/autofilter.py
+++ b/sc/qa/uitest/autofilter/autofilter.py
@@ -469,20 +469,8 @@ class AutofilterTest(UITestCase):
 
         xGridWin = self.xUITest.getTopFocusWindow().getChild("grid_window")
 
-        xGridWin.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "0", "ROW": "0"}))
-        xFloatWindow = self.xUITest.getFloatWindow()
-        #Choose Standard Filter... button
-        xMenu = xFloatWindow.getChild("menu")
-
-        xMenu.executeAction("TYPE", mkPropertyValues({"KEYCODE":"DOWN"}))
-        xMenu.executeAction("TYPE", mkPropertyValues({"KEYCODE":"DOWN"}))
-        xMenu.executeAction("TYPE", mkPropertyValues({"KEYCODE":"DOWN"}))
-        xMenu.executeAction("TYPE", mkPropertyValues({"KEYCODE":"DOWN"}))
-        xMenu.executeAction("TYPE", mkPropertyValues({"KEYCODE":"DOWN"}))
-        xMenu.executeAction("TYPE", mkPropertyValues({"KEYCODE":"DOWN"}))
-        xMenu.executeAction("TYPE", mkPropertyValues({"KEYCODE":"DOWN"}))
-        xMenu.executeAction("TYPE", mkPropertyValues({"KEYCODE":"RETURN"}))
-
+        xGridWin.executeAction("SELECT", mkPropertyValues({"RANGE": "A1:B8"}))
+        self.ui_test.execute_modeless_dialog_through_command(".uno:DataFilterStandardFilter")
         xDialog = self.xUITest.getTopFocusWindow()
         xval1 = xDialog.getChild("val1")
 
@@ -500,4 +488,61 @@ class AutofilterTest(UITestCase):
         self.assertTrue(is_row_hidden(doc, 6))
 
         self.ui_test.close_doc()
+
+    def test_tdf142910(self):
+        doc = self.ui_test.load_file(get_url_for_data_file("tdf140968.xlsx"))
+
+        xGridWin = self.xUITest.getTopFocusWindow().getChild("grid_window")
+
+        #Test '<' condition
+        xGridWin.executeAction("SELECT", mkPropertyValues({"RANGE": "A1:B8"}))
+        self.ui_test.execute_modeless_dialog_through_command(".uno:DataFilterStandardFilter")
+        xDialog = self.xUITest.getTopFocusWindow()
+
+        xfield1 = xDialog.getChild("field1")
+        xval1 = xDialog.getChild("val1")
+        xcond1 = xDialog.getChild("cond1")
+
+        select_by_text(xfield1, "Values")
+        select_by_text(xcond1, "<")
+        select_by_text(xval1, "0.365")
+
+        xOKBtn = xDialog.getChild("ok")
+        self.ui_test.close_dialog_through_button(xOKBtn)
+
+        self.assertFalse(is_row_hidden(doc, 0))
+        self.assertTrue(is_row_hidden(doc, 1))
+        self.assertFalse(is_row_hidden(doc, 2))
+        self.assertFalse(is_row_hidden(doc, 3))
+        self.assertFalse(is_row_hidden(doc, 4))
+        self.assertFalse(is_row_hidden(doc, 5))
+        self.assertTrue(is_row_hidden(doc, 6))
+        self.assertTrue(is_row_hidden(doc, 7))
+
+        #Test '>=' condition
+        xGridWin.executeAction("SELECT", mkPropertyValues({"RANGE": "A1:B8"}))
+        self.ui_test.execute_modeless_dialog_through_command(".uno:DataFilterStandardFilter")
+        xDialog = self.xUITest.getTopFocusWindow()
+        xfield1 = xDialog.getChild("field1")
+        xval1 = xDialog.getChild("val1")
+        xcond1 = xDialog.getChild("cond1")
+
+        select_by_text(xfield1, "Values")
+        select_by_text(xcond1, ">=")
+        select_by_text(xval1, "0.046")
+
+        xOKBtn = xDialog.getChild("ok")
+        self.ui_test.close_dialog_through_button(xOKBtn)
+
+        self.assertFalse(is_row_hidden(doc, 0))
+        self.assertFalse(is_row_hidden(doc, 1))
+        self.assertFalse(is_row_hidden(doc, 2))
+        self.assertFalse(is_row_hidden(doc, 3))
+        self.assertFalse(is_row_hidden(doc, 4))
+        self.assertTrue(is_row_hidden(doc, 5))
+        self.assertFalse(is_row_hidden(doc, 6))
+        self.assertFalse(is_row_hidden(doc, 7))
+
+        self.ui_test.close_doc()
+
 # vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/sc/source/core/data/column3.cxx b/sc/source/core/data/column3.cxx
index 73a2ed613548..8faa97d2a050 100644
--- a/sc/source/core/data/column3.cxx
+++ b/sc/source/core/data/column3.cxx
@@ -2499,10 +2499,11 @@ class FilterEntriesHandler
             sal_uInt32 nIndex = pFormatter->GetFormatIndex(NF_DATETIME_ISO_YYYYMMDD_HHMMSS);
             pFormatter->GetInputLineString(fVal, nIndex, aStr);
         }
-        /* use string compare later for formatted and filtered cell values
-        to avoid duplicates in the filter lists with setting the mbIsFormatted */
-        bool bFormFiltVal = mrColumn.HasFiltering() && nFormat;
-        mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, ScTypedStrData::Value, bDate, bFormFiltVal));
+        // store the formatted/rounded value for filtering
+        if (nFormat && !bDate)
+            mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, rColumn.GetDoc().RoundValueAsShown(fVal, nFormat), ScTypedStrData::Value, bDate));
+        else
+            mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, fVal, ScTypedStrData::Value, bDate));
     }
 
 public:
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index f8e9aada25b7..48c05bcaa40d 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -2339,12 +2339,16 @@ public:
         bool bOk = false;
         bool bTestEqual = false;
         double nCellVal;
+        double fRoundedValue = rItem.mfVal;
+        sal_uInt32 nNumFmt = pContext ? mrTab.GetNumberFormat(*pContext, ScAddress(nCol, nRow, mrTab.GetTab())) :
+            mrTab.GetNumberFormat(nCol, nRow);
+
         if (!rCell.isEmpty())
         {
             switch (rCell.meType)
             {
                 case CELLTYPE_VALUE :
-                    nCellVal = rCell.mfValue;
+                    nCellVal = mrDoc.RoundValueAsShown(rCell.mfValue, nNumFmt, pContext);
                 break;
                 case CELLTYPE_FORMULA :
                     nCellVal = rCell.mpFormula->GetValue();
@@ -2352,7 +2356,6 @@ public:
                 default:
                     nCellVal = 0.0;
             }
-
         }
         else
             nCellVal = mrTab.GetValue(nCol, nRow);
@@ -2363,10 +2366,9 @@ public:
          * the same, in other words only if rEntry.nVal is an integer value
          * rEntry.bQueryByDate should be true and the time fraction be
          * stripped here. */
+
         if (rItem.meType == ScQueryEntry::ByDate)
         {
-            sal_uInt32 nNumFmt = pContext ? mrTab.GetNumberFormat(*pContext, ScAddress(nCol, nRow, mrTab.GetTab())) :
-                mrTab.GetNumberFormat(nCol, nRow);
             SvNumberFormatter* pFormatter = pContext ? pContext->GetFormatTable() : mrDoc.GetFormatTable();
             const SvNumberformat* pEntry = pFormatter->GetEntry(nNumFmt);
             if (pEntry)
@@ -2386,30 +2388,32 @@ public:
                 }
             }
         }
+        else if (nNumFmt)
+            fRoundedValue = mrDoc.RoundValueAsShown(rItem.mfVal, nNumFmt, pContext);
 
         switch (rEntry.eOp)
         {
             case SC_EQUAL :
-                bOk = ::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = ::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_LESS :
-                bOk = (nCellVal < rItem.mfVal) && !::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = (nCellVal < fRoundedValue) && !::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_GREATER :
-                bOk = (nCellVal > rItem.mfVal) && !::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = (nCellVal > fRoundedValue) && !::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_LESS_EQUAL :
-                bOk = (nCellVal < rItem.mfVal) || ::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = (nCellVal < fRoundedValue) || ::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 if ( bOk && mpTestEqualCondition )
-                    bTestEqual = ::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                    bTestEqual = ::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_GREATER_EQUAL :
-                bOk = (nCellVal > rItem.mfVal) || ::rtl::math::approxEqual( nCellVal, rItem.mfVal);
+                bOk = (nCellVal > fRoundedValue) || ::rtl::math::approxEqual( nCellVal, fRoundedValue);
                 if ( bOk && mpTestEqualCondition )
-                    bTestEqual = ::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                    bTestEqual = ::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_NOT_EQUAL :
-                bOk = !::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = !::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             default:
             {
@@ -3055,10 +3059,6 @@ public:
     {
         if (rItem.meType != ScQueryEntry::ByString && rItem.meType != ScQueryEntry::ByDate)
             return;
-        // return only if the type is ByString and the values are formatted, in other cases
-        // we have to optimize the filter in CanOptimizeQueryStringToNumber().
-        if (rItem.mbFormattedValue && rItem.meType == ScQueryEntry::ByString)
-            return;
 
         sal_uInt32 nIndex = 0;
         bool bNumber = mrDoc.GetFormatTable()->
diff --git a/sc/source/core/tool/queryentry.cxx b/sc/source/core/tool/queryentry.cxx
index 5853a9760c1d..121c257e0cf6 100644
--- a/sc/source/core/tool/queryentry.cxx
+++ b/sc/source/core/tool/queryentry.cxx
@@ -34,7 +34,7 @@
 
 bool ScQueryEntry::Item::operator== (const Item& r) const
 {
-    return meType == r.meType && mfVal == r.mfVal && maString == r.maString && mbMatchEmpty == r.mbMatchEmpty && mbFormattedValue == r.mbFormattedValue;
+    return meType == r.meType && mfVal == r.mfVal && maString == r.maString && mbMatchEmpty == r.mbMatchEmpty;
 }
 
 ScQueryEntry::ScQueryEntry() :
diff --git a/sc/source/core/tool/typedstrdata.cxx b/sc/source/core/tool/typedstrdata.cxx
index 0420b359ab76..59831dac7271 100644
--- a/sc/source/core/tool/typedstrdata.cxx
+++ b/sc/source/core/tool/typedstrdata.cxx
@@ -47,22 +47,14 @@ bool ScTypedStrData::EqualCaseSensitive::operator() (const ScTypedStrData& left,
     if (left.meStrType != right.meStrType)
         return false;
 
-    if (left.meStrType == Value && left.mfValue != right.mfValue &&
-        !left.mbIsFormatted)
+    if (left.meStrType == Value && left.mfRoundedValue != right.mfRoundedValue)
         return false;
 
     if (left.mbIsDate != right.mbIsDate )
         return false;
 
-    if (ScGlobal::GetCaseCollator()->compareString(
-        left.maStrValue, right.maStrValue) == 0)
-    {
-        // hack: it's possible, because we only compare values of the same filter range
-        const_cast<bool&>(left.mbIsDuplicated) = true;
-        return true;
-    }
-    else
-        return false;
+    return ScGlobal::GetCaseCollator()->compareString(
+        left.maStrValue, right.maStrValue) == 0;
 }
 
 bool ScTypedStrData::EqualCaseInsensitive::operator() (const ScTypedStrData& left, const ScTypedStrData& right) const
@@ -70,22 +62,14 @@ bool ScTypedStrData::EqualCaseInsensitive::operator() (const ScTypedStrData& lef
     if (left.meStrType != right.meStrType)
         return false;
 
-    if (left.meStrType == Value && left.mfValue != right.mfValue &&
-        !left.mbIsFormatted)
+    if (left.meStrType == Value && left.mfRoundedValue != right.mfRoundedValue)
         return false;
 
     if (left.mbIsDate != right.mbIsDate )
         return false;
 
-    if (ScGlobal::GetCollator()->compareString(
-        left.maStrValue, right.maStrValue) == 0)
-    {
-        // hack: it's possible, because we only compare values of the same filter range
-        const_cast<bool&>(left.mbIsDuplicated) = true;
-        return true;
-    }
-    else
-        return false;
+    return ScGlobal::GetCollator()->compareString(
+        left.maStrValue, right.maStrValue) == 0;
 }
 
 bool ScTypedStrData::operator< (const ScTypedStrData& r) const
@@ -95,13 +79,12 @@ bool ScTypedStrData::operator< (const ScTypedStrData& r) const
 }
 
 ScTypedStrData::ScTypedStrData(
-    const OUString& rStr, double nVal, StringType nType, bool bDate, bool bFormatted, bool bDuplicated ) :
+    const OUString& rStr, double fVal, double fRVal, StringType nType, bool bDate ) :
     maStrValue(rStr),
-    mfValue(nVal),
+    mfValue(fVal),
+    mfRoundedValue(fRVal),
     meStrType(nType),
-    mbIsDate( bDate ),
-    mbIsFormatted( bFormatted ),
-    mbIsDuplicated( bDuplicated ) {}
+    mbIsDate( bDate ) {}
 
 FindTypedStrData::FindTypedStrData(const ScTypedStrData& rVal, bool bCaseSens) :
     maVal(rVal), mbCaseSens(bCaseSens) {}
diff --git a/sc/source/filter/xml/XMLExportDatabaseRanges.cxx b/sc/source/filter/xml/XMLExportDatabaseRanges.cxx
index 4e171d5b9da6..106f24e593c2 100644
--- a/sc/source/filter/xml/XMLExportDatabaseRanges.cxx
+++ b/sc/source/filter/xml/XMLExportDatabaseRanges.cxx
@@ -415,25 +415,11 @@ private:
     class WriteSetItem
     {
         ScXMLExport& mrExport;
-        const ScDocument* mpDoc;
     public:
-        explicit WriteSetItem(ScXMLExport& r, const ScDocument* pDoc) : mrExport(r), mpDoc(pDoc) {}
+        explicit WriteSetItem(ScXMLExport& r) : mrExport(r) {}
         void operator() (const ScQueryEntry::Item& rItem) const
         {
-            if (rItem.meType == ScQueryEntry::ByValue)
-            {
-                OUString aValStr;
-                SvNumberFormatter* pFormatter = mpDoc->GetFormatTable();
-                pFormatter->GetInputLineString(rItem.mfVal, 0, aValStr);
-                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, aValStr);
-            }
-            else
-            {
-                // Indicating the formatted filter values, by export the XML_DATA_TYPE with XML_TEXT
-                if (rItem.meType == ScQueryEntry::ByString && rItem.mbFormattedValue)
-                    mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT);
-                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
-            }
+            mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
             SvXMLElementExport aElem(mrExport, XML_NAMESPACE_TABLE, XML_FILTER_SET_ITEM, true, true);
         }
     };
@@ -454,9 +440,6 @@ private:
             const ScQueryEntry::Item& rItem = rItems.front();
             if (rItem.meType == ScQueryEntry::ByString)
             {
-                // Indicating the formatted filter values, by export the XML_DATA_TYPE with XML_TEXT
-                if (rItem.mbFormattedValue)
-                    mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT);
                 mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
             }
             else if (rItem.meType == ScQueryEntry::ByDate)
@@ -482,9 +465,7 @@ private:
             else
             {
                 mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_NUMBER);
-                OUStringBuffer aBuf;
-                ::sax::Converter::convertDouble(aBuf, rItem.mfVal);
-                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, aBuf.makeStringAndClear());
+                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
             }
 
             mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_OPERATOR, getOperatorXML(rEntry, eSearchType));
@@ -502,15 +483,10 @@ private:
             const ScQueryEntry::Item& rItem = rItems.front();
             if (rItem.meType == ScQueryEntry::ByValue)
             {
-                OUString aValStr;
-                SvNumberFormatter* pFormatter = mpDoc->GetFormatTable();
-                pFormatter->GetInputLineString(rItem.mfVal, 0, aValStr);
-                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, aValStr);
+                mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
             }
             else
             {
-                if (rItem.mbFormattedValue)
-                    mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT);
                 mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString());
             }
             mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_OPERATOR, OUString("="));
@@ -519,7 +495,7 @@ private:
                 mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_CASE_SENSITIVE, XML_TRUE);
             SvXMLElementExport aElemC(mrExport, XML_NAMESPACE_TABLE, XML_FILTER_CONDITION, true, true);
 
-            std::for_each(rItems.begin(), rItems.end(), WriteSetItem(mrExport, mpDoc));
+            std::for_each(rItems.begin(), rItems.end(), WriteSetItem(mrExport));
         }
     }
 
diff --git a/sc/source/filter/xml/xmlfilti.cxx b/sc/source/filter/xml/xmlfilti.cxx
index 3990849cf6a4..3bac65fafa15 100644
--- a/sc/source/filter/xml/xmlfilti.cxx
+++ b/sc/source/filter/xml/xmlfilti.cxx
@@ -282,7 +282,7 @@ ScXMLConditionContext::ScXMLConditionContext(
     ScXMLImportContext( rImport ),
     mrQueryParam(rParam),
     pFilterContext(pTempFilterContext),
-    sDataType(OUString()),
+    sDataType(GetXMLToken(XML_TEXT)),
     nField(0),
     bIsCaseSensitive(false)
 {
@@ -439,8 +439,6 @@ void SAL_CALL ScXMLConditionContext::endFastElement( sal_Int32 /*nElement*/ )
             svl::SharedStringPool& rPool = GetScImport().GetDocument()->GetSharedStringPool();
             rItem.maString = rPool.intern(sConditionValue);
             rItem.meType = ScQueryEntry::ByString;
-            if (IsXMLToken(sDataType, XML_TEXT))
-                rItem.mbFormattedValue = true;
         }
     }
     else
@@ -455,31 +453,22 @@ ScXMLSetItemContext::ScXMLSetItemContext(
     if ( !rAttrList.is() )
         return;
 
-    ScQueryEntry::Item aItem;
-    bool bAddSetItem = false;
-
     for (auto &aIter : *rAttrList)
     {
         switch (aIter.getToken())
         {
-            case XML_ELEMENT( TABLE, XML_DATA_TYPE ):
-            {
-                aItem.mbFormattedValue = IsXMLToken(aIter.toString(), XML_TEXT);
-            }
-            break;
             case XML_ELEMENT( TABLE, XML_VALUE ):
             {
                 svl::SharedStringPool& rPool = GetScImport().GetDocument()->GetSharedStringPool();
+                ScQueryEntry::Item aItem;
                 aItem.maString = rPool.intern(aIter.toString());
                 aItem.meType = ScQueryEntry::ByString;
                 aItem.mfVal = 0.0;
-                bAddSetItem = true;
+                rParent.AddSetItem(aItem);
             }
             break;
         }
     }
-    if (bAddSetItem)
-        rParent.AddSetItem(aItem);
 }
 
 ScXMLSetItemContext::~ScXMLSetItemContext()
@@ -669,7 +658,7 @@ ScXMLDPConditionContext::ScXMLDPConditionContext( ScXMLImport& rImport,
                                       ScXMLDPFilterContext* pTempFilterContext) :
     ScXMLImportContext( rImport ),
     pFilterContext(pTempFilterContext),
-    sDataType(OUString()),
+    sDataType(GetXMLToken(XML_TEXT)),
     nField(0),
     bIsCaseSensitive(false)
 {
@@ -782,8 +771,6 @@ void SAL_CALL ScXMLDPConditionContext::endFastElement( sal_Int32 /*nElement*/ )
             rItem.maString = rPool.intern(sConditionValue);
             rItem.meType = ScQueryEntry::ByString;
             rItem.mfVal = 0.0;
-            if (IsXMLToken(sDataType, XML_TEXT))
-                rItem.mbFormattedValue = true;
         }
     }
     pFilterContext->AddFilterField(aFilterField);
diff --git a/sc/source/ui/cctrl/checklistmenu.cxx b/sc/source/ui/cctrl/checklistmenu.cxx
index c8df91c57872..275873be1f32 100644
--- a/sc/source/ui/cctrl/checklistmenu.cxx
+++ b/sc/source/ui/cctrl/checklistmenu.cxx
@@ -997,7 +997,7 @@ void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal,
     mpChecks->thaw();
 }
 
-void ScCheckListMenuControl::addMember(const OUString& rName, const double nVal, bool bVisible, bool bValue, bool bDuplicated)
+void ScCheckListMenuControl::addMember(const OUString& rName, const double nVal, bool bVisible, bool bValue)
 {
     ScCheckListMember aMember;
     // tdf#46062 - indicate hidden whitespaces using quotes
@@ -1007,7 +1007,6 @@ void ScCheckListMenuControl::addMember(const OUString& rName, const double nVal,
     aMember.mbDate = false;
     aMember.mbLeaf = true;
     aMember.mbValue = bValue;
-    aMember.mbDuplicated = bDuplicated;
     aMember.mbVisible = bVisible;
     aMember.mxParent.reset();
     maMembers.emplace_back(std::move(aMember));
@@ -1366,7 +1365,6 @@ void ScCheckListMenuControl::getResult(ResultType& rResult)
             aResultEntry.nValue = maMembers[i].mnValue;
             aResultEntry.bDate = maMembers[i].mbDate;
             aResultEntry.bValue = maMembers[i].mbValue;
-            aResultEntry.bDuplicated = maMembers[i].mbDuplicated;
             aResult.insert(aResultEntry);
         }
     }
diff --git a/sc/source/ui/dbgui/filtdlg.cxx b/sc/source/ui/dbgui/filtdlg.cxx
index a87da245775b..f9be8368edf8 100644
--- a/sc/source/ui/dbgui/filtdlg.cxx
+++ b/sc/source/ui/dbgui/filtdlg.cxx
@@ -1106,7 +1106,6 @@ IMPL_LINK( ScFilterDlg, ValModifyHdl, weld::ComboBox&, rEd, void )
         rItem.maString = pDoc->GetSharedStringPool().intern(aStrVal);
         rItem.mfVal = 0.0;
         rItem.meType = ScQueryEntry::ByString;
-        rItem.mbFormattedValue = true;
     }
 
     const sal_Int32 nField = pLbField->get_active();
diff --git a/sc/source/ui/inc/checklistmenu.hxx b/sc/source/ui/inc/checklistmenu.hxx
index 6c3dbfd53d46..7ebb95fb0caf 100644
--- a/sc/source/ui/inc/checklistmenu.hxx
+++ b/sc/source/ui/inc/checklistmenu.hxx
@@ -73,7 +73,6 @@ public:
         bool bValid;
         bool bDate;
         bool bValue; // true if the filter condition is value
-        bool bDuplicated; // true if there were duplicated values in the filter list
 
         bool operator<(const ResultEntry& rhs) const
         {
@@ -86,8 +85,7 @@ public:
                    bValid == rhs.bValid &&
                    bDate == rhs.bDate &&
                    bValue == rhs.bValue &&
-                   nValue == rhs.nValue &&
-                   bDuplicated == rhs.bDuplicated;
+                   nValue == rhs.nValue;
         }
     };
     typedef std::set<ResultEntry> ResultType;
@@ -136,7 +134,7 @@ public:
     void setMemberSize(size_t n);
     void addDateMember(const OUString& rName, double nVal, bool bVisible);
     void addMember(const OUString& rName, const double nVal, bool bVisible,
-                   bool bValue = false, bool bDuplicated = false);
+                   bool bValue = false);
     size_t initMembers(int nMaxMemberWidth = -1);
     void setConfig(const Config& rConfig);
 
diff --git a/sc/source/ui/unoobj/datauno.cxx b/sc/source/ui/unoobj/datauno.cxx
index 0ed0529948ad..12a9bc44e949 100644
--- a/sc/source/ui/unoobj/datauno.cxx
+++ b/sc/source/ui/unoobj/datauno.cxx
@@ -1147,11 +1147,7 @@ void fillQueryParam(
                 aItem.mfVal    = rVal.NumericValue;
                 aItem.maString = rPool.intern(rVal.StringValue);
 
-                if (aItem.meType == ScQueryEntry::ByString)
-                {
-                    aItem.mbFormattedValue = true;
-                }
-                else if (aItem.meType == ScQueryEntry::ByValue)
+                if (aItem.meType == ScQueryEntry::ByValue)
                 {
                     OUString aStr;
                     pDoc->GetFormatTable()->GetInputLineString(aItem.mfVal, 0, aStr);
diff --git a/sc/source/ui/view/gridwin.cxx b/sc/source/ui/view/gridwin.cxx
index 19cac6db8737..42e982275ce3 100644
--- a/sc/source/ui/view/gridwin.cxx
+++ b/sc/source/ui/view/gridwin.cxx
@@ -541,9 +541,8 @@ public:
         {
             ScQueryEntry::Item aNew;
             aNew.maString = mrPool.intern(rEntry.aName);
-            // set the filter type to ByValue, if the filter condition is value and not a duplicated value
-            aNew.meType = rEntry.bDate ? ScQueryEntry::ByDate : rEntry.bValue && !rEntry.bDuplicated ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
-            aNew.mbFormattedValue = rEntry.bDuplicated;
+            // set the filter type to ByValue, if the filter condition is value
+            aNew.meType = rEntry.bDate ? ScQueryEntry::ByDate : rEntry.bValue ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
             aNew.mfVal = rEntry.nValue;
             mrItems.push_back(aNew);
         }
@@ -701,7 +700,7 @@ void ScGridWindow::LaunchAutoFilterMenu(SCCOL nCol, SCROW nRow)
                 bSelected = aSelectedString.count(aStringVal) > 0;
             else if (bQueryByNonEmpty)
                 bSelected = false;
-            rControl.addMember(aStringVal, aDoubleVal, bSelected, false, it->IsDuplicated());
+            rControl.addMember(aStringVal, aDoubleVal, bSelected);
             aFilterEntries.maStrData.erase(it);
             break;
         }
@@ -710,14 +709,21 @@ void ScGridWindow::LaunchAutoFilterMenu(SCCOL nCol, SCROW nRow)
     {
         const OUString& aStringVal = rEntry.GetString();
         const double aDoubleVal = rEntry.GetValue();
+        const double aRDoubleVal = rEntry.GetRoundedValue();
         bool bSelected = true;
+
         if (!aSelectedValue.empty() || !aSelectedString.empty())
-            bSelected
-                = aSelectedValue.count(aDoubleVal) > 0 || aSelectedString.count(aStringVal) > 0;
+        {
+            if (aDoubleVal == aRDoubleVal)
+                bSelected = aSelectedValue.count(aDoubleVal) > 0 || aSelectedString.count(aStringVal) > 0;
+            else
+                bSelected = aSelectedValue.count(aDoubleVal) > 0 || aSelectedValue.count(aRDoubleVal) > 0 || aSelectedString.count(aStringVal) > 0;
+        }
+
         if ( rEntry.IsDate() )
             rControl.addDateMember( aStringVal, rEntry.GetValue(), bSelected );
         else
-            rControl.addMember( aStringVal, aDoubleVal, bSelected, rEntry.GetStringType() == ScTypedStrData::Value, rEntry.IsDuplicated() );
+            rControl.addMember( aStringVal, aRDoubleVal, bSelected, rEntry.GetStringType() == ScTypedStrData::Value );
     }
 
     // Populate the menu.