tdf#89330 preserve file name case in an unresolvable external named range

Reviewed-on: https://gerrit.libreoffice.org/14474
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>
(cherry picked from commit 0a9e7293a570da8a36b2ac4fd1c4a22d6bc084f2)

add unittest for tdf#89330

Reviewed-on: https://gerrit.libreoffice.org/14473
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>
(cherry picked from commit e4882717f3dc0375e113ed692374870ab8302a16)

4c5c65733700e7e7245e96f85714221acf23bcfb

Change-Id: Ib8728a4a9c793b162de07a0cef66e242879f2aa1
Reviewed-on: https://gerrit.libreoffice.org/14483
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>

4 files changed, 40 insertions, 4 deletions

diff --git a/sc/inc/compiler.hxx b/sc/inc/compiler.hxx
index 18d788aab3c4..6a90c7a77ba4 100644
--- a/sc/inc/compiler.hxx
+++ b/sc/inc/compiler.hxx
@@ -347,7 +347,7 @@ private:
     bool IsDoubleReference( const OUString& );
     bool IsMacro( const OUString& );
     bool IsNamedRange( const OUString& );
-    bool IsExternalNamedRange( const OUString& rSymbol );
+    bool IsExternalNamedRange( const OUString& rSymbol, bool& rbInvalidExternalNameRange );
     bool IsDBRange( const OUString& );
     bool IsColRowName( const OUString& );
     bool IsBoolean( const OUString& );
diff --git a/sc/qa/unit/ucalc.hxx b/sc/qa/unit/ucalc.hxx
index 328dc596b57e..4d2cf93e5e6f 100644
--- a/sc/qa/unit/ucalc.hxx
+++ b/sc/qa/unit/ucalc.hxx
@@ -135,6 +135,7 @@ public:
     void testFormulaRefUpdateNamedExpressionMove();
     void testFormulaRefUpdateNamedExpressionExpandRef();
     void testFormulaRefUpdateValidity();
+    void testErrorOnExternalReferences();
     void testMultipleOperations();
     void testFuncCOLUMN();
     void testFuncCOUNT();
@@ -412,6 +413,7 @@ public:
     CPPUNIT_TEST(testFormulaRefUpdateNamedExpressionMove);
     CPPUNIT_TEST(testFormulaRefUpdateNamedExpressionExpandRef);
     CPPUNIT_TEST(testFormulaRefUpdateValidity);
+    CPPUNIT_TEST(testErrorOnExternalReferences);
     CPPUNIT_TEST(testMultipleOperations);
     CPPUNIT_TEST(testFuncCOLUMN);
     CPPUNIT_TEST(testFuncCOUNT);
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index b1ef5614d9c1..cbf40d2ec828 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -1384,6 +1384,23 @@ void Test::testFormulaRefUpdateRange()
     m_pDoc->DeleteTab(0);
 }
 
+void Test::testErrorOnExternalReferences()
+{
+    // Test tdf#89330
+    m_pDoc->InsertTab(0, "Sheet1");
+    m_pDoc->SetString(ScAddress(0,0,0), "='file:///Path/To/FileA.ods'#$Sheet1.A1A");
+
+    ScFormulaCell* pFC = m_pDoc->GetFormulaCell(ScAddress(0,0,0));
+    CPPUNIT_ASSERT(pFC);
+    CPPUNIT_ASSERT_EQUAL(ScErrorCodes::errNoName, pFC->GetErrCode());
+
+    if (!checkFormula(*m_pDoc, ScAddress(0,0,0), "'file:///Path/To/FileA.ods'#$Sheet1.A1A"))
+        CPPUNIT_FAIL("Formula changed");
+
+    m_pDoc->DeleteTab(0);
+}
+
+
 void Test::testFormulaRefUpdateSheets()
 {
     m_pDoc->InsertTab(0, "Sheet1");
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 98bad502e266..0e81896fd15f 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -2927,7 +2927,7 @@ bool ScCompiler::IsNamedRange( const OUString& rUpperName )
         return false;
 }
 
-bool ScCompiler::IsExternalNamedRange( const OUString& rSymbol )
+bool ScCompiler::IsExternalNamedRange( const OUString& rSymbol, bool& rbInvalidExternalNameRange )
 {
     /* FIXME: This code currently (2008-12-02T15:41+0100 in CWS mooxlsc)
      * correctly parses external named references in OOo, as required per RFE
@@ -2935,6 +2935,8 @@ bool ScCompiler::IsExternalNamedRange( const OUString& rSymbol )
      * spec first. Until then don't pretend to support external names that
      * wouldn't survive a save and reload cycle, return false instead. */
 
+    rbInvalidExternalNameRange = false;
+
     if (!pConv)
         return false;
 
@@ -2951,8 +2953,11 @@ bool ScCompiler::IsExternalNamedRange( const OUString& rSymbol )
     aFile = aTmp;
     sal_uInt16 nFileId = pRefMgr->getExternalFileId(aFile);
     if (!pRefMgr->isValidRangeName(nFileId, aName))
+    {
+        rbInvalidExternalNameRange = true;
         // range name doesn't exist in the source document.
         return false;
+    }
 
     const OUString* pRealName = pRefMgr->getRealRangeName(nFileId, aName);
     maRawToken.SetExternalName(nFileId, pRealName ? *pRealName : OUString(aTmp));
@@ -3489,7 +3494,8 @@ bool ScCompiler::NextNewToken( bool bInArray )
     if (mnPredetectedReference)
     {
         OUString aStr( cSymbol);
-        if (!IsPredetectedReference( aStr) && !IsExternalNamedRange( aStr))
+        bool bInvalidExternalNameRange;
+        if (!IsPredetectedReference( aStr) && !IsExternalNamedRange( aStr, bInvalidExternalNameRange ))
         {
             /* TODO: it would be nice to generate a #REF! error here, which
              * would need an ocBad token with additional error value.
@@ -3618,8 +3624,19 @@ bool ScCompiler::NextNewToken( bool bInArray )
         if (IsNamedRange( aUpper ))
             return true;
         // Preserve case of file names in external references.
-        if (IsExternalNamedRange( aOrg ))
+        bool bInvalidExternalNameRange;
+        if (IsExternalNamedRange( aOrg, bInvalidExternalNameRange ))
+            return true;
+        // Preserve case of file names in external references even when range
+        // is not valid and previous check failed tdf#89330
+        if (bInvalidExternalNameRange)
+        {
+            // add ocBad but do not lowercase
+            svl::SharedString aSS = pDoc->GetSharedStringPool().intern(aOrg);
+            maRawToken.SetString(aSS.getData(), aSS.getDataIgnoreCase());
+            maRawToken.NewOpCode( ocBad );
             return true;
+        }
         if (IsDBRange( aUpper ))
             return true;
         // If followed by '(' (with or without space inbetween) it can not be a