keep conflicting named ranges working with 16k columns

Named ranges named e.g. 'num1' are actually valid cell addresses
when using 16k columns. We prevent naming ranges in a way that would
make them conflict, but it's possible to read them from a saved
file that was created with fewer columns, and in such cases formulas
using them would silently refer to those cells instead of to
the named range. I don't see anything in the ODF spec, but OOXML
in 18.2.5 recommends this in case there are conflicts (only outside
of the normal Excel range of A1-XFD1048576, inside they are always
meant to be references, but our normal range currently is only 1k
columns, and it's simpler and probably harmless to always resolve
a conflict this way). I can optimize performance of this in another
commit if needed.

Change-Id: I46aef54b069700e7bf268b50fdc1a88989f3ee29
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/130891
Tested-by: Jenkins
Reviewed-by: Luboš Luňák <l.lunak@collabora.com>

4 files changed, 44 insertions, 4 deletions

diff --git a/sc/inc/compiler.hxx b/sc/inc/compiler.hxx
index c2bdb29b478d..15b3823f649d 100644
--- a/sc/inc/compiler.hxx
+++ b/sc/inc/compiler.hxx
@@ -357,7 +357,7 @@ private:
     bool ParsePredetectedReference( const OUString& rSymbol );
     bool ParsePredetectedErrRefReference( const OUString& rName, const OUString* pErrRef );
     bool ParseMacro( const OUString& );
-    bool ParseNamedRange( const OUString& );
+    bool ParseNamedRange( const OUString&, bool onlyCheck = false );
     bool ParseExternalNamedRange( const OUString& rSymbol, bool& rbInvalidExternalNameRange );
     bool ParseDBRange( const OUString& );
     bool ParseColRowName( const OUString& );
diff --git a/sc/qa/unit/data/ods/named-range-conflict.ods b/sc/qa/unit/data/ods/named-range-conflict.ods
new file mode 100644
index 000000000000..b014cad532ec
--- /dev/null
+++ b/sc/qa/unit/data/ods/named-range-conflict.ods
diff --git a/sc/qa/unit/jumbosheets-test.cxx b/sc/qa/unit/jumbosheets-test.cxx
index 4ca4eeaf1ed6..0442e54b9076 100644
--- a/sc/qa/unit/jumbosheets-test.cxx
+++ b/sc/qa/unit/jumbosheets-test.cxx
@@ -47,6 +47,7 @@ public:
     void testRoundtripColumn2000Xlsx();
     void testRoundtripColumnRange();
     void testRoundtripNamedRanges();
+    void testNamedRangeNameConflict();
     void testTdf134392();
     void testTdf133033();
     void testTdf109061();
@@ -57,6 +58,7 @@ public:
     CPPUNIT_TEST(testRoundtripColumn2000Xlsx);
     CPPUNIT_TEST(testRoundtripColumnRange);
     CPPUNIT_TEST(testRoundtripNamedRanges);
+    CPPUNIT_TEST(testNamedRangeNameConflict);
     CPPUNIT_TEST(testTdf134392);
     CPPUNIT_TEST(testTdf133033);
     CPPUNIT_TEST(testTdf109061);
@@ -233,6 +235,30 @@ void ScJumboSheetsTest::testRoundtripNamedRanges()
     xDocSh3->DoClose();
 }
 
+void ScJumboSheetsTest::testNamedRangeNameConflict()
+{
+    // The document contains named ranges named 'num1' and 'num2', that should be still treated
+    // as named references even though with 16k columns those are normally NUM1 and NUM2 cells.
+    ScDocShellRef xDocSh = loadDoc(u"named-range-conflict.", FORMAT_ODS);
+    CPPUNIT_ASSERT(xDocSh.is());
+    ScDocument& rDoc = xDocSh->GetDocument();
+    rDoc.CalcAll();
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(10022, 0, 0)); // NUM1
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(10022, 1, 0)); // NUM2
+    CPPUNIT_ASSERT_EQUAL(2.0, rDoc.GetValue(0, 0, 0)); // = num1
+    CPPUNIT_ASSERT_EQUAL(3.0, rDoc.GetValue(0, 1, 0)); // = sheet2.num2
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(0, 2, 0)); // = SUM(NUM1:NUM2) (not named ranges)
+    rDoc.SetValue(10022, 0, 0, 100); // NUM1
+    rDoc.SetValue(10022, 1, 0, 200); // NUM2
+    rDoc.CalcAll();
+    // First two are the same, the sum changes.
+    CPPUNIT_ASSERT_EQUAL(2.0, rDoc.GetValue(0, 0, 0));
+    CPPUNIT_ASSERT_EQUAL(3.0, rDoc.GetValue(0, 1, 0));
+    CPPUNIT_ASSERT_EQUAL(300.0, rDoc.GetValue(0, 2, 0));
+
+    xDocSh->DoClose();
+}
+
 void ScJumboSheetsTest::testTdf134392()
 {
     // Without the fix in place, the file would have crashed
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index ba47ab92f222..70e3899ca03e 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -3345,6 +3345,18 @@ bool ScCompiler::ParseSingleReference( const OUString& rName, const OUString* pE
             return false;
         }
 
+        // A named range named e.g. 'num1' is valid with 1k columns, but would become a reference
+        // when the document is opened later with 16k columns. Resolve the conflict by not
+        // considering it a reference.
+        OUString aUpper;
+        bool bAsciiUpper = ToUpperAsciiOrI18nIsAscii( aUpper, rName );
+        if (bAsciiUpper || mbCharClassesDiffer)
+            aUpper = ScGlobal::getCharClass().uppercase( rName );
+        mnCurrentSheetTab = aAddr.Tab(); // temporarily set for ParseNamedRange()
+        if(ParseNamedRange( aUpper, true )) // only check
+            return false;
+        mnCurrentSheetTab = -1;
+
         ScSingleRefData aRef;
         aRef.InitAddress( aAddr );
         aRef.SetColRel( (nFlags & ScRefFlags::COL_ABS) == ScRefFlags::ZERO );
@@ -3575,7 +3587,7 @@ const ScRangeData* ScCompiler::GetRangeData( SCTAB& rSheet, const OUString& rUpp
     return pData;
 }
 
-bool ScCompiler::ParseNamedRange( const OUString& rUpperName )
+bool ScCompiler::ParseNamedRange( const OUString& rUpperName, bool onlyCheck )
 {
     // ParseNamedRange is called only from NextNewToken, with an upper-case string
 
@@ -3583,7 +3595,8 @@ bool ScCompiler::ParseNamedRange( const OUString& rUpperName )
     const ScRangeData* pData = GetRangeData( nSheet, rUpperName);
     if (pData)
     {
-        maRawToken.SetName( nSheet, pData->GetIndex());
+        if (!onlyCheck)
+            maRawToken.SetName( nSheet, pData->GetIndex());
         return true;
     }
 
@@ -3597,7 +3610,8 @@ bool ScCompiler::ParseNamedRange( const OUString& rUpperName )
             pData = pRangeName->findByUpperName(aName);
             if (pData)
             {
-                maRawToken.SetName( mnCurrentSheetTab, pData->GetIndex());
+                if (!onlyCheck)
+                    maRawToken.SetName( mnCurrentSheetTab, pData->GetIndex());
                 return true;
             }
         }