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
(cherry picked from commit 3069df790cca2917e5aedd87bac1af65f9605d51)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/121279
Tested-by: Gabor Kelemen <kelemen.gabor2@nisz.hu>
Reviewed-by: Gabor Kelemen <kelemen.gabor2@nisz.hu>

14 files changed, 117 insertions, 133 deletions

diff --git a/sc/inc/queryentry.hxx b/sc/inc/queryentry.hxx
index f21b7e94a839..9e219dfe2216 100644
--- a/sc/inc/queryentry.hxx
+++ b/sc/inc/queryentry.hxx
@@ -42,7 +42,7 @@ struct SC_DLLPUBLIC ScQueryEntry
         bool              mbMatchEmpty;
         bool              mbFormattedValue;
 
-        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 fdbb6b786e04..290a051e3643 100644
--- a/sc/inc/typedstrdata.hxx
+++ b/sc/inc/typedstrdata.hxx
@@ -25,14 +25,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
     {
@@ -59,10 +59,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 76c1244342ff..4aeb6d4c0a87 100644
--- a/sc/qa/uitest/autofilter/autofilter.py
+++ b/sc/qa/uitest/autofilter/autofilter.py
@@ -441,20 +441,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")
 
@@ -472,4 +460,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 6c4e18ec0fcf..092c2ffee7b3 100644
--- a/sc/source/core/data/column3.cxx
+++ b/sc/source/core/data/column3.cxx
@@ -2481,10 +2481,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, mrColumn.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 a813b91885c2..04f502277505 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -2336,12 +2336,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();
@@ -2349,7 +2353,6 @@ public:
                 default:
                     nCellVal = 0.0;
             }
-
         }
         else
             nCellVal = mrTab.GetValue(nCol, nRow);
@@ -2360,10 +2363,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)
@@ -2383,30 +2385,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:
             {
@@ -3018,10 +3022,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 a295759e0c3c..1c4326c47b29 100644
--- a/sc/source/core/tool/queryentry.cxx
+++ b/sc/source/core/tool/queryentry.cxx
@@ -32,7 +32,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 8455275e11bb..bb5cfdfd8de0 100644
--- a/sc/source/filter/xml/XMLExportDatabaseRanges.cxx
+++ b/sc/source/filter/xml/XMLExportDatabaseRanges.cxx
@@ -417,25 +417,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);
         }
     };
@@ -460,9 +446,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)
@@ -472,9 +455,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));
@@ -489,21 +470,16 @@ 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("="));
             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 11e16bdfe6c7..9b811176faaf 100644
--- a/sc/source/filter/xml/xmlfilti.cxx
+++ b/sc/source/filter/xml/xmlfilti.cxx
@@ -281,7 +281,7 @@ ScXMLConditionContext::ScXMLConditionContext(
     ScXMLImportContext( rImport ),
     mrQueryParam(rParam),
     pFilterContext(pTempFilterContext),
-    sDataType(OUString()),
+    sDataType(GetXMLToken(XML_TEXT)),
     nField(0),
     bIsCaseSensitive(false)
 {
@@ -430,8 +430,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
@@ -446,31 +444,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()
@@ -660,7 +649,7 @@ ScXMLDPConditionContext::ScXMLDPConditionContext( ScXMLImport& rImport,
                                       ScXMLDPFilterContext* pTempFilterContext) :
     ScXMLImportContext( rImport ),
     pFilterContext(pTempFilterContext),
-    sDataType(OUString()),
+    sDataType(GetXMLToken(XML_TEXT)),
     nField(0),
     bIsCaseSensitive(false)
 {
@@ -773,8 +762,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 ef1942f753e1..405df813ce84 100644
--- a/sc/source/ui/cctrl/checklistmenu.cxx
+++ b/sc/source/ui/cctrl/checklistmenu.cxx
@@ -980,7 +980,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;
     aMember.maName = rName;
@@ -989,7 +989,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));
@@ -1351,7 +1350,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 546a4eab3f34..894fb75e3dab 100644
--- a/sc/source/ui/dbgui/filtdlg.cxx
+++ b/sc/source/ui/dbgui/filtdlg.cxx
@@ -1101,7 +1101,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 2facc0ce03ee..d591232992ea 100644
--- a/sc/source/ui/inc/checklistmenu.hxx
+++ b/sc/source/ui/inc/checklistmenu.hxx
@@ -76,7 +76,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
         {
@@ -89,8 +88,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;
@@ -139,7 +137,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 ec5ca8989634..531cc27eaa0f 100644
--- a/sc/source/ui/unoobj/datauno.cxx
+++ b/sc/source/ui/unoobj/datauno.cxx
@@ -1128,11 +1128,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 53ed9727ab7a..b665f10742df 100644
--- a/sc/source/ui/view/gridwin.cxx
+++ b/sc/source/ui/view/gridwin.cxx
@@ -560,9 +560,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);
         }
@@ -709,14 +708,17 @@ 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())
         {
             if (aStringVal.isEmpty())
                 bSelected = aSelectedString.count(aStringVal) > 0;
+            else if (aDoubleVal == aRDoubleVal)
+                bSelected = aSelectedValue.count(aDoubleVal) > 0 || aSelectedString.count(aStringVal) > 0;
             else
-                bSelected
-                    = aSelectedValue.count(aDoubleVal) > 0 || aSelectedString.count(aStringVal) > 0;
+                bSelected = aSelectedValue.count(aDoubleVal) > 0 || aSelectedValue.count(aRDoubleVal) > 0 || aSelectedString.count(aStringVal) > 0;
         }
         else if (bQueryByNonEmpty)
             bSelected = !aStringVal.isEmpty();
@@ -724,7 +726,7 @@ void ScGridWindow::LaunchAutoFilterMenu(SCCOL nCol, SCROW nRow)
         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.