[Bug 61841] Unnecessary long computation when evaluating VLOOKUP on all column reference

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=61841

--- Comment #11 from Travis Burtrum <ad...@moparisthebest.com> ---
Hello all,

We are upgrading from poi 3.17 to 4.0.1 and formula evaluation was broken, I
used git bisect on poi and tracked it down to this line specifically:

https://github.com/apache/poi/commit/6390202491c3c77a77df3a1b4f2dc205ebc5b307#diff-e3bae629e723fe5bac4ba5d2e85451cbR69

It goes back to working if I comment that line out, or change _lastDefinedRow
to _xs.getLastRowNum(), I changed it to instead in that if block do this to
attempt to track the problem down:

throw new RuntimeException(String.format("XSSFBUG: would shortcircuit wrongly:
rowIndex: %d, _lastDefinedRow: %d%n", rowIndex, _lastDefinedRow));

And the stack trace was:

Caused by: java.lang.RuntimeException: XSSFBUG: would shortcircuit wrongly:
rowIndex: 40, _lastDefinedRow: 39

        at
org.apache.poi.xssf.usermodel.XSSFEvaluationSheet.getCell(XSSFEvaluationSheet.java:72)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:782)
        at
org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:48)
        at
org.apache.poi.ss.formula.SheetRangeEvaluator.getEvalForCell(SheetRangeEvaluator.java:74)
        at
org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
        at
org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:45)
        at
org.apache.poi.ss.formula.eval.AreaEvalBase.getValue(AreaEvalBase.java:128)
        at
org.apache.poi.ss.formula.functions.LookupUtils$ColumnVector.getItem(LookupUtils.java:100)
        at
org.apache.poi.ss.formula.functions.LookupUtils.lookupIndexOfExactValue(LookupUtils.java:504)
        at
org.apache.poi.ss.formula.functions.LookupUtils.lookupIndexOfValue(LookupUtils.java:483)
        at
org.apache.poi.ss.formula.functions.Vlookup.evaluate(Vlookup.java:61)
        at
org.apache.poi.ss.formula.functions.Var3or4ArgFunction.evaluate(Var3or4ArgFunction.java:36)
        at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:144)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:534)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:216)
        at
org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:56)
        at
org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluate(BaseFormulaEvaluator.java:110)

Clearly the number of rows in the spreadsheet is increasing without
_lastDefinedRow being updated (as it's only set once in the constructor, and
only updated on a call to clearAllCachedResultValues() which never happens).  I
have tried and failed to reproduce this with a small standalone example though,
I was hoping maybe someone else can spot the problem.

Thanks much,
Travis

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org