You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2026/09/18 11:43:52 UTC

(poi) branch trunk updated: QUOTIENT, FACT and ROUND/ROUNDUP/ROUNDDOWN/TRUNC: no IllegalArgumentException for arguments outside the int range (#1278)

This is an automated email from the ASF dual-hosted git repository.

pjfanning pushed a commit to branch trunk
in repository https://gitbox.apache.org/repos/asf/poi.git


The following commit(s) were added to refs/heads/trunk by this push:
     new 92a57d296f QUOTIENT, FACT and ROUND/ROUNDUP/ROUNDDOWN/TRUNC: no IllegalArgumentException for arguments outside the int range (#1278)
92a57d296f is described below

commit 92a57d296f67f196e54aeb8f0945b5e9a0c6d4dd
Author: PJ Fanning <pj...@users.noreply.github.com>
AuthorDate: Fri Sep 18 12:43:47 2026 +0100

    QUOTIENT, FACT and ROUND/ROUNDUP/ROUNDDOWN/TRUNC: no IllegalArgumentException for arguments outside the int range (#1278)
    
    These functions converted a double argument with MathUtil.safeDoubleToInt,
    which throws IllegalArgumentException for values outside the int range. The
    exception escaped the evaluator instead of becoming an error value:
    
    * QUOTIENT(5E9,2) threw; Excel returns 2500000000. The quotient is now
      truncated towards zero as a double, and an infinite quotient is #NUM!.
    * FACT(1E10) threw; Excel returns #NUM!. factorial(double) now handles the
      range check itself (>= 171 is infinity, hence #NUM!; <= -1 is NaN).
    * ROUND(1.5,1E10), TRUNC(1.5,-1E10) etc. threw. Digit counts within the int
      range but in the hundreds of millions were worse: BigDecimal.setScale built
      a number with that many digits and ROUND(1.5,100000000) ran for minutes.
      Digit counts are now clamped to +/-400, beyond which rounding a double
      (15 significant digits, decimal exponent within +/-324) cannot change it.
    
    Co-authored-by: Claude Opus 5 (1M context) <no...@anthropic.com>
---
 .../org/apache/poi/ss/formula/functions/MathX.java | 33 ++++++++++++++----
 .../apache/poi/ss/formula/functions/Quotient.java  |  9 +++--
 .../apache/poi/ss/formula/functions/TestMathX.java | 19 ++++++++++
 .../ss/formula/functions/TestNumericFunction.java  | 40 ++++++++++++++++++++++
 .../poi/ss/formula/functions/TestQuotient.java     | 10 ++++++
 5 files changed, 103 insertions(+), 8 deletions(-)

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/MathX.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/MathX.java
index 9d90dd8be3..4573e60fe9 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/MathX.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/MathX.java
@@ -20,7 +20,6 @@ package org.apache.poi.ss.formula.functions;
 import org.apache.poi.ss.util.ExcelArithmetic;
 import org.apache.poi.ss.util.NumberToTextConverter;
 import org.apache.poi.util.Internal;
-import org.apache.poi.util.MathUtil;
 
 import java.math.BigDecimal;
 import java.math.MathContext;
@@ -58,7 +57,7 @@ final class MathX {
     }
 
     public static double round(double n, double p) {
-        return round(n, MathUtil.safeDoubleToInt(p));
+        return round(n, clampDigits(p));
     }
 
 
@@ -83,7 +82,7 @@ final class MathX {
     }
 
     public static double roundUp(double n, double p) {
-        return roundUp(n, MathUtil.safeDoubleToInt(p));
+        return roundUp(n, clampDigits(p));
     }
 
 
@@ -107,7 +106,19 @@ final class MathX {
     }
 
     public static double roundDown(double n, double p) {
-        return roundDown(n, MathUtil.safeDoubleToInt(p));
+        return roundDown(n, clampDigits(p));
+    }
+
+    /**
+     * A double has at most 15 significant digits and a decimal exponent within +/-324, so rounding
+     * to more than this many digits either side of the decimal point can never change the value.
+     * Larger digit counts are clamped to it: they would make {@link java.math.BigDecimal#setScale}
+     * build a number with billions of digits.
+     */
+    private static final int MAX_ROUNDING_DIGITS = 400;
+
+    private static int clampDigits(double p) {
+        return (int) Math.max(-MAX_ROUNDING_DIGITS, Math.min(MAX_ROUNDING_DIGITS, p));
     }
 
     private static double round(double n, int p, java.math.RoundingMode rounding) {
@@ -116,7 +127,7 @@ final class MathX {
         }
         else {
             final String excelNumber = NumberToTextConverter.toText(n);
-            return new java.math.BigDecimal(excelNumber).setScale(p, rounding).doubleValue();
+            return new java.math.BigDecimal(excelNumber).setScale(clampDigits(p), rounding).doubleValue();
         }
     }
 
@@ -320,7 +331,17 @@ final class MathX {
     }
 
     public static double factorial(double d) {
-        return factorial(MathUtil.safeDoubleToInt(d));
+        if (Double.isNaN(d)) {
+            return Double.NaN;
+        }
+        if (d >= 171) {
+            // beyond Double.MAX_VALUE (and possibly beyond the int range)
+            return Double.POSITIVE_INFINITY;
+        }
+        if (d <= -1) {
+            return Double.NaN;
+        }
+        return factorial((int) d);
     }
 
 
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Quotient.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Quotient.java
index 92ec36d97d..8c2863ed3f 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Quotient.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Quotient.java
@@ -20,7 +20,6 @@ package org.apache.poi.ss.formula.functions;
 import org.apache.poi.ss.formula.OperationEvaluationContext;
 
 import org.apache.poi.ss.formula.eval.*;
-import org.apache.poi.util.MathUtil;
 
 /**
  * <p>Implementation for Excel QUOTIENT () function.
@@ -35,6 +34,7 @@ import org.apache.poi.util.MathUtil;
  *
  * If either enumerator/denominator is non numeric, QUOTIENT returns the #VALUE! error value.
  * If denominator is equals to zero, QUOTIENT returns the #DIV/0! error value.
+ * If the quotient is too large to represent, QUOTIENT returns the #NUM! error value.
  */
 public class Quotient extends Fixed2ArgFunction implements FreeRefFunction {
 
@@ -63,7 +63,12 @@ public class Quotient extends Fixed2ArgFunction implements FreeRefFunction {
             return ErrorEval.DIV_ZERO;
         }
 
-        return new NumberEval(MathUtil.safeDoubleToInt(enumerator / denominator));
+        double quotient = enumerator / denominator;
+        if (Double.isNaN(quotient) || Double.isInfinite(quotient)) {
+            return ErrorEval.NUM_ERROR;
+        }
+        // truncate towards zero; the result is kept as a double so that quotients beyond the int range work
+        return new NumberEval(quotient < 0 ? Math.ceil(quotient) : Math.floor(quotient));
     }
 
     @Override
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMathX.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMathX.java
index b00b92b127..7914f19a2a 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMathX.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMathX.java
@@ -20,6 +20,7 @@
  */
 package org.apache.poi.ss.formula.functions;
 
+import static org.junit.jupiter.api.Assertions.assertEquals;
 import static org.junit.jupiter.api.Assertions.assertTrue;
 
 import org.apache.poi.ss.formula.functions.XYNumericFunction.Accumulator;
@@ -553,6 +554,24 @@ public class TestMathX extends BaseTestNumeric {
         assertDouble("Factorial ", Double.POSITIVE_INFINITY, s);
     }
 
+    @Test
+    void testFactorialDouble() {
+        // assertEquals rather than assertDouble: the latter does not really check NaN
+        assertEquals(1, MathX.factorial(0.0));
+        assertEquals(1, MathX.factorial(0.9));
+        assertEquals(1, MathX.factorial(-0.9));
+        assertEquals(120, MathX.factorial(5.5));
+        assertEquals(7.257415615307994E306, MathX.factorial(170.9));
+        assertEquals(Double.NaN, MathX.factorial(-1.0));
+        assertEquals(Double.NaN, MathX.factorial(Double.NaN));
+        assertEquals(Double.POSITIVE_INFINITY, MathX.factorial(171.0));
+        // beyond the int range: no IllegalArgumentException
+        assertEquals(Double.POSITIVE_INFINITY, MathX.factorial(1E10));
+        assertEquals(Double.POSITIVE_INFINITY, MathX.factorial(Double.POSITIVE_INFINITY));
+        assertEquals(Double.NaN, MathX.factorial(-1E10));
+        assertEquals(Double.NaN, MathX.factorial(Double.NEGATIVE_INFINITY));
+    }
+
     @Test
     void testSumx2my2() {
         double[] xarr;
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
index 25f27f3c98..f63b414f2c 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
@@ -222,4 +222,44 @@ final class TestNumericFunction {
         assertError(fe, cell, "INT(\"abc\")", FormulaError.VALUE);
         assertError(fe, cell, "INT(1/0)", FormulaError.DIV0);
     }
+
+    @Test
+    void testFACTOutOfIntRange() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+        assertDouble(fe, cell, "FACT(170)", 7.257415615307994E306, 0);
+        assertDouble(fe, cell, "FACT(170.9)", 7.257415615307994E306, 0);
+        assertDouble(fe, cell, "FACT(-0.5)", 1.0, 0);
+        assertError(fe, cell, "FACT(171)", FormulaError.NUM);
+        assertError(fe, cell, "FACT(-1)", FormulaError.NUM);
+        // used to escape as an IllegalArgumentException from MathUtil.safeDoubleToInt
+        assertError(fe, cell, "FACT(1E10)", FormulaError.NUM);
+        assertError(fe, cell, "FACT(1E308)", FormulaError.NUM);
+        assertError(fe, cell, "FACT(-1E10)", FormulaError.NUM);
+    }
+
+    @Test
+    void testROUNDWithHugeDigitCount() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+        // digit counts beyond the int range used to escape as an IllegalArgumentException,
+        // and counts within it but in the hundreds of millions made BigDecimal.setScale run for minutes
+        for (String digits : new String[] {"400", "1000", "100000000", "2147483647", "1E10", "1E308"}) {
+            assertDouble(fe, cell, "ROUND(1.5," + digits + ")", 1.5, 0);
+            assertDouble(fe, cell, "ROUNDUP(1.5," + digits + ")", 1.5, 0);
+            assertDouble(fe, cell, "ROUNDDOWN(1.5," + digits + ")", 1.5, 0);
+            assertDouble(fe, cell, "TRUNC(1.5," + digits + ")", 1.5, 0);
+            assertDouble(fe, cell, "ROUND(1.5,-" + digits + ")", 0.0, 0);
+            assertDouble(fe, cell, "ROUNDDOWN(1.5,-" + digits + ")", 0.0, 0);
+            assertDouble(fe, cell, "TRUNC(1.5,-" + digits + ")", 0.0, 0);
+            assertError(fe, cell, "ROUNDUP(1.5,-" + digits + ")", FormulaError.NUM);
+        }
+        // the extremes of Excel's number range are still rounded correctly (Excel has no subnormals)
+        assertDouble(fe, cell, "ROUND(2.3E-308,400)", 2.3E-308, 0);
+        assertDouble(fe, cell, "ROUND(2.3E-308,307)", 0.0, 0);
+        assertDouble(fe, cell, "ROUND(1.5E308,-307)", 1.5E308, 0);
+        assertDouble(fe, cell, "ROUND(1.5E308,-309)", 0.0, 0);
+    }
 }
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestQuotient.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestQuotient.java
index f742264393..bf48ac8c1f 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestQuotient.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestQuotient.java
@@ -64,6 +64,16 @@ class TestQuotient {
         confirmValue("Integer portion of Pi/Avogadro (0)", "3.14159", "6.02214179E+23", "0");
     }
 
+    @Test
+    void testQuotientOutsideIntRange() {
+        // used to escape as an IllegalArgumentException from MathUtil.safeDoubleToInt
+        confirmValue("Integer portion of 5E9/2", "5000000000", "2", "2500000000");
+        confirmValue("Integer portion of -5E9/2", "-5000000000", "2", "-2500000000");
+        confirmValue("Integer portion of 1E20/3", "1E20", "3", "33333333333333300000");
+        confirmValue("Integer portion of 2^53/1", "9007199254740992", "1", "9007199254740990");
+        confirmValueError("quotient overflows a double", "1E308", "1E-10", ErrorEval.NUM_ERROR);
+    }
+
     @Test
     void testErrors() {
         confirmValueError("numerator is nonnumeric", "ABCD", "", ErrorEval.VALUE_INVALID);


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org