[Bug 58975] New: Function update fails on functions with more than 127 arguments

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 58975] New: Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

            Bug ID: 58975
           Summary: Function update fails on functions with more than 127
                    arguments
           Product: POI
           Version: 3.13-FINAL
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
          Assignee: [hidden email]
          Reporter: [hidden email]

I recently encountered a problem when trying to rename a sheet.

Exception in thread "main" java.lang.NegativeArraySizeException
    at
org.apache.poi.ss.formula.FormulaRenderer.getOperands(FormulaRenderer.java:118)
    at
org.apache.poi.ss.formula.FormulaRenderer.toFormulaString(FormulaRenderer.java:100)
    at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateFormula(XSSFFormulaUtils.java:101)
    at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateSheetName(XSSFFormulaUtils.java:79)
    at
org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(XSSFWorkbook.java:1571)

After some poking around, I found that that the issue is that one of the cells
previously added had 176 arguments in a CONCATENATE function (don't ask...)
which was causing the byte value of _numberOfArgs in
org.apache.poi.ss.formula.ptg.AbstractFunctionPtg to overflow. Excel does not
actually limit the number of arguments in CONCATENATE itself, and the sheet
works fine in POI unless you try to parse the formula. Unfortunately, renaming
a sheet parses all function cells to see if they need to be renamed. A very
similar cell with 108 arguments does not cause this issue.

A workaround for anyone encountering this bug is to simply turn any call to
CONCATENATE(1,2,...,x) to CONCATENATE(1,2,...125,CONCATENATE(125,126,....x)),
repeating this split as many times as required. This causes the function to
parse properly and returns the same result.

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58975] Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--- Comment #1 from [hidden email] ---
Ah, sorry, I meant "CONCATENATE(1,2,...125,CONCATENATE(126,127,....x))" in the
workaround, as written it would duplicate the 125th value.

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58975] Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

--- Comment #2 from Javen O'Neal <[hidden email]> ---
Excel limits functions to 255 arguments [1]

> Calculation specifications and limits
> Feature                   Maximum limit
> Arguments in a function   255

POI XSSF should at least be able to support this many function arguments.

[1]
https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

128 sounds like a signed byte data type is being used when we should either be
using an unsigned byte (unwrap a signed byte) or short.

Do you have time to look through the code to try to find where this might be
happening?

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58975] Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

--- Comment #3 from [hidden email] ---
(In reply to Javen O'Neal from comment #2)

> Excel limits functions to 255 arguments [1]
>
> > Calculation specifications and limits
> > Feature                   Maximum limit
> > Arguments in a function   255
>
> POI XSSF should at least be able to support this many function arguments.
>
> [1]
> https://support.office.com/en-us/article/Excel-specifications-and-limits-
> 1672b34d-7043-467e-8e27-269d656771c3
>
> 128 sounds like a signed byte data type is being used when we should either
> be using an unsigned byte (unwrap a signed byte) or short.
>
> Do you have time to look through the code to try to find where this might be
> happening?

At line 99 of FormulaRenderer, the object relating to the formula is cast as an
OperationPtg. At line 100, getNumberOfOperands() is called on it. Examining the
type hierarchy, getNumberOfOperands() is abstract in OperationPtg, and the only
subclass that implements it that doesn't return a constant is
AbstractFunctionPtg, which returns _numberOfArgs, a private byte. The
constructor sets _numberOfArgs on creation by casting an int argument nParams
as a byte.

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58975] Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #4 from Dominik Stadler <[hidden email]> ---
Can you provide sample code which allows to reproduce this? Ideally as
standalone unit-test together with any required sample-file?

This way we can reproduce it and also ensure that it stays fixed in the future.

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58975] Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

PJ Fanning <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |RESOLVED
         Resolution|---                         |FIXED

--- Comment #5 from PJ Fanning <[hidden email]> ---
Fixed using https://svn.apache.org/viewvc?view=revision&revision=1800949

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58975] Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

--- Comment #6 from PJ Fanning <[hidden email]> ---
added test case using
https://svn.apache.org/viewvc?view=revision&revision=1801307

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58975] Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

--- Comment #7 from Javen O'Neal <[hidden email]> ---
Will be included in POI 3.17 beta 2

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58975] Function update fails on functions with more than 127 arguments

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58975

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #8 from Dominik Stadler <[hidden email]> ---
*** Bug 61385 has been marked as a duplicate of this bug. ***

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]