resolved fdo#51442 N() inconsistent argument handling

5743e7b36220e9689091812cef7a4396dd48c4e6 introduced a different handling
of arguments in the N() spreadsheet function. That lost the ability to
use position dependent range intersections and suppressed every error
instead of propagating it. Previous versions suppressed only the #N/A
error. Excel propagates all errors instead.

Note that Excel for N() does not use position dependent range
intersection (SingleRef of DoubleRef) when a range reference is passed
and does not handle evaluation in array context (instead top left value
is always used), which both is inconsistent with other functions taking
a scalar value parameter. ODFF does not specify this function different.
This change re-enables range intersection and adds array evaluation.
Gnumeric does the same.

Change-Id: Ice35271e40f1a27cea384226e25945108003841a
(cherry picked from commit d822c6afe6c1a676e9b86d67e599dbcf75512a7c)

3 files changed, 41 insertions, 47 deletions

diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index d37e3f15ba55..2cd2958648ac 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -433,25 +433,37 @@ void testFuncN(ScDocument* pDoc)
     pDoc->SetString(1, 13, 0, OUString("=N(\"foo\")"));
 
     // Range references
-    pDoc->SetString(1, 14, 0, OUString("=N(A1:A8)"));
-    pDoc->SetString(1, 15, 0, OUString("=N(A4:B8)"));
-    pDoc->SetString(1, 16, 0, OUString("=N(A6:B8)"));
-    pDoc->SetString(1, 17, 0, OUString("=N(A2:B8)"));
+    pDoc->SetString(2, 2, 0, OUString("=N(A1:A8)"));
+    pDoc->SetString(2, 3, 0, OUString("=N(A1:A8)"));
+    pDoc->SetString(2, 4, 0, OUString("=N(A1:A8)"));
+    pDoc->SetString(2, 5, 0, OUString("=N(A1:A8)"));
 
     // Calculate and check the results.
     pDoc->CalcAll();
-    double checks[] = {
+    double checks1[] = {
         0, 0,  0,    1, -1, 12.3, 0, // cell reference
-        0, 1, -1, 123,  0,    0, 0, // in-line values
-        0, 1, 12.3, 0                // range references
+        0, 1, -1, 123,  0,    0, 0   // in-line values
     };
-    for (size_t i = 0; i < SAL_N_ELEMENTS(checks); ++i)
+    for (size_t i = 0; i < SAL_N_ELEMENTS(checks1); ++i)
     {
         pDoc->GetValue(1, i, 0, result);
-        bool bGood = result == checks[i];
+        bool bGood = result == checks1[i];
         if (!bGood)
         {
-            cerr << "row " << (i+1) << ": expected=" << checks[i] << " actual=" << result << endl;
+            cerr << "row " << (i+1) << ": expected=" << checks1[i] << " actual=" << result << endl;
+            CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
+        }
+    }
+    double checks2[] = {
+        0, 1, -1, 12.3               // range references
+    };
+    for (size_t i = 0; i < SAL_N_ELEMENTS(checks2); ++i)
+    {
+        pDoc->GetValue(1, i+2, 0, result);
+        bool bGood = result == checks2[i];
+        if (!bGood)
+        {
+            cerr << "row " << (i+2+1) << ": expected=" << checks2[i] << " actual=" << result << endl;
             CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
         }
     }
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 8177136f7218..fbb8b548dff0 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -2853,40 +2853,19 @@ void ScInterpreter::ScIsOdd()
 
 void ScInterpreter::ScN()
 {
-    switch (GetRawStackType())
-    {
-        case svSingleRef:
-        case svDoubleRef:
-        case svMatrix:
-        case svExternalSingleRef:
-        case svExternalDoubleRef:
-        {
-            ScMatrixRef pMat = GetMatrix();
-            SCSIZE nC, nR;
-            pMat->GetDimensions(nC, nR);
-            if (!nC || !nR)
-                PushDouble(0);
-            else
-                PushDouble(pMat->GetDouble(0, 0));
-            return;
-        }
-        case svString:
-            PopError();
-            PushDouble(0);
-            return;
-        default:
-            ;
-    }
-
-    // Default action
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScN" );
+    sal_uInt16 nErr = nGlobalError;
+    nGlobalError = 0;
+    // Temporarily override the ConvertStringToValue() error for
+    // GetCellValue() / GetCellValueOrZero()
+    sal_uInt16 nSErr = mnStringNoValueError;
+    mnStringNoValueError = errCellNoValue;
     double fVal = GetDouble();
-    if (nGlobalError)
-    {
-        // Don't propagate the error. Push 0 instead.
-        nGlobalError = 0;
-        PushDouble(0);
-        return;
-    }
+    mnStringNoValueError = nSErr;
+    if (nErr)
+        nGlobalError = nErr;    // preserve previous error if any
+    else if (nGlobalError == errCellNoValue)
+        nGlobalError = 0;       // reset temporary detection error
     PushDouble(fVal);
 }
 
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index e8509a3e4e39..a1dc389ab0fa 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -144,7 +144,6 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocModalValue,      {{ ForceArray                                           }, true }},
     { ocMul,             {{ Array, Array                                         }, false }},
     { ocMultiArea,       {{ Reference                                            }, true }},
-    { ocN,               {{ Reference                                            }, false }},
     { ocNPV,             {{ Value, Reference                                     }, true }},
     { ocNeg,             {{ Array                                                }, false }},
     { ocNegSub,          {{ Array                                                }, false }},
@@ -195,9 +194,13 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocVLookup,         {{ Value, Reference, Value, Value                       }, false }},
     { ocZTest,           {{ Reference, Value, Value                              }, false }},
     // Excel doubts:
-    // ocT: Excel says (and handles) Reference, error? This means no position
-    // dependent SingleRef if DoubleRef, and no array calculation, just the
-    // upper left corner. We never did that.
+    // ocN, ocT: Excel says (and handles) Reference, error? This means no
+    // position dependent SingleRef if DoubleRef, and no array calculation,
+    // just the upper left corner. We never did that for ocT and now also not
+    // for ocN (position dependent intersection worked before but array
+    // didn't). No specifics in ODFF, so the general rule applies. Gnumeric
+    // does the same.
+    { ocN, {{ Value }, false }},
     { ocT, {{ Value }, false }},
     // The stopper.
     { ocNone, {{ Bounds }, false } }